Near real-time data in IBM Cognos TM1

Due to its inherent technological design, TM1 is extremely suitable for near real-time applications. The whole TM1 logic builds upon principles like in memory processing, incremental updates and immediate evaluation and execution. These out of the box characteristics make it an ideal candidate for real-time or near real-time reporting and analytical business cases.

Once you have TM1, it really can become a swiss pocketknife solution. The capability to process data on the fly in an incremental manner only reinforces this. Being able to process near real-time data activates a set of business applications that otherwise would require non integrated niche solutions. Think of applications like:

  • logistics warehouse monitoring
  • trading desk data like commodity quotes and exchange rates
  • solar PV farm monitoring
  • production process monitoring
  • railway network scheduling and planning
  • weather model data collection
  • monitoring of server and network infrastructure
  • flight planning and organizing
  • natural resource hedging
  • just in time delivery management

The list of possible applications is only limited by reality. To demonstrate how easy this principle can be applied, the only thing you need is a semi near real-time data source. There really isn’t that much to it.

In this lab setup we will demonstrate how easy it is to collect and update stock quote data every 5 minutes from an external data source into TM1. The subject of choice is the Deutscher Aktienindex (DAX) composition from the Frankfurter Wertpapierborse. We used Yahoo Finance to extract the data every 5 minutes for over a month.

Near Real Time Data in IBM Cognos TM1 DAX Quotes

So the required ingredients are:

  • An IBM Cognos TM1 environment, preferably in combination with an IBM Cognos BI setup. Collecting data without the ability to use it, is rather pointless.
  • An internet connection to the outside world.
  • A limited knowledge to write some VB.

1. A near real-time data source for IBM Cognos TM1

Yahoo Finance data can be within limitations be downloaded as a *.csv fill by calling a specific URL.

To withdraw the DAX quotes we composed the following URI: ‘http://download.finance.yahoo.com/d/quotes.csv?s=%40%5EGDAXI&f=nsol1abb2b3va2&e=.csv’

Near Real Time Data in IBM Cognos TM1 DAX Quotes Sample Data File

If you want to compose your own URI, then these resources might be useful:

The *.CSV will only contain the latest data set, so no historical data. The point of this exercise is therefore to be able to load this data periodically, allowing us to build up history. Next step is to define a method to call and download the data whenever we want. We choose to use a small VB script for this that we can run from TM1 Turbo Integrator anytime we want. Just remember that if the TM1 service runs under the ‘local system account’ you typically will not be able to run batch files. But if you configure the service to run as a specific user TM1 will be allowed to do anything that a user is able to do. 

The VB script then looks like this:

Near Real Time Data in IBM Cognos TM1 DAX Quotes Visual Basic Load Script

 We also created a small *.bat file only to delete the previous *.CSV data file every time we call the VBscript:

Near Real Time Data in IBM Cognos TM1 DAX Quotes Clean Quotes Script

2. The near real-time TM1 Turbo Integrator Process

Now we have our fake/real near real-time data source we can use it in TM1. The target is a Cube with a time dimension with 5 minute intervals, a measures dimension and quote dimension. The TI process just updates the cube every 5 minutes with new data. The only special thing added to the TI is the call of the CleanQuotes.bat and the DownloadQuotes.vbs in the Prolog section.

The Data Source in Turbo Integrator:

Near Real Time Data in IBM Cognos TM1 DAX Quotes Turbo Integrator Data Source

The Variables in Turbo Integrator:

Near Real Time Data in IBM Cognos TM1 DAX Quotes Turbo Integrator Variables

The Prolog tab in the Advanced section in Turbo Integrator:

Near Real Time Data in IBM Cognos TM1 DAX Quotes Turbo Integrator Prolog

The schedule attached to the TI and set to run every 5 minutes:

Near Real Time Data in IBM Cognos TM1 DAX Quotes Turbo Integrator Schedule

The final result is a TM1 Cube that receives new data every 5 minutes and that can serve as a source for reporting or other processes:

 Near Real Time Data in IBM Cognos TM1 DAX Quotes Cube

 

It is definitely not the most elegantly designed process, but it does illustrate how TM1 can be elevated to contribute to near real-time solutions.

Cognos Service