Determinant & Multi fact query

[Darsidious]

Hello.

I'm getting back into Cognos 8 after so many years so basically it's like I'm new ...

I'm currently dealing with a problem within Framework Manager.  Let's define my example with very basic stuff.

I have two dimensions -> Accounts and Products.
I have two fact tables -> Balance and Counter.

My Balance fact table have keys to Accounts and Products while Counter fact table only have a key pointing to Accounts.

Everything should be configured properly; relationships between the Dimensions & Facts and unique key determinant at the dimension level, no determinant within the fact.  There's no relationship between the two fact tables as from my understanding stitched query should be issued considering that there using the same dimensions.

Now, within Report Studio:
1- If I create a basic report with only the two measure; everything is fine.
2- I add the Account # which is within the two fact, again, so far so good.
3- If I then add Product # after the Account # which only belongs to the Balance fact, I get the following error:
"The option to allow local processing has not been enabled.UDA-SQL-0462 The OLAP function 'sum' ...."
I don't want to enable local processing as we prefer to only use Database only option.

4- If I start over, drag the measure from the two facts tables and then only drop the Product #, it works...  I'm kind of confused.  It shows the balance measure per product and always the same figure (total) for my measure of the other Fact table.

Any idea of what I'm doing wrong in Framework Manager?
Thanks for the help!

P.s. If I activate the Limited Local processing, it works as expected.  However I've read on the net that it should work either way...  so if any genius here, I'd really aprpeciate any hints :(

[blom0344]

Ideally your facts should just contain keys and measures. The Account# should originate from the dimension.  Not allowing local processing may be problematic, since Cognos may need to stitch on the server due to some elements in the report queries.  (not all functionality can be used in the DB) For the last effect you describe..  Did you check how Cognos composes the SQL?

[Darsidious]

Hello Blom0344

The account # does comes from the dimension.  Both of my fact tables contains only keys and measures.

You're absolutely right about the "Limited Local" query processing option; guess I didn't read properly what was the meaning of this.  Therefore I'll stick to it.

Thanks for the help.

[sir_jeroen]

You're probably using an Oracle DB 9g of 10g. One of the restrictions with using an Oracle 9/10 DB is that by default full outer joins aren't allowed. This is a result of a bug in Oracle.
When you have a Multi fact query Cognos will stitch them together by using Full Outer Join. Because this isn't allowed, Cognos will do the FOJ using local processing.
What you can try is to edit the file <Install>\Bin\COGDMOR.ini when you goto line 153 you will see: "Full_Outer=F" you could try setting this to  "Full_Outer=T" (this might not be a supported option). Let us know if this worked.

[Darsidious]

Hello ReportNet Addict,

No, I'm under SQL Server 2005.  Changing the INI file is not an option here.  But in any case, my problem is fixed by enabling the Limited Local query processing option (didn't had the proper understanding of that option).

Thanks.

[sir_jeroen]

If local processing is the only way to do this, then you can use it. But in my opinion this is an option you must try to prevent if you can.

An nice example of local processing is when you have an Oracle database and you add the following filter: [Query Item] <> '' to a report.
If you trace this query you will see that an SQL like 'SELECT [Query Item] from TABLE_X' is passed to the database (Database processing) and after the results from the DB have returned local processing will take place with the statement "SELECT [Query Item] from TABLE_X where [Query Item]<>'". So you will get a FULL table dump from the DB first and then Cognos server will filter out the  '' records. If you rewrite this query you will have the rows filtered out by the DB and no local processing will take place...

[blom0344]

Untill you run into a requirement that involves running aggregates or summary filters etc. Basically stuff that is handled very nicely on the server..

The query behavior described reminds me on issues we had with query subjects that used pass-through query. Query filters where ignored and full sets used on the server leading to memory/performance issues