Cognos Analytics

Dealing with unsupported data types

Starting with Cognos Analytics (CA) 11.2.1, you can type convert unsupported data type columns into a supported data type in the column’s expression editor.

This document describes this new method of embedding a pass-through expression into the query item’s expression, which will convey a vendor native conversion function, and alternative methods of modelling unsupported data types in a data module.

Unsupported data type overview

This section discusses the concept of an unsupported data type, how to recognize the situation, and some considerations for action.

Certain data types are not supported by Cognos. Two examples of unsupported data types are blob and bit. Another term, analogue to unsupported data type, is unknown data type.

In a data module, Cognos draws attention to the columns which are unsupported by substituting a string for the values of the column and by setting the hide from users property on. The annotations are made to the schema metadata when the schema is loaded. You will see the column as grey in the module tree and the data grid. When you view the data for a column which has an unsupported data type, either in a module or in a reporting application using the module,  you will see text similar to the following example, which is of a column whose data type has been identified as bit:

‪<unsupported: bit>

One rationale for the annotation of unsupported data type columns so that this type of text is displayed rather than any misleading, incorrect, or incomplete element or fragment of possibly viewable data from the unsupported data type column is so that a modeler or user does not inadvertently try to work with the column and rely on results which, albeit possibly superficially seemingly correct or at least not screamingly wrong, are incorrect.

A query item has two properties which display information about the data type of the source column or the expression which defines the query item. They are the data type and the technical data type properties. The latter is located in the advanced section of the properties slide out.

The data type is a high-level, logical, classification of the actual data type of the column. The technical data type is a more precise, physical, classification of it. For example, a varchar (100) technical data type would be classified as a text data type. In the picture above, a column which has a technical data type of Bit is represented as a Boolean data type. When you are working with a column in situations where the data type will matter, such as using the column in a function in a calculation expression, generally the high-level classification of the column’s data type will matter the most, although knowing things such as the size of the data can be important for operations such as converting the data type of a column.

The modeler, in conjunction with his colleagues in reporting and the data base administrators, needs to decide the approach to the problem. It is possible that the columns which have the unsupported data types are not intended to be used in reporting and no action would be necessary, as because the unsupported data type columns are already hidden and only accessible to the modelers. He would need to determine, if it is not possible address this problem at the data base level by using the view method, which of the methods available in CA modelling would be appropriate for his purposes.

Conversion mechanisms

There are two ways of dealing in a data module with unsupported data types.

One is to create a pass-through SQL table. This method has existed since the introduction of SQL tables in CA 11.1.0.

The second method, which is new to 11.2.1, is the pass-through expression. The expression of the column which has an unsupported data type is edited and a native function which converts the column to a supported data type is added to the expression.

In both cases, type conversion of the unsupported data type is second best to having a view in the data base, where the column whose data type is unsupported has already been converted to a supported data type. In addition to you having full control over how any particular value of an unsupported data type is rendered and what the converted column’s data type will be, this is because the computation of the conversion has already been done when the view was created and does not need to be done at run time.

More information is available here:

https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=modules-supported-sql-data-types

 https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=subjects-changing-type-sql

Pass-through SQL table method

You can create a SQL table with a SQL statement which will be passed directly to the data base, without intermediation of the Cognos query engine. This is done by setting the SQL type setting for the SQL table to be pass-through.

It is bought with several tradeoffs, mostly discussed in the links below.

One modelling consideration is that you need to model any relationship between the original query subject and any other query subject again, using the pass-through SQL table and the related query subjects, and that you now need to use the pass-through SQL table as the query subject which you would use in reports.

More information is available here:

https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=sql-pass-through

https://www.ibm.com/support/pages/what-difference-between-native-cognos-and-pass-through-sql

Pass-through expression method

Starting with CA 11.2.1, you can formulate expressions in the expression editor for a query item which will pass the expression to the data base as a native function to convert the column to a supported data type.

You would do this by editing the expression of the query item. As part of their properties, each query item has an expression which, in the case of a query subject derived from a source table, refers to a column of the source table. The method of creating the embedded pass-through expression entails using the query item expression in a type conversion function which your data base supports and wrapping it with symbols which tell the Cognos query engine to pass the expression to the data base unmediated by the query engine.

Embedded pass-through expression syntax

The syntax of the expression is below:

{   Native conversion function expression   }

The opening curly bracket (brace) symbol, {,  indicates the beginning of a section of pass-through expression. The closing curly bracket symbol,} , indicates the end of the pass-through expression section. The section between those symbols should be the native type conversion function expression.

Here is an example of a pass-through expression.

{
cast (FinishedGoodsFlag as integer )
}

This example uses the Microsoft SQL server cast function. In this example, the column FinishedGoodsFlag is converted to an integer data type. I have put the opening and closing curly brackets on separate lines for clarity. Please consult your vendor documentation for the appropriate type conversion functions in your case.

The pass-through expression can be used in conjunction with other expressions. Below is an example of such an expression.

substring ( {cast (LargePhoto as varchar (1000))} ,1, 10)

Pass-through expression implementation method

Here is an example of the expression of a query item. It consists of just the identifier of the column in the source table, as further qualification of the context of the column is handled elsewhere.

Here is an example of the expression with a casting function and the pass-through expression delimiters added.

If you look at the generated SQL for a situation where there is an unsupported data type, the SQL will look like this fragment below. The column has been annotated to indicate that the column has an unsupported data type, and the unsupported string is substituted for whatever values of the data type might show up.

'<unsupported: bit>' AS "FinishedGoodsFlag",

You will want to replace the string '<unsupported: bit>' with a properly defined object reference, such as in this picture.

1. 
   Select the query item whose data type you want to convert in the module metadata tree.
2. Choose properties from the more context menu.
3. Click on the view or edit button.

This will bring up the expression editor for the query item.

4. Edit the expression to have the pass-through expression.
5. Press OK.
6. Select the query item, view the properties slide out, and set the hide from users property to off. This will allow users to see the query item as, by default, a data base column which has a data type which is unsupported by Cognos is imported into a module as hidden.

Here is a fragment of the Cognos SQL which is generated with an expression with a pass-through expression defined. As you can see, the annotation of the unsupported data type – in this case '<unsupported: bit>' – has been replaced by the fragment of pass-through expression which the query engine will pass through to the data base.

              WITH

DimProduct0 AS

(

SELECT

DimProduct01.ProductKey AS ProductKey,

DimProduct01.ProductAlternateKey AS ProductAlternateKey,

DimProduct01.ProductSubcategoryKey AS ProductSubcategoryKey,

DimProduct01.WeightUnitMeasureCode AS WeightUnitMeasureCode,

DimProduct01.SizeUnitMeasureCode AS SizeUnitMeasureCode,

DimProduct01.EnglishProductName AS EnglishProductName,

DimProduct01.SpanishProductName AS SpanishProductName,

DimProduct01.FrenchProductName AS FrenchProductName,

DimProduct01.StandardCost AS StandardCost,

{cast (FinishedGoodsFlag as Int)} AS FinishedGoodsFlag,

DimProduct01.Color AS Color,

DimProduct01.SafetyStockLevel AS SafetyStockLevel,

               DimProduct01.ReorderPoint AS ReorderPoint,

Considerations and tradeoffs

If a schema was loaded prior to 11.2.1, you need to reload the schema before the embedded pass-through expression method will work. If you don't you will get an error similar to these two errors below:

XQE-DAT-0001
Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the varchar value '<unsupported: bit>' to data type int.

Native SQL expression '{cast (FinishedGoodsFlag as Int )} ' cannot be evaluated for local processing. The generated query has no column references that identify a database to obtain the result from.

The requirement to reload the schema can be a problem if you have a large investment in security filters as they will be lost.

If this is too expensive a hit then the existing method, using SQL tables with pass-through SQL, will be your only option apart from implementing views in your data base, which have the data type conversions already performed, which, as I mentioned above is probably the optimal approach to the problem in the first place.

In addition, if you have an existing module, then you need to do additional modelling to allow the embedded pass-through expression method to execute correctly. There are two methods available. One is to set the query subject’s Item list property value to used. The other is to delete the query subject which contains the column with the unsupported data type and add it back again. Usually, the relationships between it and the other tables will remain although you will need to confirm this.

It is not guaranteed that a pass-through expression will execute without error in all cases.

-------

Update July 26, 2022:

It is possible that, for an older module, testing the pass-through expression in the expression editor will produce an error similar to the following.  The pass-through expression will work in the data grid and in reporting.  If the parent query subject's Item list property is set to used the error will not happen. It probably is the case that, if you encounter this situation and the problem is restricted to the error happening only in the expression editor, you would only need to set the Item list property to used so you can test the expression and that you should set the property back to the default setting, which is automatic.

XQE-PLN-0359 Native SQL expression '{cast (FinishedGoodsFlag as integer)}' cannot be evaluated for local processing. The generated query has no column references that identify a database to obtain the result from.

--------

Thanks to Nigel Campbell for his helpful review and comments.