2

What are the steps/rules to build a DW star schema design from a production database; specifically, how do you handle many-to-many relationships.

I understand how to take basic data including a many-to-many relationship, and get to a normalized production database:

For example:

If I want to handle sales transactions, given tables Product Entity, Promotion, and Employee, the initial step is to construct a table, SaleTransaction:

SaleTransaction
- TransactionID
- ProductID
- EmployeeID
- SellingDateID
- Quantity
- SaleAmount
- PromotionID

The Promotion entity will be:

Promotion
- PromotionID
- ProductID
- DiscountAmount

However, that would only allow 1 product and 1 promotion per sale transaction. Since we want to allow one or more products, and zero or more promotions:

  • I remove PromotionID, ProductID, Quantity, and (unless required by some business rule) SaleAmount from SaleTransaction

  • I create SaleTransactionDetail:

    - DetailID
    - TransactionID
    - ProductID
    - Quantity
    - SaleAmount
    
  • I will create SaleTransactionPromotion:

    - TransPromoID
    - PromotionID
    

What would be the equivalent steps to take a production DB design to a DW star schema design?

  • 1. What is captured at the Sale Transaction that can't be captured at the Sale Transaction Detail level? 2. How will measure in a single transaction be allocated when measured against many promotions? Does each promotion get an equal share of revenue allocated? – Nick.McDermaid Aug 19 '18 at 10:25
  • @Nick.McDermaid 1. I think the only thing that SaleTransaction cannot capture is list of all product details (obviously). So how can we answer question like: "Best selling product in this month". 2. I don't get this point. But directly for your question, each promotion doesn't get get equal share of revenue allocated. – hqt Aug 19 '18 at 15:11
  • 1
    How do you have a quantity and product on different levels? Why don't you have a simple orderheader and orderline design? It's also unclear what the promotion should contain. – Tom V Aug 20 '18 at 7:42
  • @TomV I have updated about Promotion entity on my post. How do you have a quantity and product on different levels -> can you explain more. That is SaleTransactionDetail (product_id, quantity, sale_transaction_id). thanks – hqt Aug 20 '18 at 7:52
  • 1
    In your question you have a Qty on the salestransaction table too. – Tom V Aug 20 '18 at 8:01
5

Many-to-many tables are typically handled with a "bridge table." This is really just another name for a many-to-many table like you'd use in a transactional database: one column for an FK to the first table, one column for an FK to the second table, and probably a column or two for housekeeping fields.

You may also want a Weight field in the bridge table, with a value equal to one divided by the number of entries for each sale. This lets calculate SUM(Weight) instead of COUNT(*), when you want to avoid double-counting orders which had multiple promotions.

Your proposed schema is good, as is, but I think it could be improved slightly; two things come to mind. One is that I would generally collapse SalesTransactions and SalesTransactionDetails into a single table. This introduces redundancy for things like customer IDs, which ought to make your OLTP instincts flinch (transitioning from normalized databases to data warehousing is a paradigm shift!), but in this case it's worth it. By putting all of your sales into a single table, you simplify joins and, in many databases, signal that you want to use bitmap indices. In a warehouse, you often end up having to do table scans, but that's OK because your fact tables are very narrow, and fit in memory.

So we have three tables Sales, Promotions, and SalesPromotions. You might consider "order" rather than "sale," because the latter implies a finality which may not apply. If your system includes orders which have been placed but not shipped or not closed, the more general term makes sense. That's quibbling, but it never hurts to think carefully about what you name things.

CREATE TABLE Orders
    -- IDs/dimensions:
    OrderID
    EmployeeID
    ProductID
    -- Date dimensions: date placed, date contract signed, date shipped, etc.
    -- Metrics: quantity, gross price, net price, extended price
    -- Attributes: is taxable, currency code, business key

CREATE TABLE Promotions
    PromotionID
    -- Date dimensions: date effective, date expires
    -- Metrics: fixed amount, percentage amount
    -- Attributes: name, type

CREATE TABLE OrderPromotions
    OrderID
    PromotionID
    Weight

ExtendedPrice can be a computed column rather than storing a value. If attributes start to bloat your Orders table, consider a "junk dimension."

You could include ProductID in Promotions, but only if promotions will only ever apply to a single product. For example, do you have some promotions which are applied to a whole order rather than specific line items? Might a promotion apply to product code ABC1 today, but ABC2 tomorrow, after a price change?

The second caveat I had in mind is IDs. I strongly recommend that you use synthetic keys (SKs) in a data warehouse, rather than relying on IDs from the source system. There are a number of reasons, and this is discussed elsewhere on DBA.StackExchange, but offhand:

  1. A 32- or 64-bit integer for an SK will often be narrower than source system order IDs or customer IDs (e.g., a 144-bit Salesforce ID or other long alphanumeric IDs). This keeps your tables narrow, your secondary indices narrow, and your scans fast.
  2. You can avoid collisions in the source system. If Marketing some day decides to use product key ABC123 for something unrelated to what it was last year, you can give them two different synthetic keys.
  3. In particular, if your company acquires another business, you may find that their IDs don't match the data types you've been using. You'll need to map them to a common set, and handle collisions. Best to have the infrastructure for that from day one.
  4. You can use magic values for specific meanings which are relevant for reporting, but which aren't handled in the transactional system. For example, I always use SKs -1 for "N/A" and 0 for "Unknown". E.g., the Customers.FirstOrderSK field will be populated with -1 for customers who've had no orders, and 0 for customers who've had an order but for which no more specific info is available yet. Your reports can reflect this without having to put in special logic to handle NULL values.

You can take advantage of synthetic keys in your bridge table by including a "dummy record" for orders without promotions. Populate it with a PromotionID of -1, to indicate that no promotion applied; now consumers of your data can filter for orders sold at full price.

  • thanks for very detail post. I have a question: when we collapse SaleTransaction and SaleTransactionDetail into single table, quantity/net price/gross price ... is for single item or whole order. Thanks – hqt Aug 29 '18 at 17:07
  • @hqt: each record would be for a specific line item in the order; basically, you'd be including the header information in the SaleTransactionDetail table, so you can dispose of the SaleTransaction table. One of my principles of data warehouse design is: go for atomic data if you possibly can. You can always aggregate up, if necessary, but refactoring for a finer level of granularity is much harder. – Jon of All Trades Aug 30 '18 at 13:31

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.