Changing how the SQL is generated

You can specify how IBM® Cognos® Framework Manager generates the SQL that retrieves data from relational data sources for data source query subjects or model query subjects.

The SQL Generation type of a query subject can be set to either As View or Minimized. By default, it is set to Minimized.

When the generation type is set to Minimized, the generated SQL contains only the minimal set of tables and joins needed to obtain values for the selected query items.

When the generation type is set to As View, Framework Manager generates queries that contain the full SQL statement that defined the query subject. Use As View when you want to ensure that the query is run as a block. The SQL is treated as a view. For example, you want the query to return the same number of rows each time that it is run.

Using minimized SQL improves performance, resulting in a query that runs significantly faster. Generating minimized SQL is especially beneficial for query subjects that represent dimension tables. By using a single model query subject to model a dimension, you can benefit from small SQL queries that run significantly faster.

For example, the SQL Generation Type of the following query subject is As View. Note that this query subject contains a nested select statement.

select 
New_Query_Subject.COUNTRYCODE as COUNTRYCODE,
New_Query_Subject.EUROINUSESINCE as EUROINUSESINCE
from
(select 
	CONVERSIONRATE.COUNTRYCODE as COUNTRYCODE,	COUNTRY.EUROINUSESINCE as EUROINUSESINCE
from
	"2 - GOSales1 - OLE-DB".GOSALES1.dbo.CONVERSIONRATE 	CONVERSIONRATE,
	"2 - GOSales1 - OLE-DB".GOSALES1.dbo.COUNTRY COUNTRY 
where
	(COUNTRY.SALESCOUNTRYCODE = CONVERSIONRATE.COUNTRYCODE)
	) New_Query_Subject

If you change the SQL Generation Type to Minimized, Framework Manager generates the following simplified SQL:

select 
CONVERSIONRATE.COUNTRYCODE as COUNTRYCODE,
COUNTRY.EUROINUSESINCE as EUROINUSESINCE 
from
"2 - GOSales1 - OLE-DB".GOSALES1.dbo.CONVERSIONRATE CONVERSIONRATE,
"2 - GOSales1 - OLE-DB".GOSALES1.dbo.COUNTRY COUNTRY 
where
(COUNTRY.SALESCOUNTRYCODE = CONVERSIONRATE.COUNTRYCODE)

Minimized SQL works best when the returned result sets of each query item are equivalent. If there are records in one column that do not correspond to records in another column, the result of the minimized query produces additional rows. You can avoid this by setting the SQL Generation Type to As View.

For example, if there are Product Types that are not used by any of the Products and these Product Types all have a common Product Line, a Product Line is reported for which there are Product Types, but for which there are no related Products.

Procedure

  1. Click the query subject that you want to change.
  2. ClickActions, Edit Definition, and then click the Query Information tab.

    The Test Results box is initially empty until you run the query.

  3. Click Options, SQL Settings.
  4. Set Generate SQL to As View or Minimized.
  5. Click OK.
  6. If you want to see the SQL, click Test Sample.
  7. If you want to see the actual query, click Query.
  8. If you want to see the xml that IBM Cognos Analytics uses, click Response.
  9. Click OK.