Is it a good design to allow zeros in Fact table?

View previous topic View next topic Go down

Is it a good design to allow zeros in Fact table?

Post  mobzam on Wed Jul 27, 2011 6:03 pm

Hi people,
is it a good design to allow zeros in Fact table? I am developing a cube but each transaction has three option of which each can have a zero or a number. But these three options are fact values. How can I treat this?

mobzam

Posts : 9
Join date : 2011-07-27

View user profile

Back to top Go down

Re: Is it a good design to allow zeros in Fact table?

Post  ngalemmo on Wed Jul 27, 2011 8:53 pm

Zero valued measures are fine. Foreign keys that do not point to a dimension row are not. Can you elaborate on your problem?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Is it a good design to allow zeros in Fact table?

Post  hang on Wed Jul 27, 2011 9:40 pm

Zero works well with sum, however if your fact aggregation involves avg or count, null value may give you more desireble result then zero, depending your business requirements. Just bear in mind that null value is acceptable for measures, but not acceptable for dimension keys as suggested by negalmmo.

hang

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

View user profile

Back to top Go down

Re: Is it a good design to allow zeros in Fact table?

Post  mobzam on Fri Jul 29, 2011 3:46 pm

ngalemmo wrote:Zero valued measures are fine. Foreign keys that do not point to a dimension row are not. Can you elaborate on your problem?

My situation is as follows: I am trying to analyse the performance of adverts. I am getting the details of an advert such as clicks, impressions, then number of responses pertaining to the advert, number of orders pertaining to the same advert. Then cost. So I am trying to model this in a dimensional model. I have number impressions, clicks, responses and orders as measures. Now I am worried where there zero orders. How can I solve this problem.

Thanks in advance

mobzam

Posts : 9
Join date : 2011-07-27

View user profile

Back to top Go down

Re: Is it a good design to allow zeros in Fact table?

Post  mobzam on Mon Aug 01, 2011 7:00 pm

any help please

mobzam

Posts : 9
Join date : 2011-07-27

View user profile

Back to top Go down

Re: Is it a good design to allow zeros in Fact table?

Post  ngalemmo on Mon Aug 01, 2011 7:24 pm

Other than the fact that the ads are not working, why would you worry about zero orders? If it is a divide by zero problem (such as infinite cost per order), you deal with it in the query using a column expression to check for zero.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Is it a good design to allow zeros in Fact table?

Post  mobzam on Tue Aug 02, 2011 8:33 am

OK Thanks

mobzam

Posts : 9
Join date : 2011-07-27

View user profile

Back to top Go down

Re: Is it a good design to allow zeros in 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