External access to TM1 cube via MDX

Post Reply
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

External access to TM1 cube via MDX

Post by st2000 »

Did I search so inadequate, or is there really no post about this question?

I wonder if it is possible somehow to access to my TM1 cubes from outside TM1 (say Tableau, or Microsoft SSIS...). In Cognos BI, I could integrate a TM1 cube into the Framework Manager and deploy it to Cognos Connection. There, I can browse the metadata of the cube and build reports on it. So, under the hood this should be implemented.

But a generic ODBO connector seems not be available, maybe 3rd party? Or is this covered with PA 2.0 local meanwhile?
I know from the RESTful API since 10.2.2, but I'm looking for an interface I could use from ETL scripting MDX queries.

Any ideas, somebody?
-----------------------------------
Best regards,
Stefan
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: External access to TM1 cube via MDX

Post by David Usherwood »

For some strange reason IBM don't spend time or effort supporting third party access to TM1 - baffling I know. You have spotted that the (new-ish) REST/ODATA API is one option, and there are a number of third party tools which I am reluctant to name in the main forum which will do this. I have experience of one of them and it worked (rather well) delivering TM1 content via ODBC, into Qlik/Tableau/PowerBI etc.
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: External access to TM1 cube via MDX

Post by st2000 »

ODBC? Does this mean that the driver internally retrieves data via MDX and delivers the resultset flattened to the ODBC-destination? Like a .csv-export, but on the fly?
That could be sufficient...
I aim to do 2 things:
1) Load top-down-targets into TM1 and later offload the created budgets back to the DBMS from controlling
2) Loading metadata from a centralized pool to adhere internal standards about how entities are defined

I could do all of this via .csv also, certainly. But if I tell those guys who operate the controlling DBMS and the metadata system, that I want to interchange data by file juggling, they might start evaluating a new planning technology for me :roll:

And the name of the ODBC driver you are experienced with could be found somewhere in the commercial & events forum? Need I to use a particular search term? :mrgreen:
-----------------------------------
Best regards,
Stefan
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: External access to TM1 cube via MDX

Post by BrianL »

While I haven't used it personally, there is an ODBO provider that gets installed with TM1.

A quick google of "tm1 odbo" is enough to show this.

http://www-01.ibm.com/support/docview.w ... wg21406989
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: External access to TM1 cube via MDX

Post by David Usherwood »

The products are to be found on the first page of the commercial forum - you should get there pretty quickly.
But from what you have since said, why don't you use ODBC via TI? You can read and write through this route.
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: External access to TM1 cube via MDX

Post by st2000 »

Ah, I see: I always thought of the Datasource type ODBO as a connector made only for pulling data from MS SSAS Cubes (if I remember right, it was also called somehow like that before 10.2). But if this works also for retrieving data out of TM1 cubes into external databases, this would fit best for me. Thanks for the hint.

The ODBC-path I'm using also for pulling data into TM1. But for pulling TM1-data from an outside load target (e.g. ETL), I need to install a TM1 ODBC driver on this external machine. Into the TM1 setups I didn't find a setupfile for the ODBC-driver... separate download @IBM? Install & Operations manuals don't really tell... Or do I need to install TM1 also on the ETL machine just to have the ODBC-driver there?
-----------------------------------
Best regards,
Stefan
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: External access to TM1 cube via MDX

Post by David Usherwood »

There is no IBM supplied ODBC driver for TM1 - but there are tools (for $) which effectively provide one.
However, I suggested you should look at ODBC via TI. TM1 supports writing to relational databases using the keywords ODBCOPEN and ODBCOUTPUT. For the latter, despite the 'OUTPUT' term, this just executes arbitrary SQL against your database for which you have to build the execute string.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: External access to TM1 cube via MDX

Post by lotsaram »

st2000 wrote:The ODBC-path I'm using also for pulling data into TM1. But for pulling TM1-data from an outside load target (e.g. ETL), I need to install a TM1 ODBC driver on this external machine. Into the TM1 setups I didn't find a setupfile for the ODBC-driver... separate download @IBM? Install & Operations manuals don't really tell... Or do I need to install TM1 also on the ETL machine just to have the ODBC-driver there?
It is installed automatically as part of the TM1 client install. For many years after the Cognos and IBM acquisition it continued to be called the Applix OLEDB Provider but I notice now that it is called "IBM Cognos TM1 OLE DB MD Provider" in 10.2.2.

There might be a separate install available but the easy way is to just install the client.
2017-05-17_21-34-27.jpg
2017-05-17_21-34-27.jpg (155.19 KiB) Viewed 6575 times
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: External access to TM1 cube via MDX

Post by st2000 »

Got it, thank you all very much.
-----------------------------------
Best regards,
Stefan
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: External access to TM1 cube via MDX

Post by tomok »

Why in the world are you injecting ODBO into the equation? If your aim is to exchange data between TM1 and a relational database why not just use ODBC? It's much easier and more stable than the TM1 ODBO driver.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply