Hello Experts,
I am looking at implementing SCD Type 2 in analysis server. I have
handled it in SQL Server Dimension tables by adding extra records but
bit confiued how it can be delivered through the cube.

Option 1: Under Dimension Table, I can add "Effective Year" and
"Effective Month" for example "Customer" Dimension. The lowest level
customers moves into different parent level yearly and monthly. So I am



interested to see "What is my sales figures for this year vs last
year....".


Now this "Effective Month" can join back to fact table with
transactional time becase in the fact table, I have month and year for
a
particular sale for that customer. Which also means, I am having 2
joins
for customer table back to fact table in the star schema. One is
Customer Surrogate Key
back to Fact Surrogate key for Customer. In this instance "WK_Cust" and



then "Effective Month" of Customer table back to "Month" in fact table.



The cube then handles current data and historical snapshot by just
using
its Time dimension. If I plug Excel Pivot table against the cube and
take Customer on Row and Time on colums, it does shows correct numbers.



Now, I want to know whether this is a best practice before I sell this
to my customer. Can you please please reply me with some suggestions?


Thanks for any help.
Regards


Soumyadip
Melbourne

Hi Soumyadip,


Does this mean that the Customer dimension has Year and Month as the top
levels - and, if so, why wouldn't a conventional SCD Type 2
implementation work (using just surrogate key joins and a separate time
dimension)?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,
Thanks for your reply.
Actually Effective Year and Effective Month are just recorded in the
customer dimension but they are not included in the dimension
hierarchy. Then this Effective Month get joined with the month back in
fact table so that I can just use my Time dimension to filter through
"What was my sales figures last year or before compared to this year".

I have got it working but I am just bit concerned that I have two joins
for Customer Dimension back to fact table, which is Dimension surrogate
key and Effective Month. I am not sure whether this is a best practice.
Can you please provide me some guidance on this Deepak?
Cheers.
Soumyadip

Soumyadip,

I still didn't quite get it - isn't the Customer surrogate key unique
(i.e only 1 row in the table has a given key)? In that case, why would a
second join condition (based on month) be needed - maybe you could
provide some examples?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

a SCD dimension represent a customer for a point in time.
So there is no need to add any time information around this dimension.

simply use your default time dimension to display historical sales.
so if a customer has bought a product last year and this year, and this
customer has changed (like his location has changed from NY to Boston
between these 2 sales)
then you'll see a sales for NY in 2004 and in Boston in 2005, but its the
same customer.

You don't have to join anything else except your surrogate key.
if you don't have a surrogate key in your customer dimension, then its not a
good SCD and you have to creatre this surrogate key.

"Dip" <soumyadip.bhattacharya (AT) gmail (DOT) com> wrote

Thanks Deepak/Jeje,
I think you both right. May be I have overcomplecated the situation.
All I need to do is to be able to capture historical sales when a
customer, say for example "Hard Warehouse" moves from its parent
"Retail" to "Commercial" during current financial year. I am having
surrogate keys by using identities in SQL Server.
I will do a test tomorrow and provide some feedback to this post.
Thanks Deepak and Jeje again.
Cheers.
Dip