Slowly Changing Dimension Question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slowly Changing Dimension Question

How would I account for changes in cost of a product over time? I need to be able to generate correct historical gross margin numbers. I would think it would be something like a type 2 SCD but how do I tie the cost to the correct fact transaction. I have a standard time dimension. r
I think it is hard to findout releated information from Books online on Analysis services, may check the below links:
http://msdn2.microsoft.com/en-us/library/ms141715
http://msdn2.microsoft.com/en-us/library/ms189681
http://www.windowsitpro.com/SQLServer/Article/ArticleID/7835/7835.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21079980.html HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
How would I account for changes in cost of a product over time? I need to be able to generate correct historical gross margin numbers. I would think it would be something like a type 2 SCD but how do I tie the cost to the correct fact transaction. I have a standard time dimension. Yes, you are on the right track to consider the use of a Type 2 SCD. From reading your post it is not mentioned if you have a seperate Product Dimension table in your model, is this true? Type 2 SCD as you may know creates a new row in your dimension table for history tracking purposes, old facts are then joined to old dimension members and new facts are joined to current dimension members. In your Type 2 SCD there are basic attributes you may want to add, these attributes are: Start_Date (Date row was added)
End_Date (Date row was expired)
Status (Current/Expired)
Orig_Key (The original dimension key value for this member) You then join your fact table to the version of the dimension record that was valid at the time of the transaction. For more information regarding dimensional data modeling look no further http://www.ralphkimball.com/ Raulie

]]>