Forum Discussion
Recommended table setup
Sounds like you've got the right idea here. Separate out dimension tables from fact tables. Link them with keys (foreign and primary key). Share the dimension tables with the fact tables. Eg. dim x is linked to fact a and fact b using the same key, using 1:M relationships. Then you can group and slice using the dimension column.
You should avoid M:M relationships between fact tables. Performance will be poor. If you design the model well, you shouldn't need that relationship.
For simplicity, where the granularity of the data is different, you should separate the datasets into separate fact tables. If the summary data is simply a summary of one of the fact tables, you should (if size/performance allows) remove the summary data and just use DAX and/or the filtering on the visuals to group the data.
Spend time getting the model right. This will save you many hours of frustration and grief later on. Talk to the business/end users about what they want. Can you capture that with your model?
Sounds like you've got the right idea here. Separate out dimension tables from fact tables. Link them with keys (foreign and primary key). Share the dimension tables with the fact tables. Eg. dim x is linked to fact a and fact b using the same key, using 1:M relationships. Then you can group and slice using the dimension column.
You should avoid M:M relationships between fact tables. Performance will be poor. If you design the model well, you shouldn't need that relationship.
For simplicity, where the granularity of the data is different, you should separate the datasets into separate fact tables. If the summary data is simply a summary of one of the fact tables, you should (if size/performance allows) remove the summary data and just use DAX and/or the filtering on the visuals to group the data.
Spend time getting the model right. This will save you many hours of frustration and grief later on. Talk to the business/end users about what they want. Can you capture that with your model?
Thank you belvoir.
So, if i understand it correct:
- fact_table1 and fact_table 2 is linked with 1:M relationship.
- dim_tables are linked both to fact_table 1 and fact_table2, this way i dont need to use M:M releationsship,
Correct?
I also have some followup questions.
belvoir99 wrote:For simplicity, where the granularity of the data is different, you should separate the datasets into separate fact tables.
I have a budget table and a table with real data. As of now i have combined(append) this to one fact table (fact_table1). I then have one columnd for actual amount and one columnd for budget amount. Since the budget is on a less detailed level some of the columns would be empty in the budget table. Does that mean that i should have an own fact table for the budget data? As i understand the empty columns does not take up data storage because of the column based database storage, and it would not be a problem to have empty columns, or lower granularity of the budget data, in the same fact table? I guess budget could be in a own fact table but it seems more practically to have it in the same fact table?
Is there any best practice on how to include budget data?
belvoir99 wrote:If the summary data is simply a summary of one of the fact tables, you should (if size/performance allows) remove the summary data and just use DAX and/or the filtering on the visuals to group the data.
This seems smart. I would really like the reports to show the data with all detail. And not to have one report with data from fact_table1 and then one report with data from fact_table2. If there is a 1:M relationsship i guess i could delete/hide the aggregate data in fact_table1 and then append the more detailed data from fact_table2 to fact_table1, to make one datatable with all underlying transactions. Then i would get all transactions in the same "amount column".
Do i understand your comment so that we can do the same but skip the append part, and keep the data in two fact tables, and use relationsships and dax to make the combined "amount" measure? Something like this?:
- keep fact_table 1 and fact_table2
- hide/delete the aggregate transactions (coming from fact_table2) in fact_table1
- make a dax measure to sum amount from both fact_table1 and fact_table2?
- or even better: make a dax measure which sums amounts from fact_table1 only if not coming from fact_table2, and sum amount coming from fact_table2
Am i on the right track here?
And one more question: when using more fact_tables like this. If i want to make a filter based on a primary key. Then the key would exist in both fact_tables. So i cant put the filter here like i ordinary do, then i would need to put the filter twice (one for each fact_table). Istead i should put the filter on the key in the dim_table, which is linked to both fact_tables, correct?
BTW: my dataset is maybe 100.000 rows a year in fact_table1, and and i guess the same in fact_table2. I dont know what is consideres a big dataset but i would guess this would be in the lower size so that size/performance is not a big issue here?