How to start with historization of dimensions!?

SQL Server Analysis Services

SPeters


Hi there, I've got a question regarding the historization of dimension tables. How do I approach it at best Let's say I've got 4 dimensions I want to apply historization to (article, customer, agent, forwarding). What is the starting point in Analysis Services

Things that aren't clear to me:

1. If I use snapshot for historization I think all of the tables need to have 3 additional columns added (at the moment I've just got data-table without time stamps). Let's take an exemplary look at the article table:

Code Snippet

artno artgroup comgroup brand description

4711 Orange Juice Fresh TropicalWonder


So, If I am correct I need to add these rows in order to guarantee a historization:

Code Snippet

artno valid from valid to last refresh
4711 2005-01-01 2005-12-31 2006-01-01
4711 2006-01-01 2099-12-31 2006-01-01


2. If this is correct, my second problem is, how do generate these informations

3. And last but not least, how does a cube draw a distinction between the two values As the fact table is just connected to the dimension via the artno...

Thanks in advance!

Top

Re: How to start with historization of dimensions!?

Thomas Ivarsson


Hello! These are my comments:

1. Correct, but you are missing a technical/surrogate key which is an simple integer. You can create one column in the dimension table like:artkey int indentity(1,1) .

2. You do this in the ETL process, that is in SSIS(Integration services)

3. The fact table will connect to the dimension table with the surrogate key, not the source key. You will find the valid surrogate key by using the time stamps in the dimension table and compare them with the time stamp of the fact record in SSIS. In the cube you will have a problem with fragmentation of the information.

On my blog(http://thomasianalytics.spaces.live.com/) I have some comments regarding pros and cons with slowly changing dimension. Look for "Slowly changing dimensions or avoid that problem" . You can read a good comment by Tim Petersson

HTH

Thomas Ivarsson



Top

Re: How to start with historization of dimensions!?

SPeters

Thanks, great post!!!



Top