I am in the midst of refactoring my PBI Data Model.
I have a unique situation; my company is a drop-shipper, so for each Sales Order we may have multiple corresponding Purchase Orders (one per vendor). This means our Fact Tables have an interesting relationship I’m trying to model with mixed results.
There are several relationships I have so far opted not to model to avoid circular relationships. Very rarely (if ever) will we need to look up a specific line of a Sales Order in a different fact table; the header-level grain is sufficient for foreseeable uses.
- Purchase Order Line has foreign keys to Sales Order Line
- Purchase Receipt Line has foreign keys to Sales Order Line and Purchase Order Line
These connected Fact tables make for some interesting dimension connections. I believe I’ve determined how best to model each of them, but would like more eyes on the diagram to ensure I won’t get any major issues. Take Dim Vendor as an example. This dimension links to the line-level tables on the sales documents, but the header-level tables on the purchase documents. This is possible because the function that generates purchase orders and sales orders from a quote always generates
- Exactly 1 Sales Order
- Exactly 1 Purchase Order per Vendor
In addition to general feedback on my data model diagram above, here are some specific questions I am stuck on.
- Dim Date: I have multiple dates I would like to do time intelligence on; Fact Sales Invoice Header[Posting Date] and Fact Sales Order Header[Order Date] as an example. These are the two primary dates we want to report by, but these two Fact Tables are already linked in a 1:many relationship. How do I build Dim Date into the model and build time intelligence functions on both of these two date fields?
–> Is this a case where USERELATIONSHIP() is necessary? If so, I could use some guidance on the starter measure to branch from.
Dim Date: Same question as above, except both values are in the same table. For example, Fact Sales Order Header[Order Date] and Fact Sales Order Header[Award Date].
Dim Contract: This dimension connects to both the Sales Header and Sales Line tables. The values are not necessarily the same on the header and lines. How do I model both relationships and build measures to show either header contract or line contract? I will also need to connect to Sales Invoice Lines and Sales Order Lines, while also capturing Sales Order Header value.
I’m making good progress on this, but the following guidance and resources would be most helpful to me now;
- Specific cases when inactive relationships are recommended, and how to judge this for myself
- Specific DAX Measures and formulas to use when relationships are inactive
- Enterprise DNA tutorials on inactive relationships and complex data models such as mine
- Enterprise DNA tutorials on multiple interconnected fact tables, including my order:invoice and order header:order lines tables
Note: This conversation began as a different topic in Enterprise DNA concerning modeling my Sales Invoices and Sales Orders, and how best to build measures for each. Here is that post