12. TM1 Perspective

Introduction to TM1 Perspective

You can integrate all your TM1 data with Microsoft Excel and use it in graphs etc. You do this by using TM1 Perspective. It’s an excel add-in which helps you get connected to your TM1 application server. Follow below steps:

1. Go to Start > All Programs > IBM Cognos > Perspective For MS Excel (if you’re using Windows 7, figure out how you’re going to open this path, may be just search for Perspective)

2. MS Excel will open and will ask you to enable macros. Enabling macro will launch IBM’s TM1 perspective with Excel.

3. Go to TM1 menu bar, then click Connect and login to your TM1 admin server. You’ll receive a message saying that you’re now connected. Click OK. Now let’s learn how you can get the data from cubes into MS Excel.

From TM1, you can bring the data into MS Excel using 3 methods:

1. Slice

Slice lets you update TM1 cubes’ data directly from MS Excel. It’s created using DBRW formulae and retains a real time interaction with TM1 cubes. If you update any data in a slice, the same data is written back to corresponding TM1 cube.

To check this functionality, click on Explorer on your tm1 menu bar. Browse to your Products2 cube and launch the default view. Cube view opens. From File menu click on Slice. This will create a data slice in excel.

 

Check different cells of excel and notice the formula which has been used. Now change quantity value of Jan from 10 to 12 (if you have any other cube, change the value of some input cell. but remember, that cell must not be calculated cell) and press enter.

Now go to the TM1 cube and check the value of that cell. The value has been changed to 12.

Advantages:

1. Using slices you can create input screens for users who don’t have access to architect or are not very familiar of TM1 cubes and related concepts. They can enter data directly in excel without bothering about cubes etc.

2. To update data, you don’t need to install TM1 Architect. You can get your work done by just using TM1 perspective

Disadvantage:

1.  To use the cube data that you’ve brought into the slice, you need to keep yourself connected to TM1 server. Once you get disconnected from the server, the excel sheet looses the data.

2. If new items are added to the rows or column dimensions (such as new products etc.), slices will not automatically get those items. You will have to either manually add them to your slice, or create a new slice.

 

2. Snapshot

Snapshots also brings TM1 cubes’ data into MS excel but do not have real time update capability. Snapshots just bring the data values (no formula).

 

Advantage:

1. They are useful if you want to do some analysis on your cube’s data and don’t need the unnecessary TM1 formulae (you just need the data).

2. You don’t need to stay connected to the TM1 server to use values.

3. Lighter and faster than TM1 slices (as snapshots don’t have write back functionality, they are faster and excel files that’s built using them are lighter)

Disadvantage:

1. No write back functionality. You can’t update cube data using a snapshot

2. While you’re doing your analysis using snapshot data, if someone changes the data on TM1 server, you’d not receive the new data and will end up with incorrect analysis.

 

3. Active form

Active Forms, like TM1 Slices, let you view and update live TM1 cube data directly in MS Excel. Active Forms retain the ability to expand and collapse row dimensions in TM1 views. That means

 

Advantages:

1. Active forms have all advantages of slices.

2. If you add new dimension items to your dimensions, active forms reflect those changes too provided that you had created your cube views using dynamic subsets.

3.  They also have formatting unlike slices.

4. Active forms have suppress rows functionality. So based on data, they can increase rows and show only non-zero relevant data. Slices don’t have this feature. If data is zero in all rows, slices will still show those rows.

5. You can filter the data too in active forms. So if you want to top 5 performing products, you can see them using active forms.

Disadvantages:

1. They have all above disadvantages of slices.

For further information on active forms, follow IBM documentation:

IBM Documentation 

 

 

Homework

Create slice, active form and snapshot on a single worksheet of MS Excel and verify the data.