Fact table design - Showing something that did not occur

View previous topic View next topic Go down

Fact table design - Showing something that did not occur

Post  marric01 on Thu Dec 09, 2010 5:42 pm

Hi everyone,

I got a question about how to handle a situation with a user need.

I have created a fact table factClassesStudentAttended. Those classes are for paramedic. The user wanted to know wich student took what classes and what was the evaluation for a specific classes. This is working great and the people analysing the data are please with that. They can filter and group by date, by type of paramedic, ...., ....

But now, user want to know something different .... they want to know all the student who did not participate to a specific classes.

Should I build a specific fact table for that scenario, like factClassesStudentNotAttended ?

I'm using SSIS 2005 for ETL, SSAS 2005 for Olap and Radar-Soft Web Control to navigate in the Olap cube.

*sorry for the bad english*

Richard

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  ngalemmo on Thu Dec 09, 2010 7:29 pm

Should I build a specific fact table for that scenario, like factClassesStudentNotAttended ?

Well, sort of... I would create a fact table listing courses someone is required to take, assuming you have such info available from you HR system. Have a process that updates these fact when a student completes a course. It would then be easy enough to see who hasn't competed a course they are supposed to take.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  marric01 on Thu Dec 09, 2010 9:36 pm

ngalemmo wrote: assuming you have such info available from you HR system. Have a process that updates these fact when a student completes a course. It would then be easy enough to see who hasn't competed a course they are supposed to take.

Thanks for the input ... The source system doesnt have that info on who should follow what specific courses. They only have a listing of paramedic (wich is a dimension in my DW) and those are listed in courses when they are attending.

Now, while I was writing those lines, I was thinking : can it make sense to combine both fact table in one ? having a boolean value indicating if the parademic guy was registered or not. The impact could be that for each classes, i'm always gone have all the students ... but is this can be a real issue ? Do you see any problem with that scenario ?

Thanks

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  ngalemmo on Fri Dec 10, 2010 1:01 pm

Yes, it would be problematic to have a table that has everybody in it every time there is a course. I don't know how many people you are talking about, but regardless, it is a bad idea.

You could resolve the issue with a two pass query. You would select a list of all paramedics MINUS the list of parametics who took a particular course.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  marric01 on Fri Dec 10, 2010 1:11 pm

ngalemmo wrote:Yes, it would be problematic to have a table that has everybody in it every time there is a course. I don't know how many people you are talking about, but regardless, it is a bad idea.

You could resolve the issue with a two pass query. You would select a list of all paramedics MINUS the list of parametics who took a particular course.

Yes ... you are right ... I prefer to have a separeted fact table. The number of student and classes wont be an issue because the student are the paramedic and we dont have that much student. Im talking around 1000 in the last 5 years for around 20 classes.

For the ETL, its gone pretty easy to fill that fact table ...

Thanks again for the input.

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  hang on Fri Dec 10, 2010 5:04 pm

The simple solution suggested by Kimball is, in your case, adding an attendance count column in your attendance fact table and set it to 1 for attendance and 0 for non-attendance. Every time when you load attendance fact, add explicit rows for what didn't happen, so the attendance is true fact table rather than factless as the attendance fact would equal either 1 or 0.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  marric01 on Fri Dec 10, 2010 5:32 pm

hang wrote:The simple solution suggested by Kimball is, in your case, adding an attendance count column in your attendance fact table and set it to 1 for attendance and 0 for non-attendance. Every time when you load attendance fact, add explicit rows for what didn't happen, so the attendance is true fact table rather than factless as the attendance fact would equal either 1 or 0.

Thanks hang for the input.

From a developer point of view ... I say that your solution is less complicated to implement for the TI but could be more confusing for the Business user when it comes to query the Olap cube ???

In other hand. The solution provided by ngalemmo could be "longer" to implement for the TI but could be less confusing when it comes to query the olap cube ??

haha, now i'm confused on wich solution do choose ... I think that I'm gone think about that while getting a nice cold beer ;-)

But I would love to hear about other member of the forum on what they think ;-)

Thanks again and have a great weekend

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Fact table design - Showing something that did not occur

Post  hang on Fri Dec 10, 2010 5:55 pm

It's Kimball's solution. p251, The Complete Guide to Dimensional Modeling, Second Edition.

I think the Attendance count column is excellent idea, one stone two birds. Actually, Kimball even suggested adding an explicit factual column and default the value to 1 for all factless fact table so that you should always use sum on the numeric field instead of count(*), which gives you more flexibility.

With OLAP cube if it's SSAS, ignore the the count measure generated by system, instead using the explicit measure column gives you more analytic possibilities. You need to define a new calculated measure, maybe called absence, based on the attendance count value. Or you can always have a logical field in a view to cater for both OLAP cube and other reporting requirements.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Fact table design - Showing something that did not occur

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