Cognos Analytics

 View Only

Dynamic data server property overrides

By IAN HENDERSON posted Tue November 29, 2022 05:00 AM

  

Data server property overrides

Overview

In Cognos Analytics (CA) 11.2.4, you can dynamically switch between different data servers by defining override macros or values for the data server connection, catalog, and schema properties of the data servers used in a data module. The overrides perform the same function as relinking but without the requirement of user action.

One use for override properties would be to dynamically switch a data module to use different data servers for each of the development, test, and production environments, without any modelling or editing.

Another use would be for multi-tenant situations, where each tenant has a different instance of the same data base schema.  For example, there could be N number of schemas, each with the same metadata but for a different tenant.

Although you can define the override values as strings, it would probably be the case that you would want to use macros.

Description

The override settings are located in the properties slide out of a schema in the sources slide out.  You view the sources slide out by pressing the sources button.  As shown in the picture below, it is on the left, just below the save button.  Because I have clicked on the more button for the schema, the context menu is displayed, and the sources button has a border showing around it.

sources slide out

There are settings for data server connections, catalogs, and schemas. For each, there is an override checkbox and a value property. Clicking on the latter will open the overrides editor.

The override checkbox determines if the default value of the property is to be used or if the override value is to be used. If it is unchecked, the default value is used. If it is checked, the override value is used. 

The default values are derived from the data server schema used during metadata import or from the latest data server schema used during relinking.  

In the picture below, the override checkbox for Data server connection has been checked on.  The expression of the override is displayed in the value field.  You can see that it is a macro.  Because the expression cannot fit into the allotted space, the middle is truncated from the display. The Catalog and Schema checkboxes are not checked on.   The value for Catalog is empty, as the data source used does not have catalogs.  The value for Schema is GV1022DW, which is the name given by the DBA to the schema which I am using.

sources advanced panel


Both the default values for the data servers as well as the override values are stored in the data module and will persist from session to session. If you uncheck the override checkbox for any of the data server connection, catalog, or schema properties, the default value is restored and displayed.  If you check the checkbox on, the override value will be displayed.

The value property contains the override value. It can be a string or a macro. Examples of macros are shown below. You can use session parameters such as tenantID, machine, or account.personalInfo.userName in the macro.

If your data base does not use catalogs, the default value is null. A discussion of null values for override properties and how to define them is in the nulls section below.

The overrides are defined in individual data modules. If you reference a data module in another data module the override settings will be honoured.

Overrides and default values

You can use any of the overrides individually. For example, assume that you have the same data source name on each of your development, test, and production servers. Assume that the only difference between the three is that they use differently-named schemas, presumably with the dev and test environments with restricted data. You could define a macro for the schema override which would use the appropriate schemas.

A similar example would be if you have a different schema for each TenantID.  In that case you would only need to define a macro for the schema.  Another case would be if the schemas were named the same but there were differently named data servers for your environments.  In that case you would only need to define a macro for the Data source connection property override.

override editor



You can verify that the override is executing by selecting a query subject in the data module and examining the qualifiers of the table in the query information dialog.

In the picture below a table in a database which has both catalog and schemas has been used for the query.

query information for a db with catalog.png

Here is the same table in the same data module, but viewed in another environment, which causes the override values to use another database. In this case, it is a database which does not have catalogs, which is shown by the absence of a value for catalog.

query information for a db without a catalog _db2 secured.png

Thus, you would be seamlessly generating the appropriate SQL as defined by the override values.

If you relink a schema in the sources, the override property values will be removed from the module.  You can restore them by choosing to undo the relinking, via the undo button.  You need to be aware of this prior to performing relinking.  Since the override properties allow you to dynamically relink to other schemas, you normally would not need to perform the manual relink. 

The table and column metadata of the data sources used must be the same.  You need to be aware of differences of data type, precision, scale, nullable, and similar properties.  It is possible that the difference could cause different behaviour.

Data sets, uploaded files, and FM packages do not have the data server override properties.

Macros

The general Cognos macro syntax is used for the override properties and, if you have used them elsewhere, you would be familiar with what to do for the override properties. It would be out of scope of this document to provide a longer review of macro syntax. Hopefully, the examples shown in this document could be helpful to get your foot into the water.  I have included a link to the dynamic query Redbook, where there is a chapter on macros, later on in this paper. 

The result of the macro expression should be the value which you want to use for the context.

Here is an example of a macro. This macro is used to determine the machine name, which it gets from the $machine session parameter, and, depending on which machine, specifying a value which would be assigned to the variable. In this case, the variable in the macro is called datasource. The variable name is arbitrary and can be whatever you want.

This particular macro is used to dynamically switch from one data source to another depending on whether the data module is in the development environment, the testing environment, or the production environment.

#let datasource =
case $machine
when 'Holst' then 'GOSLDW_DNB_DB2'
/* this is the development machine */
when 'Scarlatti' then 'GOSLDW_DNB_MSSQL_TEST'
/* this is the testing machine */
when 'Buxtehude' then 'GOSLDW_DNB_DB2_SECURED'
/* this is the production machine */
else 'nothing'
end;
datasource #

In the below picture the preview shows the value of GOSLDW_DNB_DB2.  This is because the machine name is Holst.  This resolved value would be used as the override.  In this case it is the override value for the data server connection.

resolved value for a macro.png



As stated, the macros follow Cognos standard macro syntax, including Cognos session parameters and macro functions. Most people who have worked with macros in FM will feel quite comfortable but newcomers should feel confident that they will be able to be successful without too many tears as well.

There is an enhancement request to add the session parameters to the expression editor. Macro functions are there already. You can use FM's expression editor to discover what session parameters are available to you.

https://jsw.ibm.com/browse/MOSER-4483

Here is another macro, where the substr function operates on a session parameter.

#let schema =
case substr($account.personalInfo.userName, 0, 12)
when 'scarter' then 'DW_SCHEMA'
else 'gosalesdw'
end;schema#  

Here is a very simple example of how to concatenate within a macro.

#let datasource=
case $machine
when 'Holst' then 'GOSLDW_DNB_DB2'+ $runLocale
/* this should resolve to GOSLDW_DNB_DB2en as my run locale is English */
when 'Scarlatti'then 'GOSLDW_DNB_MSSQL_TEST'
/* this is the testing machine */
when 'Buxtehude' then 'GOSLDW_DNB_DB2_SECURED'
/* this is the production machine */
else 'nothing'
end;
datasource#

Macro concatenation example.png



Empty lines before or after a macro are included in the resolved override value for the property. Text outside of a macro is appended to the resolved value of the macro. This is a powerful and flexible but sharp tool. In some cases, you might want to make use of that. In others you might not, which is why I talk about this in the troubleshooting section.

Comments in macros

Comments associated with a macro must be contained in the macro itself, unlike the expression in the expression editor. This is the case for macros used in other places too.  If you use a comment with a non-macro value the comment will be incorporated into the resolved value, which essentially puts paid to that.  Since macros are going to be far more useful for this functionality, and should be what you use, it should not matter much.

In the example below, the latter situation is demonstrated.  The comment would be added to the resolved value being passed. The comment delimiters are treated as ordinary string values.  You can see this in the preview section of this illustration.

GOSLDW_DNB_DB2_secured
/* hello this is a comment */
resolved value for a macro.png
Here is a comment outside of the macro and the resulting resolved value, which you can also see in the preview, which will also result in the comment being appended to the resolved value.


                                     #'GOSLDW_DNB_SECURED'#
                                     /* Hello this is a comment */
comment outside of the macro actual macro.png


In the following example, because the comment is incorporated into the macro, the comment delimiters delineate the value and the comment.  You can see the preview value consists solely of the macro's resolved value.

#'GOSLDW_DNB_DB2_secured'
/* hello this is a comment */#
resolved value for a macro.png



You will have also noticed the comments in the example macro in the macro section, where I have added notation to indicate which machine is the development machine, which is the testing machine, and which is the production machine.

Further reading on macro syntax
  • Chapter 4 of the Dynamic query Redbook
https://www.redbooks.ibm.com/abstracts/sg248121.html


Override editor

The override editor, although derived from it, is not quite like the expression editor. In the expression editor you cannot have an expression which is empty. In the override editor you can have an empty expression, as it is necessary to be able to specify a null value.

Normally, overrides would be defined in macros, where you would define a null value by two adjacent single quotes. An example is shown in the nulls section, below. If you determine that you do not need a macro, the ability to define a null value could be helpful.

Here is a catalog override which is null.  You will notice that the OK button is enabled even though the expression is empty. 

resolved value for a macro.png

 

Nulls

The catalog may or may not be a property of your databases.   It may be the case that some do, and some don't. If that is the case, then you need to determine how to deal with that.

If you want a value to be null you would either use an empty expression in the override value editor, if you are using a defined string value, or a pair of single quotes, if you are using a macro.  If all instances of the databases used do not have catalogs another approach would be to simply leave the override checkbox for catalog unchecked and the default value, which would be null, would be used in all cases.

Here is a macro example for defining a null in a macro. I have formatted the macro expression on multiple lines for clarity. If the machine is called Scarlatti, then the resolved value will be GOSALESDW.  If not, then the else '' will cause the resolved value to be a null.  This is because the data base being used by the data module on Scarlatti is a MS SQL server instance of our sample GOSLDW database and it has a catalog.  

#let catalog =
case $machine
when 'Scarlatti' then 'GOSALESDW'
/* the data source for the test environment uses MS SQL server so it needs a catalog to be specified */
else ''
/* the dev and prod environments use DB2 so null must be specified if the override is enabled */
end;
catalog#

 

Troubleshooting


Here are some problems I ran in to.

1.
Empty lines at the start or end of an override, either a macro or a string, will be incorporated into the generation of the data source values in the SQL.

In the picture below, on the left, is the macro editor.  You will notice 3 empty lines after the macro.  Because those lines are included in the resolved value they were added to the generated SQL, which you see on the right. 

empty linesSometimes the SQL will generate, with the space added, as we see in the preceding picture.  Sometimes it won't.   If the latter is the case, you might run into errors like this in the log files.



CM-REQ-4158 The search path "personalDataSourceSignon('GOSLDW_DNB_DB2_secured
')" is invalid. An object may contain invalid syntax, or an unsupported character, or the user account in the namespace may not have sufficient privileges. Check the object to ensure that the target destination location does not contain special characters.


2.

As mentioned in the comments in the macros section, if you want to have comments in your expression you need to incorporate them into the macro itself.

3.

Watch out for things such as quotes if you are copying and pasting macros from other applications such as MS Word, just as you need to watch out for the proper quotes when you are copying and pasting in other contexts.

4.

Ensure that the case of strings used in your macros or as values match that of the data source.  For example, GOSLDW_DNB_DB2_secured<> GOSLDW_DNB_DB2_Secured<> GOSLDW_DNB_DB2_SECURED

5.

If the override value does not match what exists, the problem can manifest itself in different ways.

When queries are run there will be a query engine error.  The error will be displayed in the reporting tool.

For data servers the error will be similar to this:

Data source "GOSLDW_DNB_DB2_ian_wuz_here" was not found in Content Manager.

For catalogs and schemas, the error will be similar to this:

XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: "GV1022DW_ian_wuz_here.EMP_TERMINATION_LOOKUP" is an undefined name.

The data grid will display a button which will have the error message.

If this state exists and you try to view the query information you will encounter a golf flag icon in the query information dialog.   This is a known defect. The error message is not properly caught and displayed. It should be displayed in the manner which error messages are displayed in the data grid.

You can retrieve the error message by opening the network tab of your browser’s developer tools and pressing the try again button in the query information dialog.

golf flag and dev console
This is what should be displayed in query information.
Proper error handling
6. 

If you have set customized permissions, you might encounter an error when viewing the generated SQL in report studio or in a dashboard, stating that you do not have permissions to view the generated SQL.  You will need to set the View Generated Query Text capabilities checkbox on for the appropriate users for the objects complained about.  In the example below, the Gregorian calendar data module and its source, GregorianCalendar.csv needed to have the setting turned on.

XQE-PLN-0552 You are not allowed to view the generated SQL due to restrictions on the following metadata sources: '[/content/folder[@name='Calendars']/folder[@name='Source files']/uploadedFile[@name='GregorianCalendar.csv'], /content/folder[@name='Calendars']/module[@name='Gregorian calendar']]'

For an environment upgraded from prior to 11.2.4, the View Generated Query Text capabilities checkbox should be enabled regardless of whether capabilities has been set on the object or not.
Capabilities for view generated sql
Acknowledgements  
Thanks to Henk Cazemier for macro knowledge transfer and to Alan Tran, Asif Saleh, and Serge Ivanov for capabilities knowledge transfer.












#CognosAnalyticswithWatson
2 comments
44 views

Permalink

Comments

Thu December 08, 2022 03:44 AM

Thanks for sharing your knowledge in such a high quality!

Wed November 30, 2022 10:52 AM

Hi,

Thanks for this post. 

It seems to be a promising feature. I am very excited to test it.

Best regards,

Patrick Neveu