Planning & Reporting

Did you know it’s easy to create a simple dashboard in IBM Planning Analytics Workspace? IBM Planning Analytics Workspace dashboards allow you to provide quick and effective information to the end user. The following steps demonstrate how to create a simple dashboard.

1. Navigate to your instance of Planning Analytics Workspace (PAW). Select New > Book from template.
2. Note: By selecting New > Book, PAW will automatically open a new tabbed workbook. The single page option is only available when you select New > Book from template.
3. Name your view, select Tabbed and Freeform, and click Create to create a new tabbed workbook.
4. Expand your TM1 service in the list of available TM1 services in the navigation pane. Then expand Cubes, expand a cube from your list, and expand Views. Drag and drop a saved view onto Sheet 1, and position it on the left-hand side of the screen.
5. Click on the exploration to activate it. Then click on the Properties button in the upper right-hand corner of the screen. From here, select Synchronize > Synchronize dimensions. This will keep the exploration in sync with the rest of Sheet 1.
6. Click on the exploration to activate it. Then display the options by clicking on the blue rectangular button located in the top, center portion of the exploration. Select the Duplicate option, which is fifth from the right.
7. Drag and drop dimensions to/from the context, row, and column sections to change the data shown in the explorations. Display the options and select the Change visualization option, which is second from the right. Select Bar to display the data as a bar chart. Display the options again and click the Show/hide overview button all the way on the right to complete the dashboard.

You can add other objects, explorations, and customizations to create unique dashboards. For more information on Planning Analytics Workspace, refer to the IBM Planning Analytics 2.0.0 documentation.

IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help.  Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more blog posts with IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Dynamic, Attractive Views and Dashboards

IBM Planning Analytics Tips & Tricks: Planning Analytics Workspace Chart Types

IBM Planning Analytics Tips & Tricks: The Data Hold Feature in Planning Analytics Workspace

When working with clients to design a TM1 system, I sometimes have clients ask about an approach of building one cube for a model vs. building multiple cubes for a model. What do I mean by that? A cube consists of a single table/database of data whereas a model consists of a series of cubes that are linked together.

TM1 is designed to quickly and efficiently link a series of small cubes together, but this style of thinking contradicts the approach that you may be used to within Excel. An Excel spreadsheet is a single table of data that offers the ability to link together more data via multiple tabs and multiple workbooks. This approach easily gives you the ability to start with a single set of data and then expand it.

What happens though, when you take this approach, is that you end up with your TM1 cube looking like your attic might if you just toss all your stuff in it. You might have old skis, camping gear, baby keepsakes, and more all together in one room. It makes it hard to find what you need and, in some cases, hard to know what you even have up there.

When you build multiple cubes, it’s like having an organized system to store all your stuff. You might have a closet dedicated to your sporting equipment. Camping gear may live elsewhere in a storage unit. Your family heirlooms and keepsakes are kept in water-proof containers in the garage. It’s much easier for you to find what you are looking for.

Revelwood’s approach to building TM1 models is to build small, efficient cubes that each have a separate role and purpose. These cubes link together to make a model. For example, say we are going to build a TM1 model to calculate compensation costs for a mid-sized national technology business. That model has three main components:

• Data that gets entered by the planning team
• Lookup tables for various rates and dates
• Calculated results

Each cube stores only the data that is needed for its specific function and task. For example, there may be one rate cube for Federal tax rates and another cube for state tax rates. Everyone in the company has the same Federal tax rate for a single year whereas each state has a different rate.  If you were to have one large cube for all tax rates – Federal and individual states – it would be hard to navigate and may require workarounds to make the data fit (for example, creating a “federal” state).

Building smaller cubes versus one large cube has benefits beyond those that are just about organizing your data. In fact, the organization part is really just a side benefit. When you have a model that consists of smaller cubes, you have a system that is not wasting space and provides you with a flexible foundation for your TM1 applications. Because your cubes are smaller, the entire system also performs faster.

Chances are you bought TM1 because you weren’t happy with Excel. Many Excel “systems” grow into multiple workbooks with many linked spreadsheets, which turns into a messy behemoth. Why would you want your TM1 environment to look like that?

Read more blog posts with TM1 tips & tricks:

IBM Planning Analytics Tips & Tricks Video Demo: Creating a Cube

Creating TM1 Picklist Cubes

This is a guest post from Revelwood’s Shane Bethea.

Did you know that you can prevent data entry to TM1 in TM1 Web websheets? Here are a couple of different ways to do this.

The first approach can be used for input templates. Use this approach when you want your users to be able to enter data for certain cells/columns, but not others. By default, every cell in an Excel worksheet has the ‘Locked’ property set. This property has no effect on the cell unless the sheet or workbook is protected. In this example, we can select certain cells of an input template to be enterable and certain cells to be locked down or protected. To unlock certain cells in the template, right click the cells and select Format Cells. In the Format Cells dialogue box, select the Protection tab, uncheck the Locked checkbox, and select OK.

Now, in the ‘Review’ ribbon in Excel, select Protect Sheet, and then click OK.

Your worksheet is now protected. Any cell with the Locked property checked will prevent data entry to TM1 in TM1 Web. Any cell with the Locked property unchecked will allow data entry to TM1 in TM1 Web.

The second approach to disallow data entry to TM1 in TM1 Web can be used for reports. Use this approach when you don’t want your users to be able to enter data at all. To do this, in TM1 Server Explorer, expand Applications and navigate to the report you want to protect. Right click the report and select Properties.

In the TM1Web Properties box, there is a checkbox called ‘Allow Cell Write Back from Web’ that is checked by default. Unchecking this box will make the file read only and will prevent writing any data into TM1. Once you’ve made your changes, select OK.

After making any of these changes, you must close the websheet in TM1 Web, refresh the navigation tree and reopen your report.

IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help.  Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

This is a guest post from Revelwood’s Shane Bethea.

Did you know IBM Planning Analytics lets you make API calls through VBA in Planning Analytics for Microsoft Excel (PAx)? You probably know that action buttons can be used to refresh or rebuild reports in PAx. But what if you want to refresh the data in your report as part of a VBA routine? Maybe there are more tasks you want to perform through VBA and then you want to refresh the data as the last step. IBM offers the ability to make API calls through VBA in PAx with just a few steps.

The first step is to open the Visual Basic editor in Excel and import two files (one module and one class module) that ship with your PAx installation. Do this in the VBA editor by right clicking on your workbook and selecting Import File.

This opens an Import File dialogue box. Navigate to your PAx install location (either Program Files or Program Files (x86) ) and then navigate to \ibm\cognos\IBM for Microsoft Office\Automation\ .

You can then import two files: ‘CognosOfficeAutomationExample.bas’ and ‘CognosOfficeMessageSuppressor.cls’.

Once imported, you can use the public sub routines in the CognosOfficeAutomationExample module. This module includes some useful sub routines, such as:

• ClearAllData() – Functions the same as the Clear All Data toolbar/ribbon button to clear Explorations and reports from the workbook.
• RefreshAllData() – Functions the same as the Refresh All Data toolbar/ribbon button to refresh data for Explorations and reports in the workbook.
• RefreshAllDataAndFormat() – Functions the same as the Refresh All Data toolbar/ribbon button to refresh data and formats for Explorations and reports in the workbook.
• UnlinkAllData() – Functions the same as the Convert to Static toolbar/ribbon button to unlink all data in the workbook.

If you want to Refresh or Rebuild a Dynamic Report in PAx (formerly Active Forms in TM1 Perspectives), you have to get more creative. To accomplish this, follow the steps to import the two modules discussed earlier and then add the below code to the CognosOfficeAutomationExample module:

Now you can call the RebuildDynamicReport() sub routine from a Command Button or other VBA macro to rebuild your Dynamic Report.

IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!