Brian Simpson, IBM Jason Tavoularis, IBM Ronnie Rich, IBM

1. What Everyone Should Know about IBM Cognos TM1 and IBM Cognos Business Intelligence IntegrationSession Number BPM-1942B Brian Simpson, IBM Jason Tavoularis, IBM Ronnie Rich, IBM

2. Please note IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion. Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision. The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion. Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user’s job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results similar to those stated here.

3. Acknowledgements and Disclaimers: Availability. References in this presentation to IBM products, programs, or services do not imply that they will be available in all countries in which IBM operates. The workshops, sessions and materials have been prepared by IBM or the session speakers and reflect their own views. They are provided for informational purposes only, and are neither intended to, nor shall have the effect of being, legal or other guidance or advice to any participant. While efforts were made to verify the completeness and accuracy of the information contained in this presentation, it is provided AS-IS without warranty of any kind, express or implied. IBM shall not be responsible for any damages arising out of the use of, or otherwise related to, this presentation or any other materials. Nothing contained in this presentation is intended to, nor shall have the effect of, creating any warranties or representations from IBM or its suppliers or licensors, or altering the terms and conditions of the applicable license agreement governing the use of IBM software. All customer examples described are presented as illustrations of how those customers have used IBM products and the results they may have achieved. Actual environmental costs and performance characteristics may vary by customer. Nothing contained in these materials is intended to, nor shall have the effect of, stating or implying that any activities undertaken by you will result in any specific sales, revenue growth or other results.

4. Agenda • TM1 and BI – Where we’ve come from • TM1 and BI – BC Egg Case Study • Dynamic Query Mode • Authoring and Consumption • Report Server Best Practices • eXtensible Query Engine • Dynamic Cubes and Cube Technologies • Upgrade and Migration • Conformance • Performance • Cognos Insight • What’s New • In The Labs

5. Where We’ve Come From… • Platforms • Global Growth • Enterprise Scale • World Class BI • Robust Planning • Higher Scale • In-teractive Memory • Configurable OLAP • Passionate Customers 4

6. TM1 and BI: Bringing It All Together… Grading Stations Production Data Biosecurity Surveys ProducersFlock Count, Hatchery, Fowl Removal

9. BI Mobile reporting on TM1 Data

10. IBM Cognos BI Dynamic Query Mode

11. Dynamic Query Mode 101 - Definition • Refers to the execution of BI content by Cognos 10’s Java-based extensible query engine • 64-bit • Performance • In-memory caching • Optimized query processing

12. Flowchart Cognos BI User Interfaces Report Server Query mode Dynamic Compatible eXtensible Query Engine (Java) v8.4.1 Query Engine (C++) Data sources

13. Authoring and consumption

14. Report Server • Primarily serves 2 purposes • Routes query specifications to the query engines • Renders the result sets delivered by the query engines • When Report Server is configured as 32-bit on a 64-bit BI server • DQM data access will be 64-bit • CQM data access will be 32-bit • When Report Server is configured as 64-bit • Only DQM will work – use Advanced Dispatcher Routing for CQM content • Rule of Thumb • Configure 1.5 ReportService processes per CPU for 10.2 DQM

15. eXtensible Query Engine (XQE) • The lessons learned over decades of leading the BI industry culminated in the design of this next generation query engine • Embraces the principles of extensibility and abstraction Accelerated value in every new release • 10.2 extensions • Dynamic Cubes • HiveQL (see session 2545A Thursday 10-11 AM) • ERP/CRM providers

16. Dynamic Cubes – Feature Mission High performance analytics over growing data volumes Extends Dynamic Query with in-memory caching of members, data, expressions, results, and aggregates Aggregate awareness Aggregate optimization

17. Cube Technology Selection

18. Search for “Cognos Dynamic Cubes”

19. Compatible Query Mode • Very similar query processing to that in version 8.4.1 • Will continue to be maintained • Not actively enhanced Easy upgrades from Cognos 8!

20. C O G N O S C O G N O S C O G N O S Cognos 10 Upgrade Supported Path Upgrade first, then leverage new features Use LifecycleManager to validate results Cognos 8 and Cognos 10 Move business users into Cognos 10 Acceptdesired results Leverage Cognos 10 New Features (e.g. Dynamic Query, etc) Use Lifecycle Manager to validate results to leverage new features Accept desired results Promote reports to production

21. Migration • Highly recommend Dynamic Query Mode when starting new projects • Enabling DQM on existing projects is an optional migration • DQM performance may or may not be better • There may be no re-authoring required, there may be significant re-authoring required • DQM will issue an error to enforce certain best practices that are optional in CQM • Overall, migration ROI is highest for OLAP data sources • Overall, migration effort is highest with DMR • Use Lifecycle Manager to understand the ROI of migrating existing applications to DQ

22. Migration Scenarios Example #1 – Hierarchy not consecutive 2009

23. Migration Scenarios Example #2 – ‘Incorrect’ hierarchy level order 2009

24. Migration Scenarios Example #3 – Same hierarchy in rows and columns 2009

25. Levels and Attributes 24

26. Levels and Attributes Example: City World Continent Country State 2009 25

27. Levels and Attributes • Defined Levels should only be used when: • there is a Balanced Hierarchy • An author needs to select members by level • Attributes are level specific • The dimension does not have element security applied • Levels should not be defined when: • The hierarchy is Unbalanced • The report author wants to make use of attributes that span levels • Techdoc1598902: Avoiding BI Problems with Duplicate Cube Elements 26

28. Conformance

29. DQM Conformance Roadmap 10.1.0 10.2.0 10.1.1 • initial release supports: • IBM Cognos TM1 • SAP/BW • Oracle Essbase • added support for: • IBM Informix • IBM IMS • IBM InfoSphereBigInsights • SAP/ECC • Siebel • Salesforce.com • added support for: • IBM DB2 • IBM Netezza • Teradata • Microsoft SQL Server • MS Analysis Services • Oracle

30. Conformance: TM1 as a BI data source

31. Performance

32. Performance Improvements As of TM1 9.5.2 and Cognos BI 10.1.0 FP1 (DQM) • Query optimized • MDX Engine is moved to the TM1 server, closer to the data • Better use of the TM1 Stargate cache • Null suppression done on the TM1 server*

33. Avoiding wait times during interactive analysis • The following common usage pattern is NOT recommended:1) Drag item from the Insertable Objects pane to the rows / columns. 2) Drag specific members from the same hierarchy to the Context3) Since the result is sparse, turn on suppression to make it dense • Best Practices • Show only the top N members (elements) • Explicitly place members (elements) of interest on crosstab edges instead of all members and then filtering • Use parent members to avoid summary values computed in real-time

34. Cognos Insight

35. Cognos Insight • Cognos Insight is many things: • It’s a personal BI Analytic application • It’s a contribution client for TM1 • Its an integration point for BI and a method to develop Consumer Insight dashboards • If you’re a TM1 Customer of version 10.1+ any contribution licenses grant access to Insight as a contribution client, as well as a personal analytic tool. • Cognos Insight while being a highly visual interactive analytic client, is in fact running TM1 Engine • This allows Insight to respond instantaneously, and be able to quickly and efficiently handle large amounts of data.

36. Cognos Insight • When used as a contribution client there are two distinct methods: • Connected mode • This is great for central application where users are on low latency lans. All interaction with the TM1 Server is realtime. • Disconnected Mode • This allows the user to download a cache of the TM1 Server slice they are working with. • Interaction takes place locally and is committed back to the server • Offline work is available as of 10.1.1

37. Cognos Insight – Publishing to BI or TM1 • Starting with a personal analytic workspace in Insight you can choose to share this in two methods. • You can publish your Insight workspace to Cognos BI • You can also Publish your workspace to create a TM1 Server • You can share your Insight file on Cognos Connection

38. What’s New

39. Guided Import – PM and Insight Enhanced modeling and metadata/data management in Performance Modeler • Improved guided import for data and metadata from IBM Cognos BI packages and IBM Cognos Business Viewpoint • Analytics server advancements for BI reporting, including calculated and aggregated data

40. Email notification on WFP state • In TM1 10.1.1 we introduced the ability notify stakeholders of either status changes in the WFP or allow broadcast messages from the Admin. • This requires Security Mode 5 (CAM Integration) and is dependent on the BI HTS Service and email notification service. • This can be configured to show in their Portal inbox or to their corporate email. • We have provided tags to simplify messaging • Eg <owners> would broadcast to all node owners.

41. Rule Based Aggregate Awareness • Have you ever built a great rule in TM1 only to notice your BI report doesn’t think it’s so great? • Frustrated trying to recreate the outcome in your BI Report because you know it already exists in TM1? 2009

42. Rule Based Aggregate Awareness • With the introduction of Rule Based Aggregate Awareness this is no long a concern. • We have updated how our MDX responds to a BI request and now present those great Rules you haave written

43. In the Labs

44. TM1 and BI Integration “In The Labs” • Performance • CAFÉ • Metrics • Hierarchy Object • Attribute-Based Hierarchies • Subsets • Views • Strings • Automated CAM Group Maintenance • Cloud

45. Cognos Proven Practices on developerWorks ibm.com/developerworks/analytics/practices.html • Writing Efficient OLAP Queries • IBM Cognos BI Dimensional Functions • Using Dimensional Functions to Determine Current Period • IBM Cognos TM1 FEEDERS