Purchasing Fact Model problem

View previous topic View next topic Go down

Purchasing Fact Model problem

Post  PVick on Thu Feb 02, 2012 7:36 am

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?

PVick

Posts : 2
Join date : 2011-05-04
Location : UK

View user profile

Back to top Go down

Re: Purchasing Fact Model problem

Post  ngalemmo on Thu Feb 02, 2012 10:32 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Purchasing Fact Model problem

Post  PVick on Thu Feb 02, 2012 11:00 am

Thanks for the reply.
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

View user profile

Back to top Go down

Re: Purchasing Fact Model problem

Post  ngalemmo on Thu Feb 02, 2012 12:14 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Purchasing Fact Model problem

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