Understanding the Cube.Settings Worksheet: Substitution Variables Substitution variables act as global placeholders for information that changes
2. Enter the variable name in column A and its value in column B, enclosing the value in quotation marks.
Example:
CurrMonth "Jan"
See Using Substitution Variables in Administering Oracle Analytics Cloud - Essbase.
Understanding the Cube.Generations Worksheet
Cube.Generations Worksheets
The Cube.Generations worksheet is used for naming generations in an outline. The term "generation" indicates the distance of a member from the root of the dimension. Using a generation number, you can determine the location of members within the database tree. All members in a database that are the same number of branches from their root have the same generation number. The dimension is generation 1, its children are generation 2, and so on.
You can create names for generations in an outline, such as a word or phrase that describes the generation. For example, you might create a generation name called Cities for all cities in the outline.
You can also use generation names in calculation scripts wherever you need to specify a list of generation numbers. For example, you could limit a calculation in a calculation script to all members in a specific generation.
You can specify only one name per generation. The specified name must be unique; that is, it cannot duplicate a generation, level, or member name or an alias or conventional alias.
Appendix A
Note:
The Dimension section of the Cube.Generations worksheet changes if you change the dimension worksheet (Dim.dimname) by adding or deleting members in such a way that the number of generations in the dimension is changed. If you make changes to the dimension worksheet by adding or deleting members, you should always press the Update Generation Worksheet button on the Dimensions tab of the Cube Designer wizard as part of the editing process.
Cube.Generations Worksheet Format
The following image shows a Cube.Generations worksheet in a sample application workbook.
Table A-5 Fields and Valid Values in Generation Worksheets
Property or Field Valid Values Description
Dimension Name For dimension naming restrictions, see Naming Conventions for Dimensions, Members, and Aliases for naming restrictions.
The dimension name.
Appendix A
Table A-5 (Cont.) Fields and Valid Values in Generation Worksheets
Property or Field Valid Values Description
Generation Number A generation number, 1 or greater.
A root branch of the tree is generation 1. Generation numbers increase as you count from the root toward the leaf member.
Generation Name You can define only one name for each generation. When you name generations, follow the same naming rules as for members. See Naming Conventions for Dimensions, Members, and Aliases.
The generation name. You can use this field to create or change generation names. Enter the generation name and then build or update the cube using the application workbook. See Updating Cubes Incrementally in Cube Designer.
Unique • Yes
• No
For duplicate member name outlines, enter Yes to require unique member names within the associated generation.
Understanding the Cube.Textlists Worksheet
In application workbooks, the Cube.Textlists worksheet defines text lists. Text lists are used to work with text measures, which extend the analytical capabilities of Essbase Cloud. In addition to numeric values, measures can be associated with text-typed values. Storage and analysis of textual content can be useful when a cell needs to have one of a finite list of textual values; for example, a product may be sold in 5 different colors. The color is a text measure whose value must be one of the 5 colors. The colors are a set of text strings mapped to corresponding numeric IDs. These mappings are contained in tables in the Cube.Textlists worksheet.
You can add multiple text list tables to the same sheet and they can be associated with multiple measures.
The following image shows the Cube.Textlists worksheet in a sample application workbook.
Appendix A
Table A-6 Cube.Textlists Worksheet Fields and Values
Property or Field Valid Values Description
List Name Must not exceed 80 characters.
A text list must start with a list name followed by its value in the adjacent cell.
Associated Members Existing Member Names. Member names added in adjacent cells. Multiple members can be added in adjacent cells to the right. ID The first two values under ID
are #Missing and #OutOfRange. These two values must exist in every text list table. The other IDs must be integers.
Each ID, including the #Missing, #OUTOFRANGE and numeric values, must map to a text value.
The first two IDs, #Missing and #OUTOFRANGE, are for handling cases where the textual data is invalid or empty. For example, if you try to load an unmapped value such as “Average” to a text measure, the cell value would not be updated, and would display as #Missing in a subsequent query. If you load a numeric cell value that is unmapped, the subsequent query would return N/A. Text Up to 80 characters. The text column contains the
text values for each text measure.
Each text value must map to an integer in the ID column. Any text value that does not map to an integer in the text list is considered by Essbase Cloud to be invalid.
In Administering Oracle Analytics Cloud - Essbase, see: • Working with Typed Measures
• Performing Database Operations on Text and Date Measures
Understanding 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 rules syntax. For example, an X in the Storage column means that the data value is not stored.
Appendix A
The following image shows a dimension worksheet in a sample 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.
Note:
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.
Appendix A
Table A-7 (Cont.) Fields and Valid Values in Dimension Worksheets
Property or Field Valid Values Description
Rule Name A valid string. See Name and Related Artifact Limits in Administering Oracle Analytics Cloud - Essbase.
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 Cube Designer wizard, you can build a cube with either build method, but you cannot edit a cube built using the Generation build method using the wizard, 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.
Appendix A
Table A-7 (Cont.) Fields and Valid Values in Dimension Worksheets
Property or Field Valid Values Description
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
Administering Oracle Analytics Cloud - Essbase. Consolidation Operator • + • - • * • / • % • ~ • ^ • + (add) • - (subtract) • * (multiply) • / (divide) • % (percent) • ~ (no operation) • ^ (never consolidate)
Ignore IGNORE Data in a column with the
heading, IGNORE is ignored during dimension builds and data loads.
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.
Appendix A
Table A-7 (Cont.) Fields and Valid Values in Dimension Worksheets
Property or Field Valid Values Description
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
Administering Oracle Analytics Cloud - Essbase.
Time balance properties provide instructions about how to calculate data in the Accounts dimension. See Setting Time Balance Properties in Administering Oracle Analytics Cloud - Essbase.
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
Administering Oracle Analytics Cloud - Essbase.
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 Administering Oracle Analytics Cloud - Essbase.
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.
Appendix A
Table A-7 (Cont.) Fields and Valid Values in Dimension Worksheets
Property or Field Valid Values Description
User Defined Attribute Attribute names, such as specific colors or sizes
Defined attribute names used to aid in the analysis of the data.
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 Administering Oracle Analytics Cloud - Essbase.
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 Cube Designer wizard. See Working with Dimension Worksheets in Cube Designer.
See Working with Rules Files in Administering Oracle Analytics Cloud - Essbase.
Understanding Data Worksheets
Data Worksheets
You can include one or more data worksheets in an application workbook. The name of each data worksheet is Data.name. For example, for values for the eastern region, the data worksheet might be called Data.East. The name can be anything you choose. You can choose meaningful names so that you can recognize them if you need to use them again.
Note:
Multiple data worksheets are allowed in an application workbook, but they must share the exact same column layout.
Data Worksheet Format
When loading data, a member from every dimension must be defined before a data value. Therefore, the data worksheet places all but one dimension under the column headings titled, Dimension.dimension_name. One dimension is selected as the Measures dimension and members from that dimension must be added manually under the remaining column headings titled Measure.member_name. Only place members that will contain data in the columns titled Measure.member_name. When scenarios are enabled, cubes have a hidden dimension called sandbox. The sandbox dimension, named Dimension.sandbox, is the first column in the data worksheet. It contains a member called base that you must define when loading data. The following image shows a data worksheet in a sample application workbook.
Appendix A
The following table describes the settings on the data.name worksheets in application workbooks.
Property or Field Valid Values Description
File Name A valid string. See Name and Related Artifact Limits in Administering Oracle Analytics Cloud - Essbase.
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 Administering Oracle Analytics Cloud - Essbase.
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. Data Load Option • Add
• Subtract
• Replace
If you enter Replace, then the existing values of the
database are overwritten with the values of the data source. You can also use incoming data values to add to or subtract from existing database values. For example, if you load weekly values, then you can add them to create monthly values in the database.
Appendix A
Property or Field Valid Values Description
Delimiter The values can be a tab, a space, or any single character except “.
• Tab
• Space
• 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. 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. Sign Flip Dimension Dimension name Reverses the values of data
fields by flipping their signs. Enter the name of the dimension in the Sign Flip Dimension field, and enter the selected UDA within the specified dimension in the Sign Flip UDA field. This value must be updated directly in the Excel sheet. It cannot be updated using the Cube Designer interface. Sign Flip UDA • Flip
• Blank
Reverses the values of data fields by flipping their signs. Enter the name of the dimension in the Sign Flip Dimension field, and enter the selected UDA within the specified dimension in the Sign Flip UDA field. This value must be updated directly in the Excel sheet. It cannot be updated using the Cube Designer interface. Ignore column header 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.
Data Operations
When you load data, values can replace, add to, or subtract from existing data values in the cube. You indicate which of these options to use in the Data Load Option field on the data worksheet.
Appendix A
• Replace: Overwrites cube values with the data source values. Replace is the default.
• Add: Adds data source values to the cube values. For example, if you load weekly data values, you can add them to create cumulative data values in the cube. • Subtract: Subtracts data source values from the database values. For example, to
track available budget by week, you can subtract weekly data expenditures from the previous week's budget values.
Rules Files
When you build a cube, data files and data load rules files are created in the cloud service. Those files can then be used later if you want to load data to a cube. Data files are named with the file name specified in the definitions area of the data sheet and a .txt extension. For example, cube_basic.txt. Rules files are named with the file
name specified in the definitions area of the data sheet and a .rul extension. For
example, cube_basic.rul. You can also use data files and data load rules files from a
supported on-premises release of Essbase.
You can modify data worksheets in the Cube Designer wizard. See Working with Data Worksheets in Cube Designer.
See Data Sources in Administering Oracle Analytics Cloud - Essbase.
Understanding Calculation Worksheets
You can have one or more calculation worksheets in an application workbook.
The following image shows a calculation worksheet in a sample application workbook.
Within the calculation worksheet, the calculation script begins in cell C6.
The name of each calculation worksheet is Calc.scriptname, for example, for the sample CalcAll calculation script, the calculation worksheet is called Calc.calcall. The contents of the calculation worksheet are used to create a calculation script in the cloud service. The calculation script uses the file name specified in the definitions area of the calculation sheet and a has a .csc extension. For example, filename.csc.
You can execute the calculation script when you build the cube in Cube Designer, if you select Run Calculation Sheets Contained within Workbook in the Build Cube dialog box. If you do not want to execute the calculation, do not select this option.
Appendix A
Note:
The calculation scripts are executed in the order they appear in the application workbook.
Property or Field Valid Values Description
File Name See Naming Conventions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values in
Administering Oracle Analytics