Variable period data in a single periodic snapshot fact table

[mshahbazuddin]

I have to load account information into a periodic snapshot table which is at a day level. I have three different sources that provide the interest rates and balances information. Two of those sources(A and B) process interest rates and balances only on business days, however the third one(C) processes interest rates and balances on all 7 days in a week.

From a dimensional modelling point of view, do all three sources fit into a single periodic snapshot.
This way for non-business days I have data from only source C and on business days I have data for A,B and C.
This way I have all three in a single fact table.

The other option is that I seperate the source C into one fact table and A and B into the other fact table.
Please advise on which one is a better option from a dimensional modelling point view.

[hang]

If you have the source dimension key in your consolidated fact table, create a view to present proper fact based on the system date should be fairly straightforward. Possibly store the business logic as a column (eg. ShowDay) in the source dimension, say All days, Business day or Non-Business day.

[mshahbazuddin]

Thanks for your reposnse. Both the approaches that I outlined would solve the issue, what I am looking for is pros and cons of each approach and if I am violating any dimensional modelling principle by putting the two "period" data into a single fact table.

[hang]

I don’t think either approach would violate the dimensional modelling principle. Maybe I am wrong, when you use a dimension to partition your fact for different views on the fact, is it similar to having different snapshots and using date dimension to partition your fact.

Having two fact tables may be appealing, or safer per se, to dimensional modelling purists. But you have to repeat all the dimension entries in the second fact and you may have to do more work to switch between two facts based on reporting requirements. If there are only few dimensions, I would give a try on both approaches and see which one is easier to implement to cater for the user interface. However if it is typical 10-20 dimensions, and the two facts do share the same dimensionality, I would always consider consolidating the facts into one.

Another approach is to treat C as a separate measure in the same fact table, and set non-existence measures to zero, as having null value measures in the fact table doesn’t look right to me. Again the approach is similar to having two fact tables and present them in a single view by outer join.

So my suggestion is leave options open and try both until you can see through all the nuance involved.

[Jeff Smith]

I think it depends on how the data is going to be used. If, for example, the balances and interest rates are going to be used to calculate Interest Income or Interest Expense (depending on whether the balances were on deposit or credit products) and the Income/Expense was calculated during non-business days for all facts, then you'd do it one way.

If you only wanted to capture was was in the source system, you'd do it another way.

In any case, you may want to indentify whether the source provides the data on a daily or business day basis.

[Sponsored content]