Cognos PA can not connect to ClickHouse via ODBC

Post Reply
andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Cognos PA can not connect to ClickHouse via ODBC

Post by andreykadysh » Mon Aug 30, 2021 1:42 pm

Facing problems with connection Cognos Planning Analytics to database ClickHouse (https://clickhouse.tech/) via ODBC driver.
Error in tm1server.log looks like:

Code: Select all

    10800   [2]   ERROR   2021-08-26 14:11:42.175   TM1.SQLAPI   01000[Microsoft][ODBC Driver Manager] Cursor library not used.  Load failed

We use 64-bit ODBC Data Source driver provided by ClickHouse.

With the same parameters of ODBC Data Source, it turns out to connect successfully via the ODBC Connect utility (https://code.cubewise.com/odbc-connect) or via MS Excel.

Versions used:
ClickHouse server version 21.7.4.18 (official build)
IBM Cognos Planning Analytics 2.0.9.9

User avatar
paulsimon
MVP
Posts: 805
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by paulsimon » Mon Aug 30, 2021 2:03 pm

Hi

Try adding one of the following to your TM1S.CFG

# UseSQLFetch=T
# UseSQLFetchScroll = T
# UseSQLExtendedFetch = T

From memory, the first is the most basic form of ODBC that should be supported.

Regards

Paul Simon

andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by andreykadysh » Tue Aug 31, 2021 7:30 am

Hi Paul, thank you for response.

Tried each of these parameters separately, the same error.

User avatar
paulsimon
MVP
Posts: 805
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by paulsimon » Tue Aug 31, 2021 8:08 pm

Hi

All I can suggest is checking that you have a 64 bit ODBC driver and that you have the latest driver installed. Not too sure exactly what ClickHouse is. It claims to be an OLAP database, and a Columnar database and a NoSQL database. Whichever of those it is, it is not a standard SQL relational database and it is therefore probably doing some trickery to make a non-relational database look like a relational database so that it can be queried with ODBC. I presume that you have the driver installed on the same box as the TM1 Service, and that you are doing a regular Select query and not trying to call a stored procedure that might actually be using a cursor?

Regards

Paul Simon

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by Steve Rowe » Wed Sep 01, 2021 8:36 am

Also try with the "Use Unicode" check box checked (or unchecked)

Re that error message, I've noticed I've been getting it recently in the logs, but the jobs are still running correctly. I don't think that the message relates to the failure it is just noise from the ODBC driver.

HTH
Technical Director
www.infocat.co.uk

andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by andreykadysh » Thu Sep 02, 2021 12:28 pm

Hi! Already have tried this...

When we try to connect to ClickHouse from TI-process Architect reports: "SQL statement error", and at the same moment error appears in tm1server.log

But the fact is that SQL Statement is OK. It works successfully from ODBConnect or Excel.
We tried default SQL statement: SELECT 1. Result stay the same)

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by Steve Rowe » Fri Sep 03, 2021 8:12 am

Perhaps a rights issue then, make sure you are clear what account TM1 is connecting with (often the service account the DB is running against) and make sure this has the correct rights vs the DB
Technical Director
www.infocat.co.uk

andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by andreykadysh » Mon Nov 15, 2021 1:53 pm

UPD

Make PMR to IBM.
An answer from IBM:
Development has reviewed the details and they are treating connections to ClickHouse as a new feature.
A fix for this issue is expected to be made available with PA 2.0.9.12 expected tentatively in early Q1 next year.


So we are waiting :) And now, as a work around we execute ODBCConnect utility from TI-process, extract data from ClickHouse DB to csv, and then load data from csv to target cube.

David Usherwood
Site Admin
Posts: 1444
Joined: Wed May 28, 2008 9:09 am

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by David Usherwood » Mon Nov 15, 2021 3:36 pm

The Wikipedia writeup talks about
an extended SQL-like language that includes arrays and nested data structures, approximate and URI functions, and the availability to connect an external key-value store.
which suggests that it wouldn't work like a standard RDBMS query.
The same writeup mentions a Python wrapper so you could look at using TM1PY to pull the data and structures into TM1.

andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Re: Cognos PA can not connect to ClickHouse via ODBC

Post by andreykadysh » Mon Nov 15, 2021 4:03 pm

Thank you
We have not tried to do this with python. We will try.

Post Reply