SSAS and Type 2 SCD Data

1
votes

I am having trouble getting an expected result with SSAS and Type 2 SCD data. Below, I have listed the simple tables I am using, the output from SSAS that I am getting and the output from SSAS that I am hoping for. I feel SSAS should be able to retrieve the data like how I would want; I believe I am just having difficulty "hooking it up" correctly :).

Tables I am Using

DimClient
ID (PK)     AltID (Business Key)     Name        Start Date     End Date
1           1                        Client A    01/01/1995     01/31/1995
2           1                        Client ABC  02/01/1995     NULL

FactSales
ID (PK)     ClientID    Sales     SalesDate
1           1           $100      01/15/1995
2           1           $200      02/15/1995
3           1           $300      03/15/1995

Plus a DimDate table that has every date from 01/01/1900 -> 12/31/2050 entered as the PK, plus their various attributes like Day of Month, Day of Week, etc.. etc..

Outputs (Current vs Expected)

I am attempting to view client data by month, and I get this:

Month       Client      Sales
January     Client A    $100
February    Client A    $200
March       Client A    $300

When I am expecting (and want) to see this: 

Month       Client      Sales
January     Client A    $100
February    Client ABC  $200
March       Client ABC  $300

How come my SSAS cube does not recognize that Client A was changed to Client ABC for the Months of February and March?

To hopefully provide some insight to how my cube is current hooked up:

-FactSales ClientID is linked to DimClient AltID
-FactSales SalesDate is linked to my DimDate PK field

I have been unable to link DimClient to DimDate by any means.

Thank you for the input and help with my problem!

1
ssasscd

1 Answers

1
votes

You need to use another keys for linking FactSales and DimClient.

It's ID of DimClient and new key described below.

Than add another key to FactSales (let's say ClientSCD) and map it on ETL stage like this:

update f set f.ClientSCD = isnull(c.ID,0)
/* if you have default NONE member with ID = 0 */
FactSales f
left join DimClient c
on f.ClientID = c.AltID
and f.SalesDate between c.[Start Date] and isnull(c.[End Date],'12/31/9999')

Use DimClient.ID and FactSales.ClientSCD in your cube as a link.