Loading fact table and dimension indexing.

View previous topic View next topic Go down

Loading fact table and dimension indexing.

Post  platforminc on Wed Dec 19, 2012 5:39 am

Hi All,
This query concerns a SQL 2008 system, dimension and fact tables already designed and modelled. We are currently testing in operational mode at the moment. And the system is such that there are various source systems that feed into the fact table, these source systems donít have any form of unique identifiers, data isnít only loaded into the system, its sometimes updated and deleted. In order to join back to the staging table to work out what to insert or update, a combination of the dimension foreign key columns are used, about 10 dimensions in total. This has led to a question about design, should all those columns be individually indexed or indexed as a group ?

Secondly, I am thinking of generating a harsh column based on the combination of the dimension foreign keys and also some other values, technically this harsh value will always be unique, and I was thinking of using that to load the data in, as once data is in the staging table, those columns will be hashed in the staging table, and if the columns in the fact table are also used to generate a hash column as well, it means that where this matches with the staging table, then update, where it doesnít match then an insert will be required.
Please see this article:

http://www.sqlservercentral.com/articles/Data+Warehousing/69679/

I want to know if anyone else is adopting this strategy as well.
Thanks in advance.

platforminc

Posts : 7
Join date : 2012-05-25

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum