"Minimized" Versus "As View" SQL Setting in Cognos Framework Manager

While building Cognos Framework Manager model, it is very important to understand how the SQL is generated in more optimal way to read data from the underlying database.  There are '2' settings which will control the generation of SQL especially when there are multiple tables are joined together.

a. Minimized SQL

b. As View

We can find these settings in  the "Edit Definition" of each query subject. 

 

Minimized SQL: when this setting is used, queries are always generated with minimum number of tables and joins that are required for generating report output. For example, there are ‘4’ query subjects that are joined together in the below image.

Create a model query subject by including query items from these four tables as shown in the below image.

Suppose, when you build a report with the query items PRODUCT_LINE_EN and PRODUCT_TYPE_EN from the above model query subject, SQL will be always generated with '2' query subjects (PRODUCT_LINE and PRODUCT_TYPE ) with single join. Though the other two query subjects "PRODUCT" and "PRODUCT_MULTILINGUAL" are present in the join path, they will not be considered in the SQL.

Select   
PRODUCT_LINE.PRODUCT_LINE_EN,
PRODUCT_TYPE.PRODUCT_TYPE_EN

From
gosales.PRODUCT_LINE PRODUCT_LINE,
gosales.PRODUCT_TYPE PRODUCT_TYPE

Where
PRODUCT_LINE.PRODUCT_LINE_CD = PRODUCT_TYPE. PRODUCT_LINE_CD

Another example, let's assume that the SQL is generated as follows with "As View" setting:

Select
Query1.COUNTRYCODE as COUNTRYCODE,
Query1.EUROINUSESINCE as EUROINUSESINCE

From
(Select
CONVERSIONRATE.COUNTRYCODE as COUNTRYCODE,
COUNTRY.EUROINUSESINCE as EUROINUSESINCE

From
GOSALES.dbo.CONVERSIONRATE CONVERSIONRATE,
GOSALES1.dbo.COUNTRY COUNTRY

Where
COUNTRY.SALESCOUNTRYCODE = CONVERSIONRATE.COUNTRYCODE
)Query1

When you change it to "Minimized", the SQL will be generated in optimal way as follows:

Select
CONVERSIONRATE.COUNTRYCODE as COUNTRYCODE,
COUNTRY.EUROINUSESINCE as EUROINUSESINCE

From
GOSALES.dbo.CONVERSIONRATE CONVERSIONRATE,
GOSALES.dbo.COUNTRY COUNTRY

Where
COUNTRY.SALESCOUNTRYCODE = CONVERSIONRATE.COUNTRYCODE

In order to make sure that this setting should be always applied in the model, avoid following:

a.       Do not alter Data Source Query subjects by modifying the SQL.

b.       Do not alter Data Source Query subjects by adding any filters, calculations, parameterized SQL.

c.       Always create determinants and relationships on Data Source subjects.

4.       Do not create any joins between Model Query Subjects.

As View SQL: when this setting is used, Framework Manager generates queries that contain the full SQL statement that defined the query subject.

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.

Another example might be a security table that must always be joined to a fact. In the Great Outdoors Sales model, Order Header and Order Details are a set of tables that together represent a fact and you would always want them to be queried together