Common attributes across multiple facts

View previous topic View next topic Go down

Common attributes across multiple facts

Post  dbooth on Tue Sep 04, 2012 9:32 am

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

dbooth

Posts : 3
Join date : 2012-09-04

View user profile

Back to top Go down

Re: Common attributes across multiple facts

Post  ngalemmo on Tue Sep 04, 2012 2:59 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Common attributes across multiple facts

Post  dbooth on Wed Sep 05, 2012 4:53 am

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?

dbooth

Posts : 3
Join date : 2012-09-04

View user profile

Back to top Go down

Re: Common attributes across multiple facts

Post  ngalemmo on Wed Sep 05, 2012 1:07 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Interesting principles with degenerate dimensions

Post  PHough on Thu Sep 06, 2012 1:40 pm

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.

PHough

Posts : 3
Join date : 2009-02-04

View user profile

Back to top Go down

Re: Common attributes across multiple facts

Post  dbooth on Fri Sep 07, 2012 3:26 am

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.

dbooth

Posts : 3
Join date : 2012-09-04

View user profile

Back to top Go down

Re: Common attributes across multiple facts

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum