Your opinion on star schema dimension

[dean12]

So is it the case that if you are going to lookup something in a dimension table, then there must be a match between the fact table and the dimension table else rows will not be included. That is to say, a row in the fact table with code "XXX" will only be included in the cube aggregations if there is a corresponding code "XXX" in the dimension table. Is is implying referrential integrity I guess.

If this is not going to be the case with the source data (namely the fact table), then what do you do? Forget the dimension table and just use the value from the fact table as a dimension OR go through some sort of data transformation?

 

[MDXer]

Some dimensions are meant to be changing and grow. For example a time dimension is meant to increase with the obviously time. It seems that some procedure should be in place to check new fact data and see if it has corresponding members in the dimensions. This process should contain a method for determing where in the dimensions hierarchy the new item should exist. Then incremental updates of the dimensions that have new members would be done.

This does not address what to do with dimension members that change parentage which is a thread in it's self.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dean12]

Not sure I follow.

I want to link a dimension table to the fact table because the dimension table has nice english words to explain the cryptic code values stored in the fact table. Problem appears to be that unless you guarrantee that every code in the fact table appears in the dimension table, then those rows don't appear in the cube - at least in the aggregations, I haven't check the drill through.

So whats the best way to address this situation if in fact it is permitted that the code not appear in the dimension table?

 

[MDXer]

for fact rows to be brought into the cube they must be represented in the dimension. typically Analysis Services will throw errors if there is data in the fact that doesn't exist in the dimension.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[samonsey]

Typically, this situation should be handled when you load the fact table. You could create a dummy record in the dimension table like "No Code Description". When building the fact table, you do a check to see if the code exists in the dimension table. If it does not, you would insert the key for the dummy row in the dimension table. This assumes you are using surrogate keys in the dimension and fact table. If you are not using surrogate keys, you need to go about creating a record in the dimension table for each code in the fact table with a dummy description for each.

Hope this helps,

Asa Monsey
samonsey@houston.rr.com
Maxim Group Consultant
Visual Basic / SQL Server

 

[Patlin]

I agree with samonsey. You should fix this in the staging area when you load the dimension and fact tables.
But Cognos product Transformer wich builds Cognos cubes
fixes this fore you. But that is not always a good thing.
Because then you will build cubes that actually contains data that is not clean.

Patrik