Essbase: The Meta View

By Harry Lotrowski

When the Essbase adapter creates a synonym, several things take place. The dimensions are evaluated and abstracted into field names that the Essbase API and query language can understand. "Within" attributes are assigned to the field for easy use in the OLAP Reporting Panel. In addition, parent-child functionality is generated within the synonym file for use by the Financial Reporting Platform.

The final result of the synonym process is:

	A set of dimensions, defined as alphanumeric values carrying the within and parent attributes
	One set of measures, defined as decimal
	User-defined attributes, which are optional

The field names generated in the process are not at all user friendly. Unfortunately, Essbase does not contain the ability to store meaningful names to the generations of a dimension. Once the synonym has been generated, it may be edited within the synonym editor to assign meaningful names. However, if the structure of the cube (the addition or deletion of dimensions or generations) changes, you would need to recreate the process.

The cube depicted in Figure 1 is rather simple. We will review various approaches to the synonyms using this example.

Figure 1

The WebFOCUS Painter in this case will read the unmodified synonym and create the Painter window accordingly with the method used in its creation, as shown in Figure 2. This method used the NO converted dimension approach.

Figure 2

One of the secrets to successful reporting from an Essbase cube lies in ensuring the synonym is set up correctly for the report you are writing. Since all statistical data is available at every level of every hierarchy, you have the freedom to create a synonym that fits your exact need.

By default the adapter will search for the dimension with the ACCOUNT attribute assigned to it. It will then create the synonym enumerating the individual accounts. This approach is OK if you are painting a report with "over" (non-FRP) or as a heading type of report. Your measures would appear as follows:

CREATE SYNONYM MS_DEFAULT 
FOR nav.smallone DBMS ESSBASE AT servername
END
$ DIMENSION: Accoun1
SEGMENT=ACCOUN1 ,SEGTYPE=U, PARENT=SMALLONE, $
$Accounts
FIELD=0100,     ALIAS=0100,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='0100', $
FIELD=1000,     ALIAS=1000,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='1000', $
FIELD=5000,     ALIAS=5000,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='5000', $

A common approach is to break out the scenario as the measure. This allows for easy variance-type reporting.

ENGINE ESSBASE SET MEASURE 
Scenario FOR MS_SCENARIO
CREATE SYNONYM MS_SCENARIO FOR 
nav.smallone DBMS ESSBASE AT servername
END
$ DIMENSION: Scenar1
SEGMENT=SCENAR1 ,SEGTYPE=U, PARENT=SMALLONE, $
$Scenario
FIELD=ACTUAL,   ALIAS=Actual,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='Actual', $
FIELD=BUDGET,   ALIAS=Budget,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='Budget', $

This method allows you to simply set up a data value field denoting the statistical values for the cube. No dimensions are set up as the measure. This allows you to use all hierarchies as WHERE or BY fields. This synonym is generated using the following code.

ENGINE ESSBASE SET MEASURE NONE 
FOR MS_NONE
CREATE SYNONYM MS_NONE FOR nav.smallone 
DBMS ESSBASE AT servername
END
$ DIMENSION: DATA
SEGMENT=DATA ,SEGTYPE=U, PARENT=SMALLONE, $
FIELD=DATA_VALUE, ALIAS=DATA_VALUE, 
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE=DATA_VALUE $

Another method would be to define your time values as your measure. For example, say that you have a report that uses all sorts of time frames going across the page, but because of its nature, an ACROSS will not work easily. In this case, you might want to define your time dimension as the measure and then simply sum those field names.

ENGINE ESSBASE SET MEASURE Dates 
FOR MS_DATES
CREATE SYNONYM MS_DATES FOR nav.smallone 
DBMS ESSBASE AT servername
END
$ DIMENSION: Dates
SEGMENT=DATES ,SEGTYPE=U, PARENT=SMALLONE, $
$Dates
FIELD=FY2002,   ALIAS=FY2002,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='FY2002', $
FIELD=FQ1,      ALIAS=FQ1,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='FQ1', $
FIELD=FP01,     ALIAS=FP01,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='FP01', $
FIELD=CY2002,   ALIAS=CY2002,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='CY2002', $
FIELD=CQ1,      ALIAS=CQ1, 
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='CQ1', $
FIELD=CP01,     ALIAS=CP01,
USAGE=D20.2,ACTUAL=D8,MISSING=ON, TITLE='CP01', $

Taking this idea to its illogical conclusion, you could even break out your geography as separate fields. This might be useful when integrating with a package like ESRI or MS MAPPOINT.

ENGINE ESSBASE SET MEASURE 
Geography FOR MS_GEOG
CREATE SYNONYM MS_GEOG FOR nav.smallone 
DBMS ESSBASE AT servername
END
$ DIMENSION: Geogra1
SEGMENT=GEOGRA1 ,SEGTYPE=U, PARENT=SMALLONE, $
$Geography
FIELD=NORTH_AMERICA,  ALIAS='North America',
USAGE=D20.2,ACTUAL=D8,MISSING=ON, 
TITLE='North America', $
FIELD=UNITED_STATES,  ALIAS='United States',
USAGE=D20.2,ACTUAL=D8,MISSING=ON, 
TITLE='United States', $
FIELD=NEW_YORK,       ALIAS='New York',
USAGE=D20.2,ACTUAL=D8,MISSING=ON, 
TITLE='New York', $
FIELD=NEW_YORK_CITY,  ALIAS='New York City',
USAGE=D20.2,ACTUAL=D8,MISSING=ON, 
TITLE='New York City', $
FIELD=NEW_YORK_COUNTY,  ALIAS='New York County',
USAGE=D20.2,ACTUAL=D8,MISSING=ON, 
TITLE='New York Country', $

This flexibility of how the metadata can be created, while at first confusing, can give you the ability to create those tough reports without the complexity that you may come across sometimes. In addition, the user-centric view is still available with any of these synonyms if required (using the IBIESSSYN utility). Finally, you still may want to edit the synonyms you create to create more user friendly field names.