Purchasing Fact Model problem
2 posters
Page 1 of 1
Purchasing Fact Model problem
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?
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?
PVick- Posts : 2
Join date : 2011-05-04
Location : UK
Re: Purchasing Fact Model problem
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.
Re: Purchasing Fact Model problem
Thanks for the reply.
Would having FK's for dimGRN & dimReq in facPurchasing be the correct way to model for drill-across queries/reports?
Would having FK's for dimGRN & dimReq in facPurchasing be the correct way to model for drill-across queries/reports?
PVick- Posts : 2
Join date : 2011-05-04
Location : UK
Re: Purchasing Fact Model problem
Yes, you would want to carry document # and line # as degenerate dimension values to integrate across the facts.
Similar topics
» dimensional fact model problem
» Header/Detail Fact Problem
» Problem while Designing Fact table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to model fact with optional parent fact?
» Header/Detail Fact Problem
» Problem while Designing Fact table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to model fact with optional parent fact?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|