Designing 2005 Cube with SCD Type 2 Dimensions.

• dspohn23

  SSC-Addicted

  Points: 453

  More actions

  August 4, 2009 at 10:17 am

  #133693

  I have experience building SSAS 2005 cubes from a Star Schema using unique Surrogate Keys to link the Dimensions to the Fact Table. However I am having trouble with what would seem to be a common occurance.

  I am having trouble figuring out how to develop a Cube - "Data Source View" to link a fact table to Slowly Changing Dimension (SCD) Type 2 Dimensions based on the Natural Keys. For example ColA and ColB are the natural keys for a dimension in the Fact Table. The problem comes in that the ColA and ColB columns are not unique in the Dimension because it is a SCD Type 2. To unqiuely identify a Dimension Entry I also need to have specify a Date, to find the active entry (The Dimension has StartDate and EndDate).

  I can easily account for this in a SQL Query by saying:

  SELECT * FROM

  fact

  inner join dimension

  on fact.ColA = dimension.ColA

  AND fact.ColB = dimension.ColB

  AND fact.EntryDate BETWEEN dimension.StartDate and dimension.EndDate

  However I am unable to figure out how to do such a thing in the GUI for Data Source Views, when creating a Cube. Has anyone experienced this before?

  Thanks

  TSQL Tutorials

• Dirk Wegener

  Ten Centuries

  Points: 1161

  More actions

  August 5, 2009 at 6:48 am

  #1034733

  You should add a surrogate key to your dimension tables. And join your fact to this surrogate key.

  http://www.dwexplorer.com
  @dtwegener