Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

newbie question on health care modeling

3 posters

Go down

newbie question on health care modeling Empty newbie question on health care modeling

Post  jon 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

Back to top Go down

newbie question on health care modeling Empty Re: newbie question on health care modeling

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

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

http://aginity.com

Back to top Go down

newbie question on health care modeling Empty Re: newbie question on health care modeling

Post  jon 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

Back to top Go down

newbie question on health care modeling Empty Re: newbie question on health care modeling

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

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

http://aginity.com

Back to top Go down

newbie question on health care modeling Empty thank you.

Post  jon 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

Back to top Go down

newbie question on health care modeling Empty Re: newbie question on health care modeling

Post  BoxesAndLines Mon Jun 07, 2010 11:13 am

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

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

Back to top Go down

newbie question on health care modeling Empty Re: newbie question on health care modeling

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum