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.

Table A-6 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 Essbase 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. The rule name cannot be longer than thirty characters.	The build process creates a rule file with a .rul extension in Essbase 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	Merge Remove Unspecified Reset Dimension	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. Reset Dimension clears the members from the dimension and then rebuilds them, retaining the data. See Reset a Dimension in Cube Designer.
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.
Prototype	Member ID of the prototype member Qualified member name of the prototype member	Indicates the prototype member (member ID or qualified member name) for shared members.
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.
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	You can set solve order for dimensions or members, or you can use the default solve order. The minimum solve order you can set is 0, and the maximum is 127. A higher solve order means the member is calculated later; for example, a member with a solve order of 1 is solved before a member with a solve order of 2. 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. Time balance properties provide instructions about how to calculate data in the Accounts dimension. See Setting Time Balance Properties.
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. 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.
Expense Reporting	E	Treat as an expense item (applies to accounts dimensions only)
Comment	Any string	Enter a comment.
Formula	Valid formula 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.	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.