Understand 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.

Image of a data worksheet in an application workbook.

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.	The build process creates a data file with a `.txt` extension in the Essbase web interface 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 build process creates a rule file with a `.rul` extension in the Essbase web interface 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.
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 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'y 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.

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.

Rule Files

When you build a cube, data files and data load rule files are created in the Essbase web interface. 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. Rule 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 modify data worksheets in the Designer Panel. See Work with Data Worksheets in Cube Designer.