ABRACON TM1 Connector
ABRACON opens IBMs TM1 for SAP reporting tools
It is no secret that the TM1 in-memory BI platform is quite strong on importing data from external systems, but it's not that easy to retrieve data from TM1 and load into other systems like SAP BW or to build Dashboards, e.g. using Xcelsius or Design Studio, on top of TM1 cube views. ABRACON developers found a general solution and enhanced the SOA infrastructure of an international corporation by a general web service for accessing any TM1 data from outside the system in a secure way. A single multi-purpose client program on SAP BW is key for arbitrary BW process chains to pull TM1 data from any TM1 system, thus completing the often seen co-existance of SAP BW and TM1 in one IT landscape.
In order to retrieve data from a TM1 server without the MIP TM1 Connector, you may do the following:
- Open a TM1 desktop program (e.g. Architect), navigate manually to the proper server/view (using mouse and keyboard), click on the CSV export menu, configure the export dialog, choose a local target file and then wait for the process to finish.
- Do quite the same in the TM1 web application. After authentication via a specific web page and navigating inside the web application, you may download the CSV or Excel for your data.
- You may setup a TurboIntegrator (TI) process to push data from TM1 into an external storage like a database table or a CSV file. Using commercial third-party extensions, data may even be pushed into a SAP BW DSO. But the data needs to be validated and converted to the right target format by specifically developed TI programs. Here, a TM1 developer needs knowledge about the target platform (e.g. SAP) and if anything changes on the target platform (e.g. if the target system moves), these changes need to be reflected on TM1 side by a TM1 expert.
- An ODBO-capable client program might access TM1 using the ODBO protocol. The TM1 loosely implements ODBO in order to let different TM1 servers communicate with each other. Unfortunately, the ODBO implementation of TM1 is not officially supported for other non-TM1-systems and is not completely standards-compliant, which makes it hard to use for other purposes.
With none of these interfaces it is easily possible for other computer programs to access general TM1 data.
The TM1 Connector is a REST-compliant web service to access TM1 data via the web. That means, you may type the right URL into the Location field of your web browser, and after committing your user name and password you will get TM1 data displayed in the web browser. This might be nice to try out, but the real advantage of a web service is that arbitrary computer programs are able to do the same, namely to retrieve data in a standardized format by loading a proper URL. In this way, loading TM1 data is like downloading a document from the web. And a lot of computer programs are able to do exactly that. In particular, SAP ABAP programs, Excel VBA programs and general reporting tools like SAP Dashboards (Xcelsius) are perfect examples.
The TM1 Connector uses the officially supported TM1 Java API from IBM to access TM1 data. It supports standards like MDX for querying data and an XMLA-compliant XML schema for formatting the output. Depending on the deployment, it supports SSL-encrypted access as well as compression. We are deploying the TM1 Connector usually on the same Java Application Server (Tomcat) that hosts TM1 Java applications and that comes bundled with any TM1 server installation.
Background: the MIP project for an international operating healthcare company
The Management Information Platform (MIP) project started in April 2011 and is aimed to provide a broad range of management web reports – either new or replacing existing ones – acting as a unique source of well-prepared business management reference data, always accurate, easy to analyze and suited to be presented. Colleagues from ABRACON and the customer have spoken publicly about this on-going success multiple times at several events (DSAG Technologietage, DSAG AK BI, ABRACON Strategietage, BARC congress) in the past three years.
In the first wave of the project the main focus had been on quantitative Xcelsius dashboards. The second wave brought qualitative information, i.e. comments, into those reports. And besides creating and enhancing new and old quantitative and qualitative dashboards, the third wave of MIP merges SAP BW and TM1 data into a single dashboard view.
Although the foreground focus of MIP is on web applications for end-users, the real benefit behind is a strong commitment to MIP as a platform. Not only in a business sense – creating a single access point for management information – but especially in the sense of a technical platform for accessing management information. All technologies that were created during the MIP project are reusable and ready for further purposes. Existing barriers and missing interfaces were created to enhance the general access to BI information – not only for end-users, but for any kind of computer programs that are used by business users and BI developers:
- 2011 in 1st Wave, a universal web service for BEx queries was created (ABRABIQS). With this, different web clients like Xcelsius were able to not only retrieve and display BEx queries. But also to consume all the different dynamic features of BEx, like navigational commands. In addition, the web service is able to convert the BEx result into different document formats like HTML, Excel, and XML for Xcelsius and JSON for mobile web applications.
- 2012 in 2nd Wave, a universal web service for BW documents was setup. In this way, creating, editing, querying and maintaining qualitative information became possible using different frontends. The Key Products Insights application was the first of several custom frontends to maintain comments, which are strongly coupled to data dimensions on financial cubes in the BW. The MIP provides a framework for easy creation of such frontends. The solution was finalist in the BARC Best Practice Award 2013.
- 2013 in 3rd Wave, a universal web service for TM1 data was setup. Not only Xcelsius dashboards may directly query TM1 data with this service. Also, a custom BW program was setup that lets BW users download any TM1 data from any TM1 server and store into a staging table in BW for further processing into a BW cube. All this driven completely from BW side – we call it the pull approach in contrast to the push approach, where a TM1 user may setup and execute a TM1 TI process on the TM1 to convert and push data into an external system like BW.
fig.: Web based MDX-Wizard
fig.: Different Clients (web based vs. classic BW)
As ABRACON is strongly committed to the principles of professional software engineering with strong focus on aspects like maintenance and reusability, we will continue to do The Right Thing, even if it is as simple as transferring data from one BI system to another.