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.
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
