How model the fact table

View previous topic View next topic Go down

How model the fact table

Post  ftatarli on Wed Apr 10, 2013 5:48 pm

I'm building a DW but I'm not able to design a fact table that show what i need. My design until now is that :

Table: dimPatient
ID_PACIENTNAMEAGE
1AAA18
2BBB21

Table: dimSirurgy
ID_SIRURGYID_PACIENTEdate_factdate_payCOSTnm_descriptionnm_doctor
10012013-01-012013-06-0110000DESC 1DOCTOR 1
20022013-05-012013-06-0115000DESC 2DOCTOR 2

Table: dimExam
ID_EXAMid_patientdate_factnm_descriptionHOSPITAL
0512013-02-01DESC 1HOSP A
1012013-02-01DESC 2HOSP A
1522013-05-01DESC 3HOSP B
2022013-05-01DESC 4HOSP B

Table: dimDate
ID_DATEDATE
12013-01-01
22013-02-01
32013-03-01
42013-04-01
52013-05-01
62013-06-01
72013-07-01


Table: Fact
ID_PACIENTID_SIRURGYID_EXAMID_DATESUM COST
1100NULL110000
1NULL52NULL
1NULL102NULL
2200155150000
2200205150000





If you see the rows in the fact table are duplicate. For example I wanna know the cost of the sirurgy but the cube was return the sum of the duplicate rows, probably because its model in the wrong way.

The rule for these tables are that one patient could make only one sirurgy per day, but could do more than one exam in a day.

Thanks in advance

ftatarli

Posts : 2
Join date : 2013-04-10

View user profile

Back to top Go down

Re: How model the fact table

Post  ngalemmo on Thu Apr 11, 2013 12:28 am

Normally this is modeled at the procedure grain. One row per patient/procedure/date. The costs are recorded as known for that procedure, not a combination of all procedures for a given encounter. Capitated procedures, such as exams could be booked at a standard cost. Recorded as null in your example is another possible method, but can be misleading in clinical analysis and actuarial studies.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How model the fact table

Post  ftatarli on Thu Apr 11, 2013 6:31 am

Thank you for your reply ngalemmo.

Just let me see if I understand what you said. My fact table should be like that :

Table: Fact
ID_PACIENTID_SIRURGYID_EXAMID_DATESUM COST
1100NULL210000
1NULL51NULL
1NULL101NULL
2200NULL5150000
2NULL155NULL
2NULL205NULL

One row per procedure, indepedent if its in the same day ?

Thanks

ftatarli

Posts : 2
Join date : 2013-04-10

View user profile

Back to top Go down

Re: How model the fact table

Post  ngalemmo on Thu Apr 11, 2013 4:16 pm

One FK as well, Procedure ID. The procedure dimension would include all procedures (exams, surgeries, dispensing medications, etc...). Procedures are usually identified using CPT or ICD coding systems (or whatever standard coding system in your region).

The dimension would only contain information about the procedure... not the patient or location or booking dates. Those are dimensions that should be referenced from the fact.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How model the fact table

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