DatawareHouse modeling (factless table)

Hi everyone !

(To begin, I want to apologize for the bad english i'm gone use cause im french canadian)

system i'm using :
Sql Server 2005 Ent
SSAS 2005 (For cube)
SSIS 2005 (For ETL)

I'm in the process of building a datawarehouse for the paramedic. We want to be able to have the numbers of
intervention by date, by employes, by company. We want to also have measure to know the average time to get
on site for the intervention, the average time on the site and the average time between the time paramedic leave the site and the time they arrived at the hospital.

I already have built the fact table (FactIntervention) and the dimensions (DimCompany, DimEmployees, DimTime, DimIntervention) attached to it. Everything work fine for now.

But, for a intervention, they can use 0 or many medication while the paramedic are on the site. I was wondering how I should model that ?

I was thinking of creating a factless table with the FK of the DimIntervention and a FK of the DimMedication. It is a good way of doing that ? I also have also another FactLess table to create ... in each intervention, they can use one or many protocole. So i was thinking again to create a FactLess table with the FK of the DimIntervention and the FK of the DimProtocole.

Thanks

Richard

Yes, you could do it that way, but you should consider handling it as a multi-valued dimension. It provides a more efficient data structure to support analysis of combinations of medications over the structure you propose.

If you are also tracking frequency (if a drug can be administered more than once in an incident) you probably want to have both structures, with the fact table carrying a count measure as well as having a FK (on this table as well as the main fact table) to the medication group.

ngalemmo wrote:Yes, you could do it that way, but you should consider handling it as a multi-valued dimension. It provides a more efficient data structure to support analysis of combinations of medications over the structure you propose.

If you are also tracking frequency (if a drug can be administered more than once in an incident) you probably want to have both structures, with the fact table carrying a count measure as well as having a FK (on this table as well as the main fact table) to the medication group.

Hi, thank you for the answer

So, if I understand what you are saying, I should have a design similar to this :

FactIntervention	1:1	DimIntervention	1:N	FactLessIntervention_Medication	N:1	DimMedication
FK_Intervention		PK_Intervention		PK_Intervention_Medication		Name
FK_Date				FK_Medication
FK_Employe				FK_Intervention
FK_Company
someMeasures...

So, the table called FactLessIntervention_Medication is what you call a MultiValued dimension ?

In a intervention, drugs can be given more than 1 time for the same intervention. They cant be given at the same time. Thats why i'm using a PK in the FactLess table. I'm really not sure if that design is ok and if it was what your were saying to do ?

But I don't understand where you want to put the medication count. Did you wanted to have just 1 time the MedFK in the Factless table and have a field with the number of time the medication was given ? And what about the main fact table ? You said to put a FK and a count too in the main fact table ?

FactIntervention	1:1	DimIntervention	1:N	FactLessIntervention_Medication	N:1	DimMedication
FK_Intervention		PK_Intervention		PK_Intervention_Medication		Name
FK_Date				FK_Medication
FK_Employe				FK_Intervention
FK_Company				countOfMed
someMeasures...
Count ??
FKFromWhere ?

Thanks again !

Richard

If there is a count, it should go in the intervention/medication fact table. You could eliminate the count if you have a date/time value as a degenerate dimension to the intervention/medication fact... allowing you to have multiple entries for the same medication during an intervention.

Not sure what the new columns in the intervention fact refer to.

ngalemmo wrote:If there is a count, it should go in the intervention/medication fact table. You could eliminate the count if you have a date/time value as a degenerate dimension to the intervention/medication fact... allowing you to have multiple entries for the same medication during an intervention.

Not sure what the new columns in the intervention fact refer to.

Hi, so the disgn showed previously was ok ?

As for the new column in the intervention fact table, i was refering to what you wrote in the first reply : "Yes, you could do it that way, but you should consider handling it as a multi-valued dimension. It provides a more efficient data structure to support analysis of combinations of medications over the structure you propose.

If you are also tracking frequency (if a drug can be administered more than once in an incident) you probably want to have both structures, with the fact table carrying a count measure as well as having a FK (on this table as well as the main fact table) to the medication group."

But maybe its just me ... I probably didnt understand correctly what you wrote ;-)

As for the FK_Date in Intervention_Medication fact, The information is available in the OTLP table, I could add it in the DW.

And finally, they don't do analysis about medication combination.

thanks again for the time taken to read and answer this post ;-)

Richard

If you want to implement a multi-valued dimension, you would need to define a medication group table which would contain one row per unique combination of medications given during any intervention, as well as a med group/med bridge table. I didn't see that in your design, so I assumed you are not considering it (it is optional after all). Multi-valued dimensions have been discussed a number of times in this forum, here is one: http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/what-to-do-when-the-weighting-factor-of-a-bridge-table-no-longer-seems-relevant-t441.htm
You can also google the term for additional information.

Both the intervention fact and the intervention/med fact tables would add the med group FK. While the FK on the intervention/med fact is a little redundant, it doesn't cost much to add it and adds additional analysis functionality that may be useful.

ngalemmo wrote:If you want to implement a multi-valued dimension, you would need to define a medication group table which would contain one row per unique combination of medications given during any intervention, as well as a med group/med bridge table. I didn't see that in your design, so I assumed you are not considering it (it is optional after all). Multi-valued dimensions have been discussed a number of times in this forum, here is one: http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/what-to-do-when-the-weighting-factor-of-a-bridge-table-no-longer-seems-relevant-t441.htm
You can also google the term for additional information.

Both the intervention fact and the intervention/med fact tables would add the med group FK. While the FK on the intervention/med fact is a little redundant, it doesn't cost much to add it and adds additional analysis functionality that may be useful.

Thank you ngalemmo

I have received the "The Kimball Group Reader" book today ! ;-) Theres is alot of articles in that book that are gone help me !

But this forum is also a great place to have information on specific situation ;-)

thanks again and have a great day ;-)

Richard

That one is new... I checked it out on Amazon, it looks interesting. I like the picture they have of Ralph...