Missing Data in a Dimension ?

[nchapman]

Am working on a Banking Transactional Data Mart.

We have come across a potential issue with our Data Marts, and would like opinions on this.

Within my transactional mart, we have a instances where some of the facts will have an associated record in one of the dimensions, but other facts will not. To be a little more specific.

A transaction fact, can/may have a foreign key link to the Account Segmentation Dimension. (This dimension details what segment the account is in, ie. high balance/high earner). The problem is that some of the accounts have not been segmented yet.

As far as I can see there are 3 solutions.

1. Have a dummy record within the dimension, which represents "No segmentation". Any facts which are for accounts that have not been segmented, point to this record.

2. For each account that has not been segmented, create a record in the Segmentation dimension, but with values denoting that it has not been segmented. (also maybe have an indicator on the record to signify it is not a complete record yet).

3. Do not have any transactions for unsegmented accounts in the data mart. If this method is used though, we then have the question of what to do once the account has been segmented.

Would appreciate any thoughts.

 

[kishleo]

well, can you give me the structure of your dimenstion and fact table???
for this situation i think your first solution is better one
have a dummy record with -1 values or "unknown" values then point your fact table record to this record. once it is segmented you can insert a new record in your dimension tale, and reload your fact table, which solves the problem ô¿ô Kishore

 

[netDev007]

Surely you just need to outer join the fact to the dimension table. Then any NULL in the fact table won't find a dimension row and any missing dimension row will have NULL values in the dimension columns. Having said that it can be better to use isNULL(<dimension_id>,&quot;Not Found&quot and have a &quot;Not Found&quot; row in the dimension table. It depends if you users (of the raw tables) appreciate outer joins or not.

 

[Subhashab]

Keep off outer joins from DW dimensional models. you will have response problems. because Oracle might not perform star optimization or skip some indexes