February 18, 2016 at 5:56 am
I am designing a datawarehouse in SQL server and I want to see if anyone has ant thoughts on a basic design question.
In the datawarehouse (as in the OLTP database) for every project, every budget line has commitments. Every commitment can have change orders and payments.
I have two options with my budget data mart: a single fact table in a star schema where all these things are joined together creating one line for each payment and change order, or four separate fact tables in a snowflake schema (one for budgets, one for commitments and one for payments and one for change orders).
Per month, we will be adding about 500 projects X 35 budget lines (avg) x 10 commitments (avg) x 40 payments (avg) x 15 changes (avg) = 105,000,000 lines if I combine all the tables into 1. The projected life of the datawarehouse is probably around 6-8 years.
so 6 to 8 years X 12 month x 105000000 lines = 7560,000,000 rows to 10,080,000,000 rows.
Do I do 4 separate fact tables (with dimension tables) that don't get added nearly as many lines, or one fact table with a few dimension tables in a star schema that gets the 105,000,000 lines on avg every month?
I am planning on having a dedicated sql analysis server to build and query the cube.
February 22, 2016 at 7:51 pm
I'd do 4 separate fact tables as a start. That'll give you a little more flexibility in designing partitions, etc. and you could always combine them at a later stage.
The conformed dimensions will bind these together in any case, and creating calculated measures should not be an issue.
February 24, 2016 at 8:31 am
I'd also do four separate fact tables as well, because you have four different business objects. I like to keep each fact table to only one business entity because it keeps the grain of the table cleaner. Doing it all as one fact table would seem to be a nightmare to manage to me. Also with one fact table counting the number of entities for a given set of dimensions would be rough, and count is probably the most common measure of all.
February 26, 2016 at 5:53 am
Thank you guys for your input.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply