Loading fact table and dimension indexing.
Page 1 of 1
Loading fact table and dimension indexing.
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.
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
Similar topics
» Loading Fact Table with Type 2 Slowly Changing Dimension
» Dimension Table Indexing Strategy
» Loading a Fact Table with SCD2
» Fact Table Indexing Strategy
» Dimension Table Indexing Strategy
» Loading a Fact Table with SCD2
» Fact Table Indexing Strategy
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum