0

I have 32 dimensions for three fact tables, and i'm currently working on SQL Server 2012 !

As SQL Server's foreign keys are limited to 16 per index, does it mean that i have to use a surrogate key to index my data in the fact tables ?! Or is there another solution for this issue ?

As my ETL is executed every month, should i check before every datapumping that the datas(foreign keys concatenation) i'm inserting don't already exist ?!

If i decided to work with a surrogate key and as i'm using Mondrian cubes(pentaho), is the workig way(creating the schema) the same or is it different than the normal way ?

Thank you.

YOo Slim
  • 54
  • 1
  • 8
  • 2
    A few questions... Is there no way to tell from your source data which rows are new or updated, maybe some created/updated dates? Can your business keys change where a row needs to be updated because the source data has been updated to use different values? My initial thoughts are that you have 3 options (one may be much better than the others). 1)Truncate and reload your fact table each time. 2)Create a hashkey for existing fact data and your source query that is a concatenation of the values in all relevant fields and use that to identify new rows/changes. 3) Write a merge statement – mmarie Sep 01 '13 at 21:30
  • Can you convert any of your dimensions into a degenerate dimension? – Olaf Sep 04 '13 at 20:55

0 Answers0