Interesting. I've never used the Type=Scd* options. What are the advantages of doing that? Do any client tools you know of render this information differently if you mark dimension attributes as you specified?

Is the cube browser provided with management studio and BIDS able to present the data correctly with SCD type 2 and these meta data attributes set?

Hi Ella,

When you say that the cube browser in BIDS shows the data correctly, it means that it will differentiate which dimension record to show depending on the time period being looked at? Is that correct? Furthermore what happens if the time span you are analysing spans two or more possible valid dimension records?

Has anyone tried analysing SCD dimensions via Excel 2007? Do you know if the SCD support is there as well?

Great BLOG btw. :-) Much appreciated

Peter

Slowly Changing Dimension Type 2 Tips & Tricks I've blogged in the past about Slowly Changing

Hi,

I can't get your example working. On SQL, I created a table workers are described in the top. I created a table called salaries. This has the fields ID, Date, Worker_ID, Salary

In SSAS, I imported those two tables into the DSV. Linked them together. Set the primary keys to salaries.ID and workers.Surrogate_key. Made a link from salaries.worker_ID to workers.workers_ID.

Added a server time dimension. Also linked that one to Salaries (not in the DSV but in the cube). Set the types of the worker as specified above.

When I run the cube, I only get Active back not Sick.

What goes wrong ?

Constantijn

Have many rows do we have in Fact table for this employee? The dimension is changing, not the employee facts, so I would think we have a single entry for this employee.

Then which dimension surrogate do we point to? Do we keep this up to date with the active status?

Hi, I hope somebody yet read this comments. I have a problem which needs to keep histories on a Type II SCD which also contains a parent-child relationship. I'm really confused how I can do it.

Iedere BI ontwikkelaar zou het Kimball concept van een slowly changing dimension in een dimensionaal

Hi Ella,

I’ve read this article with much interest! I am in the process of designing an SSAS cube and dimensions for employee data that has to keep historical changes, therefore I created it with the Type 2 SCDs. What I am struggling with, is how I would achieve the design in such a way, that I can answer questions like how many employees there were in the company at a random given date, or how many employees started or left the company in a random given month.

From your reply to Scott’s question, I am not really sure what to put in the dimension table and what to put in the fact table. For example all the changes of employee location, department, job title etc, I would put that in the dimension table.  Do you know of any tutorial that describes this, or can you give me some tips?

Thanks, Erik

Hi Ella,

Thanks for a such an invovative post about untouched topic.

But I am still not able to make it understand & use at full. I really don't see any difference in terms of data available while browsing/querying the cube whether I configure the attributes of my Dim (SCD2) like by assigning their types to scd* or not.

Please put some more light on that, probably by giving an example with & without such property settings.

Also pls mention other properties of dim-attributes like keycolumns etc for SCD2 dimension to be set for their proper use.

Thanks again,

Munish Bansal

Thanks Ella.

Hi Ella,

I am setting up a dimension in SSAS from an employee table that is populated according to the SCD Type 2 structure. This table contains information about which division this employee is in, who the manager is, etc. Most of the fields are of changing type SCD2. These are the steps that I take in BIDS:

- Create dimension from dimension table that is populated according to the SCD Type 2 structure

- Set key usage to the surrogate key field

- Build some hierarchies

- Deploy dimension

- Process dimension

It goes wrong in the processing part. It says it has found a duplicate attribute key. I understand why, because when an employee has gone through a lot of changes, there is a record in the dimension table for every change. When I change the key collection for the business key to the business key AND the surrogate key, then I am able to process the dimension. In the dimension browser though, I see a lot of doubles on lower levels in the hierarchy...

In other words, what would be the best way to build up such a dimension in SSAS?

Thanks!

Erik

Hi Ella,

thanks for your reply. The error message that I got during processing is this one:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: '<< TABLE NAME >>', Column: '<< BUSINESS KEY >>', Value: '258'. The attribute is 'Resource'. Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate.

So I looked at the data for the resource with business key 258, and found out that there are hardly any differences in the data. The only thing I noticed, is that the column ManagerKey is different every time. This ManagerKey is a foreign key relationship to the same table to the surrogate key of the manager. Maybe that's where it goes wrong. This means anyway that if the manager gets a new record in the database because of changes, the employees do too, because the surrogate key of the manager has been updated. Would it be an idea to use the business key of the manager for the ManagerKey column?

Thanks!

Hi,

I want to design employee dimension(SCD 2) like this.

Key EmpId EmpName Status FromDate  ToDate    Flag

1   2345  John  A 1/1/2009  6/1/2009  0

2   2345  John  S 6/2/2009  12/1/2009 0

3   2345  John  D 12/2/2009 3/1/2010  0

4   2345  John  A 4/1/2010  1/1/2099  1

When the user select the data range, lets say 1/1/2009 to 1/1/2010, we need to show employee status as D only. How can we do it ?

Hi Ella.

I have the same problem with Erik. I’ll try to explain reasons.

We have

ScdProductId AlternateProductId SubCategory Category

1 50 bread food

2 100 milk food

And we have attribute relationship:

ScdProductId->AlternateProductId->SubCategory->

Category

AlternateProductId many to one with SubCategory

And we have one new row ScdProductId = 3  

ScdProductId AlternateProductId SubCategory Category

1 50 bread food

2 100 milk food

3 100 drink food

AlternateProductId is the same = 100 but SubCategory has changed.

After processing we get Error

“A duplicate attribute key has been found when processing: Column: 'AlternateProductId , Value: 100”.

AlternateProductId have two different parents milk and drink and it is wrong

because of attribute relationship AlternateProductId -> SubCategory. (many to one ).

I see than in Product Dimension in the Adventure Works we haven’t AlternateProductId at all.

And we don’t get such Errors. But we also haven’t possibility to group sales by AlternateProductId(this is requirement of our project)

What is the best way of modeling (Especially

attribute relationship and hierarchy)

in this case ?.

Ella,

I think what Vladimir means is that the product with the business key 100 changes from the subcategory "milk" to the subcategory "drink" (same example that I have when an employee changes departments, managers, locations, etc.). In his case, I'd think he wants to show the dimension like this when he browses:

Food

- Bread

 - 50

   - 1

- Milk

 - 100

   - 2

- Drink

 - 100

   - 3

In other words, the product with business key 100 should appear under milk, but also under drink.

Hi Ella,

in addition to my post from yesterday, I have added this situation a bit more extensively on my blog: www.erikmonchen.com

Thanks.

Ella,

Thank you for the post explaning the type 2 SCD in SSAS. I have one question. After we define the type attribute in dimension, how to link the dimension with the time. Will this be on SCD dates (start or end).

Hi Ella,

I hope you are the right person to ask.  Having read your excellent thread above you seem to be an expert so I am hoping you can help me!

I have taken on a BI project using SSAS 2008 and I am struggling to see how best to approach something.  

I have a complex multihierarchy dimension for clients here at the bank.  I have modelled the hierarchy in the warehouse with a single static dimension table called tDimClient and then I have snowflaked out the various hierarchies into seperate single tables.  Some also have SCD type 2 attributes!  E.g.

tDimClient.Client_Id links to the fact tables and has name attribute Client_Name

tDimClient.Client_Id -> links to -> tDimRelationship.Client_Id

tDimRelationship has additional key field Valid_From_date and attribute Relationship_Name

tDimClient.Client_Id -> links to ->  tDimGeography.Client_Id

tDimGeography has additional key field Valid_From_date and attributes City, Country, Region

tDimClient.Client_Id -> links to -> tDimAccountManager.Client_Id

tDimAccountManager has additional key field Valid_From_date and attributes Team, Department, Company

What I am trying to do is build a single Client dimension with all 4 tables and then have multiple hierarchies defined, all hanging off the lowest level tDimClient.Client_id.

My qeustion is, is this too ambitious?!  I have difficulty defining the dimension with just two of the tables (tDimClient -> tDimRelationship) and I get an error saying "the tDimClientRelationship table that is required for a join cannot be reached on the relationships in the data source view".  I assume this is because either SSAS or I am having problems understanding the implied Many-to-many relationship between a bottom level dimenstion table and a snowflaked SCD (even though it won't be a many to many once the Valid To constraint is applied - I.e. a client will only ever have one relationship at a given time, but potentially multiple over time).

So I guess in summary there are two questions.

1. Am I asking too much of SSAS in trying to build a single dimension with multiple snowflaked hierarchies with SCDs on the outrigged tables.

2. If not - Do you know what the problem could be with the error above?

Many thanks for any advice you can give me!