Tricky Model

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:

DIM_Account
--------------
Account_ID [PK]
Account_Classication_ID
Account_Number
Account_Name
[ etc, other attributes]

DIM_Product
---------------
Product_ID [PK]
Product_Classification
CUSIP
other attributes for Product

DIM_Classification
-------------------
Classification_ID
Classification_Value
HierarchyLevel1
HierarchyLevel2
HirearchyLevel3
Other attributes for this classification

FACT_Transaction
------------------
Account_ID [FK]
Product_ID [FK]
Classification_ID [FK]
MarketValue
(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!

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

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.

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.

Hi,

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.

Regards
Shiv