reduce complexity of generated SQL

[blom0344]

We notice that Cognos has a tendency to generate too complex SQL code when running very basic queries.
Example: Select just 2 objects from a package in report studio that belong to the same physical table. In many cases the SQL is not reduced to a simple select ... from table but joins are introduced to related tables.

We're wondering whether this is a version 8.1 bug or that we are missing some sort of setting within the framework (and resulting package)

Ties Blom

 

[drlex]

Ties,
Are you sure that Cognos is not following the correct meta-data path for the structure?

For example, if 'physical' Table A has fields 1 and 2, yet in your package they are separate tables (X and Y) with a direct join, then a query against 1 and 2 will show SQL with a self-join on Table A. Additionally, if X and Y are only linked via an intermediate table Z (physical table B), then a query on 1 and 2 will reference table B as well as A.

soi la, soi carré

 

[blom0344]

No , we are really talking about a bug. I am following a fasttrack course this week and managed to reproduce the same kind of behavior under the nose of my Cognos instructor (who was really baffled)

I created a new query item by merging an order header and a order detail table. Then select just the ordernumber from within reportstudio which netted:

SELECT DISTINCT  ...........ORDER_NUMBER AS ORDER_NUMBER FROM
ORDERHEADER 
INNER JOIN
ORDERDETAIL 
ON ORDERHEADER.ORDER_NUMBER = ORDERDETAIL.ORDER_NUMBER

Now with SQL set at minimized you would expect a select from 1 table, not a join strategy.

Ties Blom

 

[drlex]

Ties,
Ah, OK - gotcha. On what you've just posted, that is indeed not as simple as logic suggests it ought to be. The details of your first post suggested my possible explanation. I'm not a report studio user, so I'm unaware of the steps, but I wonder whether it is a legacy of 'merging an order header and a order detail table'.

soi la, soi carré

 

[blom0344]

Possibly. When re-modeling a truly relational schema into a virtual starschema one of the steps is to create factobjects and dimensional ones. Merging header and detail tables into one factentity is common practice.
However, simply selecting the order_number from the fact should result in a minimized SQL. That does not work properly..

Ties Blom

 

[mfgf]

Hi,

I just tested this using Cognos 8 MR2. I imported the ORDER_HEADER and ORDER_DETAILS tables from the gosales database into Framework Manager, then created a new model query subject to merge these into a single entity. I then published this in a package, and in Report Studio I brought in just the ORDER_NUMBER query item (from the model query subject). The SQL generated was:

select
ORDER_HEADER.ORDER_NUMBER as ORDER_NUMBER
from
gosales.GOSL.dbRDER_HEADER ORDER_HEADER

which does seem to be nicely minimized. If it is a bug, then it's definitely not present in the MR2 build.

One thought - when you merged the original query subjects, did you do this by creating a model query subject bringing in items from each data query subject (as I did), or did you code a new data query subject which joined the header and detail tables in the SQL? If it's the latter, then I'd expect the Cognos Query Engine to honour the join in all circumstances and produce the result you are seeing.

Regards,

MF.

 

[blom0344]

That's amazing. The instructor I received the fasttrack from was really puzzled , so he create a new framework and only imported the order header and order detail table. The resulting SQL on only the order_number was as neat as can be , just a select from a table.
So ,his venture was EXACTLY the same exercise you did and almost at the same time! What a coincidence!
We actually did a lot in the fasttrack course and I am very tired now, but we certainly did not code anything , whe just merged the header and details and deleted the redundant order_number from the result.


Ties Blom

 

[mfgf]

Ah, but did you do anything extra like adding calculations or filters to either of the underlying query subjects before you merged?

Just a thought...

MF.

PS - The moral of the story here could be that to get the expected results from C8, just have a Cognos instructor on hand to perform the exercise for you. Guaranteed magic success every time!

 

[blom0344]

No, we just took objects from one of the demo frameworks.
Anyway, we have confirmation from Cognos that this is indeed a bug and that it will be fixed..

Ties Blom