Dimensional Modeling - How to deal with a single fact table with facts with inconsistent dimensions?

Question

I want to set up a fact table for restaurant sales transactions. Adding up the entire fact table will give the entire sales across the restaurant(s). The restaurant has two main sources of revenue - food and beverage. The dimensions for each are very different.

For example, for food, I might want to track whether it's dairy free, gluten free, etc. Or I might want to see whether the dish is Italian, French, etc. For wine, I might be interested in the vintage, where the wine is from, what grape the wine is.

How do I accomplish this with one fact table? Should I simply have a Wine dimension that is NULL if the item is a food, and a Food dimension that is NULL if the item is a wine?

Answer 1

Your fact probably looks something like this?

SALES_LINE_ITEM_FACT
   TRAN_DATE
   TRAN_HOUR (or other time buckets if needed)
   SERVER_KEY
   TABLE_KEY
   SEAT_KEY
   PROMOTION_KEY
   PRODUCT_KEY
   REGULAR_PRICE
   NET_SALE_PRICE
   PRODUCT_COST

Your "product" dimension is where you need to focus your attention on, if you want to report from a sales fact how many people ordered a specific wine.

To start, it might just look something like:

PRODUCT_DIM
   PRODUCT_KEY
   PRODUCT_NAME
   PRODUCT_CATEGORY (food / beverage)
   PRODUCT_SUBCATEGORY (wine / beer / dairy / french / italian etc)
   CURRENT_AVERAGE_PRODUCT_COST

You could either add the detail information as another level on the category hierarchy, or if you want to do more detailed analysis, create specific snowflakes for certain product types and connect them to the product dim.