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
Q: Other than the Cognos CAFE license, is there any other Cognos license required to use CAFE?
Q: Which TM1 version is Cognos CAFE compatible with?
A: The TM1 features of CAFE were introduced with v10.2.0 of TM1 and are not compatible with earlier versions.
Q: What version of Cognos CAFE, BI and TM1 are you using in the demo?
A: 10.2.2 of CAFE, BI and TM1.
Q: Do you expect to identify some differences between CAFE and Cognos for Excel?
A: We won’t be specifically addressing this today; however, we have another webinar in our resources library that addresses this topic called: Integrating IBM Cognos Data Into MS Office.
Q: Does Cognos CAFE pull data from Cognos data cache or from databases (e.g. Oracle) directly through Cognos datasource?
A: CAFE maintains its own cache. This cache can be cleared using the Options button on the IBM Cognos ribbon if the Excel file size gets too large.
Q: How does writeback capability work in Cognos CAFE?
A: Overwrite cell(s) in an Exploration or Flex View and click Commit (check mark button) on the Cognos toolbar.
Q: How can I use the data items in the Information folder?
A: Drag and drop to an Excel cell, and then reference in a COGNAME or COGVAL function, if applicable.
Q: Can I set up a summary tab and set up links to reference in both the BI & TM1 tabs?
A: Yes, you can use Excel formulas to reference data link from BI or TM1 anywhere in the workbook.
Q: Does the security in TM1 carry over to Cognos CAFE?
A: Yes, the security carries over when querying or refreshing data security; however, this feature is available based on the permissions assigned to the account used to logon.
Q: I have a relational database in BI; can this do a simple list?
A: Yes, use the Create a New List option instead of a crosstab.
Q: Can Cognos CAFE open with both relationally modeled sources and OLAP sources such as cubes?
A: Yes, CAFE can be used with relationally modeled sources.
Q: Is there a shortcut key for refreshing data and if not, can I add a button to the cell area to run a macro to refresh?
A: The Refresh button is on the toolbar and ribbon.
Q: How does Cognos CAFE deal with Excel updates? For example, reports written using VBA have to be updated with each new release of Excel. Will CAFE reports have to be updated as well?
A: No, updates need to be made to CAFE reports for Excel updates. CAFE is fully supported for Excel 2007, 2010, 2013 and future fix packs for each. For Excel 2016, you will likely need to upgrade to a new release of CAFE.
Q: If I create a sheet using a BI source, can I save it as an .xlsx document and retrieve it as an independent spreadsheet without reconnecting with the Cognos BI package?
A: Yes, data saved in .xlsx can be viewed without reconnecting with the Cognos BI package or by an Excel user without CAFE.
Q: When connecting to a BI source, how do I apply data filters?
A: For a list report use the Filter button on the Cognos toolbar. For a crosstab, use the Context area and right-click on the Edit Set.
Q: How is a Sandbox in the flex mode used?
A: Sandboxes are available in TM1 to enable you to evaluate different scenarios (what-if analysis) for your slice of data.
Q: How can I access the attribute of a dimension in TM1?
A: You can right-click a dimension hierarchy and select Search Metadata; otherwise, there is no access to TM1 attributes in the current release.
Q: Do I have to hardcode Excel workbooks before I send them to a user without access to BI or TM1?
A: No, but there is an option to convert linked data static values if you have other reasons to do so.
Q: How do I create a single sign-on for TM1 or BI?
A: On the Cognos ribbon, click Options and under Authentication there is a checkbox to enable single sign-on.
Q: Can I use predefined standalone filters against relational models?
A: When connecting to a relational BI source a Filters or Filters and Calculations folder appears, so if filters are present in the source, they would be available in CAFE.
Q: Is Cognos CAFE primarily for crosstabs?
A: In our opinion it is, but CAFE does have list report capabilities similar to Query Studio, which can be combined with local Excel functionality.
Q: Where are Flex View saves actually stored: on the TM1 server or locally in my Cognos CAFE configuration?
A: They are stored locally in the Excel file.
Q: How does write back work in Cognos CAFE?
A: You can overwrite cell values and use the Commit button on the Cognos toolbar.
Q: Where can I open previous Flex Views in the tree?
A: If you have a TM1 source selected, there is a Current Flex Views folder that appears at the bottom that will expand to show Flex Views in the current workbook.
Q: Can I drill down or expand/burst the details from the “exploration” or “cell-based” data into an Excel workbook?
A: In Exploration mode, double-click to drill down or up. You can also right click on the IBM Cognos Analysis menu to access the information. There is no bursting capability.
Q: Can I use subsets in Flex View?
A: Yes, if you expand a dimension for a TM1 source, you will see the subsets.
Q: Why does Cognos CAFE not support dynamic rows?
A: TM1 Active Forms have not been added to the feature set, but are likely on the roadmap for a future release.
Q: Can you provide guidelines for when to use or not use Cognos CAFE?
A: The following are some criteria that might factor into guidelines:
Frequency – one-time analyses and reports that are rarely run are probably not worth developing with Cognos BI
Many reports with multiple data sources or complex layout/formatting/calculations may be too costly to justify doing in Cognos BI
Requirements related to SOX, audits and regulations might exclude certain reports from being done in Excel
Reports supporting recurring business processes or reaching a broad audience are better suited for Cognos BI
The main point is that Excel has maintenance challenges, is error prone and can often fall outside of prudent management and control. So the intent of these guidelines is to inform you of situations where self-service is okay versus when report requests should go through IT.
Q: What version of Excel does Cognos CAFE work with?
A: CAFE 10.2.x works fully supported for Excel 2007, 2010, 2013 and future fix packs for each.
Q: When using integrated Cognos auth I get a writeback error. It disappears when switching to auth method 1. Is this a common error?
A: In the webinar demo environment, the TM1 servers are configured with security mode 5 (CAM integration), but not single sign-on. We have not encountered any errors with writeback.
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