Forum Discussion
Same Dimensions, multiple Fact tables, different measures, millions of rows
I think you've got exactly the right idea! Give it a go. Every data model is different but for example a normal pattern I use in education has a number of fact tables will shared dimensions:
We often use measures that require calculations from more than one table!
The diagram you provide with the two fact tables and 3 dimensions is exactly the way to go. Certainly try and avoid snowflakes.
What are you worried about not working in that setup?
Well my main concen is having just some theoritical knowledge ( enphasis on some) without practical experience with such volumes. And this is the first time I'm dealing with combining fact tables into one ( right now I'm trying to fixe someone else's design and would like to avoid making things worse than before) .
Aside from doing it for the 1st time and the amount of data, the main concern is wheter calculated columns based on measures from both fact tables will work correctly ( I guess this is why the person before me went for joining the two tables)
For example -
Calculate (Sum( Fact Delivery[Measure 1] - Sum ( Fact Invoice[Measure 17])
KEEPFILTERS('DimDate'[Period]=5))
I think you've got exactly the right idea! Give it a go. Every data model is different but for example a normal pattern I use in education has a number of fact tables will shared dimensions:
We often use measures that require calculations from more than one table!
Hello Ben,
Thank you for your help ! Yours and Adamboer's confirmations are what I was hoping for.Hopefully it will work 🙂
Best regards,
Pavel