Core/Custom Fact Table

Question

I have a fact table defined at the order/line grain. Each order belongs to a certain vertical and each vertical has custom attributes for describing it's data. I want to be able to allow users to query across all orders regardless of vertical but when querying for data specific by verticals be able to filter by vertical specific attributes.

Here's how I plan on structuring this but would like input if this seems like a good design or please recommend another approach if this is bad.

The fact table will contain the VerticalKey FK. These are the Dims I'm planning on making:

1. DimVertical (supertype/core)

   • VerticalKey (Auto Increment)
   • OrderId (Alternate key)

2. DimVertical-Car (subtype/custom)

   • VerticalKey (Key Id from DimVertical.VerticalKey)
   • CustomAttributeABC
   • CustomAttributeDEF
   • CustomAttributeGHI

3. DimVertical-Motorcyle (subtype/custom)

   • VerticalKey (Key from DimVertical.VerticalKey)
   • CustomAttribute123
   • CustomAttribute456

In order to query across all orders a join would just be done to the supertype DimVertical. However when I want to query across a specific vertical, by vertical specific attributes, I would just include the optional subtype Dimension.

Does this seem like a good approach? Secondly, if this is a fine approach, let's say "OrderType" is a super type attribute so it could go into the DimVertical dimension, is that bad? I'm questioning that cause I know you are not suppose to have a header dimension which is kind of what this is but I don't know how else to support "custom" order header search ability.

Thanks in advance!

Answer 1

In the theory there are three possible mapping of class hierarchy to relational schema:

• table per class hierarchy

• table per subclass

• table per concrete class

If I get you right you you follows the table per subclass strategy. This could be fine, but can't be commented without knowled of your data.

The best approach is simple set up a sample schema with non trivial data; you'll see quickly if the access queries perform and are simple to create.

From my experiance an offen used approach in data warehouse design is table per class hierarchy (i.e. all subsclasses are implemented in one table) because

• the access is effective (without joins) and
• downside of possible inconsistency (i.e. you may store car attributes in motorcycle record) is in data warehouse not critical as the consistency is typically done with clening ETL jobs and not by the database.