Advanced Settings Definition
In the application template workbook, include a worksheet for advanced settings, using the advanced settings worksheet provided in the template for the Sample application as your guideline.
Advanced settings are:
-
Evaluation order
-
Performance settings
-
Dimension properties such as hierarchy type, two-pass calculations, and data storage options
-
Density (for aggregate storage applications only)
-
Dimension assignments to cubes
Follow this workflow for setting up an advanced settings worksheet in your application template workbook:
Define the Worksheet Name
Name the advanced settings definition worksheet, Advanced Settings
. For example:
![Worksheet tabs from an Excel application template working showing the naming convention for the advanced settings sheet, "Advanced Settings". Worksheet tabs from an Excel application template working showing the naming convention for the advanced settings sheet, "Advanced Settings".](img/sv_app_cr_worksheet_name_advanced_110xd0d8307a.jpg)
Define the Worksheet Type
In cell A1 in the data worksheet, type Advanced Settings
to signify that this sheet contains the advanced, or miscellaneous settings for the application. In Figure C-9, cell A1 contains the worksheet type, Advanced Settings, to indicate that the worksheet will contain advanced settings for the application.
Figure C-9 Worksheet Type Shown in Advanced Settings Worksheet of Application Template
![Portion of Excel application template worksheet showing "Advanced Settings" in cell A1 to indicate that this is an advanced settings type. Portion of Excel application template worksheet showing "Advanced Settings" in cell A1 to indicate that this is an advanced settings type.](img/sv_app_cr_adv_settings_100xd13d6376.jpg)
Define Evaluation Order
In the Advanced Settings worksheet, define the evaluation order of dimensions in the "Evaluation Order" section. Dimensions are listed in the first column under the heading "Dimensions". Cubes are listed in subsequent columns. The dimensions in each cube can have a different evaluation order.
![Evaluation Order section of the Advanced Settings worksheet. Column A heading is Dimensions. Subsequent columns are the cube names in the application. In the application in this figure, column B is Plan1, column C is PBCS. Evaluation Order section of the Advanced Settings worksheet. Column A heading is Dimensions. Subsequent columns are the cube names in the application. In the application in this figure, column B is Plan1, column C is PBCS.](img/sv_app_adv_eval_order_102xd14122c2.jpg)
Define Performance Settings
For Planning applications only.
In the Advanced Settings worksheet, define dimension storage property of each dimension for each cube in the "Performance Settings" section. Valid values are Dense or Sparse. Each cube requires at least one dense dimension. Dimensions are listed in the first column under the heading "Dimensions". Cubes are listed in subsequent columns. The sparse or dense value of each dimension can differ between cubes.
![Performance Setting section of the Advanced Settings worksheet. Under "Performance Setting," the column A heading is Dimensions, and dimensions are listed below that. Under the Cubes heading in column B, cubes are listed in subsequent columns starting in column B. At the intersection of each dimension and cube is the dimension storage property setting, Sparse or Dense. Performance Setting section of the Advanced Settings worksheet. Under "Performance Setting," the column A heading is Dimensions, and dimensions are listed below that. Under the Cubes heading in column B, cubes are listed in subsequent columns starting in column B. At the intersection of each dimension and cube is the dimension storage property setting, Sparse or Dense.](img/sv_app_adv_perform_108xd141e758.jpg)
Define Dimension Settings
In the Advanced Settings worksheet, define dimensions properties in the "Dimension Settings" section. Dimensions are listed in the first column under the heading "Dimensions". Dimension properties are listed in subsequent columns. See Table C-5 for the list of dimension properties
Table C-5 Dimension Properties Used in Dimension Settings Section of the Advanced Settings Worksheet
Dimension Property | Description or Valid Values |
---|---|
Description |
Optional descriptive text |
Alias Table |
Optional alias table |
Hierarchy Type |
|
Two Pass Calculation |
Yes | No |
Apply Security |
Yes | No |
Data Storage |
|
Display Option |
Set application default display options for the Member Selection dialog box. Select Member Name or Alias to display members or aliases. Member Name:Alias displays members on the left and aliases on the right. Alias:Member Name displays aliases on the left and members on the right.
|
You can also use Figure C-10 for reference in adding information to the Dimension Settings section.
Figure C-10 Dimension Settings Section of the Advanced Settings Worksheet
![Dimensions Settings section of the Advanced Settings worksheet. Under "Dimension Settings," the first column heading is Properties. Dimensions are listed under Properties. Starting in the column B, subsequent column headings are Description, Alias Table, Hierarchy Type, Two Pass Calculation, Apply Security, Data Storage, and Display Option. For each dimension, non-default data is entered at the corresponding property intersection. For example, the Hierarchy Type property for the Period dimension is "Dynamic." Dimensions Settings section of the Advanced Settings worksheet. Under "Dimension Settings," the first column heading is Properties. Dimensions are listed under Properties. Starting in the column B, subsequent column headings are Description, Alias Table, Hierarchy Type, Two Pass Calculation, Apply Security, Data Storage, and Display Option. For each dimension, non-default data is entered at the corresponding property intersection. For example, the Hierarchy Type property for the Period dimension is "Dynamic."](img/sv_app_adv_dim_settings_106xd1418b4c.jpg)
Define Density Settings
For Planning Modules applications only.
In the Advanced Settings worksheet, define the dimension storage property of each dimension for each cube in the "Density" section. Each cube requires at least one dense dimension. Dimensions are listed in the first column under the heading "Dimensions". Cubes are listed in subsequent columns. The sparse or dense value of each dimension can differ between cubes.
![Density section of the Advanced Settings worksheet. Under "Density," the column A heading is Dimensions, and dimensions are listed below that. Under the Cubes heading in column B, cubes are listed in subsequent columns starting in column B. At the intersection of each dimension and cube is the dimension storage property setting, Sparse or Dense. Density section of the Advanced Settings worksheet. Under "Density," the column A heading is Dimensions, and dimensions are listed below that. Under the Cubes heading in column B, cubes are listed in subsequent columns starting in column B. At the intersection of each dimension and cube is the dimension storage property setting, Sparse or Dense.](img/sv_app_adv_density_112xd1467e68.jpg)
Assign Dimensions to Cubes
In the Advanced Settings worksheet, assign dimensions to cubes in the "Dimension Valid for" section. Dimensions are listed in the first column under the heading "Dimensions". Cubes are listed in subsequent columns.
For each dimension and cube intersection:
-
Type
Yes
to include the dimension in the cube. -
Type
No
or leave the cell blank to exclude the dimension from the cube.
!["Dimension Valid for" section of the Advanced Settings worksheet. Under "Dimension Valid for," the column A heading is Dimensions, and dimensions are listed below that. Under the Cubes heading in column B, cubes are listed in subsequent columns starting in column B. Yes is entered for each dimension that is required for a specific cube. When a cell at a particular intersection is left blank, the dimension will not be included in the cube at that intersection. "Dimension Valid for" section of the Advanced Settings worksheet. Under "Dimension Valid for," the column A heading is Dimensions, and dimensions are listed below that. Under the Cubes heading in column B, cubes are listed in subsequent columns starting in column B. Yes is entered for each dimension that is required for a specific cube. When a cell at a particular intersection is left blank, the dimension will not be included in the cube at that intersection.](img/sv_app_adv_dim_valid_110xd1464c69.jpg)