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 :).
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..
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!
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.