Multi fact query and NULL

[Deep750]

Hi,
I have a issue at a client, where they do some reporting them selves, usually using WSA.
The issue comes when they do multi fact calculations, where there are NULL-values, in either fact.
How can I model this in Framework manager, so that users can do calculations, without having to deal with NULL-values?
I have tried using COALESCE, IF, CASE and NVL, but none of them works, as I guess the functions is used before the queries are joined, instead of after.

My case is that the users should get following result
10 -  NULL = 10
instead of
10 - NULL = NULL

We are using Oracle-DB, the model is relational CQM.

[MFGF]

Hi,
I have a issue at a client, where they do some reporting them selves, usually using WSA.
The issue comes when they do multi fact calculations, where there are NULL-values, in either fact.
How can I model this in Framework manager, so that users can do calculations, without having to deal with NULL-values?
I have tried using COALESCE, IF, CASE and NVL, but none of them works, as I guess the functions is used before the queries are joined, instead of after.

My case is that the users should get following result
10 -  NULL = 10
instead of
10 - NULL = NULL

We are using Oracle-DB, the model is relational CQM.

Hi,

It's not clear from your post whether there are rows stored in each fact table with null measures, or whether the nulls are being generated as a facet of using the full outer join (ie there are no rows in one fact table that correspond to a row in the other).

If the former, you can use calculations in the query subjects for each fact to replace the null measures with zero (if that's what you really need to do).
If the latter, than a null result is the correct answer - if there are no fact rows in one fact table used in the stitch, then returning a null result is the right thing to do. Consider the logic of this - eg I have sales of a product in one fact table but no target for the product in the other. If I create a calculation that compares sales to target, how can any number other than null be the correct outcome (since there is no target)?

MF.

[Deep750]

Thank you for your answer, MFGF.

Scenario two is correct, that nulls are facets of the full outer join.
I agree, that it is correct the way it is handled, but can it be manipulated, so that users can do simple calculations, like budget minus actual?

I guess I can use a UNION with the fact-tables. Will that work?
Are there any other solutions to gain the goal of end users (self service) can do calculations?

[MFGF]

Thank you for your answer, MFGF.

Scenario two is correct, that nulls are facets of the full outer join.
I agree, that it is correct the way it is handled, but can it be manipulated, so that users can do simple calculations, like budget minus actual?

I guess I can use a UNION with the fact-tables. Will that work?
Are there any other solutions to gain the goal of end users (self service) can do calculations?

Hi,

If there are no rows in one fact that correspond to rows in the other, then any calculation (minus etc) will only ever return null. There are no ways around this I know in Framework Manager since the issue isn't a model issue - it's data that just physically isn't there.

If you think about it, budget minus actual makes no sense if a budget doesn't physically exist. I would be pushing this conundrum back to the users, advising that to make it work, they need to enter appropriate data - ie enter a budget of zero, rather than not entering a budget at all.

Cheers!

MF.

[bdbits]

If this is a data warehouse being populated by ETL, you might be able to have your ETL guys populate "missing" rows with 0s for budgets. If that makes sense, which it very well might not, but I thought I would throw the idea out there.