Unnecessary joins after converting Cognos Impromptu Catalogs to FM

[bonniehsueh]

Reports are experiencing slow performance. The FM model was built by converting impromptu catalogs, with no other modeling. The SQL from a report shows that its pulling in joins to tables that are not relevant.

For example:
When report is built in Cognos impromptu, the sql is short and concise.
select retailer_name, order_method
where table1.order_method_number=table2.ordermethod_number

When built in Report Studio:
select retailer_name, order_method
where table1.order_method_number=table2.ordermethod_number
AND
joins from all other tables that table1 is joined to that uses the same key.

I double checked the GO Sales and Retailers and confirmed that joins from other tables are not part of the sql.

Set the Usage of columns in the tables that are being used (ie Changed FACTS & identifiers to attributes as necessary) 

The Query Subject they are using consists of data items from a few tables, so I created a separate query subjects for each table

I suspect the performance issue is because its joining to other tables that are not needed. Any ideas? Is there a step when converting from Impromptu Catalogs that would prevent this issue?

[blom0344]

We have experienced this as a major bug and brought it Cognos' attention.
There is a setting under options that indicates either 'minimized SQL' or 'View'. In most cases the minimized SQL option does not work properly and unneeded joins are produced.
Did you check on this setting? Does it make any difference with your model?

[bonniehsueh]

Thanks for your reply Blom. I did check and the property is was set as minimized sql.

The problem ended up being that the report was joining 2 dimensions on the reports with no fact so the dimensions didn't know which FACT to join through therefore pulled ran all the joins to the FACT that its connected to. We had to do two things 1) Pull a column from the fact into the query 2) Select the column from the fact in the properties of all the report objects (Lists) displayed on the report page.

We had to remove a chart in the report because there isn't a properties feature to render data not displayed in the chart. I tried to set the column in the legend because you can associate a query there, but it didn't work. Also unlocked the report to put the column in the xy axis area and tried to hide the object.

Any thoughts on how to render a data item on a chart without making it visible would be good to hear. 

Thanks

[blom0344]

Well, I guess the lesson to be learned here is that Cognos does require you to model into a starschema (even if no real physical one exists).
Just porting everything from version 7 to 8 is not enough.

Did you try to associate the data item with the page?

[bonniehsueh]

I did try that actaully, however I kept on getting a error about the master and detail link is not working type of error. I haven't worked very much with setting up master detail relationships so I'm thinking I may be missing a step here...