SQL Server Performance

Slowly Changing Dimension Question

Discussion in 'Analysis Services/Data Warehousing' started by racerx, Oct 21, 2005.

  1. racerx New Member

    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
  2. satya Moderator

    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.
  3. Raulie New Member

    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


Share This Page