Using Excel with Cognos TM1 and Cognos BI
Cognos Analysis For Excel (CAFE) is a popular way for business and financial analysts to combine enterprise information with local data in an Excel spreadsheet that can be refreshed as server information changes. In this webinar recording, we look at the capabilities of CAFE, compare them when using the tool with TM1 versus Cognos BI as a source and share useful tips and caveats along the way.
Providing demos of concepts, we cover the following topics:
- Differences between using CAFE with TM1 and Cognos BI
- When and why to consider using CAFE
- Useful techniques and common pitfalls
If you'd like to learn more, you may be interested in taking our Cognos Analysis for Excel training course. You will learn how to combine queries of Cognos data with Excel features to create complex, presentation quality reports. It also covers customization options and sharing of CAFE content.
IBM Cognos BI, CAFE (Cognos Analysis for Excel), Cognos TM1
BI Report Authors, BI Report Consumers, BI Power Users (Developers, Support Staff), BI Managers, Financial Planners, Financial Analysts, Finance Managers, Marketing Analysts
What is CAFE?
- Excel add-in (IBM Cognos tab/ribbon)
- Provides analytical and report authoring capabilities against Cognos BI packages and TM1 cubes
- Evolving as the successor to TM1 Perspectives, the legacy add-in for TM1 forms and reports
- Productivity tool to streamline report writing tasks
Why use CAFE?
- Excel is the most widely used reporting tool with estimates of nearly 1 billion users. Many reports will be done in Excel; CAFE can make the task more productive, reliable and accurate.
- Leveraging Excel skills can broaden authoring and self-service capabilities within the organization.
- Enables IT/BI groups to focus on high priority, high impact projects and delegate some report writing to business users.
- Best of both worlds: Excel ease-of-use, cell-based calculations and formatting, charting, print precision combined with refreshable links to governed, system of record data.
- Exploration – List or Crosstab (BI and TM1)
- Cell-based (BI)
- Flex View (TM1)
- Click IBM Cognos icon
- IBM Cognos panel opens to access server content
- Logon to BI, TM1 or both
- Functionality is based on the selected system
- Works with both BI and TM1 data sources
- Behaves like Analysis Studio/TM1 Cube viewer within
- Limit: one exploration per worksheet/tab
- Each exploration can reference a different data source within a single Excel workbook
- Governed data can be combined with local, cell-level calculations, formatting and commentary
- Starting point for other modes
- Multi-page report referencing a BI and a TM1 data source
- Excel formatting, chart and commentary
- Excel page setup/print functionality
- Works with BI sources only (note: TM1 can be configured as a BI source)
- Data from multiple sources can be included on a single sheet
- Data access implemented with Excel functions (COGNAME and COGVAL)
- No IBM Cognos toolbar, limited right-mouse menu
- Multiple data sources on a single sheet
Flex View Overview
- Works with TM1 sources only
- Ability to combine data from multiple cubes/servers on a single sheet
- Views can be linked to each other to share parameters
- TM1 functionality includes write back, spreading and sandboxes
- Excel cell-level calculations, formatting and commentary
Flex View Example
- Multiple Flex Views on a single sheet
- Link context cells
- Asymmetric crosstab
- Excel formatting, calculations
- Enables IT to focus on data management and enterprise wide reporting, and delegate some report writing to business users
- Improves productivity
- Combines governed data with local data, calculations, formatting and commentary
- Easy to reference multiple data sources in a single workbook and a single sheet in some cases
- Business users can overcome technically challenging authoring by leveraging Excel skills
- Train CAFE users on BI/TM1 exploration tools; experience with Excel does not eliminate the need for Cognos BI or TM1 knowledge
- Develop guidelines within your organization for when to use CAFE versus Cognos BI for report development
- Leverage CAFE as tool for business users to prototype and provide specifications for BI report requests
- Do not use CAFE to download large data sets, there are more efficient ways to move large amounts of data to the desktop