Author Topic: Cognos analytics mongoDB connectivity  (Read 858 times)

Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Cognos analytics mongoDB connectivity
« on: 18 Jan 2018 08:26:20 pm »
Hi

Can someone help me with the process of establishing a connection to mongoDB 3.2 from cognos analytics 11.0.8 (or even 11.0.6) that I can use in FM? Is there a help guide or blog explaining how to do this either with a free driver or a commercial one?

Thanks
Kiran

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,331
  • Forum Citizenship: +623/-10
  • Cognos Software Muppet
Re: Cognos analytics mongoDB connectivity
« Reply #1 on: 19 Jan 2018 02:28:11 am »
Hi

Can someone help me with the process of establishing a connection to mongoDB 3.2 from cognos analytics 11.0.8 (or even 11.0.6) that I can use in FM? Is there a help guide or blog explaining how to do this either with a free driver or a commercial one?

Thanks
Kiran

Hi,

This isn't something I have ever done, but the process appears to be that you get a copy of "MongoDB Connector for BI" (version 2.2) and use this as the plumbing between MongoDB and Cognos Analytics. I found the documentation for it here, but there are no explicit instructions for connecting from Cognos Analytics. I'd look at the instructions for connecting from one of the other tools they mention, and see if you can relate it back to the connection dialog from CA.

Good luck!

MF.
Meep!

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 96
  • Forum Citizenship: +6/-1
Re: Cognos analytics mongoDB connectivity
« Reply #2 on: 21 Jan 2018 08:59:56 am »
Cognos Analytics 11.8+ requires that you use Mongo BI Connector 2.2. 

Use the Mongo BI Connector connection editor in Cognos Analytics to point to a Mongo BI Connector server.

Mongo BI Connector 2.2 requires applications use the MySQL JDBC driver. Subject to how you set up security, you may need to use an optional security JAR provided by Mongo. This is covered in the Mongo BI Connector documentation. The Mongo documentation also explains how it exposes documents in Mongo via their BI Connector product.

Mongo BI Connector 1.x was supported by prior versions of Cognos Analytics.
Mongo discontinued support of 1.x.

DO NOT attempt to use the existing MySQL support in older Cognos Analytics releases to access Mongo BI Connector 2.
It is not supported and will not work correctly. Only Cognos Analytics 11.8+ can be used with Mongo BI Connecter 2.2+.





« Last Edit: 21 Jan 2018 09:28:19 am by the6campbells »

Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Re: Cognos analytics mongoDB connectivity
« Reply #3 on: 22 Jan 2018 02:11:10 am »
Thanks for the replies. I could only find MongoDB connector for BI 2.3.1 for download and I will go ahead and use that with 11.0.8 to see how it works.

Thanks,
Kiran

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 96
  • Forum Citizenship: +6/-1
Re: Cognos analytics mongoDB connectivity
« Reply #4 on: 23 Jan 2018 06:24:46 am »
You can get 2.2 from the archived versions link on the same page where you saw the drop list only showing 2.3.1.

Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Re: Cognos analytics mongoDB connectivity
« Reply #5 on: 24 Jan 2018 01:19:45 am »
Yes, I saw that afterwards. But looks like we don't need the connector to be installed. Looks like we need to get compatible MySQL driver and cognos internally uses connector 2.2.1 and hence all references to cognos are removed from mongodb site. Lack of documentation is making this difficult :) I am trying with MySQL driver; till now if I give any incorrect details I see an error but if I give all details correctly cognos just keeps on trying without being succeeded or without throwing any error. Don't see anything in log files too.

Thanks,
Kiran

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 96
  • Forum Citizenship: +6/-1
Re: Cognos analytics mongoDB connectivity
« Reply #6 on: 24 Jan 2018 07:44:42 am »
"But looks like we don't need the connector to be installed"

You have to have a Mongo BI Connector installed and running somewhere in your environment for Cognos BI  to connect to.

"Looks like we need to get compatible MySQL driver and cognos internally uses connector 2.2.1"

Mongo requires that Java applications use the MySQL JDBC driver to establish a connection to a running Mongo BI Connector server.

Once a connection is made to the Mongo BI Connector server, Cognos Analytics will detect that the server is Mongo BI Connector server and will issue requests to it that Mongo BI Connector supports.

Why don't you confirm your ability to connect to your Mongo BI Connector server by using a simple Java program or open source tool such as SQLSquirrel. If it cannot connect using the MySQL JDBC driver debug your configuration/environment issues from that perspective.

If you have to use the Mongo security plugin (https://docs.mongodb.com/bi-connector/master/tutorial/install-auth-plugin-jdbc/) you would have to include their JAR in <cognos>\drivers along with the MySQL JAR. The connection string they expect an application to use pass to the driver would need to include the name value pair shown in figure 3 of the Mongo documentation (authenticationPlugins=org.mongodb.mongosql.auth.plugin.MongoSqlAuthenticationPlugin)



Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Re: Cognos analytics mongoDB connectivity
« Reply #7 on: 29 Jan 2018 09:22:55 am »
Hi,

Thanks for the inputs. I have now installed mongodb connector for BI 2.2 rc1, followed the steps to generate schema drdl. When I tried to create a mongosqld service using config file, the service is created but it is not starting up. So, I opened a command prompt and started the daemon on 3307 port to first test the connectivity. I placed the mysql driver in /drivers folder on app tier. Below are two environments I tried and the problems i am facing.

1) Single server environment - 11.0.8;
mongodb - no authentication (hence did not install / use any auth plugin)
no ssl to mongodb

When I try to connect to BI Connector server on 3307 port I get the following error.

2018-01-29T18:40:58.859+0530 F NETWORK    [conn2] handshake error: ERROR 1043 (0
8S01): recv handshake response error: ERROR 1105 (HY000): connection was bad
2018-01-29T18:40:58.861+0530 I NETWORK    [conn2] end connection <ipaddress masked>:55287 (0 connections now open)

In cognos portal while testing connection, I get this:

xqe-ds-0006 Unable to logon to the data source.
No operations allowed after connection closed.

When tried from squirrel i get the below error.

Unexpected Error occurred attempting to open an SQL connection.
class com.mysql.cj.core.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.

2) gateway and app tier on separate servers - 11.0.8

After starting the BI Connector server on 3307 port, when I try to test the connection, I get the below error:
XQE-JDB-0004 A problem occurred finding the driver class "com.mysql.jdbc.Driver".

This is weird because I placed the driver in /drivers folder and restarted the cognos service. Somehow I see that cognos service is not picking up the driver because I could delete the driver when the service is running (which was not the case in single server environment where it says the driver is in use) . I verified the folder to be the one being used by cognos service. I included the path in environment variables in windows server as well.

Any help would be appreciated.

Thanks,
Kiran

Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Re: Cognos analytics mongoDB connectivity
« Reply #8 on: 30 Jan 2018 11:59:36 am »
Hi Campbell

Thanks for your help. I could finally get it to work.

Regards
Kiran

Sent from my ZUK Z2132 using Tapatalk


Offline crowemi

  • Associate
  • **
  • Join Date: Aug 2018
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: Cognos analytics mongoDB connectivity
« Reply #9 on: 24 Aug 2018 01:19:25 pm »
Hi Campbell

Thanks for your help. I could finally get it to work.

Regards
Kiran

Sent from my ZUK Z2132 using Tapatalk

What was the solution to this issue?

Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Re: Cognos analytics mongoDB connectivity
« Reply #10 on: 12 Sep 2018 04:41:17 am »
Hi,

Looks like the documentation is clear now and moreover it asks us to use ODBC connectivity now. I actually followed the below steps to get it working.

1) install mongodb connector for BI
2) using mongodrdl exe, generate the schema file for your database as outlined in the documentation
3) create a windows service for mongosqld , using the config file that contains the schema path , mongo dB server and port etc, as outlined in the documentation
4) create a jdbc connection  in cognos to this service

But now there seem to be two changes - 1) generating schema file is optional and 2) odbc is used instead of jdbc. See below documentation.

https://docs.mongodb.com/bi-connector/master/launch/

Thanks,
Kiran

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 96
  • Forum Citizenship: +6/-1
Re: Cognos analytics mongoDB connectivity
« Reply #11 on: 15 Sep 2018 11:33:11 am »
But now there seem to be two changes - 1) generating schema file is optional and 2) odbc is used instead of jdbc. See below documentation.

ANSWER>

Cognos Analytics requires that you use JDBC to connect to Mongo BI-Connector. Some other vendor products are reliant on ODBC but not Cognos Analytics
Assuming you have correctly installed/configure Mongo-BI to access MongoDB etc, you would define a JDBC connection to the Mongo BI server.
Mongo requires that you use the MySQL J/Connect JDBC driver and their authentication JAR file

Contact Mongo if you cannot connect to their BI-Connector server with the MySQL J/Connect driver etc

Offline Kiran P

  • Community Leader
  • *****
  • Join Date: Oct 2013
  • Posts: 118
  • Forum Citizenship: +2/-0
Re: Cognos analytics mongoDB connectivity
« Reply #12 on: 18 Sep 2018 10:43:06 am »
I am able to create both JDBC and ODBC.

After running the mongosqld service (I created it as windows service), I am able to create a JDBC (mysql driver should be placed in /drivers folder) as well as odbc (installed mongodb-odbc-windows-32.msi and created a system dsn pointing to the mongosqld service running on 3307 port) .

Of course, if you are trying to create a connection under Data servers then cognos will create a jdbc connection.

Thanks,
Kiran

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 96
  • Forum Citizenship: +6/-1
Re: Cognos analytics mongoDB connectivity
« Reply #13 on: 19 Sep 2018 06:58:14 pm »
While Mongo may claim their BI Connector is a derivative of MySQL 5.x protocol, it is not identical.

It was not verified/certified with Cognos Analytics via ODBC.

Cognos Analytics only supports Mongo BI via JDBC and includes code etc specific to when a connection is detected to be a Mongo BI server.


 


       
Twittear