I would like to set up a dimension table for “Dim Overlay”. This table will store Overlay ID, Overlay Name.
An overlay, per our company’s definition, is a somewhat irregular sales territory that is based on individual lines on an order rather than an entire order.
Example:
Dell Team: Filter lines to Manufacturer = "Dell", Manufacturer = "Dell RSA", Manufacturer = "VMWare", or Vendor = "Dell Federal Systems, LLC"
I have many fact tables to consider, however, Manufacturer and Vendor are line-level dimensions and are therefore connected to every single line fact table.
I’m trying to determine how to build this complex filter which is based on a dimension table. A generalized data model diagram is below.
GAI Data Model Filter Propagation.pdf (65.6 KB)
The simplest way I can imagine would be to connect Dim Overlay via bridge tables to Dim Manufacturer and Dim Vendor. However, I don’t think I can build bridge tables to Manufacturers and Vendors because of the OR relationship of the example. I need to show lines where EITHER the Manufacturer is Dell OR the Vendor is Dell, and these bridge tables would filter out all cases where only one condition is met.
A more complex method would be to build a bridge table from Dim Overlay to Fact Sales_Order_Line, but due to the structure of my data model, I would need to build an additional bridge table for Fact Sales_Invoice_Line, Fact Sales_Credit_Memo_Line, Fact Purchase_Order_Line, Fact Sales_Shipment_Line, etc…
My line fact tables do not interconnect, just the headers. I would need a bridge table for every single line table.
Perhaps there is a very elegant way to build this, but I am not seeing it. The hurdle I can’t get over is the OR relationship with the filters in the example.