Tuesday, November 1, 2016

How to make Cognos connection dynamic based on selected environment

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

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.
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.

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.

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].

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

                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
                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
                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
Add this table into data layer with select * from [Cognos Connection].[OTHER_DB_MAP]
And then make it available in parameters map.
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]

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

1 comment:

  1. Shilpi SrivastavaJuly 6, 2017 at 12:01 PM

    This is a great post and helped me in my project Charles . You are awesome !