coverage fact less fact table
4 posters
Page 1 of 1
coverage fact less fact table
Hi there,
I have a huge problem that I can’t overcome for several weeks now. I have to create cube (which I did, that shows survey usage (question and answer) across hundred hospitals where many to many relationship dominates (survey have many questions and question belongs to many surveys, same with answers). Since it is many to many relationships across a cube I created separate dimensions: (DimSurvey, DimQuestion, DimAnswer, Time, DimAnwerTime). However they want to see questions and answers for the given survey and given month even if there are no answers for that month. I learned from “The Data Warehouse Toolkit” book that “coverage fact less fact table” are used to show no data. My problem is that I have over 350,000 records (answers across all hospitals) in fact table and in order to create fact less table, I need to include each date with each (Survey –Question-Answer) combination. Even if I use only month key (120 records for 10 years) my fact less table will blow to over 40 million records.
My additional challenge is that they want to show date and time of each answer ( that is DimAnwerTime table with only two columns Key and date and time stump ).
I’m really desperate and any help would be greatly appreciated.
Thank you very much!
gb
I have a huge problem that I can’t overcome for several weeks now. I have to create cube (which I did, that shows survey usage (question and answer) across hundred hospitals where many to many relationship dominates (survey have many questions and question belongs to many surveys, same with answers). Since it is many to many relationships across a cube I created separate dimensions: (DimSurvey, DimQuestion, DimAnswer, Time, DimAnwerTime). However they want to see questions and answers for the given survey and given month even if there are no answers for that month. I learned from “The Data Warehouse Toolkit” book that “coverage fact less fact table” are used to show no data. My problem is that I have over 350,000 records (answers across all hospitals) in fact table and in order to create fact less table, I need to include each date with each (Survey –Question-Answer) combination. Even if I use only month key (120 records for 10 years) my fact less table will blow to over 40 million records.
My additional challenge is that they want to show date and time of each answer ( that is DimAnwerTime table with only two columns Key and date and time stump ).
I’m really desperate and any help would be greatly appreciated.
Thank you very much!
gb
gb- Posts : 1
Join date : 2012-05-15
Re: coverage fact less fact table
You should partition the coverage fact table, and possibly cube as well, by month. 40M is a very moderate size for a fact table in DW.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: coverage fact less fact table
I don't get the emphasis on many to many... fact tables by their very nature represent many to many relationships if you take those relationships out of context.
Break the problem down... you have surveys that have questions. Create a fact to represent that. Surveys have a lifespan. You can either handle that as effective date ranges in the same fact table or have another fact that enumerates the survey lifespan. Either way, you are not going to wind up anywhere near the numbers you are estimating... by many orders of magnitude.
Break the problem down... you have surveys that have questions. Create a fact to represent that. Surveys have a lifespan. You can either handle that as effective date ranges in the same fact table or have another fact that enumerates the survey lifespan. Either way, you are not going to wind up anywhere near the numbers you are estimating... by many orders of magnitude.
Re: coverage fact less fact table
Hi gb,
You may be able to meet the requirement to show all months using your Query tool?
If you are coding MDX, it might be as simple as removing the NON EMPTY clause for your Month attribute.
If you are using Excel, within Pivot Table Options / Display, you can choose to "Show items with no data on rows" or columns (which removes the NON EMPTY clause in the MDX it generates).
For the requirement to show date and time, I'd probably get the date portion via your standard Date dimension, and build a Time of Day dimension which goes down to minute grain. Time of Day can then have useful aggregate levels e.g. hour, business hours, morning / afternoon / evening etc as required.
Good luck!
Mike
You may be able to meet the requirement to show all months using your Query tool?
If you are coding MDX, it might be as simple as removing the NON EMPTY clause for your Month attribute.
If you are using Excel, within Pivot Table Options / Display, you can choose to "Show items with no data on rows" or columns (which removes the NON EMPTY clause in the MDX it generates).
For the requirement to show date and time, I'd probably get the date portion via your standard Date dimension, and build a Time of Day dimension which goes down to minute grain. Time of Day can then have useful aggregate levels e.g. hour, business hours, morning / afternoon / evening etc as required.
Good luck!
Mike
Similar topics
» Help designing star schema
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum