TM1 Forum

Facing problems with connection Cognos Planning Analytics to database ClickHouse (https://clickhouse.tech/) via ODBC driver.
Error in tm1server.log looks like:
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

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

Hi Paul, thank you for response.

Tried each of these parameters separately, the same error.

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

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

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)

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

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.

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.

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