DatawareHouse modeling (factless table)

View previous topic View next topic Go down

DatawareHouse modeling (factless table)

Post  marric on Mon Mar 15, 2010 10:26 am

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

marric

Posts : 8
Join date : 2010-02-24

View user profile

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  ngalemmo on Mon Mar 15, 2010 11:54 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  marric on Mon Mar 15, 2010 1:24 pm

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 DimIntervention1:N FactLessIntervention_Medication N:1DimMedication
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 DimIntervention1:N FactLessIntervention_Medication N:1DimMedication
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

marric

Posts : 8
Join date : 2010-02-24

View user profile

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  ngalemmo on Mon Mar 15, 2010 1:34 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  marric on Mon Mar 15, 2010 1:47 pm

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

marric

Posts : 8
Join date : 2010-02-24

View user profile

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  ngalemmo on Mon Mar 15, 2010 2:42 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  marric on Wed Mar 17, 2010 8:58 am

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

marric

Posts : 8
Join date : 2010-02-24

View user profile

Back to top Go down

Re: DatawareHouse modeling (factless table)

Post  ngalemmo on Wed Mar 17, 2010 12:34 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: DatawareHouse modeling (factless 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