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.
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:
Attribute dimensions can be used as filters in a query whether it is based on level hierarchies or parent/child hierarchies. In order to use the not-equal operator with an attribute dimension, you must use a WHERE TOTAL phrase rather than a WHERE phrase.
With a level hierarchy, an attribute dimension can also be used as a BY field, as long as the field to which it is linked is also a BY field in the request.
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.
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:
Allows the use of ALIAS names in a request.
Does not allow the use of ALIAS names in a request. OFF is the default value.
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.
How to: |
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.
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.
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
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.
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:
Is the ALIAS table name to be used.
Sets the alias table back to the current default for the outline in Essbase.
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.
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.
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:
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.
Is the Master File name for one application.database combination.
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.
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'
How to: |
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.
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:
Does not allow the use of SUM on non-aggregated fields. ON is the default value.
Allows the use of SUM on non-aggregated fields.
Is the Master File name for one application.database combination.
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
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, $
You must turn set AGGREGATE NOOP to OFF before you create the synonym.
ENGINE ESSBASE SET AGGREGATE NOOP {ON|OFF}
where:
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.
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.
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.
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:
Excludes shared members in a report.
Includes shared members in a report. OFF is the default value.
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.
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.
ENGINE ESSBASE SET SUPZEROS [ON|OFF]
where:
Suppresses an entire row that contains 0 values.
Does not suppress zero values. OFF is the default value.
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
How to: |
In Essbase, empty cells are known as missing or #MISSING data. You can suppress missing data during reporting using the SUPMISSING setting.
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:
Suppresses an entire row that contains #MISSING data.
Does not suppress rows with #MISSING data. OFF is the default value.
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
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.
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:
Suppresses an entire row that contains either zero values, #MISSING data, or a combination of the two.
Does not suppress rows that contain either zero values or #MISSING data. OFF is the default value.
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
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.
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
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.
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:
Uses the sparse data extraction method when a high proportion of the reported data rows are #MISSING.
Does not use the sparse data extraction method. OFF is the default value.
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.
ENGINE ESSBASE SET DEFAULT_CONNECTION connection
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
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:
FOC1671, Command out of sequence.
The following SET DEFAULT_CONNECTION command selects the database server named SAMPLENAME as the default database server:
ENGINE ESSBASE SET DEFAULT_CONNECTION SAMPLENAME
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.
ENGINE ESSBASE SET UNICODE {OFF|ON}
where:
Does not support Unicode mode applications when the server is not configured for Unicode. OFF is the default value.
Supports Unicode mode applications when the server is not configured for Unicode.
iWay Software |