Multifact query challenges

[Annette]

Does anybody else have difficulty dealing with multifact queries?Ã,  Our data is in a star schema, and I published a package with two facts and some conformed dimensions.Ã,  One of the facts is "sales", the other is "returns".Ã,  It is possible when querying for both sales and returns that one of the other will be NULL, in which case the calculation ("sales" - "returns") is also NULL.Ã,  I have to use an if-then-else or a case statement with four different states in order to get the correct answer.Ã,  Creating a calculation that sets anything that's NULL or MISSING to 0 does not work---if it did then I would do the final calculation using these intermediary ones.Ã, 

We run on UNIX and Sybase, and have problems that you who run on Windows and/or SQL Server do not.Ã,  Is this one of those problems?Ã,  If you all have the same experience regardless of platform and db, what do you do to simplify such calculations for authors?

[Darek]

How did you try to overwrite the NULL/MISSING value? It should work properly most of the time.

[Annette]

I use this statement to try to turn a NULL to zero:

Ã,  Ã,  Ã,  Ã,  Ã,  Ã, If ([fact_measure1] is missing) then (0) else ([fact_measure1])

I have also tried "is null" instead of "is missing".Ã,  Neither of these work.Ã,  Ã, Strangely, the following calculation works when trying to produce a difference where the second element could be NULL:Ã, 

Ã,  Ã,  Ã,  Ã,  Ã,  If ([fact_measure2] is missing) then ([fact_measure1]) else ([fact_measure1] - [fact_measure2])

[cognosfreelancer]

If your underlying database is Oracle why don't you try to modify all metrics in the two fact tables Ã, on the following lines

nvl(metric1,0)

fact measure1 may not be null, it may be a blank string. Create a check for that.

NKT

[cognosguru]

Hi Annette,

We ran into the same problem on AIX & DB2.Ã,  However, I belive it is not the problem of the environment.Ã,  The word from Cognos support is that it is a BUG.Ã,  The work around that we did is to build a 1 Fact table with both returns and sales information, called Sales_Returns Fact (very original).Ã,  We had a left outer join between the Sales Fact and Returns Fact to create the Sales_Returns Fact.Ã,  Sales_Returns in our case was a Model Query Subject.Ã, 

Then we joined that to the confomed dimensions and it works fine.Ã, 

Good luck.Ã,  I hope this helps in at least telling you you are not alone :)

[Annette]

Thanks for letting me know I'm not alone.Ã,  Just to confirm:Ã,  your Sales_Return fact was done as a Model Query Subject, and not as a table in the data warehouse, correct?Ã,  If so, did you find performance on that package to be noticably slower?