Loading Pulse data in TM1

Pulse is constantly tracking your IBM Planning Analytics (TM1) systems. All this very valuable data is stored in the Pulse database. Pulse comes with many dashboards to visualize this data. One option if you want to do further analysis is to send this data into your TM1 application.

This article explains how to load the information about the TM1 sessions into your TM1 application.

Thanks to the TM1 sessions information, you can see who are the top or bottom users and who is using which software (TM1 Web, Workspace, PAfE…):

To load data from the Pulse database into TM1, you need to setup the ODBC Connection.

Follow the steps below to set up the connection:

• https://code.cubewise.com/docs/connecting-to-the-pulse-databases-through-an-odbc-driver

The database password can be found in the Pulse Configuration page:

Once the connection is setup, you can use the ODBC feature of Arc to test the connection.

The information we are interested in is inside the Session table, we’re going to use the following SQL query:

SELECT ServerName , ServiceName , UserName , Context , Substring(LoginTime , 1, 10 ) AS LoginDate , Count(LoginTime) AS LoginCount , Sum(SessionTime) AS SessionTime , Sum(LogCount) AS LogCount , Sum(WaitingCount) AS WaitingCount , Sum(RunningCount) AS RunningCount , Sum(CommittingCount) AS CommittingCount , Sum(RollingBackCount) AS RollingBackCount , Sum(UserWaitingCount) AS UserWaitingCount

FROM [Session]

WHERE SessionTime > 0

GROUP BY ServerName, ServiceName, UserName, LoginDate, Context;

By executing the SQL query in the ODBC feature of Arc, you should now see the Sessions table data:

To help you getting started, we created a Pulse migration package including a process. This process will then create a new cube and load the sessions data.

First click the button below to download the Pulse package

Go to Pulse, under Migration go to Import Package and import the package, you should now see it in your packages list:

Now execute the package and select the TM1 instance where these objects will be created.

Once executed, open the process cub.pulse.session.update. Go to the Prolog tab and update the ODBC password:

The process cub.pulse.session.update is going to create a new cube and load the sessions information from the sessions table. To load all history, you can set the parameter pDataLoadAll to 1.

That’s it!

Now you can do further analysis using the Pulse Session cube: