The Great OLAP Debate! TM1, PowerPlay & DMRs April 29, 2011

1. The Great OLAP Debate!TM1, PowerPlay & DMRsApril 29, 2011

2. Panel Presenters Michael Langton Scott Luck-Baker Mike Roberts Pedro Mendoza

3. Panel Debate Format • Each one of the panelists will present evidence that their approach is the best way to handle OLAP reporting • Your job as a participant is to ask questions to challenge each OLAP approach …

4. Goals For Session • IBM provides several options for OLAP reporting • Does one size fit all? • We will review each technology: • Description • Product Background • Key Functionality • Business Use Case “Sweet Spot” • Usage Notes, Design and Deployment Considerations

5. IBM TM1

6. IBM TM1 - Overview • Developed in the late 80’s as a backend for spreadsheets • Multi-dimensional database designed to simplify complex spreadsheets and separate the data from the formulas • TM1 cubes are essentially collections of business hierarchies (dimensions); numeric and text data can be stored at the intersections of every dimension element • TM1 cubes sit in RAM so that data consolidation and formulas (cube rules) are performed in “real-time” • TM1 clients include Excel, TM1Web, Contributor (for workflow), Executive Viewer, and Cognos BI

7. TM1 Web

8. TM1 Contributor

9. Executive Viewer

10. IBM TM1 - Sweet Spot • TM1 is designed for the WRITEBACK of numeric and text data • It is ultimately flexible and models can be built from a variety of data and meta-data sources to hold almost any type of data • TM1 includes a rule language for writing complex formulas into your model; rules are evaluated in real-time for instant feedback • Non-technical users can perform administrative/modeling tasks via wizards, drag & drop actions, or using customizable buttons through the web • Users can slice & dice cube views, and drill through to further levels of detail

11. IBM TM1 - Usage Cases • Replacing Excel as a planning tool • Slicing & dicing aggregated data • Comparing apples to apples • Processes that require manual entry • Processes that require real-time feedback/calculations • Processes that require workflow/security • What-if analysis / Driver-based planning • New ways of rolling up your data • Anywhere non-technical users need to build reports, add/remove elements, launch imports/exports

12. IBM Cognos PowerPlay

13. IBM Cognos PowerPlay- Overview • Originally developed by Cognos in 1989 • PowerPlay Transformer is used to define OLAP cube structures and building static “cubes” for analysis or reporting, usually on a scheduled basis • PowerPlay Cubes contain summarized data organized into dimensions and measures, can be built from very large datasets and are highly optimized for data retrieval • PowerPlay Cubes can be viewed via the web (Analysis Studio, Query Studio, Report Studio, C10 Biz Insight/Advanced) or via a full client (PowerPlay Client, CAFÉ Excel)

17. IBM Cognos PowerPlay- Sweet Spot • Ideal where users have large datasets that require flexible summarization and reporting options, as opposed to a list of canned reports • Transformer provides advanced multi-dimensional model support and varied data sources (via Framework Manager), incremental refresh options, alternate drill paths, automatic category counts, time-based and volume-based partitioning strategies • Non-technical users can explore data through simple click and drag operations and can gain insight through functions such as rank, sort, nesting and calculations • Users can drill from cube-to-cube or cube-to-database

18. IBM Cognos PowerPlay- Usage Cases • Great for sales, marketing and financial analysis • Users have large datasets, possibly in an existing database or across multiple sources • Users or IT want “self serve” analysis capabilities • Users want “zero footprint” • Users have no interest in budgeting or planning • Users don’t need real time reporting • Users don’t need to report on “non-dimensional data” elements • Warning: Prone to User Misuse (esp. in S7)

19. DMR Framework Designs

20. DMR Framework Designs - Overview • Introduced in Cognos 8 • Uses Framework Manager to model Relational Data to appear “like a cube” • Model can be used in Analysis Studio and Report Studio Express

21. Define Regular Dimensions • Consists of one or more user-defined hierarchies • Each hierarchy consists of • levels • keys • captions • attributes

22. Edit DMR in the Dimension Map • View, create, or modify: • regular or measure dimensions • hierarchies or levels • scope relationships

23. What the Authors See Dimension Hierarchy Level Member Child members Report Studio Data Tree

24. DMR Framework Designs - Sweet Spot • You do not need another application to build cubes • No need to wait while cube is being built • Data changes in the underlying tables are immediately available • Complex security rules can be created in one place (Framework Manager) • Define multiple Hierarchies for a Dimension • Define as many member attributes as you want

25. DMR Framework Designs - Usage Cases • Implement Drill Up/Down in reports without cubes • Analysis of Real-time data or data that would take too long to build into a cube • Models that have complex business rules that would be difficult to implement in a cube • Solutions where security is defined at the database level

26. DMR – Deployment Considerations • Aggregations are not stored in a cube. They are calculated from detail every time a report or analysis is run. • Performance is dependent on good hardware and design • Databases must be optimized to maximize performance • It may be necessary to employ a form of database vendor materialization to improve performance • DMR packages are usually built on top of existing FM models and are deployed the same way.

27. DMR – Design Considerations Design for Performance • Physical data should be in star schemas to minimize complex joins • Create summary tables to avoid aggregating on the fly • Model for high level analyses and rely on drill-through reports to give detail • DMR works best with small narrow dimensions rather than large wide dimensions • Build on top of a good well-designed relational Framework. • Build mandatory filters into your model to ensure that end users do not accidentally retrieve excessively large data sets

28. Panel Debate