Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Loading fact table and dimension indexing.

Go down

Loading fact table and dimension indexing. Empty Loading fact table and dimension indexing.

Post  platforminc 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

Back to top Go down

Back to top

- Similar topics

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