Understand Dimension Worksheets

Application workbooks contain one dimension worksheet for each of the dimensions listed in the Essbase.Cube worksheet. The name of each dimension worksheet is Dim.dimname; for example, the Year dimension worksheet is called Dim.Year. Dimension names can contain up to 1024 characters, but long dimension names (longer than 31 characters, including "Dim.") are truncated in the dimension sheet name.

Dimension worksheets use load rule syntax. For example, an X in the Storage column means that the data value is not stored.

The following image shows a dimension worksheet in a sample application workbook.


Image of a dimension worksheet in an application workbook.

Table A-7 Fields and Valid Values in Dimension Worksheets

Property or Field Valid Values Description

Dimension Name

The name of the dimension.

Do not change the dimension name in this field.

Any dimension or attribute dimension in the outline.

Defined on the Essbase.Cube worksheet.

Use no more than 1024 characters when naming dimensions, members, or aliases.

The following special characters are not allowed: @, ., ,, !, {, }, [, ]. /, \, *.

File Name

A valid string.

The file name cannot be longer than thirty characters.

The build process creates a data file with a .txt extension in the cloud service for every data worksheet in the application workbook. You can give them meaningful names so that they are easily recognizable if they need to be used again.

Rule Name

A valid string. See Name and Related Artifact Limits in Designing and Maintaining Essbase Cubes.

The rule name cannot be longer than thirty characters.

The build process creates a rule file with a .rul extension in the cloud service for every dimension worksheet in the workbook. You can give them meaningful names so that they are easily recognizable if they need to be used again.

Build Method

  • PARENT-CHILD

  • GENERATION

In Designer Panel, you can build a cube with either build method, but you cannot edit a cube built using the Generation build method using the panel, and you cannot view hierarchies using Cube Designer Dimension Hierarchy viewer.

Incremental Mode

  • Remove Unspecified
  • Merge

Incremental dimension builds enable you to update existing dimensions with new members.

Merge is the default. This option adds the new members to the dimension while retaining the existing members.

Remove Unspecified removes members that are not specified in the source file.

Delimiter

The values can be a tab, a space, or any single character except “.

This value must be updated directly in the Excel sheet. It cannot be updated using the Cube Designer interface.

Header Rows to Skip

A positive number or zero.

Zero is the default.

The number of header rows to skip when performing a data load or dimension build.

This value must be updated directly in the Excel sheet. It cannot be updated using the Cube Designer interface.

Allow Moves

  • Yes
  • No

Within a dimension, moves members and their children to new parents; recognizes primary members and matches them with the data source; not available for duplicate member outlines.

This value must be updated directly in the Excel sheet. It cannot be updated using the Cube Designer interface.

Data Source A valid Data Source name. This value is used to retrieve data from the source defined in the data source definition. This value must be updated directly in the application workbook. It can't be updated using the Cube Designer interface.

Member ID

Any unique key

Used to uniquely identify a member in an outline.

Required for duplicate outlines.

Storage Type

  • N

    Never allow data sharing.

  • O

    Tag as label only (store no data).

  • S

    Set member as stored (non dynamic calc and not label only).

  • X

    Create as dynamic calc.

Uses load rules member property codes. See Using the Data Source to Work with Member Properties in Designing and Maintaining Essbase Cubes.

Consolidation Operator

  • +
  • -
  • *
  • /
  • %
  • ~
  • ^
  • + (add)
  • - (subtract)
  • * (multiply)
  • / (divide)
  • % (percent)
  • ~ (no operation)
  • ^ (never consolidate)

IGNORE

Ignore

Data in a column with the heading, IGNORE is ignored during data loads and dimension builds.

This value must be updated directly in the Excel sheet. It cannot be updated using the Cube Designer interface.

Two-Pass Calculation

  • Yes
  • No

If you enter Yes, after a default calculation, then members that are tagged as two-pass are recalculated. The two-pass tag is effective on members of the dimension tagged as Accounts and on Dynamic Calc and Dynamic Calc and Store members of any dimension.

Two-pass calculation applies only to block storage outlines.

Solve Order

Any number, 0 to 127

Assign a calculation priority (0-127). The formula on the dimension or member that is assigned the highest solve order is calculated first. Values less than 0 or greater then 127 are reset to 0 and 127 respectively. The default value is 0.

Members that are not assigned a solve order are assigned the solve order of their dimension.

Time Balance

  • A

    Treat as an average time balance item (Applies to accounts dimensions only).

  • F

    Treat as the first time balance item (Applies to accounts dimensions only).

  • L

    Treat as the last time balance item (Applies to accounts dimensions only).

Uses load rules member property codes. See Using the Data Source to Work with Member Properties in Designing and Maintaining Essbase Cubes.

Time balance properties provide instructions about how to calculate data in the Accounts dimension. See Setting Time Balance Properties in Designing and Maintaining Essbase Cubes.

Skip Value

  • B

    Exclude data values of zero or #MISSING in the time balance (applies to accounts dimensions only).

  • M

    Exclude data values of #MISSING from the time balance (applies to accounts dimensions only).

  • Z

    Exclude data values of zero from the time balance (applies to accounts dimensions only).

Uses load rules member property codes. See Using the Data Source to Work with Member Properties in Designing and Maintaining Essbase Cubes.

If you set the time balance as first, last, or average, then set the Skip property to indicate what to do when missing values or values of 0 are encountered. See Setting Skip Properties in Designing and Maintaining Essbase Cubes.

Expense Reporting

E

Treat as an expense item (applies to accounts dimensions only)

Comment

Any string

Enter a comment.

Formula

Valid calculation syntax

Enter a member formula.

User Defined Attribute

Attribute names, such as specific colors or sizes

Defined attribute names used to aid in the analysis of the data.

When making changes to user-defined attributes (UDAs) while updating a cube incrementally using Cube Designer and an application workbook, you must specify all the UDAs in the dimension sheet, both new ones you are adding and existing UDAs in the outline. If you specify some UDAs (such as those you are adding), but not all of them, those that are not specified are deleted.

Number of UDAs

A numeral

The number of UDAs for this member.

Available Alias Tables

Naming conventions for member names apply. See Naming Conventions for Dimensions, Members, and Aliases in Designing and Maintaining Essbase Cubes.

ALIAS.table_name

After the column heading with ALIAS.table_name, the column is populated with the aliases for the cube.

You can modify dimension worksheets in the Designer Panel. See Work with Dimension Worksheets in Cube Designer.

See Working with Rules Files in Designing and Maintaining Essbase Cubes.