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

[mobzam]

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?

[ngalemmo]

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

[hang]

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.

[mobzam]

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]

any help please

[ngalemmo]

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.

[mobzam]

OK Thanks

[Sponsored content]