newbie question on health care modeling

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

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

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.

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.

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.

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