Unexpected Results with Stichted Queries

[charon]

HI Guys,

happy new year, may u all have a wonderfull and succesfull 2013  ;D

Im sry to bother you guys so fresh in the new year, but i am facing some unexpected results with my package, and the reason might be my FM-Model.
I have chosen the title because i know there are some "specialities" working with stichted queries, and that there is a IBM Paper out there ("IBM Cognos Proven Practices: IBM Cognos BI - Unexpected Results When Performing MultiFact Queries (Stitch Queries)") which i have read several times bevor i decided 2 bother you with.

Imagine followin model.

2 Fact tables, both aggregated, both share 5 dimensions (like time, region and so on)...but Fact Table Nr. 2 has also a 6th dimension.
The Joins are in the Data Layer, there are also a Business Layer (for business logic and calculations) and a Dimensional Layer.
The issue im challenged with is, i want some facts of Fact Table 2 AND Fact Table 1 for a new calculation in Framework Manager, e.g. [Fact_table2].[Fact_a] + [Fact_table2].[Fact_b] + [Fact_table1].[Fact_c]

And the results are totaly wrong, i cant even find a pattern in it. Sometimes the resulting, calculated fact is way to high, sometimes even negative. But never correct...
After reading the paper, ive tried a standalone calculations with some coalesce - functions, but it didnt help.
Ive tried
coalesce ([Fact_table2].[Fact_a];0) + coalesce([Fact_table2].[Fact_b];0) + coalesce([Fact_table1].[Fact_c];0)

So, either
1) im Doing it wrong
2) or there are further things to consider

I know the best way might be to calculate these new facts in ETl (we are using Data Stage) either way, and this will be executed in the next 2 months or so (the etl-jobs and uc4 job environments are quite complex, cant build and deploy new fact(tables) in a short time).
But in the meantime i do need correct results...also, i really want to know why framework Manager behaves the way it does.

Any ideas are welcome, thx a lot in advanced
cheerz charon :P

[blom0344]

Do you have any chance to verify how the SQL generated is used against the database? In the ideal case a single statement would be generated involving full outer joins and coalesce functions provided the correct determinants are set on the dimension query subjects in the layer that contains the joins

[charon]

Hi Blom,

thx for the reply.
Let me be honest, im not quite sure what exactly you are asking. Of course i have the possibility to check the generated SQL of both Fact Tables in my Framework Manager, both Cognos SQL and native SQL as well as you know yourself.

So what do you mean with "is used against the database"? (DB is a oracle 10 btw).

The SQL holds an full outer join, naturaly because of the stitched queries, and i have tried the coalesce functions and also have implemented determinants in my FM modell on the layer where the joins are made.

The issue still remains...
Maybe i dont understand your approach, so in that case it would be awesome if you could get into more details pls.

Thx and greets
charon :P

[MFGF]

Hi charon,

Without seeing your model and/or the generated stitch query, it's impossible to give you a definite answer as to what's wrong. If I was a betting man, I would wager that you are missing determinants in one or more of your conformed dimensions somewhere. Are the two fact tables held at the same level of granularity across all the conformed dimensions?

Cheers!

MF.

[blom0344]

I do not rely on the proposed SQL from FM. With SQL Server it is relatively easy to run traces to check the actual execution path. With Oracle you will probably need a friendly DBA..

[charon]

Hi All,


ive tried several options now, remodelled it, traced the sql..but i have still noe clue whats the problem. But, (also because of performance issues) ive decided to create a new joined fact table and model a simple star scheme to be safe.

That way it should work :) And maybe one day il find the error in my fm modell.
Thx to all for all ideas and have a nice day
cheerz

[blom0344]

Okay, but that would suggest that the granularity of both facts where the same to start with.. ?

One last suggestion: what does the multifact model do when you do NOT define calculations within the model, but bring in the seperate measures in Report Studio from both facts?

[charon]

Hi blom,

i like your attitude..never giving up  ;D

1) granularity of the fact tables-> not in the original data sources, but ive created fact table F_Fact_1 by aggregating on month level for 3 fact tables, the secont fact table F_FACT_2 is also aggregated on month, but also has some dimensions and therefore keys more/ Different then the F_FACT_1.

2) On report Studio level it works. Had to use the whole reference path and set the calculations to "calculated" aggregation, but yea, it works. But this wont do the trick, because the key users want to work with BIA (or workspace advanced now) and therefore they want modeled calculations and facts ready to go...

Again, its not urgend (anymore), ive communicated the new way and as long this will work its ok. still i would like to know why its so hard for Frame Work Manager to work with these stichted queries...and why the solution with the coalesce (i think there has to be the problem, dont aggregating correctly over the fact tables because of some null values) didnt work.

So, if you have any further ideas your quite welcome :)
cheerz

[MFGF]

Did you take a look at the determinants? It sounds to me like these are missing or incorrectly defined on your dimensions.

Cheers!

MF.

[charon]

Hi MG,

ive thought so to. Therefore, i considered determinants and even "distincts" in the data layer selects in FM.
But even after double checking the determinants the results still didnt work out. =/

Of course i might have errors in my logik of the fm, wronly set determinants would explain it all...

[CognosPaul]

Manually writing SQL in the data layer may cause issues. Also check if you have any loops by using the same dimensions for both fact tables. To solve issues like this I'd start small, create a model containing only the fact tables and work upwards in increasing complexity.

It sounds like you're using a proto-ETL to populate a new fact table. That may be the best solution anyway.

Also, Charon, I'd like to remind you of your post here.

[charon]

Hi Paul,
i should have created the modell the way you suggested, but im a very impatient and" fast apm mouse clicker"... thx  =)
But the etl -solution will solve the issue i think.

Also, link you have posted does not work for me. Is there a good opr bad thing hiding for me? =D

[CognosPaul]

whoops, try the link again. 

[charon]

Well...i almost forgot.
'thank you' paul for remembering me  :P

alright, prepare for a genius painting in 2 colors during the weekend  :o