Top Ten Tips and Techniques From the Field

1. Top Ten Tips and TechniquesFrom the Field Mark Karas Senior Consultant BrightStar Partners, Inc.

2. Tenured Professional Services Implementation-Based Products Worldwide CPM Support Communities Depth and Breadth / Thought Leadership

3. Proven Track Record BrightStar Partners has a proven track record of success across numerous industries and customers of all shapes and sizes. Several of our marquis clients include:

4. Professional Services • Tenured staff of consultants • Best Practices / Trademarked Methodology • Focused on delivering solutions; not just reports • BSP Software arm provides unmatched technical muscle • Technical assessments • Full life cycle implementation projects • Custom SDK and web app development

5. BSP Software • Implementation-based Software™ focused on delivering greater efficiencies and tighter controls for your IBM Cognos environments. • MetaManager – Allows administrators and developers to streamline and automate the administration and maintenance of IBM Cognos environments. • Integrated Version Control – IVC is an integrated, real-time, user-driven method for capturing, maintaining, and managing previous versions of IBM Cognos content. • CPM Explorer – Provides access to IBM Cognos content for end-users, developers, and administrators through a file tree metaphor in either Windows File Explorer or directly in Microsoft Outlook. • Many additional product offerings can be found on our website at www.bspsoftware.com.

6. COGNOiSe.com • Worldwide Cognos support community • 9,500 members and growing • 25,000+ posts • Forums for everything Cognos • Cognos 8 Platform • Legacy BI • Planning & Consolidation • Scorecarding and Dashboards • TM1 • CPM

7. Top Ten Tips and Techniques from the Field This technical presentation highlights numerous challenges that BSP consultants and software developers have overcome during the course of delivering superior solutions to our clients

8. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running Cognos reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

9. 1. Security-driven columnar data display • This technique is a simple yet functional way to restrict sensitive data so only a certain group of users will “See” the sensitive data’s values. • Requires: • Creating a group in the Cognos namespace • Adding the appropriate members to the new group • Creating a derived query item to use in place of the standard query item

10. 1. Security-driven columnar data display, cont’d • Create a Security Group in Cognos namespace (See_Cost in this example) • This security group should have all members that are allowed to see the secured data

11. 1. Security-driven columnar data display, cont’d • Modify the definition of the column to be secured. The definition should refer to the security group. • Publish the package

12. 1. Security-driven columnar data display, cont’d • Create a report using the modified column in the layout (Unit cost in this example)

13. 1. Security-driven columnar data display, cont’d • Run the report as a member of security group • Run the report as a non-member of security group

14. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running Cognos reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

15. Advanced Report Studio 2. Dynamically nesting data in a report Nice usage of some relatively simple JavaScript that can transform a grouped list into a decent drill-down-like, nested style replacement for true OLAP analysis, when you are working with relationally modeled data. Some simple JavaScript, a couple of new image files and a little creativity is all that is required!!

16. 2. Dynamically nesting data in a report, cont’d 16

17. 2. Dynamically nesting data in a report, cont’d 17

18. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running Cognos reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

19. 3. Express vs. Professional authoring Facts about, and prerequisites for the Express Authoring mode: • Main purpose: Provide non-Cognos report authors the ability to create Financial reports • Works best with a package that contains a multi-dimensional data source (DMR, Cube) • Reports created in either authoring mode can be edited using the other mode • The mode that is set in Report Studio at the time of authoring a report is not tied to the report specification or vice-versa • Like Query and Analysis Studios, the default Express authoring environment works with live data

20. 3. Express vs. Professional authoring, cont’d Express Authoring facts, cont’d • The Express Authoring mode works only with Crosstabs. Lists and Charts are not available in the tool set, nor are there any prompting capabilities. • By default, the reports authored in Express mode are not Drill-Down enabled, but drilling up and down is available • Levels are not part of the toolset, strictly entire hierarchies and the members, a la Analysis Studio To get an understanding of the differences in the two modes, I used the GO Finance cube as a data source to create a Balance Sheet report in both authoring modes. My findings are as follows:

21. Positives/Neutrals Formatting is similar from the toolbar aspect. There is no properties window like in Professional mode, so the toolbar is the place to change font, justification, add borders, indenting (padding), etc. Creating a Balance Sheet report in Express mode was indeed faster than using the Professional mode From the Financial Analyst’s viewpoint, he or she will be working with the accounts and account roll-ups… things they are already very familiar with, as opposed to levels of a hierarchy which is often confusing to them. Negatives Changes in the underlying hierarchies will probably break these member-only reports. Additions to the Chart of Accounts will not be picked up automatically as they would if levels were used. Lack of flexibility in object usage and setting. No way to turn off the data preview 3. Express vs. Professional authoring, cont’d

22. 3. Express vs. Professional authoring, cont’d In Summary: I found Express Mode to be fairly easy to use for creating a quick financial report. It is probably not, however, going to replace a truly professionally authored and formatted report when it comes to the total end user/consumer experience, given its “Ad Hoc” feel.

23. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running Cognos reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

24. 4. Running Cognos Reports using URLs • Cognos offers the ability to run reports through a URL • You can pass along information such as: • Format • Runtime Parameters • Items per page • Locale • Many more… • User Interface Options • Hide the toolbars or buttons • ‘Return’ button behavior • Action • Run report or view saved output • Prompt values

25. 4. Running Cognos Reports using URLs, cont’d • Why would I want to run a report using a URL? • Integration with other web applications • Embed the reports in an IFrame to make it part of the page • Replace data driven web pages with Cognos reports • Cut down on development time • No need for a developer • Create interactive reports • Create expand/collapse sections within a report to display details using a second report • Only run the detail data as needed, cut down on query time! • Use more advanced AJAX techniques to create Fly-outs • Special output format ‘HTMLFragment’ • Impress your friends

26. 4. Running Cognos Reports using URLs, cont’d Some Simple HTML in any web page… …embeds a report within it.

27. 4. Running Cognos Reports using URLs, cont’d A little JavaScript and a few HTML items… …and you get a custom master / detail report

28. 4. Running Cognos Reports using URLs, cont’d • URL Format • Starts with the path to your Cognos installation • http://servername/cognos8/cgi-bin/cognos.cgi ...or... • http://server:9300/p2pd/servlet/dispatch • Required values • b_action=cognosViewer • ui.action=run ( or view) • ui.object=<search path goes here> • Optional values • p_<prompt name>=<value> • ui.outputFormat=<format> • cv.header=false • cv.toolbar=false

29. 4. Running Cognos Reports using URL, cont’d Where can I get more information? Chapter 17 of the Cognos SDK Developer Guide is dedicated entirely to performing tasks with URLs. Cognoise.com

30. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

31. 5. Preserving Top-N functionality in Drill-Downs • Goal: Have a drill-down enabled report showing only the Top 2 values rather than showing all values, at any level of the hierarchy • Problem: While the simple usage of an MDX function, TopCount() will provide the desired effect on the report’s initial output, when the user drills-down on one of the Top 2 values, however, the TopCount functionality is lost. • Solution: • Utilize the Children() MDX function • Incorporate Member Sets • Set Advanced Drill Behavior

32. 5. Preserving Top-N functionality in Drill-Downs, cont’d • Example: • Uses DMR package, GO Sales • Query: • Series: [Sales].[Time].[Time].[Year] • Measure: [Sales].[Sales].[Revenue] • X-Axis: TopCount([Sales].[Product].[Product].[Product line],2,[Measure])

33. 5. Preserving Top-N functionality in Drill-Downs, cont’d We want to see the breakdown of Revenue by Product Types under Camping Equipment so we drill-down on Camping Equipment. Note how the Top 2 is lost…we see 5 product types. Our desire is to still only see the top 2, Tents and Packs in this case

34. 5. Preserving Top-N functionality in Drill-Downs, cont’d • Changes: • Query updated: • All Products (new)[Sales].[Product].[Product].[Product(All)]->[all]… is a MUN (member unique name) where [Sales].[Product].[Product].[Product(All)] represents the Top or “All” level of the Product Hierarchy and ->[all] represents the member of this level • Product Lines (new): children([All Products]) • X-Axis (changed): TopCount([Product Lines],2,[Measure]) • Query Property Define Member Sets set to Yes.

35. 5. Preserving Top-N functionality in Drill-Downs, cont’d • Changes cont’d: • Define Member sets: • From the Insertable Objects window in the upper left, drag the Product Lines query item into the member sets window (on the right, currently empty). Rename this item Product Lines MS • From the Insertable Objects window in the upper left, drag the X-Axis query item into the member sets window and onto the existing Product Lines MS member set. Rename this to X-Axis MS

36. 5. Preserving Top-N functionality in Drill-Downs, cont’d • Changes cont’d: • Modify the Report’s Drill Behavior • From the Data menu, select Drill Behavior… • Click the Advanced tab • Click the Product Lines data item • Set both the Drill-Up and Down behavior to “Replace Expression” • Click the X-Axis data item • Set both the Drill-Up and Down behaviors to “Preserve”

37. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

38. 6. Effective Dashboard Navigation • Allows easy intuitive navigation between summary and detail views of data using Table of Contents panel • Table of Content panel contains links to detail reports which is displayed on each report • Simple and quick setup in Report Studio • Easy replicable, flexible, and efficient design • Setup uses the following major components: • Cognos Connection Portlet Tab • Layout component reference • Drill-through • Summary report • Detail reports

39. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

40. 7. Performance Tuning – Native vs. Cognos SQL • You can optimize your report performance by analyzing the Native and Cognos SQL produced by a slow performing query. • Cognos SQL is SQL which is required to be processed in order for Cognos to produce the report. • The Native SQL is the query which is passed to database for execution. Native SQL is subset of Cognos SQL.

41. 7. Performance Tuning – Native vs. Cognos SQL, cont’d • Native SQL select "RETURNED_ITEM"."RETURN_REASON_CODE" from "GOSL"."dbo"."RETURNED_ITEM" "RETURNED_ITEM" order by 1 asc select "RETAILER"."RETAILER_CODE" from "GORT"."dbo"."RETAILER" "RETAILER" order by 1 asc • Cognos SQL select 1 as C_____CubeDetailsItem, XSUM(RETAILER.RETAILER_CODE ) as RETAILER_CODE, XSUM(RETURNED_ITEM.RETURN_REASON_CODE ) as RETURN_REASON_CODE from GOSL.GOSL.dbo.RETURNED_ITEM RETURNED_ITEM left outer join GORT.GORT.dbo.RETAILER RETAILER on (RETAILER.RETAILER_CODE = RETURNED_ITEM.RETURN_REASON_CODE) group by 1

42. 7. Performance Tuning – Native vs. Cognos SQL, cont’d • The Native SQL consists of two separate select statements. You may notice that the qualifications are different. This indicates that each of the two columns come from a table in a different schema. • The Cognos SQL shows that the query requires a left outer join relationship between the two tables. However, since this relationship is not included in the Native SQL, it is being processed locally. Depending on the size of the tables involved, this may impact performance • Actual source of the problem is the tables were imported as two different data sources.

43. 7. Performance Tuning – Native vs. Cognos SQL, cont’d • The model will need to be updated to include both tables under the same data source • This will change the Native SQL to select distinct "RETAILER"."RETAILER_CODE" "RETAILER_CODE", "RETURNED_ITEM"."RETURN_CODE" "RETURN_CODE" from "GORT"."RETAILER" "RETAILER" LEFT OUTER JOIN "GOSL"."RETURNED_ITEM" "RETURNED_ITEM" on "RETAILER"."RETAILER_CODE"="RETURNED_ITEM"."RETURN_REASON_CODE"

44. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

45. 8. Automated Promotion of PowerCubes • IBM Cognos 8 creates a file lock when a user accesses the cube through any studio of IBM Cognos. Even after user closes the cube, IBM Cognos keeps those locks for a specific time. • You will get an error if you try to build cube while the file is locked. • Steps involved in building cubes in Cognos 8 - disable cube, build the cube, and enable the cube. • PCConn – PCConn is Cognos utility which accepts Cognos commands in batch mode. It accepts file as input. It is under c8\webapps\utilities directory. This will be used to disable and enable the Cube • CogTr – Cognos Transformer command line utility. It is under c8\bin directory. This is used to build the Cube

46. 8. Automated Promotion of PowerCubes, cont’d • Create one file as DisableCube.txt under D:\Script directory connect <Domain Name> <CognosLogon> <Cognos Password> Disable <Data Source Name> Exit • Create another as EnableCube.txt under D:\Script directory connect <Domain Name> <CognosLogon> <Cognos Password> Enable <Data Source Name> Exit

47. 8. Automated Promotion of PowerCubes, cont’d • Create batch file CubeBuild.bat under the D:\Script directory cd “C:\Program Files\cognos\c8\webapps\utilities\PCConn" cmd /c PCConn -f "D:\Script\DisableCube.txt" ping localhost -n 300 > nul cd "D:\Program Files\cognos\c8\bin" cmd /c cogtr -n -p"D:\Cognos OLAP Models\PNL Reporting.pyj" cd "D:\Program Files\cognos\c8\webapps\utilities\PCConn" cmd /c PCConn -f "D:\Script\EnableCube.txt"

48. Security-driven columnar data display • Dynamically nesting data in a report • Express vs. Professional authoring • Running reports from URLs • Preserving Top-N functionality in drill-downs • Effective dashboard navigation • Performance Tuning – Native vs. Cognos SQL • Automated Promotion of PowerCubes • Dynamically showing/hiding in-report prompts • Framework Model Namespaces and Folders

49. 9. Dynamically showing/hiding in-report prompts 49

50. 9. Dynamically showing/hiding in-report prompts, cont’d 50