We have a need to use Excel files as a data sources and the files get updated daily.  Since Cognos Analytics V11.1.3 only lets us manually update Excel data we want to automate this process.  In my research I found a JDBC driver for Microsoft Excel that would do exactly what we needed. Only problem is how to add this JDBC . jar file to the Cognos Analytics system so it is an option in the data connection selection.  Does anyone know how to add this driver to the existing system?  The admin & security documentation talks about JDBC drivers but doesn't show how to add them.  I did put the driver in the <install>\driver folder but the selection options don't give me the right connection string options.
Thanks for help experts!

------------------------------
Scott Taylor
Cognos Administrator
Chelan County PUD
Wenatchee WA
------------------------------

#CognosAnalyticswithWatson

Hi Scott
If it is a third-party JDBC driver (i.e. not validated or a supported data source by IBM) then it is highly unlikely that you can use it to access your data.
You are correct all drivers go into the <install>\drivers folder and are configured through the admin console under the old generic JDBC driver config.
I tried to use some third-party CData drivers recently without luck and gave up.
IBM really needs to improve it's data connectivity options in this respect - other competitive solutions support these sort of integrations quite easily frustratingly !
Nick

------------------------------
Nick Waters
------------------------------

Thanks Nick for confirm what I thought was the case.  I totally agree that IBM needs to up their game in connectivity options.  Every time I turn around someone is asking to connect to our governed data and it doesn't fit the cookie cutter approach IBM has set up.  Maybe the Jupiter notebook connection approach is what we'll need. Since Python can / has connectors for almost anything, IBM is driving us towards this method.
Thanks again.

------------------------------
Scott Taylor
Cognos Administrator
Chelan County PUD
Wenatchee WA
------------------------------

Original Message:
Sent: Tue November 26, 2019 03:42 AM
From: Nick Waters
Subject: Adding new JDBC driver to Cognos Analytics?

Hi Scott
If it is a third-party JDBC driver (i.e. not validated or a supported data source by IBM) then it is highly unlikely that you can use it to access your data.
You are correct all drivers go into the <install>\drivers folder and are configured through the admin console under the old generic JDBC driver config.
I tried to use some third-party CData drivers recently without luck and gave up.
IBM really needs to improve it's data connectivity options in this respect - other competitive solutions support these sort of integrations quite easily frustratingly !
Nick

------------------------------
Nick Waters
------------------------------

There is an enhancement request marked as "future consideration" to schedule the upload of files: https://ibm-data-and-ai.ideas.aha.io/ideas/CAOP-I-1209 
Once this feature is enabled then there is no need for a data source.

Personally, I believe excel and csv uploads are for one-off exploration and discovery. Once data like this becomes important enough to be refreshed every day, it should be in the enterprise data warehouse. Governed, cleansed, automated, and secured. 

I think of the development lifecycle and your datasources. Files in dev / test / prod all with same name, file location, structure? Unlikely and sounds like a potential admin nightmare. Other tools might allow you do do this, but just because you can doesn't mean you should =) 

------------------------------
Luke Peterson
Business Analytics Advocate
IBM
------------------------------

Original Message:
Sent: Tue November 26, 2019 02:03 PM
From: Scott Taylor
Subject: Adding new JDBC driver to Cognos Analytics?

Thanks Nick for confirm what I thought was the case.  I totally agree that IBM needs to up their game in connectivity options.  Every time I turn around someone is asking to connect to our governed data and it doesn't fit the cookie cutter approach IBM has set up.  Maybe the Jupiter notebook connection approach is what we'll need. Since Python can / has connectors for almost anything, IBM is driving us towards this method.
Thanks again.

------------------------------
Scott Taylor
Cognos Administrator
Chelan County PUD
Wenatchee WA
------------------------------


Original Message:
Sent: Tue November 26, 2019 03:42 AM
From: Nick Waters
Subject: Adding new JDBC driver to Cognos Analytics?

Hi Scott
If it is a third-party JDBC driver (i.e. not validated or a supported data source by IBM) then it is highly unlikely that you can use it to access your data.
You are correct all drivers go into the <install>\drivers folder and are configured through the admin console under the old generic JDBC driver config.
I tried to use some third-party CData drivers recently without luck and gave up.
IBM really needs to improve it's data connectivity options in this respect - other competitive solutions support these sort of integrations quite easily frustratingly !
Nick

------------------------------
Nick Waters

The ODBC connection will work as I've used that for many years.  I just wanted a method to connect with JDBC so I could use the web-based modeling tool as well as automate the data capture.  ODBC doesn't do that for me unless I use Framework Manager packages and we are trying to move away from that technology.

------------------------------
Scott Taylor
Cognos Administrator
Chelan County PUD
Wenatchee WA
------------------------------

Original Message:
Sent: Tue November 26, 2019 03:42 AM
From: Nick Waters
Subject: Adding new JDBC driver to Cognos Analytics?

Hi Scott
If it is a third-party JDBC driver (i.e. not validated or a supported data source by IBM) then it is highly unlikely that you can use it to access your data.
You are correct all drivers go into the <install>\drivers folder and are configured through the admin console under the old generic JDBC driver config.
I tried to use some third-party CData drivers recently without luck and gave up.
IBM really needs to improve it's data connectivity options in this respect - other competitive solutions support these sort of integrations quite easily frustratingly !
Nick

------------------------------
Nick Waters
------------------------------

As always, cast your vote on the RFE system.

Both newer and older Admin pages can be used to define connections via the supported sources where a JDBC driver is involved.

Approximately 40 of the supported data sources are accessed via a supported JDBC driver.

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Tue November 26, 2019 03:42 AM
From: Nick Waters
Subject: Adding new JDBC driver to Cognos Analytics?

Hi Scott
If it is a third-party JDBC driver (i.e. not validated or a supported data source by IBM) then it is highly unlikely that you can use it to access your data.
You are correct all drivers go into the <install>\drivers folder and are configured through the admin console under the old generic JDBC driver config.
I tried to use some third-party CData drivers recently without luck and gave up.
IBM really needs to improve it's data connectivity options in this respect - other competitive solutions support these sort of integrations quite easily frustratingly !
Nick

------------------------------
Nick Waters
------------------------------

Hi Scott,
Here's the procedure:
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ig_rolap.doc/t_ig_rolap_sql_connect.html
Regards,
Andrejs

------------------------------
Andrejs kuliks
------------------------------

You may create a ODBC connection on your server and clients machines and use this ODBC connection to create a data source in Cognos Administration Portal.

------------------------------
JEAM COELHO
------------------------------