Repointing existing 10.2 reports sourcing from oracle to Amazon Redshift

[Vizmonk]

Hi All,

Our Scenerio:

Cognos version 10.2 currently reports and framework model are pointing to oracle 12 database
We would be migrating these oracle data bases to cloud Redshift
Now wanted to understand the process of repointing the package and reports to new Redshift cloud connection

1. what are the steps to be performed at Administration to create new Redshift connection
    2. what are the steps to be performed at framework manager level to point to new data source with same structures as oracle
    3. How to validate and publish these packages and test the reports. Do I need to republish these packages again?
    4. How to repoint the reports to new packages any method to repoint in single go(bulk 2000+ reports)
    5. Kindly let me know if there are any challenges

[MFGF]

Hi All,

Our Scenerio:

Cognos version 10.2 currently reports and framework model are pointing to oracle 12 database
We would be migrating these oracle data bases to cloud Redshift
Now wanted to understand the process of repointing the package and reports to new Redshift cloud connection

1. what are the steps to be performed at Administration to create new Redshift connection
    2. what are the steps to be performed at framework manager level to point to new data source with same structures as oracle
    3. How to validate and publish these packages and test the reports. Do I need to republish these packages again?
    4. How to repoint the reports to new packages any method to repoint in single go(bulk 2000+ reports)
    5. Kindly let me know if there are any challenges

Hi,

The first major issue I can see is that Amazon Redshift isn't a supported data source for Cognos 10.2

https://www.ibm.com/support/pages/ibm-cognos-business-intelligence-102-supported-software-environments

The earliest version I can see that supports Redshift is 10.2.2 with Fix Pack 2. If you really are on Cognos 10.2 and not 10.2.2 your first step is going to be upgrading your Cognos instance. You might as well upgrade to Cognos Analytics at that point.

The second thing to consider is which query engine your Oracle connection and packages/reports are using. With Oracle there are multiple ways to connect. ODBC and Oracle Native Client are both options that use the Compatible Query Engine (CQE). JDBC uses Dynamic Query Mode (DQM). Amazon Redshift is DQM only. If your connections to Oracle are via JDBC and your packages are published in Dynamic Query Mode you have a lot less work to do, otherwise you are going to need to convert all your Framework Manager models to DQM (no simple task) and then re-test your entire set of reports and fix any that are broken. If I were you I'd probably convert to DQM before you move away from Oracle.

https://community.ibm.com/community/user/businessanalytics/blogs/norbert-bracke1/2019/07/29/cognos-migrating-form-cqm-to-dqm

There is a link here that covers setting up a connection from Cognos 10.2.2 to Amazon Redshift

https://www.ibm.com/support/pages/connecting-ibm-cognos-bi-1022-amazon-redshift

Other challenges you might face are things like use of Stored Procedures and Database Functions in your models and reports. I don't know Redshift well enough to know if it even supports these, and if it does whether the syntax is different from Oracle. You will need to evaluate where (if) these are used and how to work around them.

Framework Manager allows you to add new data source connections to existing models. Assuming you are using a multi-tier modelling approach, best practice would be to import new data source query subjects for the Redshift tables, join them as appropriate, then re-map the existing Model query subjects from the Oracle data source query subjects to the Redshift data source query subjects.

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=administration-remapping-objects-new-sources

You will definitely need to re-publish your packages. If you use the same package names and locations your reports won't need to be re-pointed.

Lastly, yes, there are huge challenges as you can see above. What you are attempting to do here is a big, complex project with many pitfalls. Probably the most painful part will be converting from Compatible to Dynamic Query, followed by the differences you will then find in Redshift vs Oracle.

Good luck!

MF.

[Vizmonk]

Thanks for quick reply

Is it really required to change CQM to DQM as we have previously connected Cognos Analytics to Redshift using CQM FM model. Just wanted to Check Cognos through Redshift only support DQM?

[MFGF]

Thanks for quick reply

Is it really required to change CQM to DQM as we have previously connected Cognos Analytics to Redshift using CQM FM model. Just wanted to Check Cognos through Redshift only support DQM?

Hi,

According to the Supported Environments page for 10.2.2 the connection to Amazon Redshift is DQM only.
https://www.ibm.com/support/pages/node/614217
From the page above, click on the Software link, and then click across to the Supported Software tab. Scroll down to the Data Sources section and you'll see Amazon Redshift, with a note that says "Available in the Dynamic Query Mode. For information on how to create a connection to this data source please review the following video."

The only other way I can imagine it's possible to connect to Redshift is if there is an ODBC driver. This would be Compatible Query mode if it's available, but you're then limited by the capabilities and stability of the ODBC driver - it may or may not support all the functionality Oracle supports.

Cheers!

MF.