1 / 28

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

The Great OLAP Debate! TM1, PowerPlay & DMRs April 29, 2011. Panel Presenters Michael Langton Scott Luck-Baker Mike Roberts Pedro Mendoza. Panel Debate Format. Each one of the panelists will present evidence that their approach is the best way to handle OLAP reporting

Télécharger la présentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.


Presentation Transcript

  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)

  14. 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

  15. 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)

  16. DMR Framework Designs

  17. 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

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

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

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

  21. 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

  22. 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

  23. 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.

  24. 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

  25. Panel Debate

More Related