Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Tricky Model

5 posters

Go down

Tricky Model Empty Tricky Model

Post  CityModeler Wed Jun 09, 2010 1:30 am

Hi, I am pretty new to data warehousing but have been doing a ton of research and reading of Kimball's books and crawling the web. We are trying to design a system which show movement in and out of accounts, and the business requirements is to roll up the data into a classification system. The complication is that: an Account sometimes has a classification, and a product always has a classification. If the Account has a classification, this is the classification that becomes associated with all transactions on that account, regardless of the product classification. Otherwise, the product classification is the classification for the transaction. Users want the ability to come in from both the Account and Product dimension by the classification and see the totals in different ways. We also need to historically track the changes to these attributes at both an account and a product level. The classification itself is a dimension as has many attributes and information with which users want to query.

so, a simplified version of our model:

Account_ID [PK]
[ etc, other attributes]

Product_ID [PK]
other attributes for Product

Other attributes for this classification

Account_ID [FK]
Product_ID [FK]
Classification_ID [FK]
(we also have a date dimension, but I am keeping what is relevant)

Some scenarios that need to be handled:
1. There can be changes to the Classification dimension (changed rollup)
2. An account can be assigned a classification when there previously was none, so now the transactions take on this classification value - we would have to alter the fact to the new classification_ID. this loses the history.
3. A product can be assigned a new classification : so the transaction may or may not take on the classification value.
For 2 & 3 : How can I maintain the historical classification view with history of what the transaction was previously assigned to.

Is there a way to model all this without altering the original fact when there is essentially an attribute change within the Account / product dimension?

Hopefully i'm not too wordy and I've explained the question.... Any suggestions would be really appreciated!


Posts : 1
Join date : 2010-06-09

Back to top Go down

Tricky Model Empty Re: Tricky Model

Post  BoxesAndLines Wed Jun 09, 2010 9:39 am

Build a daily snapshot table. This will store the history day over day.

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Tricky Model Empty Re: Tricky Model

Post  ngalemmo Wed Jun 09, 2010 12:12 pm

Somthing to consider...

Since the classification is a function of account and product, why not make account and product (or some derivation of those) the natural key for the classification dimension? This should provide a stable foreign key for the facts, and something that can be easily updated rather than changing fact keys.

From the looks of your sample, you are involved in securities trading. So it is safe to assume there are a lot of accounts and a lot of products. So, this could be a very large table, but then again, an average portfolio probably doesn't contain a lot of products, so while large, it may not grow beyond 10x the number of accounts.

Another alternative would be to define a view (or meta-layer object) that selects either the account classification or the product classification at query time. This is a very simple column expression and, depending on your BI tool, could be easy to implement.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

Back to top Go down

Tricky Model Empty Re: Tricky Model

Post  LAndrews Wed Jun 09, 2010 1:18 pm

Some additional thoughts ....

- the fact should retain the classification at the time of the transaction. Your ETL process will determine which classification (Account or product) applies to the transaction. This is the historical truth of the transaction.

- the dimensions should treat the classification values as type-2 attributes, again this will retain history.

- In order to avoid updating fact records, you could add the type 3 field "Current Classification Value" to both the product and account dimensions. This will allow you to see all transactions based on the current classifications.


Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

Back to top Go down

Tricky Model Empty Re: Tricky Model

Post  sgudavalli Thu Jun 10, 2010 10:00 am


Putting my thoughts...

We have 4 entities, account, product, classification, transactions....

account , products, classifications are dimensions
transactions are facts

We need to handle type 2 changes for
classification in account dimension (separate table)
classification in product dimension (separate table)

Since transaction is always time variant have one more row with a different time dimension and new classification in the fact table.



Posts : 29
Join date : 2010-06-10
Age : 38
Location : Pune, India

Back to top Go down

Tricky Model Empty Re: Tricky Model

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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