Heterogeneous Products with Transaction Facts

[mayidunk]

I am in the process of modeling a Budget Data Mart and have a question about a scenario that is described on page 215 of, “The Data Warehouse Toolkit, Second Edition” (Kimball), by Ralph Kimball and Margy Ross. I believe that the description of “Heterogeneous Products with Transaction Facts” describes the heterogeneous G/L Accounts with transaction facts that I am attempting to model.

The University of Connecticut is a Research Institution and the Budget Data Mart is used to manage the budget for Research Departments that acquire funding though Grants as well as Academic Departments that acquire funding through Tuition and Fees. The data mart includes a G/L Account Dimension and G/L Journal Entry Fact as illustrated on page 178, figure 7.2 in Kimball. There are a number of G/L Account Dimension attributes that are specific to Research Accounts and are not found in Academic Accounts. For example, Principal Investigator, Sponsor, Proposal Number, Award Number…etc. Would it make sense to create a Research G/L Account Dimension outrigger for these custom attributes with the core attributes remaining in the G/L Account Dimension?

 

[avjoshi]

mayidunk, I don't have the book handy with me so my answer is going to be a little vague but have you considered creating a single dimension table with all the pertinent attributes for both the account types? If they are mutually exclusive then those would simply be null. As you know, null doesn't take up any storage space.

Anand

 

[johnherman]

Either. You can create a dimension for the Research GL Account subtype of the GL Account (this is a snowflake dimension). Alternatively, you can denormalize the Research GL columns into the main GL Account column and have them as null for non-Research GL Accounts. It's a matter of preference, data volumne, and the way each layout would interface with the user query tools and other such third-party interfaces.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[mayidunk]

Hello All,

We decided not to pursue this course of action, however your responses were good. Thank you for taking the time to provide thoughtful answers.

Bob