Planning Analytics Reporting Cubes

Rob Baierle, March 5, 2020

Table of Contents

Best Practices for Reporting in Cognos Analytics

Creating a Planning Analytics (PA) reporting cube is not a difficult task to accomplish provided you think through the requirements before designing and building the cube. Planning Analytics cubes can be used as a data source for reporting, dashboards, visualizations, and stories in Cognos Analytics (CA). A single Cognos Analytics dashboard can support multiple data sources. This means you can mix Planning Analytics cube data on the same dashboard as other relational or OLAP data sources. Cognos Analytics allows the report author to create pixel-perfect reports that can be managed for delivery throughout the enterprise as well as self-service reporting. By creating a dedicated reporting cube, you remove the potential for data changing as it is a static load versus a cube that users can input data into and data can change throughout the day. This is accomplished using TurboIntegrator (TI) processes to move data between cubes. A reporting cube can combine data from multiple Planning Analytics cubes into a single cube. This is something not easily accomplished within Cognos Analytics data modeling. By creating a cube that is read-only, you eliminate the contention of users trying to write or contribute data to the cube. Reporting cubes can also be consumed in Workspace, Planning Analytics for Excel (PAx), TM1 Web, or Architect, depending on where your users consume this data. Connections to the Planning Analytics cubes are setup in Cognos Administration / Manage and connect through the Planning Analytics REST API and queries are executed from the Cognos Analytics server using MDX to query the Planning Analytics cube. See the Cognos Analytics and Planning Analytics Integration Documentation for more information.

Reporting Cube Development in Planning Analytics

To build a reporting cube in Planning Analytics, you need to develop a TurboIntegrator (TI) process to move data to the reporting cube. Some suggestions for how to build the cube are as follows:

• Create a new cube for reporting with the required dimensions. You may need to build new dimensions for the reporting cube to accommodate your specific reporting requirements.
• Each dimension should have a top-level consolidation (All Products, All Locations, etc.)
• An ‘Alias’ attribute named “Caption” should be created and populated for all elements in each dimension. Cognos Analytics uses this value to display on the report. If the Caption is not available, it will display the element name, which may not be user-friendly. Caption Alias
• Each dimension should have balanced hierarchies or levels to be used in reporting. Setup the level names in the }HierarchyProperties control cube. }HierarchyProperties
• After updating the }HierarchyProperties control cube, run the RefreshMdxHierarchy() function on your reporting cube from a TurboIntegrator process. This will ensure that the named levels will be available in Cognos Analytics without having to restart the Planning Analytics server instance.  RefreshMdxHierarchy
• Set the Measure and Time dimension properties on the reporting cube. Measure and Time Dimensions
• Depending on your reporting requirements, you may want to have separate Month and Year dimensions. This approach may make reporting easier to do year-over-year comparisons.
• Once the process has been developed and tested, schedule the process to run as a Chore daily.

Setting up your Planning Analytics Data Source Configuration in Cognos Analytics

If you are using TM1 10.x, you will need to install the Planning Analytics client files on your Cognos Analytics server. If you are using Planning Analytics, you can skip the installation and go straight to setting up your data source connection and security as detailed below.

• Locate the Planning Analytics Client Installation File.
  • tm1client_winx64h_2.0.x.xx_ml.tar.gz
• Install the TM1 API components on all Cognos Analytics servers that are running the Query Service and Report Service. This gets installed in a directory adjacent to the Cognos Analytics installation.
  • Cognos Analytics: D:\Program Files\ibm\cognos\analytics
  • Planning Analytics: D:\Program Files\ibm\cognos\tm1_64
• Setup a data source connection to the Planning Analytics cube
  • Select the correct connection type based on your version of Planning Analytics (v2.0.x +) or Legacy TM1 (<= v10.2.2) Creating a Planning Analytics data server connection in IBM Cognos Analytics
• Setup security on the reporting cube. You can use a single Planning Analytics user or Integrated Cognos security (if Planning Analytics users authenticate with Cognos Analytics). Ensure your user(s) have access to read the necessary objects in Planning Analytics, such as Cubes, Dimensions, and Elements.

Cognos Analytics Reporting Best Practices

When building a report in Cognos Analytics against a Planning Analytics data source, you should follow a few guiding principles.

Things To Do

• Add specific elements to the report one at a time, then filter down as much as possible before adding additional elements. This should yield the best performance and allow you to identify and issues as they arise.
• Filtering with a MUN (Member Unique Name) provides quicker query response times.
• Use Cognos dimensional functions to build sets, return parents, children, or descendants of an element. You can also perform lead and lag movements in a dimension as well as pulling unions, intersect, and except sets together. Cognos BI Dimensional Functions
• Use consolidated elements for summaries in the report rather than trying to calculate a summary. This utilizes the built-in consolidation algorithm in Planning Analytics. Consolidations are returned much faster as they are already pre-calculated in the cube.

Things to Avoid

• Avoid filtering on attributes. This requires the system to iterate through all elements in the set or dimension, causing performance impacts.
• Avoid filtering with slicer. They only remove cell values and not the members/elements. You should use detail filters to remove the members/elements.

Conclusion

As you can see, it takes some planning, and maybe some trial and error, to get a reporting cube setup properly in Planning Analytics for use as a data source in Cognos Analytics. Be sure to install the Planning Analytics API Components on your Cognos Analytics server(s) before setting up the data source connection. Create your cube with the tips above and you will be in a good spot to start reporting. Just remember that you may need to iterate on the cube development based on feedback from your Cognos Analytics report authors. 

Next Steps

We hope you found this article informative. Be sure to subscribe to our newsletter for data and analytics news, updates, and insights that are delivered directly to your inbox.

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.