Common attributes across multiple facts

Hello,

In a purchase order processing model I've built three fact tables : orderline, receipt, and invoice. They share dimensions such as time, supplier, and product. Some attributes of an individual purchase order (for instance "Order Type (direct delivery to customer / ship to stock)" are currently stored in the orderline fact table and accessed by the user via a degenerate dimension.

I'm now looking to make those attributes available to cubes with the receipt or invoice fact table as the source. The common business key is order/order line. There is a one-to-many relation between orderline and receipt tables, where many can also be zero (no receipts to date).

Which method is most appropriate for this scenario? A cube combining orderline & receipt facts didn't work (in SSAS2008), when a value for the Order Type attribute was specified no records were returned.

Regards,
Dave Booth

When you have a dependent relationship between facts, the dependent fact should hold FKs to the dimensions of the other fact. Fact tables should be independent of each other.

The fact tables are independent & share common dimensions (but not all dimensions they use are shared). Here's the cube as it appears in SSAS :



In order to make fields like DirectPO available through factpurchasereceipt, is it worth adding them to the factpurchasereceipt table, or a junk dimension?

I don't know what 'directPO' represents, so I can't say. It would all depend on what is know at the time of receipt. If directPO represents a requisition, then I would say no if requisitions are bundled into purchase orders.

One would generally not join fact tables, not because it is 'not allowed', but because the differences in granularity cause cross-product effects for processing the fact measures. However, with a degenerate dimension the fact table is one-to-one to the 'virtual' dimension table and so the fact table itself can fulfil the role of a dimension table as well - as long as you treat all attributes, including numric metrics, as descriptive attributes and not as additive measures.

The PK of the 'virtual' dimension table is also the PK of the fact table (PurchaseOrder) so you can join your receipts fact directly to the order fact via the order number to look up the Order Type.

IN the DW relational structures we somtimes 'materialize' the virtual degenerate dimension into a real dimension, move (some of) the other dimensions FKs from the fact table to the dimension table, and then use the dimension table for other fact tables. But we mostly simply use the fact table in a 'dimension role' and then we even also have e.g. QtyOrdered not as an aggregable value, but as a 'descriptive numeric' attribute of the order. So e.g. if you join your receipts table to the order table via PurchaseOrder (presuming that it is unique in FactPurchaseOrder) you must not then ever sum up the QtyOrdered, but you can still use it to describe the original total quantity ordered and e.g. to derive the proportion of the original order amount that was actually received for each pert receipt transaction: FactPurchaseReceipt.ReceiptQty / FactPurchaseOrder.QtyOrdered. (If I understand your schema correctly).

However, in Analysis Services, I think you can create an OrderType dimension from the FactPurchaseOrder table (technically OrderType should really be a FK itself in the FactPurchaseOrder table in your DW schema with a separate DimOrderType table, but in AS you can simulute this), and then I think you can then use a Referenced dimension type in th eCube / Dimension Usage tab to link the Purchase Receipt measure group to this dimension via the FactPurchaseReceipt table using the the PurchaseOrder field.

The scenario's working now. The error I'd made was in having separate measure groups for the PurchaseOrder and PurchaseReceipt facts, and in SSAS Dimension Usage, each measure group referred only to its base fact table. Now there is only one measure group, for PurchaseReceipts, and the FactPurchaseOrder degenerate dimension ("Purchase Order Details" in the screenshot below) is linked to it by the business keyfields present in both tables, which is represented in the PurchaseOrder fact as 'Product'.



Thanks all for your assistance.