How to make Cognos connection dynamic based on selected environment

Check list of all posts

Assuming that we have DEV,SIT and UAT, we would like to connect Cognos with different databases based on selected environment. Certainly, we want framework to be same for all three different environments.  This document will demonstrate a practical solution with Netezza, where framework manager connect with MULTIPLE databases.

 Environment in detail

There are three different environments: DEV, SIT and UAT with single Netezza server
Each environment has one main database and multiple other databases.

DEV: MainDB_DEV, OtherDB1_DEV, OtherDB2_DEV and OtherDBn_DEV

SIT: MainDB_SIT, OtherDB1_SIT, OtherDB2_SIT and OtherDBn_SIT
UAT: MainDB_UAT, OtherDB1_UAT, OtherDB2_UAT and OtherDBn_UAT

Objective

To share the same report portal, same framework manager and connect to different databases based on user selection.
In other word, when user selects DEV, then Framework Manager Package will point to MainDB_DEV, OtherDB1_DEV, OtherDB2_DEV and OtherDBn_DEV. when user selects SIT, then Framework Manager Package will point to MainDB_SIT, OtherDB1_SIT, OtherDB2_SIT and OtherDBn_SIT.
 

Analysis

Two typical solutions below are not applicable for this situation:
How to prompt for selection of data source and/or schema (https://www-304.ibm.com/support/docview.wss?uid=swg21374654)
Dynamically change the data source based on logged in user (without SDK) (https://www-304.ibm.com/support/docview.wss?uid=swg21342840)
The main challenge is that there are multiple databases with each environment, it is not single database.

 Solution

Step1:
Make Cognos functional ID (Cognos User ID, password) can access all databases, including MainDB_DEV, OtherDB1_DEV, OtherDB2_DEV and OtherDBn_DEV, and all databases for SIT and UAT.

Step2:

Make a single Cognos data source called [Cognos Data Source] for the sake of explanation. Then make three real Netezza connections under [Cognos Data Source] to [MainDB_DEV], [MainDB_SIT] and [MainDB_UAT]. This gives users a possibility to choose what environment to use:  [MainDB_DEV], [MainDB_SIT] and [MainDB_UAT]

Make Database connection in framework manager as [Cognos Connection] point to [Cognos Data Source].

 

Step3:

Create a new Table OTHER_DB_MAP (DB_NAME_KEY, DB_NAME_VALUE) into MainDB and insert data as below

MainDB_DEV:

                DB_NAME_KEY = OtherDB1; DB_NAME_VALUE = OtherDB1_DEV
                DB_NAME_KEY = OtherDB2; DB_NAME_VALUE = OtherDB2_DEV
                DB_NAME_KEY = OtherDBn; DB_NAME_VALUE = OtherDBn_DEV
MainDB_SIT:
                DB_NAME_KEY = OtherDB1; DB_NAME_VALUE = OtherDB1_SIT
                DB_NAME_KEY = OtherDB2; DB_NAME_VALUE = OtherDB2_SIT
                DB_NAME_KEY = OtherDBn; DB_NAME_VALUE = OtherDBn_SIT
MainDB_UAT:
                DB_NAME_KEY = OtherDB1; DB_NAME_VALUE = OtherDB1_UAT
                DB_NAME_KEY = OtherDB2; DB_NAME_VALUE = OtherDB2_UAT
                DB_NAME_KEY = OtherDBn; DB_NAME_VALUE = OtherDBn_UAT
Step4:
Add this table into data layer with select * from [Cognos Connection].[OTHER_DB_MAP]
And then make it available in parameters map.

 
Step5:
Model query objects from MainDB at data tier as below
Select * from [Cognos Connection].[TableName]
Please note that scheme is specified in framework manager

Model query objects from OtherDB at data tier as below

Select * from #$OTHER_DB_MAP{' OtherDB1'}#.[schemeName].[TableName]
Select * from #$OTHER_DB_MAP{' OtherDB2'}#.[schemeName].[TableName]
Select * from #$OTHER_DB_MAP{' OtherDBn'}#.[schemeName].[TableName]

Notes

This solution is just for a practical situation, where all tables from other database are used as single table exposed in framework.  However, if these tables from other databases have to join with main database tables, it is not good solution, as it will impair the performance. The best solution in this case would be loads all these data into the main database, so that tables can be joined to get better performance.
Please also make sure that Catalog is NOT specified