<< VIEW FULL RESOURCE LIBRARY

Cognos Dynamic Cubes: Set to Retire Transformer?

July 30, 2015

Cognos 10, OLAP

10.2.2 Update: Pros & Cons

With version 10.2.2, is Dynamic Cubes now ready to replace Transformer implementations? In this webinar, the business analytics experts at Senturus take an unbiased look at the pros and cons of switching. Drawing on our own experience and providing real client anecdotes, we examine:

  • Types of Transformer implementations that could benefit by switching to Dynamic Cubes
  • Pre-requisites for replacing a Transformer implementation with Dynamic Cubes
  • Typical pitfalls you may encounter in the process
TECHNOLOGIES COVERED

IBM COGNOS BI, Cognos Dynamic Cubes, Cognos Transformer

RECOMMENDED AUDIENCE

BI Report Authors, BI Report Consumers, BI Power Users (Developers, Support Staff), BI Managers

PRESENTER

Pedro Ining
Senior BI Architect
Senturus, Inc.

PRESENTATION OUTLINE

Cognos Dynamic Cubes: Set to Retire Transformer?

  • Typical Customer Questions
    • What are Cognos Dynamic Cubes?
    • Is Transformer going away?
    • Should I replace Transformer with Dynamic Cubes?
    • What’s the effort to replace Transformer with Dynamic Cubes?
    • Will Dynamic Cubes resolve my current Transformer issues?
    • Can I still use PowerPlay Studio against Dynamic Cubes?
  • Current Transformer Architecture
  • Cognos Transformer Pros
    • Fairly easy to model, build, and deploy a cube
    • ‘ETL’ like functionality, allows creation of cubes from a variety of data sources
      • Star schema
      • Excel and text files
      • Operational sources
    • Performance
      • For properly sized cubes, performance is quite good
      • Once the cube is build, very little on-going tuning is required
    • Cognos Transformer Pain Points
      • Transformer build times are too long
      • Size limitations prevent full analysis of data
        • Create separate cubes for different years
        • Transformer is a 32bit app and consequently limits the file size to 2GB
        • Various partitioning schemes are required to implement cubes with sizes > 2GB
      • Performance
        • Large cubes slow as you nest dimensions
        • Suppress ZERO is expensive for large data sets
      • Drill through to detail
        • Requires different packages and harder to configure
    • Cognos BI Stack
    • Dynamic Cubes: Key Architectural Differences
      • A ROLAP in-memory engine that sits on top of star-schema data warehouse
      • Does not extract all data to build a physical cube
      • Cube startup is relatively quick
      • Uses a variety of in-memory and disk caches to enable fast query retrieval
      • Not limited by the physical limitations of cube size like Transformer
      • Can query the full breadth of data warehouse facts through the use of database and in-memory aggregates
      • Aggregate aware query engine
      • Requires optimization maintenance processes in order for the cube to continually perform adequately
    • Dynamic Cubes Architecture
    • Dynamic Cubes Aggregate Layers
      • Load time of in-memory aggregates will depend on performance of the in-database aggregates layer
    • Dynamic Cubes Development Lifestyle
    • Dynamic Cubes Product Evolution
      • Dynamic Cubes was initially released in 10.2 and IBM has continually added features that may close the features gap between Dynamic Cubes and Cognos Transformer
    • Dynamic Cubes Support of Transformer Features
      • Relative time support
        • Supported
        • Custom Relative Time became available in 10.2.1 FP3
        • Custom Single Period e.g. Same Month, Last Quarter
        • Custom Period-to-date e.g. Quarter-to-Date, Last Year
        • Custom N-Period Running Total e.g. Trailing Six Months, Next Year
      • Semi-Aggregate Time-State Rollups
        • FIRST, LAST are supported but cannot be optimized in-memory
      • Transformer Style Security – Suppress, Apex, Cloak
        • Can be replicated via MDX Expressions within dimension security
      • Orphan Categories
        • Not supported as this should be handled in the star schema

Dynamic Cubes Checklist

  • Dynamic Cubes Checklist: Data Source
    • Is the data stored in a star or snow-flake schema?
      • If not, can it be ported to one
      • Use of DB Views to create a star schema are not recommended due to performance reasons
    • If data is in a star schema, is there referential integrity between dimensions and facts?
      • Ignoring this check will result in erroneous totals as you drill up and down the cube
    • Can the underlying database support execution of multiple queries against a star schema?
      • Reports executed against a dynamic cube may result in serial execution of multiple queries
    • Are most measures Semi-Aggregate in nature?
      • Semi-Aggregate measures are not supported by in-memory aggregates. Manual optimization of in-database aggregates is required.
  • Dynamic Cubes Checklist: Resources
    • Is there access to resources with DBA skills and privileges?
      • DBAs are a key resource in the optimal tuning of a Dynamic Cube
      • As data volumes grow and query patterns change, creation of in-database aggregates will be required
    • Are the personnel developing a Dynamic Cube have advanced modeling/authoring skills?
      • Dynamic Cubes requires dimensional modeling skills as well as a good understanding of relational star schemas and SQL Queries
      • Report developers need to understand how to author reports against dimensional sources
    • Is the LOB responsible for application maintenance?
      • As data volumes grow and more users write reports, a Dynamic Cubes will need to be continually optimized. This may be beyond the skill set of the LOB. Unlike Transformer, Dynamic Cubes require optimization across the full stack.
  • Dynamic Cubes Checklist: Change Management
    • Do you rely on Cognos PowerPlay Studio?
      • PowerPlay Studio is only used for Transformer Cubes
      • Transitioning to Dynamic Cubes will require a change management strategy for shifting users to Cognos Workspace Advanced
    • Do you have many Cognos Report Studio reports against Transformer cubes?
      • Each report will require conversion to the new Dynamic Cube
      • Depending on the complexity of the report and structural differences between the Transformer and Dynamic Cubes, this can take 1-3 days per report

Case Study

  • Summary
    • Customer requested a POC of Dynamic Cubes in their environment to replace a problematic Transformer implementation
  • Key Current State Issues
    • Transformer Cube takes 20+ hours to build for 3 years of data
    • Various smaller cubes and packages were created as a workaround
    • Performance using PowerPlay Studio is slow
    • Fact table contains 600+ million rows and growing
    • Would like to create a cube with 5 years of data for trending analysis
    • Slow PowerPlay Studio Reports takes 4+ minutes to render
    • Advanced complex reports are maintained by the support group
  • Our Findings
    • This use case could benefit from the use of a Dynamic Cubes
    • Transformer load time of 20+ hours goes away to a fully optimized cube load of 30 minutes
    • Performance of a majority of PowerPlay studio reports went from minutes to seconds when hitting in-memory aggregates
  • But Need To
    • Clean up star schema further to resolve RI issues
    • Roadmap to optimize star schema with integer keys
    • DBA Resources will need to be allocated up-front and on-going
    • Continually optimize cube
    • Plan for report conversion and change management from PowerPlay Studio to Cognos Workspace Advanced
    • Recommend moving to 10.2.2 in order to speed up optimization via the ‘user-defined’ in-memory aggregate feature
  • What We Did
    • Environment was 10.2.1 FP5
    • Analyzed one Transformer cube
    • Analyzed the Transformer data sources and validated the underlying star schema
    • Modeled one Dynamic Cube against two fact tables. Based the design on how the Transformer cube was structured.
    • Implemented one virtual cube that combines the two fact tables
    • Optimized and validated the cube. Worked with DBAs to create in-database aggregates.
    • Create 44 in-memory aggregates
    • Converted one Transformer-based report to Dynamic Cubes in consultation with in-house developer
  • What We Determined
    • Performance
      • Queries that hit in-memory aggregates were nearly instantaneous
      • Queries that did not hit in-memory aggregates but hit in-database aggregates performed slower but were still in under 10secs
      • Subsequent request of the same queries performed well due to data cache hits
      • Queries that hit the 600M row fact table performed poorly as expected
      • After creation of in-database aggregates, load time of in-memory aggregates went from 4 hours to 30 minutes
    • Optimization
      • Several runs of the Dynamic Query Analyzer were required against an adequate workload log to get satisfactory in-memory aggregates
      • The new 10.2.2 ‘user-defined’ in-memory aggregates would have sped up optimization
    • Data Source
      • Star schema but:
        • No surrogate keys were used
        • Dimension level keys were not unique. Some were blank and rolled up to multiple parents.
      • Later determined referential integrity between facts and certain dimensions was lacking
    • Data Quality
      • Due to referential integrity issues, totals and sub-totals were not footing across various dimensions
      • Did not tie back to Transformer totals as Transformer used the ‘orphan’ category feature
    • Report Conversion
      • One complex report took approximately 3 days to convert. Required export to XML and search/replace parsing.

Demo

  • 2.2 user defined in-memory aggregates
  • What happens when RI goes bad

OPEN