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?
Hi Hanspw
Sorry, I probably wasn't clear enough. You link the fact table (many side) to the dimension table (one side) using a key. In the fact table, the key is called the foreign key. In the dimension table, the key is called the primary key.
If you then link the dimension table to another fact table, then the dimension table is a shared dimension table.
You can then use the dimension column as a slicer (or row or column on a visual) and can then drag and drop columns from both of the fact tables onto the same visual. It's powerful.
The standard model design is called a star model - a fact table with many dimension tables attached. This is the best form for tabular systems such as Power BI.
SQLBI.com have a great introductory DAX course (which is free!) - it's worth working through. There is also an article on budget vs actuals with different granularities (e.g. budget is more summarized than actuals) on their website, but the DAX is more complex to understand.
Keeping the two tables separate is simpler but may have limitations. A definitive answer can't be given without in-depth review. The other simpler way is to use Power Query to summarize the actuals and then join together with the budget data into a single table adding a column called say Type with the values of either "Budget" or "Actuals". That makes visual production much easier.
100,000 rows is a small to medium table. You shouldn't need to consider performance until your tables are 1,000,000 or more rows (many factors determine performance). The biggest issue is no of rows x cardinality of the columns. Basically if your column has lots of different values (e.g. Amount $) that's bad ; if it has few (e.g. Gender: Male, Female), that's really good. Removing high cardinality columns such as ID is usually a good thing to do.
I don't think I can answer all your detailed questions I'm afraid but you are definitely heading in the right direction!
I would start with the 2 fact tables joined to the shared dimension table. Note that, in the filter context, the filters automatically flow from dimension to fact, so you just drag and drop from the tables onto the visuals.
A really good beginners book on DAX and modelling is 'Power Pivot & Power BI' by Rob Collie and Avi Singh. Really old now but clear and simple, and still relevant for Power BI, even though it was written in 2016 when Power BI had just come out! I still refer to it at times.
If you can give me a 'like' for my posts, that would be great - thanks!
Thank you belvoir.
As for budget vs actual i guess my current method is a OK way to do it. I have done almost what you suggest "summarize the actuals and then join together with the budget data into a single table adding a column called say Type with the values of either "Budget" or "Actuals".",
Only that i have not used summarized data for actuals, i hav just put them together. I dont have a problem with the budget data being more aggregated than actuals (if it doesnt affect model performance).
For the two different fact tables, as i understand it could be an solution to append them as well to a combined data table. That would make the star schema easier. I will try both solutions later on and look how they work.
Thanks again for the good info, il look into the dax courses!
