IBM DB2 OLAP and Hyperion Essbase data sources are interchangeable and Cognos 8 works with them in exactly the same way. For example, you can connect to an Essbase data source using a DB2 OLAP connection. When you define the data source, you must choose the specific cube for the data source.
Before connecting to a DB2 OLAP or Essbase data source, some configuration is required if the data source uses scenario dimensions, balanced hierarchies, or measures.
You must choose either simple or query outlines when making calls in Essbase API.
Base your choice on the performance that you need and factors such as your network environment, the size of the databases, and the types of queries.
Use simple outline if you primarily run simple queries and use numerous cubes. If you use this option for large cubes, your queries may take a long time to run. Large temporary files may also be created as Essbase builds the necessary grid. After the grid is created, these queries typically run faster and do not increase network traffic. However, more memory is consumed.
This option is the default behavior in the Cognos environment.
Use query outline if you run numerous, complex, or advanced queries that use Cognos 8 features such as nested filtering or sorting.
To enable this option, edit the qfs_config.xml file in the cognos 8 location\configuration directory. Type the following line at the end of the <parameters> subsection of the DB2OlapODP provider section:
<parameter name="UseSecondOutline" value="true"/>
If you connect to a DB2 OLAP data source and it contains a scenario dimension, you must manually configure the scenario dimension so that Cognos 8 recognizes it. Otherwise, Cognos 8 will treat the scenario dimension as a regular dimension.
In the Hyperion Solutions Essbase Administration Services tool, assign a User Defined Attribute (UDA) named COGNOS_SCENARIO_DIMENSION to the scenario dimension.
DB2 OLAP Provider does not determine if a hierarchy is balanced or unbalanced. It considers all hierarchies as being unbalanced by default.
In a balanced hierarchy, each path descends to the same depth while the branches in an unbalanced hierarchy descend to different levels.
In the Hyperion Solutions Essbase Administration Services tool, create a special User Defined Attribute (UDA) named COGNOS_HIERARCHY_BALANCED in the outline of the DB2 OLAP database. The UDA is created for the root member of the corresponding dimension containing a balanced hierarchy.
Set the attribute to 1.
To improve the readability of the values reported, you can specify an alternative format string for any measure. Define a UDA for the appropriate members in the Account dimension:
COGNOS_FORMAT=format_string
The format_string value can be any one of the predefined number formats listed in the table below. You can use a preset numeric format to show values as millions (M) or thousands (K). For example, 1,801,791 can be shown as 1.8M or 1,801.8K.
The predefined format strings are as follows:
| Format Option | Sample Value | Example |
General | 1000000 | 1000000 |
| 0 | 1000000 | 1000000 |
| #,##0 | 1000000 | 1,000,000 |
| $0 | 1000000 | $1000000 |
| $#,##0 | 1000000 | $1,000,000 |
| 0% | 1000000 | 100000000% |
| %0 | 1000000 | %100000000 |
| 0E+000 | 1000000 | 1E+006 |
| 0K | 1000000 | 1000K |
| #,##0K | 1000000 | 1,000K |
| K0 | 1000000 | K1000 |
| K#,##0 | 1000000 | K1,000 |
| $0K | 1000000 | $1000K |
| $#,##0K | 1000000 | $1,000K |
| 0M | 1000000000 | 1000M |
| #,##0M | 1000000000 | 1,000M |
| M0 | 1000000000 | M1000 |
| M#,##0 | 1000000000 | M1,000 |
| $0M | 1000000000 | $1000M |
| $#,##0M | 1000000000 | $1,000M |
With the exception of the General format string, you can also preset the number of decimal places to show, using the format_string ~n, where n is the number of decimal places. For example, 1,801,791 can be shown as $1,801,791.00 using the format string $#,##0~2. If you do not want any decimal places, end the format string with ~0.
If your client application uses a different locale, you must replace the Currency ($), Thousands (,) and Decimal (.) symbols in the format_string value for the COGNOS_FORMAT UDA with the corresponding locale symbols in effect for the client application.
If you do not specify the number of decimal places to show, or if the format string does not match one of the predefined values (including locale symbols), the General format string is used by default.
You can apply a different format for each measure. The following illustrates some examples of how you can apply different formatting to different measures:
| Measure | Applied Format |
| Measures (Account dimension) | COGNOS_FORMAT=#,##0 |
| Units | COGNOS_FORMAT=#,##K |
| Costs | COGNOS_FORMAT=$#,### |
| Profits | COGNOS_FORMAT=0% |
You can choose how an attribute of a dimension is represented in a model and all associated packages. An attribute of a dimension can be represented as a separate dimension or as a property (query item) of the dimension with which it is associated. You can set this behavior when creating a package using DB2 OLAP or Essbase data sources.
In Cognos Connection, in the upper-right corner, click Launch, Cognos Administration.
On the Configuration tab, click Data Source Connections.
Click More beside the data source with which you want to create a package.
Click Create a Package.
The New Package Wizard appears.
Enter a name for the package and click Next.
Select a catalogue and click Next.
Select a cube and click Next.
Assign a language specific alias table and click Next.
Choose a language for the alias table and click Next.
Choose how to present model attributes. Select Separate the Dimensions or Properties of the dimension they are associated with. The default selection is Properties of the dimension they are associated with.
Click Finish.