Datawarehouse budget datamart design

• fantom

  SSC-Addicted

  Points: 467

  More actions

  February 15, 2016 at 1:20 pm

  #311434

  I am designing a datawarehouse in SQL server and I want to see if anyone has a definitive answer 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.

  thanks.