Slowly changing heterogeneous dimensions

[kpdw166]

Hi,

I've been looking into setting up some heterogeneous dimensions, but I'm having trouble understanding how you can record history against these tables. In esence how you would handle them being slowly changing dimensions with type 2 attributes. The two dimensions I have are detailed below.

Accounts
Account_ID
Record_Start_Date
Record_End_Date
Account_no
Account_status
Account_type

Mortgage_Accounts (Heterogeneous)
Account_ID
Record_Start_Date
Record_End_Date
Repayment_Type
LTV
Offset_Ind

Please can you explain how slowly changing dimensions and Heterogeneous dimensions work together and some pointers on how I would handling the loading of data into slowly changing Heterogeneous dimensions.

Many thanks

[chade25]

Hi,

I've been looking into setting up some heterogeneous dimensions, but I'm having trouble understanding how you can record history against these tables. In esence how you would handle them being slowly changing dimensions with type 2 attributes. The two dimensions I have are detailed below.

Accounts
Account_ID
Record_Start_Date
Record_End_Date
Account_no
Account_status
Account_type

Mortgage_Accounts (Heterogeneous)
Account_ID
Record_Start_Date
Record_End_Date
Repayment_Type
LTV
Offset_Ind

Please can you explain how slowly changing dimensions and Heterogeneous dimensions work together and some pointers on how I would handling the loading of data into slowly changing Heterogeneous dimensions.

Many thanks

Question, these are dimensions? Why couldnt you have attributes combined to have one dimension with say a mortgage indicator that could be 1 or 0 then you can aggregate across this measure and have a count of mortgage accounts?

[kpdw166]

Hi chade25,

Thanks for your reply. The reason I'm using heterogeneous dimensions rather than having one dimension that holds all attributes is the following;

In this example I've only included the banking type of mortgages, but I may also like to include detail for Card accounts, Saving accounts, Personal Loan Accounts as well. Many of the attributes for these different banking types are distinct to that type only. For example you wouldn't need to report LTV on savings accounts. The idea of using heterogeneous dimensions means I can limit the number of relevant attributes per banking type (mortgages, cards, savings, personal loans, etc), but still have a linking account dimension that has the keys that the heterogeneous dimensions match too.

This method gives me two distinct advantages.
1. I only need show the attributes that are relevant to a banking type if I am reporting say mortgage detail only.
2. I can have a full view of all the banking types by use of the accounts dimension for high level reporting.

I had managed to get my brain around the use of Slowly changing heterogeneous dimensions since posting this topic. So if anyone is interested in the logic I'd be more than happy to share it with you.

Many thanks

[BoxesAndLines]

What's a heterogeneous dimension? Is that a junk dimension?

[kpdw166]

Hi BoxesAndLines,

Heterogeneous dimensions only hold specific attributes related to the type of business you are reporting against. For example, a bank may have many types of business (mortgages, savings, cards, insurance, loans, etc) at the attributes for each type are specific to this type of business.

Account DIM
Account_ID
Account_no
Account_status
Account_type
Etc...

Mortgage Accounts DIM
Account_ID
Repayment_Type
LTV
Offset_Ind
Etc...

Savings Accounts DIM
Account_ID
Minimum_Balance
Childrens_Account_Ind
Etc...

The account dimension holds all of the account IDs for each of the Heterogeneous dimensions, but the Heterogeneous dimensions only hold the account IDs that link to that type of Busienss.

For example

Accounts DIM
Account ID 1, 2, 3, 4, 5, 6, 7

Mortgage Accounts DIM
Account ID 2, 5, 6

Savings Accounts DIM
Account ID 1, 3, 4, 7

Hope this helps to explain this type of Dimension a little better. Failing that The Ralph Kimball Toolkit (Second Edition) book has a full page dedicated to explaining this method

Many thanks

[ngalemmo]

For this to work, the PK of the primary dimension row and the sub-type dimension row need to be the same. So, if there is an attribute update to the mortgage sub-type that triggers a new row for that table, you also need to create a new row in the primary (account) dimension, even though none of its attributes may have changed. Otherwise, if you do not keep the keys in sync, you need to maintain multiple FKs on fact tables, which defeats the purpose of doing dimensions this way.

[kpdw166]

Hi ngalemmo,

Thanks for the confirmation on this. After posting this topic I had a bit of a brain wave and decided to use a tracking table in my staging area to indentify when records in the main dimension or sub-type dimension were going to change, so I could successfully manage these changes when updating the dimension.

[Sponsored content]