Fact table to track history on 4 dimensions?

View previous topic View next topic Go down

Fact table to track history on 4 dimensions?

Post  Demitri on Fri Feb 03, 2012 4:40 pm

Hello, gurus. A question to all of you:

I'm working with a banking-related data warehouse, and there's a requirement asking for the FULL HISTORY of some information related to each customer: I need to track which is his primary account, his market segment and the name of his manager. Basically, I need a table that will have one row per customer, and should his primary account, segment or manager change, I would need to version the old data and create a new row.

The problem is that I don't know any "traditional design solution" to this. The concepts involved with the solution that would be the closest to what I need to fullfill such requirement could be called a "SCD-2 factless fact", since it would have effective start and end dates, and would not represent a fact. My question, then: would a fact table like this look bizarre in a multidimensional model?

SK_CUSTOMER (SK to customer dimension)
SK_ACCOUNT (SK to account dimension)
SK_SEGMENT (SK to market segment dimension)
SK_MANAGER (SK to manager dimension)
EFFECTIVE_FROM_DT
EFFECTIVE_TO_DT

If this is not the best structure to handle this situation, can you suggest something else?
However, if it's OK to have such table, I need to ask something else: can you indicate me any documentation (book, white paper, business case, etc) where this implementation is mentioned as valid? Our Data Administrators are very harsh when validating the data models, especially when they come from a contractor, who's proposing an unusual structure...

Thanks in advance,
Robson

Demitri

Posts : 9
Join date : 2010-07-27

View user profile

Back to top Go down

Re: Fact table to track history on 4 dimensions?

Post  hang on Fri Feb 03, 2012 6:05 pm

What you have is close to Kimball's transaction dimension on page 188 in the Dimension Modeling Tookit, 2nd edition. You should have a dedicated SK for the dimension so that you may use it as a dimension entry in other fact tables.

In a nutshell, it's mixture of facts and dimensions to handle the dynamic relationship changes for a large dimension. It's similar to fact table as it does not rely on a single NK for SCD changes, and is moderately normalised to minimise the data redundancy. However technically, it is a dimension that may have some direct attribute values, SCD dates and a SK to be referenced directly by other fact tables.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Fact table to track history on 4 dimensions?

Post  Demitri on Fri Feb 03, 2012 7:37 pm

Thanks, hang. Just to confirm: should the structure you mentioned be created as a dimension that receives the SKs from the four dimensions I mentioned, or should I create a new table with a single SK plus the the relevant fields from the four original dimensions? I believe you're talking about the 1st option, so the only adjustment I'd need in my original design would be creating a new surrogate key; is that correct?

Demitri

Posts : 9
Join date : 2010-07-27

View user profile

Back to top Go down

Re: Fact table to track history on 4 dimensions?

Post  hang on Fri Feb 03, 2012 11:17 pm

Correct! 1st option, besides its own SK, also put SKs from other dimensions as well as some isolated attributes like dates or flags in the transaction dimension. Base on Kimball methodology, it's valid snowflaking unique to transaction dimension.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Fact table to track history on 4 dimensions?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum