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?


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.

  • This ^^, except I would put in N/A or something instead of nulls for the missing attributes, See kimball
    – Tom V
    Feb 15 '18 at 9:17

hear I would employ a swappable dimension pattern. Create three dimensions with the same SK's:

1) **Employee** Base table with the common attributes
2) **Driver** table with driver specific attributes note the SK' needs to match the table above
3) **DriverEmployee** a view that joins the values from one and two.

then when you report on the data choose the structure that best suits the requirements: Employee, Driver, DriverEmployee

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.