coverage fact less fact table

View previous topic View next topic Go down

coverage fact less fact table

Post  gb on Tue May 15, 2012 4:42 pm

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

gb

Posts : 1
Join date : 2012-05-15

View user profile

Back to top Go down

Re: coverage fact less fact table

Post  hang on Tue May 15, 2012 6:00 pm

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

View user profile

Back to top Go down

Re: coverage fact less fact table

Post  ngalemmo on Tue May 15, 2012 8:46 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: coverage fact less fact table

Post  Mike Honey on Mon May 21, 2012 6:51 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: coverage fact less fact 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