Designing a accumlating snapshot

Hi,

Please help me regarding this scenario:

Case - We have a Healthcare program in which we have presciptions and qty dispensed agains them from a pharmacy .After accumlating qty from various prescriptions for a particular location a PO is generated .We have a many to many relationship between Presription and PO as we can have 1 prescription part of many orders and vice versa.We have cases in which we have overordering or underordering.We need to design a datamart which will give me all the details regarding the presription ,when it was ordered ,invoiced and so on .I need to track it till closure and find out whether qty dispensed against it is completely ordered and recieved .I am thinking of designing a accumlating snapshot but we are at lower grain than Order line .So please suggest what approach i need to take .

You need to create bridge table between your fact table and all the transaction table to address many to many relationship.

You might want to get more specific reporting requirement.
Regardless, i would recommend creating below fact table:
1) Lowest grain transaction fact: Quantity reveived , Quantity ordered
by Prescription LineItem dim (Individual line items of the prescription)
by pharmacy dim
by Date recieved Calendar dim
by Date ordered Calendar dim
by Status dim
by Order Dim (Create a bridge between Order Dim and this fact),
Order Id (Degenerate Dim)
Prescription Id (Degenerate Dim)


You can also create an accumulating snapshot for the above by splitting the status in various columns along with respective dates.
Pls let me know if this helps.

Thanks for your reply ...

Thanks for your inputs .

Let me make myself more clear.

We have a case in which we have one Fill/Presecription attached to only one Drug at a time with some qty .Now we will accumlate these fills till a particular threshold value is reached and then order it .So we have a case a prescrition can be a part of multiple orders and vice versa.We can split our prescription based on the orders .

So if P1 has 100 qty associated then we can have a scenario
Prescription Qty_order qty_rcvd PO line item id

P1 50 10 PL1
P1 20 20 PL2
P1 30 20 PL1
P2 20 10 PL1

where p1,p2 are prescriptions and PL1 is Po lineitem1 ,PL2 is PO linetitem2 .

Based on the example you quoted, I would recommend modifying the grain of the above recommended fact table to :
transaction per Prescription LineItem per order fulfilled.

by Prescription LineItem dim (Individual line items of the prescription)
by pharmacy dim
by Date recieved Calendar dim
by Date ordered Calendar dim
by Status dim
by Order Dim (Create a bridge between Order Dim and this fact),
Order Id (Degenerate Dim)
Prescription Id (Degenerate Dim)
Prescription LineItemId (Degenerate Dim)

Please let me know if you have data at even lower grain than this.

Deleted.

Your statements are not clear.

preeti wrote:Case - We have a Healthcare program in which we have presciptions and qty dispensed agains them from a pharmacy .After accumlating qty from various prescriptions for a particular location a PO is generated .We have a many to many relationship between Presription and PO as we can have 1 prescription part of many orders and vice versa.We have cases in which we have overordering or underordering.We need to design a datamart which will give me all the details regarding the presription ,when it was ordered ,invoiced and so on .I need to track it till closure and find out whether qty dispensed against it is completely ordered and recieved .I am thinking of designing a accumlating snapshot but we are at lower grain than Order line .So please suggest what approach i need to take .

You get prescriptions and dispense them. Ok. Why is a PO generated and to whom? Is this for insurance reimbursement or is to to order more inventory? As far as one prescription being part of many orders, do you mean the aforementioned PO's? I mean, it is normal a prescription gets filled multiple times. Each fill is an independent event.

Heres the thing, you are discussing a multitude of individual business events that, at the base level, need to be treated as such. You would have a fact for fulfillment, fact for invoicing/PO/whatever... If you want to consolidate these into an aggregate fact, fine, but not until you have collected each event independently.

PO is generated to the supplier for ordering inventory but since we can't order for each prescription individually we order them by clubbing them together till threshold is reached.In this scenario we can have a prescription which can be part of mutiple PO lines.So we are at a lower grain than prescription .

We are having multiple events but if we want to track all the events for a particular Fill/Order then what approach i need to take .Should i create a TXN table which will record all the events by date (Ordering ,Invoicing,Fulfillment ) .

Please let me know if i am in right direction .

Hi,

is it for a detail line of a PO, you can have prescription filled for more than one individual or one order detail line in a PO is for one person only. Could you tell how from a PO detail line we can go and link to a particular person ?




thanks

What makes you think you need an accumulating snapshot? It looks to me like you need a transaction fact table.