Reporting TM1 data from SAP BO

[saulcf]

Hello,

Just a simple question. Could be possible reporting TM1 data using SAP Business Objects and how to? I mean connecting to a cube from a BO universe. I know there is a connector for SAP BW (now deprecated) but I didn´t find any information regarding Business Objects.

Thanks.
Regards

[lotsaram]

You seem to be confusing source system and reporting system. The (old) Syskoplan TM1 SAP connector and the (current) TM1 Package Connector are to get data out of SAP BW and into TM1. You are asking about getting data out of TM1 and into BO therefore the connector is not going to be in TM1 but in Business Objects so if there is a "TM1 connector for BO" this is where it will be. In theory this should be "not too un-straightforward" as TM1 supports an MDX query interface with OLEDB so as long as you have the Applix TM1 OLEDB driver installed then this will be able to provide data. The TM1 OLEDB driver doesn't have the best reputation so if there is a "proper" connector then this would be better but the OLEDB driver is the basic option.

[saulcf]

Thanks Iotsaram. The issue is that there is no a proper connector so the solution should be use the TM1 OLEDB driver but before we should check the perfomance of this driver.

Regards

[foogy]

Dear saulcf,

any progress on that issue? I've managed to use the "IBM Cognos TM1 OLE DB MD Provider" to recieve a list of available cubes in the TM1 model. However, when trying to request cube data from the connection, it always fails.

You may google for this driver name and will find some information on parameters, options, AuthenticationMode=5, even connection strings. The driver is available when selected as component during TM1 installation. See screenshots below for an example on how to access the driver, once successfully installed.

I've also read that this OLEDB driver is only meant for importing TM1 data from TM1 to another TM1.

My intention is to use some OLEDB connection to analyze TM1 data with some arbitrary analysis tool supporting OLEDB. But on the other hand, IBM stated somewhere that the TM1 MDX langauge subset is not fully compatible with the general MDX specifications. May be this also leads to errors in "standard" tools that do not now how to treat TM1 connections.

Good luck. Leave a reply if you found anything new.

tm1-oledb-1.png (61.52 KiB) Viewed 13872 times

tm1-oledb-2.png (17.41 KiB) Viewed 13872 times

tm1-oledb-3.png (6.18 KiB) Viewed 13872 times

[RJ!]

Has anyone got any further on this?

We're also thinking of connecting BO to TM1 for some Dashboard reporting.

[xyzzy]

We are trying to use this to get data out of TM1 into Excel without the huge performance impact perspectives imposes, pretty much the way you can from other data sources connected using Excel.

Again we are not managing to successfully connect. We have tried using the provider string "CAMNamespace=<>" using both the namespace ID and name - one site we found said use the ID and another the name. - but with no success.

We can get the data into Excel using CDM. This allows a connection to be made to TM1 (looking rather similar to the boxes requested in the previous post screenshots) and a query to be built (usual drag and drop of dimensions with some MDX support) with the results dumped into a tab of the Excel workbook (and then we use VLOOKUP to extract the data into the Excel cells). This is much faster than using perspectives to extract the same data but of course more labour intensive to build in the first place.

I know CDM is using this provider as the OLE DB driver ships with CDM. Somebody has manager to connect using a native TM1 server but we have not yet managed it remotely. Has anybody else got anywhere with this?

In relation to the earlier comment on MDX, because TM1 uses unbalanced trees it does not fully support MDX but it should be able to handle the basics.

[David Usherwood]

@xyzzy:

We are trying to use this to get data out of TM1 into Excel without the huge performance impact perspectives imposes, pretty much the way you can from other data sources connected using Excel.

Interesting that you have issues with the approach followed by 90% of the TM1 community, suggesting that you are doing something quite unusual. Yes, perspectives is not the right approach for 'data dumps' or for Wans. For data dumps, TI works well, and for Wans, you should use Cafe. You could also look at the REST/ODATA api which came out with 10.2.2 - this is reputed to be rather fast.

[Marcus Scherer]

For getting TM1 data into Excel you may also use the approach from J. Wakefield http://www.bihints.com/tm1_sdk. I find it very useful, especially for placing views from different TM1 instances (e.g. DEV/PROD) in the same worksheet.The MDX for queriying TM1 cubes is standard MDX. You just have to specify your dimension elements/hierarchies in the WHERE-clause. You can use NON EMPTY for zero suppression etc.

Regarding BO I suppose the BO product developpers are not putting much weight on interfacaes to the MOLAP world. In the past we were trying to connect to MSAS cubes and the interface did not even recognize the calculated members. For some dashboard reporting with condensed data it might be sufficient to populate some tables in your relational DB via ODBC.

[xyzzy]

Raised a PMR with IBM, and querying TM1 through Excel via the TM1 OLE DB MD provider is not possible.

http://www-969.ibm.com/software/reports ... rm=Windows

Footnotes against Excel versions 2007, 2010 and 2013:

(1)
Excel not required for Architect.
ODBO support via Excel 2007 Pivot tables is not supported.
Advanced Rules Editor requires .NET Framework 2.0 on the client.

(2)
Excel not required for Architect.
ODBO support via Excel 2010 Pivot tables is not supported.
Advanced Rules Editor requires .NET Framework 2.0 on the client.
Excel 32 and 64 bit supported
Office 2010 Service Pack 2 required

(3)
Excel not required for Architect.
ODBO support via Excel 2010 Pivot tables is not supported. (I assume this is a typo and should be 2013)
Advanced Rules Editor requires .NET Framework 2.0 on the client.
Excel 32 and 64 bit supported

[David Usherwood]

querying TM1 through Excel via the TM1 OLE DB MD provider is not possible

when the link to James Wakefield's toolkit shows that it is?
What IBM actually say is:

ODBO support via Excel 2007 Pivot tables is not supported.

[moby91]

You are asking about getting data out of TM1 and into BO therefore the connector is not going to be in TM1 but in Business Objects so if there is a "TM1 connector for BO" this is where it will be.

You seem to be looking for something like the ABRACON TM1 Connector which claims to open TM1 for SAP reporting tools.

https://www.abracon.de/t_tm1connector_r.html
ABRACON TM1 Connector
ABRACON öffnet IBMs TM1 für die SAP Reporting Tools

https://www.abracon.de/blog-technologie ... ector.html
ABRACON entwickelt IBM TM1 Connector für SAP Reporting Tools

[xyzzy]

Ok, you used to be able to do this in Excel 2003 but IBM have not upgraded the provider since then to support Excel 2007, 2010 and 2013. IBM support did suggest we regress back to Excel 2003 , bless them!

I have raised an Enhancement Request with IBM to get this provider updated. So if anybody else is interested in this functionality you can vote on it.

http://www.ibm.com/developerworks/rfe/e ... R_ID=63648

Headline:TM1 OLE DB MD Provider for Excel 2010/2013

ID:63648

Description:
The TM1 OLE DB MD provider used to work with Excel 2003 providing ODBO support via Excel pivot tables.

This functionality has not been supported on Excel versions since 2003 (2007, 2010, 2013) and we would like to connect Excel directly to TM1 for data extraction.

Your votes appreciated.

[lotsaram]

Ok, you used to be able to do this in Excel 2003 but IBM have not upgraded the provider since then to support Excel 2007, 2010 and 2013. IBM support did suggest we regress back to Excel 2003 , bless them!

I have raised an Enhancement Request with IBM to get this provider updated. So if anybody else is interested in this functionality you can vote on it.

http://www.ibm.com/developerworks/rfe/e ... R_ID=63648

Headline:TM1 OLE DB MD Provider for Excel 2010/2013

ID:63648

Description:
The TM1 OLE DB MD provider used to work with Excel 2003 providing ODBO support via Excel pivot tables.

This functionality has not been supported on Excel versions since 2003 (2007, 2010, 2013) and we would like to connect Excel directly to TM1 for data extraction.

Your votes appreciated.

I'd vote for that. Being able to directly query a TM1 cube in a native Excel pivot table was a great feature. It hurts TM1 a lot that this is so easy to do with analysis services but can't be done for TM1.