newbie question on health care modeling

View previous topic View next topic Go down

newbie question on health care modeling

Post  jon on Tue Jun 01, 2010 6:36 am

I have a question on The Data Warehouse Toolkit, 2nd Ed. The complete guide to Dimensional Modeling.

In Chapter 13 pg 264

"an SQL view could be defined combining the fact table and the diagnosis group bridge table so that these two tables, when combined, could appear to data access tools as a standard fat table with a normal diagnosis foreign key."

My question is on the syntax of the view. Suppose the table "HealthCareBillingLineItemFact" is join with table DiagnosisGroupBridge
does it mean a view with 1) LEFT join or a 2) denormalized one

create view "HealthCareBillingLineItemFactWithDiagnosisGroup"
as
select f.* , b.*
from HealthCareBillingLineItemFact f
LEFT join DiagnosisGroupBridge b
on f.DiagnosisGroupKey=b.DiagnosisGroupKey
;

or
a denormailzed fact
create view HealthCareBillingLineItemFactWithDiagnosisGroup
as
SELECT us.*,
MAX(CASE WHEN st.DiagnosisGroupBridge = 'Diag1' THEN us.value ELSE NULL END) AS Diag1,
MAX(CASE WHEN st.DiagnosisGroupBridge = 'Diag2' THEN us.value ELSE NULL END) AS Diag2,
MAX(CASE WHEN st.DiagnosisGroupBridge = 'Diag3' THEN us.value ELSE NULL END) AS Diag3,
MAX(CASE WHEN st.DiagnosisGroupBridge = 'Diag4' THEN us.value ELSE NULL END) AS Diag4
FROM HealthCareBillingLineItemFact AS us
INNER JOIN DiagnosisGroupBridge AS st
ON us.DiagnosisGroupKey = st.DiagnosisGroupKey
GROUP BY us.DiagnosisGroupKey

jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: newbie question on health care modeling

Post  ngalemmo on Tue Jun 01, 2010 11:39 am

In both cases they would be inner joins. Wither you denormalize or not depends on the nature of the query.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: newbie question on health care modeling

Post  jon on Thu Jun 03, 2010 10:44 am

thanks for the reply.

pardon my little knowledge on SQL. I am not certain about the inner join.

Assuming the FACT table is on the left side of the join , won't LEFT join include all results from fact joining to the dimension regardless if there is a matching diagnosis group on the right side?

Thanks.

jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: newbie question on health care modeling

Post  ngalemmo on Thu Jun 03, 2010 2:39 pm

Best practice, when building dimensional models, is to always have a foreign key that points to something. So, every row should reference a diagnosis group. This would include entries that do not have diagnoses... they would reference a 'no diagnosis' group entry in the dimension table. There should never be a need for an outer join in a proper dimensional warehouse.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

thank you.

Post  jon on Sun Jun 06, 2010 2:33 pm

i didn't think from that way, guess I never had a good training. all the training has been on-the-job and all i hear are inconsistent piece meals.

so, i guess, i've accidentally find a way to debug my DW.

Thank you, sir. you have a wonderful weekend.

jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: newbie question on health care modeling

Post  BoxesAndLines on Mon Jun 07, 2010 11:13 am

Get the data warehouse toolkit, second edition. It goes through the data modeling process in depth.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: newbie question on health care modeling

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