Integrating IBM Cognos Enterprise Planning with Essbase Proven Practice

by user


auto racing






Integrating IBM Cognos Enterprise Planning with Essbase Proven Practice
Proven Practice
Integrating IBM Cognos
Enterprise Planning with Essbase
Product(s): IBM Cognos Planning
Area of Interest: Modeling
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
INTRODUCTION ............................................................................................ 4
ENVIRONMENT ............................................................................................. 4
ESSBASE DEFINITIONS ................................................................................ 5
INTEGRATION TESTS .................................................................................... 6
ANALYST > ESSBASE (METADATA)............................................................... 7
ANALYST > ESSBASE (DATA)........................................................................ 9
ESSBASE > ANALYST (DATA)........................................................................ 9
ESSBASE > ANALYST (METADATA)............................................................... 9
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
This document outlines the results of a study into techniques and
considerations for integrating IBM Cognos Enterprise Planning with Hyperion
Essbase. Several existing implementations have been identified where varying
degrees of integration have been achieved.
This study is the first step toward producing a formal integration
methodology, as well as producing a knowledge base for use in the pre-sales
environment when discussing integration requirements with existing Essbase
applications at prospect sites.
The integration testing was carried out using Essbase Version 7.1 and IBM Cognos
Enterprise Planning Analyst 7.3. For the purposes of this first study no testing for
direct integration with Contributor was done, though it would seem more likely
that Analyst would be used as a staging area for integration to Contributor in a live
The testing environment used is detailed in the following schematic :
Excel Add-in
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
Overall, the test involved first creating the ‘Overheads’ cube from the
standard EP CubeDemoCo model in Essbase, then populating it with data
derived from Analyst. Then the data for one Essbase version was loaded back
into Analyst from Essbase as a separate Analyst version, and finally updated
structures for the cube from Essbase were replicated back into the Analyst
Essbase Definitions
This section provides definitions of various components of any Essbase
environment :
This is the named ‘container’ for all objects in an Essbase application. This is
akin to a library in Analyst.
This is the data storage component of an application. It takes the form of a
multi-dimensional OLAP cube.
A data dimension of the database – much like a Dlist in Analyst.
One of the items in a dimension. This can be detail or calculated. ALL
member names in a database must be UNIQUE (across ALL dimensions).
Contains all the members of a dimension and their attributes, displayed in a
hierarchal tree structure.
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
Definitions for how data and/or metadata are loaded from an external file.
This is similar to a file map in Analyst.
Carries out operations on the database, such as performing calculations or
running reports.
Integration Tests
Four tests were conducted in order to successfully complete full bi-directional
These were :
Analyst > Essbase (Metadata)
Analyst > Essbase (Data)
Essbase > Analyst (Metadata)
Essbase > Analyst (Data)
There are various methods which may be employed to achieve these different
integration paths. These include :
1. Standard functions for loading data from flat files (e.g. file maps /
data load)
2. ODBC linkage (using Text drivers and flat file extracts)
3. The Essbase API
For this exercise, flat file extracts without the presence of an ODBC driver
(method 1) were used. This is the quickest and easiest method to employ.
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
During a live implementation, use of the Essbase API (method 3) would
provide a more closely-knitted level of integration from the Essbase
viewpoint, but would require a greater degree of more specialist consultancy,
due to the need for specific knowledge of the Essbase scripting language. In
any case, flat files would still be the method of transporting data and
metadata between the applications.
Method 2 would allow the use of SQL statements in order to carry out any
data manipulation and/or data cleansing that may be required, but given the
results of this study, it is felt that this would not add any significant value to
the integration mechanics.
Obviously, a combination of these methods could be used (e.g. ODBC to load
data into Analyst and the Essbase API to load data in the other direction).
Analyst > Essbase (Metadata)
In order to create the Essbase overheads cube, three of the four Dlists in the
Analyst cube (Overheads, Months and Versions) were combined with a one
item ‘dummy’ dimension to create cubes which were then exported to flat
It is important to note that due to the fact that all member names in an
Essbase database must be unique, it follows that ALL Dlist items in a cube
must also be unique. This means that having two Dlists both containing the
same item name (e.g. ‘TOTAL’) is not acceptable.
The first step to take in Essbase is to create a RULES object which defines
how to load a flat file in order to create member items in a dimension. For
these three dimensions the items were imported at a detail level with no
The calculations for these dimensions were then added manually in Essbase,
including the ‘Variance’ item in the Versions dimension, the Overheads
calculation items and the ‘Full Year’ item in the Months dimension. The new
Months dimension was then formatted at a ‘time’ dimension in Essbase and
the Overheads formatted as an ‘accounts’ dimension. These assigned
categories form the mechanism that Essbase uses to assign calculation
priorities in the database.
The fourth dimension (‘depts’) was created using the same ‘nodes.csv’ file
that is used to create the CubeDemoCo Contributor eList.
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
Essbase has an option for assigning a child/parent relationship in the RULES
definition. This is limited to a two-column relationship as per the nodes.csv
file. It should therefore be noted that a multi-column format (as Analyst can
read – up to 8 levels) cannot be used for this purpose in Essbase.
Additionally, it should be noted that the top level of the members in a
hierarchical dimension in Essbase also forms the name of the dimension.
Therefore, the resulting depts dimension in Essbase was called ‘Company’.
Each dimension can be assigned to calculate automatically or not. Instant
calculation means instant results. However, this does put extra load on the
server component and slows the system down.
Without automatic calculation, a routine must be run to calculate the entire
database. For example, Overheads and months might be set to calculate
automatically. While the aggregation of the Company hierarchy and the
variance between versions might only be calculated on demand.
Finally, it is necessary to assign a ‘dense’ or ‘sparse’ attribute to all
dimensions of an Essbase database. What results is that all dense dimensions
are formed into data ‘BLOCKS’. These blocks are then aggregated via the
sparse dimensions.
What should be considered when assigning these types?
1. By definition, the dense dimensions should be those that would be
deemed to be highly populated with data. This helps to speed up
calculation times as the resulting database has more densely
populated clusters of data which reside closer together in the overall
2. Any non-aggregating calculations (e.g. those that require a weighted
average) WILL aggregate when the sparse dimensions calculate. This
requires the additional step of creating a script to recalculate these
items properly after the database has been calculated.
In this case, the following settings were applied :
Dense or Sparse
IBM Cognos Proprietary Information
Calculation Setting
On Demand
On Demand
Integrating IBM Cognos Enterprise Planning with Essbase
Analyst > Essbase (Data)
The Overheads cube from Analyst was exported in a multi-column format
with the Months as the data dimension.
It was found that there is no specific limitation on the format of this file (it
could also be a single column format). Column ordering is also not an issue.
As with the Metadata load, a Rules object must be created to define the
columns in the flat file and whether they are to be used for Dimension
matching or data.
Once this has been created, the data load is executed and this was found to
be 100% correct in the Essbase database when checked back to the Analyst
The Essbase add-in for Excel must be used to query the database to see the
data it contains in this environment.
Essbase > Analyst (Data)
The Essbase Add-in for Excel was used to define the layout of the data
required for the flat file. This can then be saved back to the Essbase
application as a query definition and that can then be used as an export
format when exporting the database to a flat file.
The Overheads database in Essbase was exported to a flat file with a csv
format. Again, this was in a multi-column style with months as the data.
A file map was then created in Analyst based on this file and the data for one
of the versions in the file loaded into a new version in the Analyst cube.
The resulting data was verified back to the Essbase database for accuracy
and this was found to be correct.
Essbase > Analyst (Metadata)
IBM Cognos Proprietary Information
Integrating IBM Cognos Enterprise Planning with Essbase
This final part of the study was found to be the most difficult of the four
steps, but was still achieved within the 2 day timeframe.
There is no standard method for exporting an Essbase hierarchical dimension
in hierarchical format. This can be achieved with the Essbase API but time
constraints meant this could not be carried out.
The same technique was used to generate a flat file as in the data export
step. The Essbase add-in for Excel was used to generate a query with the
Company dimension on the rows and one of the versions as a column.
When a report is created in this way the different levels of the hierarchy are
displayed with indentations on the label (i.e. the lower the level – the greater
the indent).
When this query format is saved in the Essbase application, one of the
parameters is the IndentGen
alue. Manually editing this to ‘50’ creates clear separation between the indent
fields (50 was used as this is the maximum length of a Dlist item).
An Analyst file map using ‘fixed width’ can then be created, and the column
separation can be manually edited if required within the file map.
The file map ‘follow-on’ setting must also be used as parent levels are only
named once in their column.
This can then be used to perform a D-list update in Analyst.
IBM Cognos Proprietary Information
Fly UP