Query Subjects

Information about query subjects for SAP BW metadata appears in a different topic .

• data source query subjects

• model query subjects

• stored procedure query subjects

You may also be interested in this topic, Query Subjects vs. Dimensions.

Data Source Query Subjects

Query subjects that are based on relational metadata are defined by SQL statements that describe how to retrieve data from the data source.

Data source query subjects directly reference data in a single data source. Framework Manager automatically creates a data source query subject for each table and view that you import into your model.

For example, you import the Employee detail fact table from the Great Outdoors Warehouse sample database. Framework Manager then creates a query subject using the following SQL statement:

Select * from [go_data_warehouse].EMPLOYEE_DETAIL_FACT

Framework Manager generates query subjects that represent tabular data from the data source. For example, a query subject that references an entire table contains query items that represent each column in the table. If the SQL selects only specific columns, only those columns are represented as query items.

Each data source query subject can reference data from only one data source at a time. However, the advantage of data source query subjects is that you can directly edit the SQL that defines the data to be retrieved. This means that you can insert parameters that control the data that the query retrieves and create query subjects based on arbitrary SQL.

You may have created SQL statements for query subjects without enclosing references to columns and tables in quotes. This works in relational data source environments that do not use a case-sensitive lookup against the metadata tables they parse. The SQL statement for the query subject may be Cognos SQL , native SQL , or pass-through SQL . Native and pass-through SQL statements must be completely self-contained and must not reference anything outside that SQL, such as database prompts, variables, or native formatting that would normally be supplied by the calling application. Cognos SQL statements, however, are analyzed using metadata from either the model or the relational data source. By default, Cognos SQL is case-sensitive, so it looks up metadata using the names as they appear in the SQL statement.

To use multiple data sources for a query subject, use a model query subject that accesses the data source query subjects or other model query subjects.

Create a Data Source Query Subject

Data source query subjects directly reference data in a single data source. Framework Manager automatically creates a data source query subject for each table and view that you import into your model. You can create additional data source query subjects.

You can add any combination of objects to a query subject, such as query items, other query subjects, or dimensions. You can add stand-alone calculations and filters, and you can also embed calculations and filters in the query subject.

You can also create model query subjects, which are based on metadata that exists in your model , and stored procedure query subjects, which are generated from the stored procedures in a relational data source .

Steps

1. Select the namespace folder and, from the Actions menu, click Create, Query Subject.

2. In the Name box, type a name for the new query subject.

3. Click Data Source, and click OK.

4. Complete all the steps in the New Query Subject wizard.

   To ensure that the data source is uniquely identified for a data source query subject, do not exit the wizard before the Finish button appears.

5. Click Finish.

6. Right-click the query subject you created and click Edit Definition.

7. Click the SQL tab, and from the Available database objects box, drag objects to the SQL box.

   You can also insert a data source reference, insert a macro, embed a calculation , and embed a filter .

8. Choose the action you want:

   Goal	Action
   Provide control over granularity	Click the Determinants tab .
   Test the query subject	Click the Test tab .
   View the SQL	Click the Query Information tab .
   View the system tables from the data source	Select the Show System Objects check box.

9. Click OK.

   A warning appears if any modifications invalidated relationships, other query subjects, calculations, or filters.

10. Ensure that the Usage and Regular Aggregate properties are set correctly .

You can now organize objects in the Diagram tab. Tip: From the Diagram menu, click Auto Layout.

You can also control the model area that is visible in the diagram. Tip: Click and hold the overview button in the bottom right corner and drag the pointer over the diagram .

Model Query Subjects

Information about model query subjects for SAP BW metadata appears in a different topic

Usually, model query subjects are created in the business view, not the import view. For information about the business view, see Organizing the Model.

Because model query subjects are based on the metadata in your model, they let you

• reuse complex SQL statements that exist in the model

• reference objects from different data sources in the same query subject

If you import a model query subject from another model, the model query subject will not work unless you also import the data source query subjects that the model query subject references.

If you want to edit the SQL, you must convert the model query subject into a data source query subject .

Create a Model Query Subject for Relational Metadata

Model query subjects are based on query items in other query subjects or dimensions, including other model query subjects. By using model query subjects, you can create a more abstract, business-oriented view of a data source.

You can add any combination of objects to a query subject, such as query items, other query subjects, or dimensions. You can add stand-alone calculations and filters, and you can also embed calculations and filters in the query subject.

You can create a new model query subject by merging existing query subjects and query items . You can also create data source query subjects, which directly reference data in a single data source , and stored procedure query subjects, which are generated from the stored procedures in a relational data source .

When you use a model query subject in the Cognos 8 studios, Cognos 8 uses the relationships you have defined for the model query subject, not the relationships defined for the underlying data source query subjects. If you want to ensure that the relationships defined for the underlying data source query subjects are used, do not define relationships or determinants for the model query subject.

Steps

1. Select the namespace folder and, from the Actions menu, click Create, Query Subject.

2. In the Name box, type a name for the new query subject.

3. Click Model, and click OK.

4. To add items to the model query subject, click the Query Subject Definition tab, and from the Available Model Objects box, drag objects to the Query Items and Calculations box.

   You can also embed a filter.

   If you add calculations or filters to a model query subject, Cognos 8 must go to the data source instead of simply accessing the model.

   You can change the order of items and calculations for a model query subject. However, if the query subject contains a query item folder, you can change the order only in the Project Viewer.

5. If you want to test the query subject, click the Test tab .

6. Click OK.

   A warning appears if any modifications invalidated relationships, other query subjects, calculations, or filters.

7. Ensure that the Usage and Regular Aggregate properties are set correctly .

You can now organize objects in the Diagram tab. Tip: From the Diagram menu, click Auto Layout.

You can also control the model area that is visible in the diagram. Tip: Click and hold the overview button in the bottom right corner and drag the pointer over the diagram .

Stored Procedure Query Subjects

Stored procedure query subjects are generated when you import a procedure from a relational data source. Framework Manager supports only user-defined stored procedures. System stored procedures are not supported.

The procedure must be run in Framework Manager to get a description of the result set that the procedure may return.

The stored procedure must return a single uniform result set. Cognos 8 supports only the first result set that is returned. If the procedure could conditionally return a different result set, the format must be consistent with the one used to define the metadata in Framework Manager.

Each result set must return the same form, such as the same number, types, and names of columns. Overloaded signatures are supported by Cognos 8, but each procedure must be defined as a uniquely named procedure with a separate query subject for each result set.

Output parameters are not supported.

After you import or create a stored procedure query subject, it appears as a broken object. You must run it to validate the underlying stored procedure and specify the projection list. Static metadata often does not exist for the stored procedure in the relational data source that describes what a result set may look like. The result set may be known only at run time. When a stored procedure is updated in the data source, running the stored procedure in Framework Manager updates the query subject using the newly generated query items.

Sometimes functions are imported as stored procedure query subjects. Review the stored procedure definition to determine what the procedure expects to be passed and what it attempts to return. Edit and test each stored procedure query subject that you think could be a function. If the test fails, the query subject is a function and must be deleted.

For more information, see Create or Modify a Stored Procedure Query Subject.

Create or Modify a Stored Procedure Query Subject

After you import or create a stored procedure query subject, you can modify it. To avoid inconsistencies, the modified query subject should return the same result set structure as the original stored procedure.

Framework Manager supports only user-defined stored procedures. System stored procedures are not supported.

There are different types of stored procedures:

You can also create data source query subjects, which directly reference data in a single data source , and model query subjects, which are based on metadata that exists in your model .

Steps

1. Do the following:

   Goal	Action
   Create a stored procedure query subject	Select the namespace folder and, from the Actions menu, click Create, Query Subject. In the Name box, type a name for the new query subject. Click Stored Procedure, and click OK. Complete all the steps in the New Query Subject wizard.
   Modify a stored procedure query subject	Select the stored procedure query subject that you want to modify. From the Actions menu, click Edit Definition.

2. Click the Definition tab and choose the action that you want.

   Goal	Action
   Use a different stored procedure	In the Stored Procedure Name box, type the name of the stored procedure.
   Change the type of the stored procedure	From the Type box, select Data Query or Data Modification.
   Change which data source the stored procedure is in	Click the ellipsis (...) button next to the Data Source box. When you import a stored procedure, a new data source is created. You can point to the original data source and delete the new one.
   Edit an argument	Click the argument and click the ellipsis (...) button. The Syntax box in the Query Subject Definition dialog box shows the correct syntax to use.
   Generate the projected query items	Click the Test tab .

3. Click OK.

   Framework Manager runs the stored procedure and, if the query subject returns a result set, validates the query subject.

   If the stored procedure does not return a result set, the query subject becomes an invalid query subject if saved in the model. If the invalid query subject is included in the published package, the invalid query subject cannot be used in a report.

4. Ensure that the Usage and Regular Aggregate properties are set correctly for each newly created query item.

   For example, a query item may be set as a fact when it is an identifier.

You can update the stored procedure query subject if the data source changes .

Example - Use Prompts with a Stored Procedure

If you define prompts for stored procedure variables, your users can set the variables in reports.

Steps

1. Create a stored procedure query subject that uses the sp_FIND_ORDER_DATE stored procedure.

   The Query Subject Definition dialog box appears.

2. On the Definition tab, select the @order_number argument, and click the ellipsis (...) button.

3. In the Value box, type the following macro syntax and then click OK:

   #prompt('Order Number','integer')#

   Note: Framework Manager removes anything that is outside the number signs when running the macro.

4. If you want to test the prompt for the variable, do the following:

   • Click the Test tab, and then click Test Sample.

     The Prompt Values dialog box appears.

   • In the Name column, click Order Number.

   • In the Value field, type 1234 and click OK.

     One record is returned, showing the date for Order Number 1234.

     Framework Manager uses this value for the duration of the current session or until you clear the prompt value.

5. Click OK.

Determinants

• A query subject that behaves as a dimension has multiple levels of granularity and will be joined on different sets of keys to fact data.

  For example, Time has multiple levels, and it is joined to Inventory on the Month Key and to Sales on the Day Key. For more information, see Multiple-fact, Multiple-grain Queries.

• There is a need to count or perform other aggregate functions on a key or attribute that is repeated.

  For example, Time has a Month Key and an attribute, Days in the month, that is repeated for each day. If you want to use Days in the month in a report, you do not want the sum of Days in the month for each day in the month. Instead, you want the unique value of Days in the month for the chosen Month Key. In SQL, that is XMIN(Days in the month for Month_Key). There is also a Group by clause in the Cognos SQL.

There are less common cases when you need to use determinants:

• You want to uniquely identify the row of data when retrieving text blob data from the data source.

  Querying blobs requires additional key or index type information. If this information is not present in the data source, you can add it using determinants. Override the determinants imported from the data source that conflict with relationships created for reporting.

  You cannot use multiple-segment keys when the query subject accesses blob data. With summary queries, blob data must be retrieved separately from the summary portion of the query. To do this, you need a key that uniquely identifies the row and the key must not have multiple segments.

• A join is specified that uses fewer keys than a unique determinant that is specified for a query subject.

  If your join is built on fewer columns than what is stored in Framework Manager within the determinants, there will be a conflict. Resolve this conflict by modifying the relationship to fully agree with the determinant or by modifying the determinant to support the relationship.

• You want to override the determinants imported from the data source that conflict with relationships created for reporting.

  For example, there are determinants on two query subjects for multiple columns but the relationship between the query subjects uses only a subset of these columns. Modify the determinant information of the query subject if it is not appropriate to use the additional columns in the relationship.

Specify a Determinant

Determinants provide control over granularity for query subjects.

If a query subject has determinants, each query item of the query subject must be included in one of the determinants.

Determinants are processed in the order in which they are specified in the model. You can change the order of the determinants. If a query subject has more than one determinant, the first one that covers all the requested items is used. Determinants are evaluated in the context of each required join as well as the context of requested items.

Data source query subjects are imported with determinants defined for them. These default determinants are generated based on keys and indexes in the data source.

Steps

1. Click the query subject you want and, from the Actions menu, click Edit Definition.

2. Click the Determinants tab.

3. To add a query item to a determinant, select the query item from the Available items box and click Add.

4. To specify that the selected query item is the lowest level of the hierarchy and should be used as the unique identifier, select the Uniquely Identified check box.

   Do this only if the data in this item is unique for every row in the underlying data source.

   If you define a determinant as unique and you select the Group By check box, grouping is applied after the determinants are processed. The Group By clause is used in the generated SQL, which enables aggregate functions in the data source to be used in Cognos 8.

   If you have multiple determinants with Uniquely Identified check box selected, only the Group By setting of the first determinant is used. This is because the processing of determinants stops at the first determinant with Uniquely Identified check box selected.

5. To indicate that, when the keys or attributes associated with the determinant are repeated in the data, Cognos 8 should apply aggregate functions and grouping to avoid double-counting, select the Group By check box.

   It is not recommended that you specify determinants that have both uniquely identified and group by selected or have neither selected.

6. To define a key, right-click one or more query items in the Available items box and click Add as Key.

   You can also drag query items to the Key box.

7. To identify which query items users must report on, right-click query items in the Available items box and click Add as Attributes.

   You can also drag query items to the Attributes box.

   If a determinant defines attributes, all query items in the query subject must be associated with the determinant and defined as either a key or an attribute. You can have a determinant with no attributes defined for it. Framework Manager uses this type of determinant to indicate which query items are indexed.

8. If you want to change the order of the determinants, use the arrow buttons.

   Determinants are processed in the order in which they are specified in the model.

9. Click OK.

For more information, see Determinants and The SQL Generated by Cognos 8.

The Effect of Determinants on SQL

It is important to understand the effect that determinants have on the SQL that is generated. Determinants affect the grouping and aggregation of data, including other query subjects that have relationships with the query subject as well as the query subject itself.

For example, consider the following information. Each Product Line contains many occurrences of Product Type. Each Product Type contains many occurrences of Product. For Product, Product Key is a surrogate key and Product Number is a business key that is used as an attribute. Data joined on Product Key is aggregated correctly when reported by Product Line or Product Type or both.

Create a Model Query Subject Based on Existing Objects

Information about SAP BW model query subjects appears in a different topic .

You can select existing model objects and merge them into a new model query subject. This means that you can reuse existing metadata to quickly create query subjects.

The objects that you can merge include

• relational data source query subjects and their shortcuts

• model query subjects and their shortcuts

• query items, filters, and calculations in model and data source query subjects

• relationships and relationship shortcuts between model and data source query subjects

Steps

1. Ctrl+click the objects that you want to merge into a single query subject.

2. From the Actions menu, click Merge in New Query Subject.

View Related Objects

Steps

1. Select one or more objects that you want to explore.

2. From the Tools menu, click Launch Context Explorer.

3. To see the connected objects, click one or more objects and click the appropriate button.

   Goal	Button
   View the objects that are related to the selected object.
   View the immediate references for the objects.
   View all references for the objects.

4. If you want to see details about an object, such as its relationships and query items, right-click the object, click Diagram Settings, and then select the details you want.

Create a Query Set

There are many reasons for creating a query set. A query set may be needed to create a conformed dimension across disparate data sources, such as relational and SAP BW data sources. Or, you may want to compare the contents of two queries to determine whether the queries contain the same data; this is common in test environments. Or, you may want to compare queries that return nulls. Or, you want to handle a fact-to-fact relationship that is truly a one-to-one relationship. (If it is not truly a one-to-one relationship, create a multiple-grain query .)

A query set can consist of only two query subjects. You can create a query set that merges two other query sets together. A query set can contain

• all the rows of two query subjects (union operation)

  For example, your company recently acquired another company and you need a complete list of all customers.

• only the rows that are shared between the query subjects (intersect operation)

  For example, you want to find out which staff members are also managers.

• only the rows that exist in the first query subject and not in the second query subject in the query set (except operation)

  For example, you want to highlight the differences between where your products were sold this year and ten years ago.

The names of the items in the projection list default to the items assigned to the first query subject in the set operation.

Relationships between the two query subjects in the query set and other query subjects are not included in the query set.

Reports show different results depending on which operator is used. For example, you have two query subjects with the names of various employees.

The first query subject contains these rows:

The second query subject contains these rows:

You create a query set. You see different results depending on the operator you use.

Steps

1. Select two query subjects that meet these requirements:

   • Each query subject must have the same number of columns.

   • Columns must be in the same order.

   • Columns must have the same or similar data types.

     The data types do not need to be exactly the same if those in the second result set can be automatically converted by the data source to data types compatible with those in the first result set.

     For example, one query subject contains country data and uses int as the data type. Another query subject contains country data and uses smallint as the data type. Framework Manager imports these query subjects as int16 and int32 and performs a set operation.

2. From the Actions menu, click Define Query Set.

3. Click the Definition tab.

4. In the Name box, give the query set a name.

5. Review the Query Subject boxes to ensure the order that the query subjects will appear in the Select clause is correct.

   The order could be important if you want a specific set of column names (aliases) that appears in only one of the query subjects. If the order is incorrect, cancel this query set and start again.

   For union and intersect, the order of the query subjects does not matter. You can change the order and receive the same answer. For except, the order of the query subjects does matter.

6. Use the Operator box to define how the rows of the query subjects are combined.

   Option	Description
   Union	Retrieves all unique rows from both sets. Duplicates are removed.
   Intersect	Retrieves rows that are common between the query subjects.
   Except	Retrieves rows that exist in the first query subject and not in the second query subject.

7. To create a Union All, Intersect All, or Except All operation, clear the Remove Duplicate Row check box.

8. Choose the action that you want.

   Goal	Action
   Work with the calculations that are embedded in the query subjects	Click the Calculations tab. You can add or edit the calculations and change the order of the calculations.
   Work with the filters that are embedded in the query subjects	Click the Filters tab. You can add or edit the filters, change the order of the filters, and change the usage of filters.
   Test the query set	Click the Test tab.

9. Click OK.

You may be interested in the following related topics:

• embedded calculations

• embedded filters

• determinants

• testing the query set or changing the test settings

Test a Query Subject or Query Set

Governor settings may affect the testing results. For more information, see Set Governors.

You can change existing test settings to customize the results that the test shows. For example, in addition to other settings, you can control the number of rows returned.

Steps When Creating or Modifying the Object

1. Select the object you want to test.

2. From the Actions menu, click Edit Definition, and click the Test or Query Information tab.

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

   Any result sets that contain binary large objects are shown as [blob].

3. To run the query and bring back all the test results, click Test Sample.

4. If you want to add a count of the rows, click Total Rows.

5. If you want to apply the Regular Aggregate property of the query item or the Aggregate Rules property of a semi-additive measure that is referenced in the expression, select the Auto Sum check box.

   If you clear this check box, a row is returned for each row in the result set of the query.

6. If you want to obtain more information about the query results, click the Query Information tab.

7. Click OK.

Steps to View the Data That Will Appear in a Specific Report

1. Select the objects that will appear in the report.

2. From the Tools menu, click Test.

   A message appears, telling you if the test was successful or if problems were found.

3. To view details about any problem that is found, click the Query Information tab.

   If you do not see the results of the query in the test window, the data from your data source may exceed the value of the Runtime Limits governor. The query stops at the specified limit, but the test result window does not contain any data. Tip: Set the Runtime Limits governor to zero.

Change the Test Settings

You can customize the tests by changing the test settings.

Steps

1. Select the object that you want.

2. From the Actions menu, click Edit Definition and then click the Test tab or the Query Information tab.

3. Click Options and then click the Test Settings tab.

4. Choose the options that you want.

   Goal	Action	Persistence
   Retrieve all data and show a specified number of rows	Select the Restrict the maximum number of rows to be returned check box and type the required number of rows. This setting does not improve performance for retrieving data when testing dimensions, query subjects, and query sets.	This setting applies to all dimensions, query subjects, and query sets in the model. This setting is saved and used in your next session with any model.
   Specify the level of detail	Drag the Level of Information shown in Results Information slider to the location that represents the amount of detail you require.	This setting is saved and used in your next session with this model.
   Temporarily override session parameters	In the Session Parameters box, click Set. The Session Parameters dialog box appears.	The override values are not saved with the model. This setting is for your current session only.
   Apply relevant design mode filters	Select the Apply all relevant design mode filters when testing check box. This applies all relevant filters whose usage is set to design mode in another dimension, query subject, or query set.	This setting is saved and used in your next session with any model.
   Apply a security filter	In the Security Filters box, click Edit.	This setting is saved and used in your next session with this model.
   Change the prompt values	In The Current Prompt Values box, click Prompts. The Model Prompts Manager dialog box appears, which shows all prompts, and their values, that are in the model.	The prompt values are not saved with the model. This setting is for your current session only.

5. Click OK two times.

You may be interested in the following related topics:

• setting governors

• security filters

• temporarily overriding session parameters

• changing prompt values

• working with dimensions

• working with query subjects

Validate a Query Subject

Information about validating SAP BW query subjects appears in a different topic .

You can validate the definition of the query subject without having to open the Query Subject Definition dialog box. This is useful to do when

• new query items were added to a query subject

• the definition of the underlying query subject has changed

• the parameters of a stored procedure were changed

The Evaluate Object command completes an exhaustive check of all query subjects and ensures that they can run.

What happens in the evaluation process depends on the type of query subject selected.

You can also update the query subject if it is from a relational data source, or synchronize the entire project .

Steps

1. Select the query subject that you want to evaluate.

2. From the Tools menu, click Evaluate Object.

If you changed the Regular Aggregate property to unsupported, the property is reset when you evaluate the query subject. If the property is set to any other value, the property is not changed.

Update Query Subjects

If you are using a relational data source, you can choose to update only the query subjects instead of performing a full project synchronization. You must perform a project synchronization to synchronize changes in a third-party data source.

The query subject is updated based on the definition in the data source. When you update a query subject, new metadata is fetched from the data source and query items are re-synchronized.

You can also evaluate the query subject, if it is from a relational data source .

Steps

1. Select one or more query subjects.

2. From the Tools menu, click Update Object.

   Tip: You can instead open the Query Subject Definition dialog box and click OK.

Convert a Query Subject into a Dimension

You can convert a query subject into a regular dimension or a measure dimension when you want to use features associated with dimensions, such as defining hierarchies and levels. A model query subject becomes a model dimension.

While you can convert data source query subjects to data source dimensions, data source dimensions have limited functionality in comparison to query subjects or model dimensions. It is recommended that you discontinue using data source dimensions (both regular and measure). Create new models following the recommendations in Guidelines for Modeling Metadata to use query subjects as the relational foundation of the model. Define regular and measure dimensions as model objects based on data source query subjects or model query subjects or both. Guidance on migration for existing users of data source dimensions will be provided in a future release.

If the query subject has determinants specified for it, the keys you specified for each determinant is used to build a hierarchy with a business key for each level. The determinants form one hierarchy. The first string attribute for each determinant is used for the business caption. If the caption is not the attribute you want to use, you must change it manually.

Converting a query subject into a dimension is simply a starting point. We recommend that you examine each dimension that is created this way to ensure that it reflects your requirements.

You can also convert a dimension into a query subject .

Steps

1. Select the query subjects that you want to convert.

2. From the Actions menu, click Convert to Regular Dimension or Convert to Measure Dimension.

Convert a Model Query Subject into a Data Source Query Subject

You can convert a model query subject into a data source query subject if you want to edit the SQL.

Steps

1. Select the model query subject that you want to convert.

2. From the Actions menu, click Convert to Data Source Query Subject.

   This command is available only if you have run the query and the Query Information tab in the Edit Definition dialog box contains SQL.

Edit the SQL

SQL is the industry-standard language for creating, updating, and querying relational database management systems. When you edit the definition of a relational data source query subject, you can use

• Cognos SQL

• native SQL

• pass-through SQL

If you want to edit the SQL of a model query subject, you must copy the SQL for the model query subject from the Query Information tab and paste it into a new data source query subject. You can also convert the model query subject into a data source query subject . Do not edit the SQL if you want the model query subject to reference multiple data sources.

Changing the alias of a column regenerates the query item that represents that column. Any modifications that you made to the query item are not retained because Framework Manager considers it a new query item.

You can add comments to the SQL by using /* before the comment and */ at the end.

Here is an example:

select country /* this is a multiline
comment

another line

another line

*/

Steps

1. Click the data source query subject that you want to change.

2. From the Actions menu, click Edit Definition.

3. Click the SQL tab, and drag objects into the SQL box or type in the SQL you want.

4. Click OK.

Change the Type of SQL

When choosing the type of SQL in which to generate a data source query subject, you must weigh the following factors and decide which are most important.

Note that you cannot change the type of SQL for query subjects that are based on OLAP data sources.

• Edit existing table names.

  Cognos SQL uses a two-part structure to name query subjects. For example, [gosales].[ProductLine] means that the ProductLine query subject comes from the gosales database. Therefore, when you switch to native SQL, you must ensure that all table names include the parent elements required by the data source vendor.

  For information about naming conventions, see Naming Conventions for Objects in a Project.

• Ensure that the SQL is valid for subqueries.

  Framework Manager processes native SQL query subjects as subqueries. For example, here is a Cognos SQL query subject:

  Select 

  P.ProductName, P.Margin From Product P 

  If you change it to native SQL, Framework Manager generates the following statement:

  Select 

  oracle_plain.ProductName as Productname,

  oracle_plain.Margin as Margin

  From 

  (GOSALES1_OR_92_WE...SELECT

  P.PRODUCTNAME, P.MARGIN 

  FROM 

  PRODUCT P} 

  )oracle_plain

  Therefore, you must ensure that the query subject adheres to additional database restrictions that are imposed on subqueries, such as not using the With clause. Pass-through SQL does not have the same restrictions. However, the fact that native SQL is processed as part of a larger query improves its performance.

  To test native SQL using a query tool, such as Oracle's SQL*Plus, you must place the SQL in the From clause of a Select statement. For example, you can use the following syntax in a query tool:

  Select * from (<Native SQL>) T1

• Assign aliases to derived columns.

  Assign alias names to any column whose values are calculated. Here is an example:

  SELECT
  Length(Country) as LGTH

  FROM Country

• Insert double quotation marks around alias names.

  Changing the SQL type of a query subject can change the case of alias names. When this happens, any query subject that references the changed query item becomes invalid. To ensure that there is no case change, insert double quotation marks around the alias, such as

  Select
  COUNTRY as "test" from COUNTRY

• If a data source query subject contains a macro in the projection list (Select clause) of the SQL statement, specify an alias in the SQL that matches the Column Name property of the query item.

  An error could occur because the macro evaluates to a column name that is different from the Column Name property of the corresponding query item. The result is that the system is unable to locate the item in the projection list. Projection lists are static.

  Assigning an alias ensures that the name of the item in the projection list remains constant, as the results of evaluating the macro change.

  For example, the following query contains a session parameter, runLocale, whose value specifies which column the query retrieves:

  Select

  #$ColumnMap{$runLocale}# as
  CountryNameAlias

  From

  [GoSales].Country

  Note that the number sign (#) is reserved for macros. Framework Manager removes anything that is outside the number signs when running the macro.

Steps

1. Click the query subject that you want to change.

2. From the Actions menu, click Edit Definition, and then click the Query Information tab.

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

3. Click Options, and then click the SQL Settings tab.

4. Use the SQL Type list to change the type of SQL.

   If you are changing the type to native SQL, see the checklist above to ensure that the SQL reflects the rules that apply to the native data source.

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 Cognos 8 uses, click Response.

9. Click OK.

Cognos SQL

By default, Framework Manager uses Cognos SQL to create and edit query subjects. Cognos SQL adheres to SQL standards and works with all relational and tabular data sources. Framework Manager generates the most optimized SQL possible. In this way, Cognos SQL is preferable.

Because query subjects in Framework Manager are similar to views in databases, the SQL for each query subject must conform to the SQL standards that apply to views. For example, you must assign aliases to any column that is empty or whose name is not unique. This level of conformance means that Cognos SQL behaves more consistently than vendor-specific SQL, which does not adhere to SQL standards.

In general, using Cognos SQL is preferable because you can create query subjects that

• can contain metadata from multiple data sources

• have fewer database restrictions

• interact more effectively with Cognos applications

SELECT * FROM 

(SELECT SNO C1, AVG(QTY) C2, COUNT(*) C3 FROM
SUPPLY
GROUP BY SNO) T1, 

(SELECT MAX(QTY) C1 FROM SUPPLY) T2 

WITH T1 AS (SELECT SNO C1, AVG(QTY) C2, COUNT(*)
C3 FROM
SUPPLY GROUP BY SNO), 

T2 AS (SELECT MAX(QTY) C1 FROM SUPPLY) 

SELECT *FROM T1, T2 

For more information about the With clause, see Set Governors.

Cognos continues to improve data type checking and SQL validation. Because of this and because not all vendors are completely compliant with the SQL standard, invalid or ambiguous SQL expressions that previously were passed to the data source will no longer be passed down. If you have an expression that returns a data type not specified by the SQL standard, we recommend that you pass the expression to the data source by using the syntax {expr}. Your users should use the same technique.

Native SQL

Native SQL is the SQL that the data source uses, such as Oracle SQL. Use Native SQL to pass the SQL statement that you enter to the database. Cognos 8 may add statements to what you enter. You can not use native SQL in a query subject that references more than one data source in the project.

SQL specified in Framework Manager and processed by the database, whether native or pass-through, must be completely self-contained. It must not reference anything outside that SQL, such as database prompts, variables, or native formatting that would normally be supplied by the calling application.

If you are comfortable working with a native SQL version, you may want to use it for query subjects that are based on a single data source. By doing so, you can use keywords that are not available in Cognos SQL, and copy and paste SQL from another application into Framework Manager.

When the query is generated, Framework Manager combines the SQL of each query subject that uses a given data source connection into a single query. This helps improve the performance of the query. However, because the SQL is being generated as a series of subqueries, native SQL queries must adhere to any restrictions that their database vendor places on derived tables.

Here is an example of native SQL that returns a list of employees and managers:

SELECT

	lpad(' ', (level-1)* 4) ename
EMP_CHART,

	level, empno, ename, job, mgr

FROM

	emp

CONNECT BY PRIOR 

	empno = mgr

AND 

	deptno not in (20,30)

START WITH 

	mgr IS NULL

ORDER BY 

	level, job

Pass-through SQL

Use pass-through SQL when the SQL statement that you enter is not valid inside a derived table. Pass-through SQL lets you use native SQL without any of the restrictions that the data source imposes on subqueries. This is because pass-through SQL query subjects are not processed as subqueries. Instead, the SQL for each query subject is sent directly to the data source where the query results are generated.

Because each query subject is sent to the data source as a separate statement rather than being optimized by Framework Manager, performance is slower. Therefore, in choosing between native SQL and pass-through SQL, you must decide which is more important: performance or using SQL that is not permitted in a subquery.

Generally, you should use pass-through SQL only if you must create a query subject that contains constructs that are specific to a data source and that cannot be used inside a derived table, such as in a With or OrderBy clause.

SQL specified in Framework Manager and processed by the database, whether native or pass-through, must be completely self-contained. It must not reference anything outside of that SQL, such as database prompts, variables, or native formatting that would normally be supplied by the calling application.

For example, here is a systems-oriented report that contains the system date:

SELECT 

	TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') 

FROM 

	SYS.DUAL

Note that the number sign (#) is reserved for macros and that column names must be unique. Framework Manager removes anything that is outside the number signs when running the macro.

Change How the SQL Is Generated

You can specify how 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.

Steps

1. Click the query subject that you want to change.

2. From the Actions menu, click Edit Definition, and then click the Query Information tab.

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

3. Click Options, and then click the SQL Settings tab.

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 Cognos 8 uses, click Response.

9. Click OK.

Model Query Subjects and SQL Types

A model query subject that is based on another model query subject may use the logic of the parent query subject instead of its own logic. If the child model query subject uses the Minimized SQL type, it does not use the logic of the parent. If the child model query subject uses the As View SQL type, it uses the logic of the parent.

For example, you create a model query subject named Returned Products, which shows all return reasons for all products. When you run Returned Products, you see a list of over 700 items. You then create another model query subject based on Returned Products that is named Return Reasons. This model query subject contains only the Return Reason query item. If the SQL type is set to Minimized, the Return Reasons query subject shows five return reasons when it is run. If the SQL type is set to As View, the Return Reasons query subject uses the logic of the Returned Products query subject and shows over 700 items.