MS Reporting Services with TM1

[Northdoor]

This is a research and development issue to find out how we can use MS Reporting Services with TM1.

Environment details:
Win XP SP3 32-bit
TM1 9.4 SP1 FP3 64 bit runs on a Win2003 64-bit server.
Reporting Services is from SQL Server 2005 or 2008


The Issue :
We’re looking into the possibility of using Reporting Services to report off TM1.

I looked at IBM.com site and one technote said "..obtain the ODBO certification toolkit to review the details of implementation before proceeding”.
Can anyone tell what this means, and if there are any examples or documents to help me get started with this?
(Full technote: http://www-01.ibm.com/support/docview.w ... wg21406989)

For example,
Can/should one connect to TM1 using standard Microsoft adomd.net?

Are SQL Server Linked Servers using OpenRowset supported or should one use the TM1 .Net API (or can one use the COM DLL?) and the Custom Code feature?

Please share your knowledge if you already using the Reporting Services to report off TM1 or if you know how we can achieve this or what are the issues need to know before we start any setup.
Many thanks for your time,
ND

[George Regateiro]

We looked into some of this some time ago and the biggest issue was that the ODBO adapter for TM1 is not entirely standards based so it did not plug in as nicely as we would have liked. Where we did achieve success is creating a reporting server in Analysis Services and reporting off of that. You do have the added overhead of the ETLs to get the data over there, but it has worked out fairly nice.

[paulfelce]

This is something I am really keen to do to.

George, are you having success in exporting data from a TM1 cube into analysis services and then reporting from AS? Does this method involve an etl process and a lag between the data in AS and and TM1?
In this case would you be tempted to not use TM1 and hold your data purely in AS? Or are you using lots of other funky stuff in TM1 that is simply unavailable in AS?

How do people feel IBM will support linking to reporting services? Is it in their interests to tie TM1 to their BI Reporting stack and to make it difficult to connect reporting services to TM1, or do they think they'll improve revenue with people using TM1 as a backend with reporting services at the front?

Personally I prefer reporting services to the cognos BI stack, but perhaps that is because I have a microsoft development background.

[Marcus Scherer]

Paul,

if you are working in a Microsoft environment, why are you using TM1 for pure reporting? For Reporting you can use Analysis Services alone.

I suppose IBM will not invest much effort in the Reporting Services connection because there is Cognos Connection , Report Studio, Analysis Studio etc already available. They want to sell the suite.

BTW, I would not focus on the frontend when building an BI architecture. Mixing products of different vendors may also cause problems because the interfaces are not always tested carefully and sometimes lack transfer of functionality.

Regards,

Marcus

[paulfelce]

Paul,
if you are working in a Microsoft environment, why are you using TM1 for pure reporting? For Reporting you can use Analysis Services alone.

Politics

I suppose IBM will not invest much effort in the Reporting Services connection because there is Cognos Connection , Report Studio, Analysis Studio etc already available. They want to sell the suite.

BTW, I would not focus on the frontend when building an BI architecture. Mixing products of different vendors may also cause problems because the interfaces are not always tested carefully and sometimes lack transfer of functionality.

Marcus, these are,unfortunately, the same conclusions I have come to.

[George Regateiro]

In our case we wanted to have historical reporting integrated into our data warehouse, but did not want the reporting to affect the current TM1 model nor did we want to recreate all the TM1 logic in AS. AS is just a dumb copy of TM1s histroy and it will remain that way since we know TM1 and have not developed any solutions in AS other than this dumb copy. Currently we export out the data nighly (since we are only concerned with history records) then we have a ETL from our datawarehouse tool that populates the data into AS\SQL.

We looked into a realtime type scenario, but to really do that and not impact the users that were entering into TM1 you would have to set up replicating to a reporting server and processing there. In our case it was not worth the effort.

If you are looking into something and building from scratch the C8 BI route is the cleanest and simplest. Any other route will require jumping through hoops that may or may not be worth it in your enviornment. For us C8 was not an option since we own a enterprise license to the other BI tool.

As far as support goes I would not bank on alot of support if any on the reporting services or any other non Cognos BI tool. There was talk before the acquisitions about getting the ODBO adapter improved, but I have not seen anything in the release notes. Our BI vendor had received multiple customer requests for TM1 interfaces, but I dont think they were able to get very far. IBM has a vested interest in making C8 BI the best option and it will probably remain that way, they want to sell a cradle to grave OLAP BI stack. With varying amounts of workarounds you can build something but you will have to make sacrifices in functionality as compared to C8.

[belair22]

We have been able to integrate TM1 with Sharepoint in a real-time Reporting environment - including the use of parameterised dropdowns to drive MDX statements for self-service reporting (no intermediary data-storage/data-hosting point is required).

The integration was reasonably tricky to set up with a number of hurdles to jump through (integrated login arguably the trickiest).

[paulfelce]

We have been able to integrate TM1 with Sharepoint in a real-time Reporting environment - including the use of parameterised dropdowns to drive MDX statements for self-service reporting (no intermediary data-storage/data-hosting point is required).

The integration was reasonably tricky to set up with a number of hurdles to jump through (integrated login arguably the trickiest).

Integrated security seems to be a 'known issue' , even within the IBM productset!

[paulsimon]

Hi

TM1 will work with Reporting Services. However, the need to hand code MDX means that it wouldn't be my tool of choice. Cognos Report Studio has more functionality.

Even if you are doing reporting only, there are still reasons to use TM1 in preference to Analysis Services, particularly if you need anything other than trivial calculations.

Regards


Paul Simon

[Northdoor]

Thank you so much guys,

This discussion gave me great help to understand the issue and possible alternative routes to report off TM1.

Once again, many thanks.
ND.

[jameswebber]

Hi Guys,
Just wondering if this situation has improved with later versions of TM1 or Reporting services.

Looks to me like not much has changed but we are looking at building a reporting services BW soln so I'm keen to hear from anyone who is porting tm1 data into SQL server.

James

[jameswebber]

So after a bit of research it seems that while it is possible to ODBC out TM1 data it is fiddly.

This was a good reference site I found was: http://www.bimonkey.com/2010/02/migrati ... rosoft-bi/

Some organisations have built SSAS cubes, but this does seem to require some good tech knowledge.
Our aim was to rationalise our reporting tools (Currently have MS Reporting services, Crystal and looking to get Cognos Reporter via Cognos Express).
The fundamental issue was that IBM don’t make it easy to get TM1 data out of a TM1 cube. Even though the licencing model for Microsoft reporting services is ALOT more attractive.

So in the end we have decided to use CE reporter and will look to burst most of our reports financial. Unfortunately then maintaining a separate MSSQL reporting data warehouse for our .net applications.