2

I'm using Cognos 10.1 and I have a report that uses two queries each with the same primary key.

Query 1: UniqueIds
Query 2: DetailedInfo

I'm not sure how to tell whether it's better build a report using the DetailedInfo query with a filter that says PrimaryKey in (UniqueIds.PrimaryKey) or should I create a third query that joins UniqueIds to DetailedInfo on PrimaryKey.

I'm new to Cognos and I'm learning to think differently. Using MicroSoft SQL Server I'd just use an inner join.

So my question is, in Cognos 10.1 which way is better and how can tell what the performance differences are?

Michael Riley - AKA Gunny
  • 5,058
  • 4
  • 38
  • 78
  • 2
    Cognos is just going to generate SQL, it's not going to do anything radical. And an inner join and in statement serve very different purposes. – Andrew Sep 16 '15 at 02:21

3 Answers3

3

You'd better start from the beginning. You queries (I hope Query Subjects) should be joined in Framework Manager, in a model. Then you can easily filter second query by applying filters to first query. Joins in Report Studio is the last solution.

Alexey Baturin
  • 1,163
  • 1
  • 7
  • 11
  • There is NO WAY you can possible anticipate every join you might need and set them up within framework manager. The bottom line is I'm in Report Studio and I have two choices: 1 a Join 2: A filter that uses an IN clause. – Michael Riley - AKA Gunny Sep 16 '15 at 11:22
  • 1
    So try both options. You can see generated SQL and choose best choice for you. – Alexey Baturin Sep 16 '15 at 16:10
  • 1
    There is no Cognos special processing for simple cases. Just a translation from visual representation to SQL queries. – Alexey Baturin Sep 16 '15 at 16:12
2

The report writers ultimate weapon is a well indexed data warehouse, with a solid framework model built on top.

You want all of your filtering and joining to happen on the database side as much as possible. If not, then large data sets are brought over to the Cognos server before they are joined and filtered by Cognos.

The more work that happens on the database, the faster your reports will be. By building your reports in certain ways, you can mitigate Cognos side processing, and promote database side processing.

The first and best way to do this is with a good Framework Model, as Alexey pointed out. This will allow your reports to be simpler, and pushes most of the work to the database.

However a good model still exposes table keys to report authors so that they can have the flexibility to create unique data sets. Not every report warrants a new Star Schema, and sometimes you want to join the results of queries against two different Star Schema sources.

When using a join or a filter, Cognos attempts to push all of the work to the database as a default. It wants to have the final data set sent to it, and nothing else.

However when creating your filters, you have two ways of defining variables... with explicit names that refer to modeled data sources (ie. [Presentation View].[Sales].[Sales Detail].[Net Profit] ) or by referring to a column in the current data set (such as [Net Profit] ). Using explicit columns from the model will help ensure the filters are applied at the database.

Sometimes that is not possible, such as with a calculated column. For example, if you dont have Net Profit in your database or within your model, you may establish it with a Calculated column. If you filter on [Net Profit] > 1000, Cognos will pull the dataset into Cognos before applying your filter. Your final result will be the same, but depending on the size of data before and after the filter is applied, you could see a performance decrease.

It is possible to have nested queries within your report, and cognos will generate a single giant SQL statement for the highest level query, which includes sub queries for all the lower level data. You can generate SQL/MDX in order to see how Cognos is building the queries.

Also, try experimenting. Save your report with a new name, try it one way and time it. Run it a few times and take an average execution speed. Time it again with the alternate method and compare.

With smaller data sets, you are unlikely to see any difference. The larger your data set gets, the bigger a difference your method will affect the report speed.

Damienknight
  • 1,768
  • 2
  • 16
  • 30
0

Use joins to merge two queries together so that columns from both queries can be used in the report. Use IN() syntax if your only desire is to filter one query using the existence of corresponding rows in a second. That said, there are likely to be many cases that both methods will be equally performant, depending on the number of rows involved, indexes etc.

By the way, within a report Cognos only supports joins and unions between different queries. You can reference other queries directly in filters even without an established relationship but I've seen quirks with this, like it works when run interactively but not scheduled or exported. I would avoid doing this in reports.

Johnsonium
  • 1,985
  • 1
  • 10
  • 15