Customizing the Essbase Environment

In this section:

 

The Adapter for Essbase provides several parameters for customizing the environment and optimizing performance. The following topics provide an overview of customization options.


Top of page

x
Using the MDX Adapter

How to:

Essbase Version 7 makes an interface available that communicates with the Essbase engine using MDX instead of the Essbase proprietary query language. If you issue the SET MDX ON command in any supported profile, the Adapter for Essbase will submit queries to Essbase using MDX.

The MDX version of the Adapter for Essbase provides the following capabilities:



x
Syntax: How to Enable the MDX Adapter for Essbase
ENGINE ESSBASE SET MDX {ON|OFF}

where:

ON

Enables the MDX adapter.

OFF

Uses the native adapter for Essbase. OFF is the default value.


Top of page

x
Specifying ALIAS Names

How to:

In Essbase, you can assign more than one name to a member or a shared member. For example, in the Sample.Basic database, the PRODUCT dimension contains members that can be identified by both product codes, such as 200, or by more descriptive names, such as COLA. By default, the output values for members specified in a request are the member values. The server allows you to take advantage of the more descriptive member names in the Essbase outline when formulating a request.



x
Syntax: How to Specify ALIAS Names in a Request
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET USEALIASNAME {ON|OFF} [FOR synonym]

where:

ON

Allows the use of ALIAS names in a request.

OFF

Does not allow the use of ALIAS names in a request. OFF is the default value.

synonym

Is the Master File name for one application.database combination.

Note: If the SET USEALIASNAME command is used without FOR synonym, the setting applies to all Master Files. If the synonym option is used, the setting applies only to that Master File. You can issue multiple commands to control the use of the ALIAS name at the Master File level.

In an Essbase script, the reporting keyword OUTALTNAMES is equivalent to ALIAS.


Top of page

x
Specifying ALIAS and Member Names in One Request

How to:



x
Procedure: How to Specify ALIAS and Member Names in a Request

When creating the synonym, check the Make alias fields check box to create a Master File that contains an alias field for each field in the generation view. For information on creating synonyms, see Creating Synonyms.



Example: Generated Master File basic.mas
FILENAME=SAMPLE, SUFFIX=ESSBASE, $
SEGMENT=BASIC, SEGTYPE=S0, $
.
.
.
 $  DIMENSION: Product
 DIMENSION=Product,CAPTION=Product, $
  HIERARCHY=Product,CAPTION=Product Levels,
      HRY_DIMENSION=Product,HRY_STRUCTURE=S, $
    FIELDNAME=PRODUCT, ALIAS='Lev2,Product', USAGE=A7, ACTUAL=A7,
      WITHIN='*Product', $
    FIELDNAME=FAMILY, ALIAS=Family, USAGE=A4, ACTUAL=A4,
      WITHIN=PRODUCT, $
    FIELDNAME=FAMILY_ALIAS, ALIAS=Family_ALIAS, USAGE=A11, ACTUAL=A11,
      REFERENCE=FAMILY, PROPERTY=CAPTION,  $
    FIELDNAME=SKU, ALIAS=SKU, USAGE=A6, ACTUAL=A6,
      WITHIN=FAMILY, $
    FIELDNAME=SKU_ALIAS, ALIAS=SKU_ALIAS, USAGE=A18, ACTUAL=A18,
      REFERENCE=SKU, PROPERTY=CAPTION,  $

Note: The SET USEALIASNAME ON/OFF command is ignored when the Master File is created using this option.

For information about the SET USEALIASNAME command, see Specifying ALIAS Names.



Example: Using ALIAS and Member Names in an Essbase Request
TABLE FILE BASIC
PRINT SALES PROFIT_K
BY SKU
BY SKU_ALIAS
WHERE SKU_ALIAS EQ 'Cola'
END

The output is:

SKU      SKU_ALIAS   Sales      Profit
---      ---------   -------    --------
100-10   Cola        62,824.00  22,777.00

Top of page

x
Specifying ALIAS Tables

How to:

In Essbase, aliases are generally stored in one or more tables as part of the database outline. Once the SET command is active, screening conditions must use the ALIAS value, not the member value.

If multiple ALIAS tables exist for an outline, you can use the SET ALIASTABLE command to specify which ALIAS table to use for a query.



x
Syntax: How to Specify an ALIAS Table
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET ALIASTABLE {aliastablename|RESET} FOR synonym

where:

aliastablename

Is the ALIAS table name to be used.

RESET

Sets the alias table back to the current default for the outline in Essbase.

synonym

Is the Master File name for one application.database combination.

Note: This SET ALIASTABLE command is used in conjunction with the SET USEALIASNAME command. If that command is not issued, the SET ALIASTABLE command is ignored. For more information, see Specifying ALIAS Names.


Top of page

x
Setting the Maximum Number of Rows Returned

How to:

The default number of rows that can be returned from an Essbase cube using the server is 10,000. This can be controlled by the MAXROWS setting, which can be set in any supported server profile.

Note: The MAXROWS setting does not apply to the MDX Adapter, which has no default limit and no maximum number of rows returned.



x
Syntax: How to Set the Maximum Number of Rows Returned
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

To limit or extend the number of rows returned, use the syntax

ENGINE ESSBASE SET MAXROWS n [FOR synonym]

where:

n

Is a valid number that either limits or extends the number of rows to be returned. The default number of rows returned without this setting is 10,000. The maximum number for this setting is 999999999.

synonym

Is the Master File name for one application.database combination.


Top of page

x
Time Series Reporting

By using Time Series and Accounts tags within your Essbase outline, you can tell Essbase how to calculate your accounts data. When you tag a dimension as Time, Essbase knows that this is the dimension on which to base the time periods for the Accounts tags.

The server supports all eight levels of period-to-date reporting within Essbase. See the Hyperion Essbase Database Administrator's Guide for further information.

In order to retrieve values from members in an Accounts dimension using time-series reporting, you must refer to at least one member of the Time dimension using the WHERE clause.



Example: Using Time Series Reporting
TABLE FILE BASIC
PRINT PRODUCT Q_T_D PROFIT_K AS 'Profit'
WHERE Q_T_D EQ 'Mar' 
END

Where PROFIT_K is a member of the Accounts dimension, PRODUCT is a member of a non-Accounts dimension, and Q_T_D is a member of the Time dimension. The above request returns the following row:

Product Q-T-D(Mar)            24703.00

Note: All results reference the Dynamic Time series member with dashes between letters.

The equivalent request using SQL is:

SELECT Q_T_D, PRODUCT, PROFIT_K FROM BASIC WHERE Q_T_D = 'Mar'

Top of page

x
Summing on Non-Aggregated Fields

How to:

x

The Essbase outline can contain two-pass calculated members, as well as members with different consolidation properties. Any two-pass calculated members that are found in the accounts (or Scenario) dimension members with the (-), (\), (*), and (%) consolidation properties in the Essbase outline, have the following attributes in the associated Access File:

MEMBER=membername, AGGREGATE=NO, $

By default (when Aggregation is ON), you cannot use the SUM command on two-pass calculated members or on members with the (-), (\),(*), and (%) consolidation properties. If a request contains a SUM action that uses one of these members, the following error message is displayed:

(FOC43241) Aggregation is requested for non aggregatable member(s)

However, when Aggregation is turned OFF, the server allows SUM to be used on two-pass calculated members and members with the (-), (\),(*), and (%) consolidation properties.



x
Syntax: How to Turn Aggregation ON/OFF For Non-Aggregated Fields
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET RESTRICTSUM [ON|OFF] FOR synonym

where:

ON

Does not allow the use of SUM on non-aggregated fields. ON is the default value.

OFF

Allows the use of SUM on non-aggregated fields.

synonym

Is the Master File name for one application.database combination.



Example: Using RESTRICTSUM on Non-Aggregated Fields

Using this Access File

SEGNAME=BASIC, SERVER=unxsol26, DBNAME=Basic, APPLNAME=Sample, $
TIMEDIM=Year, $
MEASURE=Measures, $
MEMBER=COGS, AGGREGATE=NO, $
MEMBER=TOTAL_EXPENSES, AGGREGATE=NO, $
MEMBER=PROFIT_%, AGGREGATE=NO, $
MEMBER=PROFIT_PER_OUNCE, AGGREGATE=NO, $

issue the following request:

TABLE FILE BASIC
SUM COGS
BY QUARTER
END

If RESTRICTSUM ON (default) is set in the profile, the following message is displayed and no output is generated:

(FOC43241) Aggregation is requested for non aggregatable member(s)

If you set RESTRICTSUM OFF in the profile, SUM is permitted on non-aggregated fields, producing this output:

QUARTER                      COGS
-------                 --------- 
Qtr1                    42,877.00
Qtr2                    45,362.00
Qtr3                    47,343.00
Qtr4                    43,754.00 

Top of page

x
Preventing Aggregation of Non-Consolidating Members

How to:

Member consolidation properties determine how children roll up into their parents in the Essbase outline. The members with the (~) as a consolidation property in the Essbase outline, are not rolled up in the database. The SET AGGREGATE NOOP (No Operation) setting only affects those Essbase members in the Accounts dimension with the (~) consolidation property. If AGGREGATE NOOP is set to OFF, SUM is not permitted on Essbase members with the (~) consolidation property and those members appear in the Access File as:

MEMBER=ADDITIONS, AGGREGATE=NO, $


x
Syntax: How to Prevent Aggregation of Non-Consolidating Members

You must turn set AGGREGATE NOOP to OFF before you create the synonym.

ENGINE ESSBASE SET AGGREGATE NOOP {ON|OFF}

where:

ON

Allows the use of SUM on NOOP Essbase members. With this setting, Essbase members are not added to the Access File. ON is the default value.

OFF

Does not allow the use of SUM on NOOP Essbase members. Members with the (~) consolidation property are added to the Access File.

Note: Any member of the Accounts dimension in the Essbase outline tagged with (-), (/), (*), or (%) as a consolidation property automatically appears in the Access File with the AGGREGATE=NO setting.


Top of page

x
Suppressing Shared Members

How to:

Shared Members in Essbase store pointers to data that is stored in the real member. Therefore, although the data is shared between the two members, it is only stored one time. You can exclude shared members from reports using the SUPSHARE setting.



x
Syntax: How to Suppress Shared Members in Essbase
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET SUPSHARE [ON|OFF]

where:

ON

Excludes shared members in a report.

OFF

Includes shared members in a report. OFF is the default value.



Example: Suppressing Shared Members

The following request and output illustrates the affect of turning SUPSHARE OFF and ON.

TABLE FILE SAMPLE
PRINT SALES BY FAMILY BY SKU
AND COLUMN-TOTAL
WHERE FAMILY EQ 'Diet' OR '200'
END

With SUPSHARE OFF, the output is:

FAMILY  SKU         SALES
------  ------  ---------
200     200-10  41,537.00
        200-20  38,240.00
        200-30  17,559.00
        200-40  11,750.00
Diet    100-20  30,469.00
        200-20  38,240.00
        300-30  36,969.00
==========================
TOTAL          214,764.00

With SUPSHARE ON, the output is:

FAMILY  SKU         SALES
------  ------  ---------
200     200-10  41,537.00
        200-20  38,240.00
        200-30  17,559.00
        200-40  11,750.00
Diet    100-20  30,469.00
        300-30  36,969.00
==========================
TOTAL          176,524.00

Notice that member 200-20 is displayed twice in the first report, but only once in the second report. Turning SUPSHARE ON prevents a shared member from being duplicated in a report request.


Top of page

x
Suppressing Zero Values

How to:

Essbase stores zero values that you can exclude from reports using the SUPZEROS setting. This setting applies to an entire row. If one member in a row has a value of zero, and the other values in the same row have values other than zero, the zero value will not be suppressed.



x
Syntax: How to Suppress Rows With Zero Values
x
ENGINE ESSBASE SET SUPZEROS [ON|OFF]

where:

ON

Suppresses an entire row that contains 0 values.

OFF

Does not suppress zero values. OFF is the default value.



Example: Suppressing Zero Values

The following output illustrates the affect of turning SUPZEROS OFF and ON.

With SUPZEROS OFF, the output is:

STATE           SALES      PROFIT
---------   ---------   ---------
Colorado    38,240.00   42,877.00 
Louisiana   0.00        0.00
Nevada      29,156.00   47,343.00

With SUPZEROS ON, the output is:

STATE           SALES     PROFIT
--------    ---------  ---------
Colorado    38,240.00  42,877.00
Nevada      29,156.00  47,343.00

Top of page

x
Suppressing Missing Data

How to:

In Essbase, empty cells are known as missing or #MISSING data. You can suppress missing data during reporting using the SUPMISSING setting.



x
Syntax: How to Suppress Rows With Missing Data
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET SUPMISSING [ON|OFF]

where:

ON

Suppresses an entire row that contains #MISSING data.

OFF

Does not suppress rows with #MISSING data. OFF is the default value.



Example: Suppressing Missing Data

The following output illustrates the affect of turning SUPMISSING OFF and ON.

With SUPMISSING OFF, the output is:

STATE           SALES     PROFIT
---------   ---------  ---------
Colorado    38,240.00  42,877.00
Louisiana   #MISSING   #MISSING
Nevada      29,156.00  47,343.00

With SUPMISSING ON, the output is:

STATE           SALES     PROFIT
--------    ---------  ---------
Colorado    38,240.00  42,877.00
Nevada      29,156.00  47,343.00

Top of page

x
Suppressing Zero Values and Missing Data

How to:

You can use the SUPEMPTY setting to suppress an entire row that contains either zero values, missing (#MISSING) data, or a combination of the two.



x
Syntax: How to Suppress Rows With Zero Values or Missing Data
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET SUPEMPTY [ON|OFF]

where:

ON

Suppresses an entire row that contains either zero values, #MISSING data, or a combination of the two.

OFF

Does not suppress rows that contain either zero values or #MISSING data. OFF is the default value.



Example: Suppressing Rows With Zero Values or Missing Data

The following output illustrates the affect of turning SUPEMPTY OFF and ON.

With SUPEMPTY OFF, the output is:

STATE           SALES     PROFIT
---------   ---------  ---------
Colorado    38,240.00  42,877.00
Louisiana   #MISSING   #MISSING
Nevada      0.00       0.00

With SUPEMPTY ON, the output is:

STATE           SALES     PROFIT
--------    ---------  ---------
Colorado    38,240.00  42,877.00

Top of page

x
Substitution Variables

Substitution variables act as global placeholders for information that changes regularly. Each substitution variable configured in Essbase is assigned a value. You can change the value at any time, thus limiting the number of manual changes required in your scripts when you are running reports.

When you reference an Essbase substitution variable in a TABLE command, the substitution variable must be preceded with an up arrow (^) and enclosed in single quotation marks.



Example: Using a Substitution Variable in a Request

In this example, CurrMonth is the name of the substitution variable that was set in Essbase. The ^ and quotation marks are required.

TABLE FILE BASIC
PRINT Product
BY
PRODUCT
WHERE
MONTH EQ '^CurrMonth'
END

Top of page

x
Using the SPARSE Data Extraction Method

How to:

The SET SPARSE ON command tells Hyperion Essbase to use the sparse data extraction method, which optimizes performance when a high proportion of the reported data rows are #MISSING. This data extraction method is different from the regular method. Hyperion Essbase cannot use the sparse data retrieval optimization method on Dynamic Calc or Dynamic Calc And Store members.



x
Syntax: How to Use the SPARSE Data Extraction Method
x

Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.

ENGINE ESSBASE SET SPARSE {ON|OFF}

where:

ON

Uses the sparse data extraction method when a high proportion of the reported data rows are #MISSING.

OFF

Does not use the sparse data extraction method. OFF is the default value.


Top of page

x
Setting a Default Connection

How to:

Once connections have been defined in the edasprof.prf server global profile, the connection named in the first SET CONNECTION_ATTRIBUTES command serves as the default connection, which is used if the Access File does not specify connection name or server. You can override this default using the SET DEFAULT_CONNECTION command.



x
Syntax: How to Change the Default Connection
ENGINE ESSBASE SET DEFAULT_CONNECTION connection

where:

ESSBASE

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

connection

Is the connection defined in a previously issued SET CONNECTION_ATTRIBUTES command. If this name was not previously declared, the following message is issued:

FOC1671, Command out of sequence

Note:



Example: Selecting the Default Connection

The following SET DEFAULT_CONNECTION command selects the database server named SAMPLENAME as the default database server:

ENGINE ESSBASE SET DEFAULT_CONNECTION SAMPLENAME

Top of page

x
Supporting Unicode Mode Applications

How to:

An Essbase application can be set to work in Unicode or non-Unicode mode. If the Reporting Server is configured for Unicode, no setting is required to use a cube set for Unicode mode in a Unicode mode application. However, if the server is not configured for Unicode but your Essbase cube is configured for Unicode, you must issue the ENGINE ESSBASE SET UNICODE ON command.



x
Syntax: How to Configure the Adapter for Unicode Mode Applications
ENGINE ESSBASE SET UNICODE {OFF|ON}

where:

OFF

Does not support Unicode mode applications when the server is not configured for Unicode. OFF is the default value.

ON

Supports Unicode mode applications when the server is not configured for Unicode.



x
Syntax: How to Determine Whether Unicode Mode Applications Are Supported
ENGINE ESSBASE SET ?

When you issue this command, the following message indicates whether Unicode mode is on for your Essbase cube:

(FOC43249) Essbase Unicode Option : on

iWay Software