Purchasing Fact Model problem

[PVick]

I think the purchasing model I have taken on may be incorrect.
We currently store Purchase Requisition(Req), Purchase Order(PO), and Goods Recipt(GRN) facts in a single facPurchasing table. Grain should be Req Line/PO Line.
Lines in facPurchasing are updated as processing occurs ie. A GRN key will be inserted against an existing line when an item is received against a PO.

The problem I have is that a single GRN can exist for more than 1 PO Line, but also multiple GRN's can exist for a single line (eg. when 3 out of 6 items on line are delivered). How should I model this? I think trying to refine the grain further would be too difficult.

Should I separate out a GRN fact table, and if I want a report to show Req's, PO's and GRN's together, will having an FK to each dimension in each fact table work?

[ngalemmo]

Requisition, purchasing, and receiving are three different, somewhat independent, business processes. They should be represented by 3 fact tables. You can create an aggregate from the detail if necessary.

[PVick]

Thanks for the reply.
Would having FK's for dimGRN & dimReq in facPurchasing be the correct way to model for drill-across queries/reports?

[ngalemmo]

Yes, you would want to carry document # and line # as degenerate dimension values to integrate across the facts.

[Sponsored content]