Rebuilding dimension tables

[BKhanna]

From time to time the structure of some of the dimensions in my cube changes. The dimension tables are created from text files and I want to automate this process with DTS so that whenever the text file changes, the table is reloaded. The problem I have, is that if I truncate a dimension table in order to reload it, I will lose all data in the fact table since they are joined. Is there any other way to update the dimension tables?

Thanks!

BK

 

[johnherman]

You can do an incremental update on dimension tables.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[BKhanna]

What if some dimension members are going to be deleted, and others moved around? Also, isn't 'Incremental Update' an Analysis services command rather than for updating a SQL server table? I'm thinking I first need to rebuild my SQL server table from the text file before I rebuild the dimension, and the only options in rebuilding a table in DTS are:
Delete rows in destination table; Drop and recreate destination table; Append rows to destination table. If I choose append rows, it would not remove the rows with members that have been deleted from the dimension. And it would duplicate some rows if they are already in the table, thus giving me errors. Thanks.

 

[johnherman]

Generally speaking, in the DW/OLAP world, we do not delete dimension members since there is or could be history associated with that code and description. Instead, we create a column called "Inactive" or "Deleted" and update that column with a 'Y' when there is a delete.

You are correct to rebuild your SQL Server table before rebuilding the dimension. Your DTS should, instead of deleting, update the flag indicating inactive (sometimes referred to as logical delete) instead of physical delete.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[BKhanna]

You're right, I would lose the history if I delete members with data in them.

I am still confused about how I would automate updating a SQL server table from a flat file though. For example, my table looks like this to begin with (it's a parent child dimension):

Parent Child
A A1
A A2
B B1
B B2
C C1

and my new text file looks like this:

Parent Child
A A1
A B1
A B2
C C1
C A2

If I read in this file using DTS, I can either append rows or drop and rebuild the table, or delete existing rows before importing the text file into the table. I cannot delete everthing and read the new text file in because the table is joined to the fact table. If I choose the 'append rows' option, it will create duplicate 'Child' members which will give me errors. So how can the above change be automated in SQL Server? Thanks for your help!

 

[johnherman]

I don't understand the problem, mostly because I don't understand the data structure. Can you show the actual tables or at least the natural keys (and surrogate keys if you have them) for parent and child?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[BKhanna]

The Location dimension table has two columns, location_parent and location. The child column is the primary key which is what is in the fact table as a foreign key. The Location hierarchy in Analysis Services looks like this (just an example):


A
A1
A2
B
B1
B2
C
C1

and the table looks like this:

Location_Parent Location (PK)
A A1
A A2
B B1
B B2
C C1

I would like to change the hierarchy to look like this

A
A1
B1
B2
C
A2
C1

So the original table would have to be changed to:

Location_Parent Location
A A1
A B1
A B2
C C1
C A2

The fact table looks like this:

Period (FK) Location (FK) Amount

Jan_01 A1 100
Jan_01 A2 50
Jan_01 B1 25

....etc.

The question I have is, how can I make changes to the original dimension table since the locations have moved under different parents. I would have to drop all rows and then reload the table from the text file with the new parent child definitions, but I can't do that when the table is joined to my fact table due to integrity issues.

I don't know if I explained this properly. Let me know if I didn't. Thanks again!

 

[johnherman]

I think you need to set up a Slowly Changing Dimension SCD) for the Location. The location, it seems, wanders around from one parent to another across the passage of time. I am not sure whether DTS or Analysis Services is set to handle SCD's directly or if you will need to code them yourself. The SCD will have an effective date range. That is, from 1-Jan-05 to 14-Apr-05, location B1 was assigned to B.
Next row, 15-Apr-05 to (either null or 12-31-9999), location B1 is assigned to A.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[BKhanna]

I was reading up on slowly changing dimensions too. Currently I use Essbase, and when the locations change from one parent to another, I am not required to keep track of the dates the change took place. I just restate the history to show up under the new parent. Wouldn't I get duplicate key errors if I have two rows with the same key member in them like below:

Loc_Parent Location EffectiveDate
B B1 1/1/05 - 2/15/05
A B1 2/16/05 - 6/10/05

 

[johnherman]

No, the Surrogate Key will provide uniqueness. The SK is usually assigned at the DW level, Essbase in this case will apparently handle the SCD and the SK for you.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw