Dimensional Model for inheritance in dimensions

data-warehousedimensiondimensional-modelinginheritance

How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension?

For example, assume I have an Employee dimension and a Driver dimension, where the Driver has all the attributes of an Employee but also has a Truck Number attribute. How should these dimensions be modelled?

Best Answer

While in a traditional OLTP system you may need to be concerned about normalizing and inheritance, in a typical data warehouse system you will want to denormalize to accelerate query responses.

In this case, I would design an Employee_Dimension table which had all relevant columns about an employee which you might want to query against, along with an additional column which I might call employee_role, which I would fill in with entries like Driver, Manager, etc. and then have another column which had either the truck_number filled for employees who were a driver, or leave the column as NULL when they aren't drivers. Of course, you can add even further columns to include information for other employee roles.

Long story short, if you're building a typical star schema, you can achieve faster query results by using heavy denormalization, at the cost of storage space of course.

Related Solutions

Mysql – Type of Foreign Keys for a Dimensional Model Fact Table

Yes, that is the correct method.

Database Design – Hierarchy for Dimensional Model

My take on this is that you may perhaps want to try using a parent-child dimension, so the model will allow for a theoretically infinited amount of levels. That way, you can connect facts to each level of the hierarchy.

Here's an example:

CREATE TABLE dimGeography (
    id
    parent_id
    geographyType
    geographyCode
    geographyName
    PRIMARY KEY (id)
);

CREATE TABLE factDemographics (
    date
    geography_id
    monthlyIncome
    workingPopulationAge
    numberOfPeople
    PRIMARY KEY (date, geography_id)
    FOREIGN KEY (geography_id) REFERENCES dimGeography (id)
);

The advantages of this approach are:

you're free to add or remove levels as you like over time without changing the data model
you can connect facts (demographics) to each level
no duplicate measure columns, i.e. just one column called "number of people"
future-proof: some countries or regions may use different types of levels - for instance, not all countries use "state".

The disadvantages that I can think of are:

you may have to redesign your existing (?) dimension or fact table
your BI tool may not be able to handle parent-child dimensions
more complex SQL queries when aggregating the facts on higher levels

I haven't the slightest idea if this is the "correct" solution according to Kimball, Inmon, etc. Instead, I would urge you to see if it applies to your particular needs. ;)

Best Answer

Related Solutions

Mysql – Type of Foreign Keys for a Dimensional Model Fact Table

Database Design – Hierarchy for Dimensional Model

Related Question