Best Practices for SCD Dimension Tables for a Banking Data Warehouse

Hello,

I am looking for a list of dimension tables to track historical changes on for a banking data warehouse, based on "best practices". Customer is a prime candidate. Are there others?

Thanks,

Ike

Hello Ike,

I have been working in this domain for quite a while and would like to offer some help, but am not sure I have enough information from you. It all gets back to your business needs.

We have several business process dimensional models in place currently and are tracking history in our Customer, Online Subscriber, Account, and (Bank)Product Dimensions.

One learned best practice we employ is, early in the requirements gathering process, we investigate whether or not our users need to know the attribute status of an entity as it existed when the measurement was taken or if they simply need to know the value as it exists currently. If they need to know the status as it existed when the measure was taken, that attribute probably should be type 2, and the dimension must support that.

For example, an account interest rate may change over the lifetime of an account. If the users need to know what the interest rate was for a given account at a given point in time, account interest rate is a good candidate to be a type 2 attribute in your account dimension.

As a side note - Customer, subscriber, and account can quickly become 'rapidly changing monster dimensions' in banking environments so keep that in mind as you move forward. (As a result, our "customer" dimension is actually several customer dimensions "CustomerDemographics", "CustomerLocation", "CustomerStatus", etc.)

I hope this helps and is not too far off base...
Patrick