Designing a accumlating snapshot

View previous topic View next topic Go down

Designing a accumlating snapshot

Post  preeti on Wed Jul 03, 2013 1:39 am

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 .

preeti

Posts : 3
Join date : 2013-07-02

View user profile

Back to top Go down

Designing a accumlating snapshot

Post  zoom on Sun Jul 07, 2013 1:28 pm

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

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

Post  sachij3u on Thu Jul 18, 2013 4:28 pm

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

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

Post  preeti on Mon Jul 22, 2013 7:35 am

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 .

preeti

Posts : 3
Join date : 2013-07-02

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

Post  sachij3u on Mon Jul 22, 2013 12:00 pm

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

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

Post  ngalemmo on Mon Jul 22, 2013 12:54 pm

Deleted.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Designing a accumlating snapshot

Post  ngalemmo on Mon Jul 22, 2013 1:06 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Designing a accumlating snapshot

Post  preeti on Tue Jul 23, 2013 8:13 am

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 .

preeti

Posts : 3
Join date : 2013-07-02

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

Post  hkandpal on Tue Jul 23, 2013 8:34 am

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


hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

Post  BoxesAndLines on Tue Jul 23, 2013 9:45 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Designing a accumlating snapshot

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