Cognos analytics mongoDB connectivity

[Kiran P]

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

[MFGF]

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.

[the6campbells]

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

[Kiran P]

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

[the6campbells]

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.

[Kiran P]

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

[the6campbells]

"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)

[Kiran P]

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

[Kiran P]

Hi Campbell

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

Regards
Kiran

Sent from my ZUK Z2132 using Tapatalk

[crowemi]

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?

[Kiran P]

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

[the6campbells]

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

[Kiran P]

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

[the6campbells]

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.

[kslanicky]

Is SSL REQUIRED to connect Cognos to Mongo? 
I believe I have everything set up correctly on the Mongo BI Connector, but i'm getting this error from Cognos

xqe-ds-0006 unable to logon to the data source
ssl is required when using cleartext authentication

I can connect via SQL (using the System DSN ODBC connection)

Connection information attached in screenshot

[MFGF]

Hi,

Did you copy the authentication JAR file into your Drivers folder?

The image you posted shows a connect string that doesn't look like the instructions in the below technote. I'd amend your connection string to look like the one IBM suggest and see if it gets you any further:

https://www-01.ibm.com/support/docview.wss?uid=ibm10873328

Cheers!

MF.

[kslanicky]

Thanks for the quick reply... you are right... and I TRIED to get it to look MORE like that connection string... but i'm still not 100% successful.

Where is this 'authentication jar' that you speak of?  I will reread the install instructions, but if you can point me in the right direction, that would be great.

I did put the MySQL Driver in there... is that what you mean, or is there another one?

[kslanicky]

I see what you are talking about now... we have two instances of Mongo... one secured, one that is not... let me play around with those two and see if I can get anything different

What I THINK my solution is is to get the useSSL=false in the connection string... but that's the part I'm not able to get looking like the technote

I can get it like this... but it's still not exactly the same... and its still giving me the same error

[kslanicky]

Still no luck - but a different error now

The authentication driver was already in place... and when I add in the auth link in the connection... which gives me the following connection string:
;LOCAL;JDBC;URL=jdbc:mysql://servername:3307/DBNAME;DRIVER_NAME=com.mysql.jdbc.Driver;useSSL=false;authenticationPlugins=org.mongodb.mongosql.auth.plugin.MongoSqlAuthenticationPlugin;

I get an error that states:
Unable to load authentication plugin
org.mongodb.mongosql.auth.plugin.MongoSqlAuthenticationPlugin
com.mysql.jdbc.AuthenticationPlugin


Will keep plugging away on this... and will also try connecting to the MongoDB with no authentication (although that won't be a long term solution, as that's a sandbox environment)

[kslanicky]

I was able to get this working finally... my key issue was that I was using a version of the MySQL data connector that did not support the authentication driver

this one doesn't work:
mysql-connector-java-8.0.13.jar.bak

this one does work:
mysql-connector-java-5.1.47.jar


This was mentioned in the documentation here:  https://docs.mongodb.com/bi-connector/master/reference/auth-plugin-jdbc/

I'm able to connect now!

Next issue is performance of handling arrays in collections... currently performance is abysmal when trying to 'join' the child tables (arrays) to the main collection

[the6campbells]

refer to Mongo's docs with respect to using the MySQL JDBC driver to connect.

note that Mongo does not support the MySQL 8.x JDBC driver, must use older 5.x drivers

https://docs.mongodb.com/bi-connector/master/reference/auth-plugin-jdbc/