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?
The entire discussion of this concept can be found under the heading of "Heterogeneous Product Schemas," starting on page 210 in Kimball.