Native SQL vs IBM Cognos SQL

[bi4business]

Hi All,

We are using a DQM connection and Framework Model.
When we build a Report Studio and go to "Generate SQL" I can see the filter object in the IBM Cognos SQL but in the Native we don't see the filter objects.

What is the BI Suite pushing to the database and why don't I see the filter object in the Native SQL?
On the properties off the Query we have set the "Use Local Cache" to "No"

Kind Regards,

BI4BUSINESS

[charon]

Hi bi4bus,

im not quite sure, but as far as i remember cognos report studio wont deliver Sql but mdx once you have set the dqm active?! Something i have experienced some months ago (in combination with several "features" of dqm) which drove me away for now from using dqm :S

Again, im not sure, you might wanen check the generated code.
regards
charon

[bi4business]

Hi Charon,

Thanks for your reply.
There's only one option to look at the SQL/MDX, but your right I should post the option "Generate SQL/MDX"  ;D

But still I am expecting to read some kind off filter objects, now it looks like Cognos is collecting the complete fact table and not a subset because I can't any filter objects.
In our environment we only have DQM connections and I think IBM Cognos is going to DQM in the future ......

I also "experience" some DQM features but we have to use this connection.

BI4BUSINESS

[MFGF]

Hi bi4bus,

im not quite sure, but as far as i remember cognos report studio wont deliver Sql but mdx once you have set the dqm active?! Something i have experienced some months ago (in combination with several "features" of dqm) which drove me away for now from using dqm :S

Again, im not sure, you might wanen check the generated code.
regards
charon

This is only if you are using a DMR package (in which case you can see the generated SQL if you use Dynamic Query Analyzer). If you are using a relational package you will see SQL.

My guess is that you are doing other complex stuff in your report that prevents a single query from being able to deliver the aggregations as well as the other stuff. It's hard to say for sure, though, since we don't know what else your report is doing.

Cheers!

MF.

[bi4business]

Hi MFGF,

Sorry to say but the it's a simple list report with no calculations or other complex stuff.
We only drag/drop the FM object into a list and put some hardcoded filters in the report.

I don't understand why I don't see the filter objects in the Native SQL.
We don't use DMR (yet)

BI4BUSINESS

[charon]

Hi BI4B,

when you are talking about "hardcoded filters in the report", are you building detailfilters in the query or do you build up own sql or data items with some filterlogic?
In case you are NOT using detailfilter, could the issue be local processing?

Could you deliver some more information about the report spec bi4b?
regards
charon

@MFGF: thanks for the insight mr. muppet, i didnt know that  :o

[bi4business]

Hi All,

Thanks for reply and trying to help me out here.

Situation for me: I haven't build the Framework model and had no access to look into this Framework model.
When I asked to get it for analysing it looked oke to me.
Joins ect. looked ok, but what I did not checked was how the tables where imported  :P (yes my mistake.....)

What I see now is the DIM_DATE is imported from an Oracle database and all the other tables are from a Netezza database.
Yes I know technical it's posible, but Cognos is loading hole tables and is joining this on the BI Server, not on the RDBMS ......

That's why I don't see any filters in the SQL, and that's why this report takes long to run.  ;D

Small detail: there's a same DIM_DATE table on the Netezza  :P

Issue solved!

Thanks all.

[charon]

Ty for sharing the Solution  ;D