Using TM1 Cubes with Cognos BI -


Using TM1 Cubes with Cognos BI

April 23, 2015

Data Prep & Modeling

Three Tips for TM1 Cube Design

TM1 Cubes

TM1 can be a high performance dimensional data source for Cognos BI. Learn more about how these two product suites can work together to improve the BI report building process in this webinar, as we share examples of key TM1 capabilities.

Featured demonstrationsusing the latest Cognos BI and TM1 versions (10.2.2) cover the following topics:

  • Reporting examples from a TM1 cube source
  • How TM1 cube design choices impact reporting capabilities
  • Including text formatted cells in a BI report

BI Report Authors, BI Report Consumers, BI Power Users (Developers, Support Staff), BI Managers, Financial Analysts, Finance Managers, Marketing Analysts


IBM COGNOS BI, Cognos Workspace Advanced, Cognos TM1


Ken O’Boyle
Solution Architect
Senturus, Inc.


Overview: Why Use TM1 with Cognos BI?

  • For BI Users: Reasons to Use TM1 as a Data Source
    • Faster cycles from input/data load to content delivery
    • Reduced ETL processes
    • Reporting and analysis directly against an application
    • Streamlined inclusion of planning information within the enterprise reporting platform
  • For TM1 Users: Reasons to Use Cognos BI
    • Enterprise-scale, web-based reporting platform
    • Broaden the audience for TM1 content
    • Reduce dependence on Excel
  • Common BI Architecture Including Planning Information
  • Streamlined BI Architecture with TM1
  • Key Points
    • The BI Package for a TM1 cube has a similar look and capabilities as other OLAP/DMR sources
    • Common requirements can be delivered with TM1
      • Balanced and Unbalanced Hierarchies
      • Alternate Hierarchies
      • Dimension Attributes
      • Relative Time
    • TM1 Dimension and Cube design impacts BI capabilities
    • TM1 allows you to store text values in an OLAP source

TM1 Reports: Examples

  • TM1 Reports: Package Example
    • TM1 Package Characteristics
      • Package is a reference to one or more TM1 Cubes
      • Displayed the same as other OLAP/DMR sources
      • Measures Dimension is specified in TM1 cube design
  • TM1 Reports: Hierarchies
    • Balanced vs. Unbalanced
      • Balanced: Example = Product
        • Named levels listed accurately and consistently
        • Attributes available within each level
      • Unbalanced: Example = Organization
        • Level based reporting is problematic
        • Attributes available across the entire hierarchy
  • TM1 Reports: Alternate Hierarchies
    • Example: Promotion_Alt_Hiers Dimension
      • Use Case: Marketing Promotions
      • Two hierarchies each with a with a distinct root element
      • Each leaf-level element rolls up to two parent elements
      • Rollups by Bundle Group and Campaign
  • TM1 Reports: Relative Time
    • Time Dimension Characteristics
      • Relative time elements can be constructed (in TM1 10.2.2, they can also be generated)
      • Implemented as alternate hierarchies
  • TM1 Reports: Example 1 – Crosstab with Nested Level and Attribute
    • Product Sales by Year Report Design
      • Product Level is nested to the right of Product Type
      • Color attribute is included for each Product
  • TM1 Reports: Example 2 – Crosstab with Relative Time
    • Product Sales Comparison Report Design
      • Columns are defined relative to the current time period
  • TM1 Reports: Example 3 – Crosstab with Separate Year and Month Dimensions
    • Product Sales Year-over-Year Comparison Report Design
      • Month/ quarter can be nested with year
      • Not limited to current/relative time periods

TM1 Cube Design Options: Dimensions, Hierarchies and Attributes

  • TM1 Design: Hierarchies
    • Balanced vs. Unbalanced
    • Balanced
      • All leaf-elements are the same distance from the root element
    • Unbalanced
      • Distance from root element varies for leaf-level elements
  • TM1 Design: Alternate Hierarchies
    • Dimension Design for Alternate Hierarchies
      • Elements are assigned to more than one parent
      • Level names are not hierarchy specific
      • Care must be taken to avoid in the design and reporting to avoid double counts
  • TM1 Design: Named Levels
    • Level Attribute
      • System-generated attribute
      • Defaults Level 1 … Level n (top down)
      • Defaults are ignored in BI
      • Editing the Level for one element updates all peers
      • Named Levels provide additional capabilities in BI
    • TM1 Design: Attributes
    • Attribute Characteristics
      • Attribute Types: Alias, Text, Numeric
      • One Alias attribute for each dimension can be set as the default display name in the BI package
      • Populate at any or all levels of the hierarchy
      • Attributes can be included in BI content
  • TM1 Design: Relative Time
    • Time Dimension Features
      • Dimension Type = Time: generates common attributes
      • Populate Time Dimension Wizard populate elements, hierarchy and attributes
      • Right-mouse menu has option to generate relative time elements
  • TM1 Design: Measure Dimension
    • Measure Dimension Characteristics
      • Cube Property: Measure Reporting Dimension
      • Mandatory selection before creating BI package
      • Elements are displayed as Measures in BI

TM1 Text Cells in BI Reports: List Report Example

  • TM1 Text in Cells
    • Common Challenge
      • TM1, unlike many OLAP platforms, supports Text values in cube cells
      • BI crosstabs do not render text, so the typical method for reporting and analysis against an OLAP source is not applicable to this use case
    • Solution
      • Use BI list reports when you need to deliver text values in reports
    • Scenario
      • TM1 Supports Text/Date-formatted Measures
      • Requirement: Formatted Report with Crosstab Layout
      • BI Crosstab Component does not Display Dates or Text
    • Display TM1 Text with List Report
      • Be sure to make a specific selection for each dimension in the report body, page or context area
      • Include at least one measure as a column
    • List Report Design