Essbase Report Command GuideLines

Essbase Report Writer Optimization Techniques

DO’S

1) Use <SPARSE  command in the report script. Simply add the following line to the beginning of the report script: <Sparse

Example: Optimized Report with the Sparse Command:

<SPARSE

<Column (D1, D2)

Desc 01

D22, D23

<Row (S6, S5, S4, S3, S2, S1 )

desc S6

desc S5

desc S4

desc S3

S2

S1

!

2) Order the Sparse dimension using the “bottom up sparse outline” method

Order the dimensions in the Row Command from a bottom up Sparse (according to the outline) method to

take advantage of the natural order of the outline in which Essbase works in the most optimal fashion:

Assume the following outline and scenario:

D1 (accts dimension, 1st dense dim)

D2 (time dimension. 2nd dense dim)

S1 (1st sparse dimension)

S2 (2nd sparse dimension)

S3 (3rd sparse dimension)

S4 (4th sparse dimension)

S5 (5th sparse dimension)

S6 (6th sparse dimension)

One example of a poor report when using the <ROW command is as follows that will yield very poor report writer retrieval performance:

<Row (S2, S4, S5, S3, S6, D1 , D2)

After optimizing the report with this method, it should look as follows:

<Row (S6, S5, S4, S3, S2, S1)

* If possible, do not use dense dimensions in the <ROW command, but if you must, place them at the very

end. See the next tip for where dense dimensions should be placed in the order of the Report script.

3) Use <Columns only with Dense dimensions. In the following example, I started with the first dense dimension in the outline and when top down from there.

i.e. <COLUMN (D1 , D2)

4) Use Symmetric Reports.

See the v5 Database Administrator’s Guide Volume2, page 37-5 for an example of a Symmetric report vs. the “bad” Asymmetric report.

5) Adjust the report retrieval buffer as needed.

To correctly set the Retrieval Buffer in the Application Mgr \ DB \ Settings, you have try to set the

RETRIEVAL BUFFER to have enough memory to hold the output of the report that is request; You will only be concerned with the dense dimensions for this. To set this, use the following formula below set the RETRIEVAL BUFFER.

Multiply all the dense dimensions stored members by each other that is going to be retrieved by the

report.

Example 1 : Assume the following dense dimensions: ( Product (dense): 1706 members & time

(dense): 13 members) and they all are going to be retrieved.

1706 . 13 = 22178 . 8 bytes per cell = 177 ,424 bytes needed for the REPORT RETRIEVAL BUFFER

Example 2: If you’re only going to retrieve the dimbottom from the Time dense dimension, which

is 5 members( at the dimbottom level) and all the members from the Product (dense) dimension, then you would calculate your RETRIEVAL BUFFER as follows:

1706 .5 = 8530 . 8 bytes per cell = 68240 bytes needed or the REPORT RETRIEVAL BUFFER

* After you set the new values for the REPORT RETRIEVAL BUFFER. the application should be stopped and restarted to take to set the new values.

Don’ts

1) DO NOT use the page command at all if possible since this is very expensive in terms of performance. Rather, use the dense Dimensions in the Columns (see #3 above in the Do Sections) and the sparse dimension in the BOTTOM UP SPARSE METHOD (see #2 in the Do Section)

2) DO NOT use asymmetrical reports: they are much more expensive in terms of performance  since it needs to process each block separately, rather than in one pass.

Source:               Falcon Gee, Lead Tech Support Engineer


Delimited Extract Report Summary

Sample Requirements

Budget data must be moved from Essbase to another database.  Only base level monthly values will be transferred.  The following Essbase report script will provide a TAB delimited text file.

<PAGE(Budget)

{ SUPHEADING

SUPFEED

NOINDENTGEN

ROWREPEAT

SUPEMPTYROWS

SUPBRACKET

SUPCOMMA

TABDELIMIT

MISSINGTEXT ” ”

UNDERSCORECHAR ” ” }

<COLUMN(Year)

Lev0,Year

<ROW (Markets, Products, Measures)

Lev0,Markets

<SUPSHARE

Lev0,Products

<LINK (<DESC(“profit”) AND <LEV (measures, 0))

!

Report Delimiters

The < or {} delimiters are required for Report Writer commands. If you do not use a delimiter, Report Writer assumes that the command name is a member name.

Delimiter Use in Report Writer: Example
{} Encloses report formatting commands {SUPFORMATS}
< Precedes layout and member sorting, selection, calculation, and some formatting commands <PAGE

<PAGE, <COLUMN, and <ROW – These are the standard report layout commands.

{ REPORT FORMAT } commands appear in brackets.


Format and Extraction Command Definitions

<PAGE ( dimList )

dimList Dimension name or a comma-delimited list of dimensions.

This command specifies the dimension or dimensions to be used such that each member or combination of members of these dimensions is an attribute of all data cells on a page.  In the example, the PAGE command is being used to focus the report on the Budget category.

{ SUPHEADING }

The SUPHEADING command suppresses the display of the default heading (page header and column headers) or custom header, if defined, at the top of each page.

{ SUPFEED }

This command suppresses the automatic insertion of a page break whenever the number of lines on a page exceeds the current PAGELENGTH setting. The command FEEDON re-enables page breaks. The default page length is 66 lines unless reset with the PAGELENGTH command.

{ NOINDENTGEN }

The NOINDENTGEN command displays all row member names left-justified in the row names column without indenting members based on generation in the database outline.

{ ROWREPEAT }

The ROWREPEAT command displays all applicable row members on each row of the report even if a member describing a row is the same as in the previous row.

{ SUPEMPTYROWS }

This command suppresses the display of zero rows, for example, rows that have only 0 or missing values in the row, in the final report. The report will contain only rows which have at least one data value which is neither #MISSING nor zero.

{ SUPBRACKETS }

This command suppresses the display of parenthesis around negative numbers. The negative sign,(-) indicates negative numbers in the report.

{ SUPCOMMAS }

The SUPCOMMAS command suppresses the display of commas in numbers greater than 999.

{ TABDELIMIT }

The TABDELIMIT command places tabs rather than spaces between columns.

{MISSINGTEXT [ “text” ] }

text Optional text to use for missing values.

This command replaces the word #MISSING with text when a missing data value is generated on a line in the report. If you do not specify text, the default #MISSING is restored.

{ UNDERSCORECHAR “char”}

char Single character, enclosed by quotation marks, that displays in place of underscore.

The UNDERSCORECHAR command replaces the _ (underscore) character in a member name with another character.

<COLUMN ( dimList)

dimList Dimension name or a comma-delimited list of dimensions

This command defines the dimensions that are displayed as column members in the final report. Column members are displayed above the data columns in the report.

<ROW ( dimList )

dimList Dimension name or a comma-delimited list of dimensions.

This command determines the row dimensions for a report whose member names appear in the data rows of the report. The member(s) in the command determine which dimensions from the Database Outline are displayed in the rows.  Each row in the example will contain member names from the Markets, Products and Measures dimensions.

The remaining commands will further define the level of detail to be returned for each ROW in the report.

<SUPSHARE

The SUPSHARE command suppresses the display of duplicate shared members when you use generation or level names to extract data for your report.  This command must precede commands that may further define the detail returned for a dimension.

LEV level,dimName

This command is an Essbase function that specifies the level of information returned.  The example, [LEV0,Products] returns the base level row members for the Products dimension.  There are shared members in this dimension that would be duplicate values in an extract.  The <SUPSHARE command must precede this command line.

<LINK (extractionCommand [operator extractionCommand])

extractionCommand Any of the following extraction commands or another AND/OR expression:
<ALLINSAMEDIM (member)
<ALLSIBLINGS (member)
<ANCESTORS (member)
<CHILDREN (member)
<DESCENDANTS (member)
<DIMBOTTOM (member)
<DIMTOP (member)
<IANCESTORS (member)
<ICHILDREN (member)
<IDESCENDANT (member)
<IPARENT (member)
<MATCH (Dimension, match_string)
<MEMBER (member)
<OFSAMEGEN (member)
<ONSAMELEVELAS (member)
<PARENT (member)
<UDA (Dimension, UDA_name)
Operator Any of the following Boolean operators:

  • Use the AND operator when all conditions must be met.
  • Use the OR operator when either one condition or another must be met.
  • Use the NOT operator to choose the inverse of the selected condition.

This command produces member selections using Boolean operators to refine the search. NOT can only be associated with an extraction command, and does not apply to the entire expression. You must use NOT in conjunction with either the AND or OR operators.

The MEMBER extraction command is only used within a LINK expression; you can use the MEMBER selection to select a single member. Do not use the MEMBER command outside of a LINK expression.

<DESC (member)

The <DESC extraction command or function is the abbreviation for DESCENDANTS.  In the example, the Measures dimension has three children (Profit, Inventory, Ratios).  Only the descendants of Profit will be needed for this extract.  Even though the documentation does not specify quotation marks around the member name, it did not produce the desired results without them.

<LEV ( dimName, level )

The syntax of the <LEV extraction command, or function, is different within the <LINK selection command.

The <LINK (<DESC(“profit”) AND <LEV (measures, 0)) command returns only the base level row members of the Measures dimension for only the descendants of the member Profit.

Syntax Guidelines

  • Separate commands with at least one space, tab, or new line. Report processing is not affected by extra blank lines, spaces, or tabs.
  • Enter commands in either upper or lower case. Commands are not case sensitive. If the Database Outline is case-sensitive, then the member names used in the report script must match the outline.
  • To start report processing, enter the ! report output command (exclamation point or “bang”), or one or more consecutive numeric values. You can place one or more report scripts, each terminated by its own ! command, in the same report file.
  • You can group more than one format command within a single set of curly braces. For example, these formats are synonyms:

{UDATA SKIP}

{UDATA} {SKIP}

  • You can use command abbreviations to speed up command entry. For example, instead of <SORTMBRNAMES, you can enter <SORTMBRN. Use only the abbreviations documented in the online Technical Reference. If you use other abbreviations, the Report Writer may execute the wrong command.
  • Enclose member names that contain spaces or the member name “Default” in double quotes; for example, “Cost of Goods Sold” “Default”.
  • If a formatting command is preceded by three or more of the characters “=,” “-,” and “_,” the Report Extractor assumes that the characters are extraneous underline characters and ignores them. For example, ==={SKIP 1}
  • Use // (double slash) to indicate a comment. Everything on the line following a comment is ignored by the Report Writer. Each line of a comment must start with a double slash.

Source: Bill Donovan, Application Consultant


Using Report Scripts for Data Exporting

You can use report scripts to export Hyperion Essbase data to other programs in text format. Report Writer enables you to create text files that meet the import format specifications of most other programs.

Before you can import data into some programs, you must separate, or delimit, the data with specific characters.

If you plan to import Hyperion Essbase data into a program that requires special delimiters, use the MASK command. For the syntax and usage of Report Writer commands, see the online Technical Reference in the DOCS directory.

Note: You cannot export data generated by Dynamic Calc members. Because attributes are Dynamic Calc members, you cannot export data generated by attributes.

When you export data to a program that uses a two-dimensional, fixed-field format, you do not need to specify page or column dimensions. To create a two-dimensional report, you can specify every dimension as a row dimension. Use the ROWREPEAT command to add the name of each member specified to each row (rather than the default, nested style). The following script example and report illustrate this situation for a five-dimensional database:

<ROW (Year, Measures, Product, Market, Scenario)

{ROWREPEAT}

<ICHILDREN Year

Sales

<ICHILDREN “400”

East

Budget

!

This example produces the following report:

Qtr1          Sales        400-10       East      Budget      900

Qtr1          Sales        400-20       East      Budget    1,100

Qtr1          Sales        400-30       East      Budget      800

Qtr1          Sales          400        East      Budget    2,800

Qtr2          Sales        400-10       East      Budget    1,100

Qtr2          Sales        400-20       East      Budget    1,200

Qtr2          Sales        400-30       East      Budget      900

Qtr2          Sales          400        East      Budget    3,200

Qtr3          Sales        400-10       East      Budget    1,200

Qtr3          Sales        400-20       East      Budget    1,100

Qtr3          Sales        400-30       East      Budget      900

Qtr3          Sales          400        East      Budget    3,200

Qtr4          Sales        400-10       East      Budget    1,000

Qtr4          Sales        400-20       East      Budget    1,200

Qtr4          Sales        400-30       East      Budget      600

Qtr4          Sales          400        East      Budget    2,800

Year        Sales        400-10       East      Budget    4,200

Year        Sales        400-20       East      Budget    4,600

Year        Sales        400-30       East      Budget    3,200

Year        Sales          400        East      Budget   12,000

If you want to create a two-dimensional report that contains only bottom-level (level 0) data, use CHILDREN or DIMBOTTOM to select level 0 members.

  • To list only level 0 data for specific members, use the CHILDREN command with the level 1 member as a parameter above the data you want to print.
  • To list only level 0 data for a given member (including a dimension), use the DIMBOTTOM command as a parameter with the dimension name that contains the data you want to print.

For example, the following script uses the CHILDREN command to select the children of Qtr1, which is a level 1 member, and the DIMBOTTOM command to select all level 0 data in the Product dimension.

<ROW (Year, Measures, Product, Market, Scenario)

{ROWREPEAT}

{DECIMAL 2}

<CHILDREN Qtr1

Sales

<DIMBOTTOM Product

East

Budget

!

This example produces the following report:

Jan      Sales    100-10     East       Budget        1,600.00

Jan      Sales    100-20     East       Budget          400.00

Jan      Sales    100-30     East       Budget          200.00

Jan      Sales    200-10     East       Budget          300.00

Jan      Sales    200-20     East       Budget          200.00

Jan      Sales    200-30     East       Budget        #Missing

Jan      Sales    200-40     East       Budget          700.00

Jan      Sales    300-10     East       Budget        #Missing

Jan      Sales    300-20     East       Budget          400.00

Jan      Sales    300-30     East       Budget          300.00

Jan      Sales    400-10     East       Budget          300.00

Jan      Sales    400-20     East       Budget          400.00

Jan      Sales    400-30     East       Budget          200.00

Feb      Sales    100-10     East       Budget        1,400.00

Feb      Sales    100-20     East       Budget          300.00

Feb      Sales    100-30     East       Budget          300.00

Feb      Sales    200-10     East       Budget          400.00

Feb      Sales    200-20     East       Budget          200.00

Feb      Sales    200-30     East       Budget        #Missing

Feb      Sales    200-40     East       Budget          700.00

Feb      Sales    300-10     East       Budget        #Missing

Feb      Sales    300-20     East       Budget          400.00

Feb      Sales    300-30     East       Budget          300.00

Feb      Sales    400-10     East       Budget          300.00

Feb      Sales    400-20     East       Budget          300.00

Feb      Sales    400-30     East       Budget          300.00

Mar      Sales    100-10     East       Budget        1,600.00

Mar      Sales    100-20     East       Budget          300.00

Mar      Sales    100-30     East       Budget          400.00

Mar      Sales    200-10     East       Budget          400.00

Mar      Sales    200-20     East       Budget          200.00

Mar      Sales    200-30     East       Budget        #Missing

Mar      Sales    200-40     East       Budget          600.00

Mar      Sales    300-10     East       Budget        #Missing

Mar      Sales    300-20     East       Budget          400.00

Mar      Sales    300-30     East       Budget          300.00

Mar      Sales    400-10     East       Budget          300.00

Mar      Sales    400-20     East       Budget          400.00

Mar      Sales    400-30     East       Budget          300.00

Source:  Database Administrator Guide


Sample: Formatting for Data Export

This sample creates a report with a member name in each column. This format is required when you export Hyperion Essbase data to another product, such as an SQL database, with a flat file.

New York        Stereo           Sales              1000.0    950.0

New York        Stereo           Cost of Goods Sold  580.0    551.0

New York        Stereo           Margin              420.0    399.0

New York        Stereo           Marketing            80.0     80.0

New York        Stereo           Payroll             340.0    340.0

New York        Stereo           Miscellaneous         0.0      0.0

New York        Stereo           Total Expenses      420.0    420.0

New York        Stereo           Profit                0.0    -21.0

New York        Stereo           Profit %              0.0     -2.2

New York        Stereo           Margin %             42.0     42.0

New York        Compact Disc     Sales              1200.0   1150.0

New York        Compact Disc     Cost of Goods Sold  456.0    437.0

New York        Compact Disc     Margin              744.0    713.0

New York        Compact Disc     Marketing            95.0     95.0

New York        Compact Disc     Payroll             310.0    310.0

New York        Compact Disc     Miscellaneous         0.0      0.0

New York        Compact Disc     Total Expenses      405.0    405.0

New York        Compact Disc     Profit              339.0    308.0

New York        Compact Disc     Margin %             62.0     62.0

Use the following script to create the Sample:

<PAGE(Scenario)

<COLUMN(Year)

<ROW (Market, Product, Accounts)

<CHILDREN East

<DESCENDANTS Product

{ DECIMAL 1

WIDTH 9

SUPBRACKETS

SUPCOMMA

MISSINGTEXT ” ”

UNDERSCORECHAR ” ”

SUPHEADING

NOINDENTGEN

SUPFEED

ROWREPEAT

Budget

Jan Feb

<DESCENDANTS Accounts

!

The ROWREPEAT command produces rows of data that have the member names repeat for each row dimension.

The SUPFEED command suppresses page feeds. A page feed automatically occurs when the report output reaches the default page length of 66 rows, unless you enter the PAGELENGTH command to change this setting. When a large flat file is created, you can use this command to prevent page breaks (blank rows) from appearing in the report every time output reaches a logical page length.