Using Hyperion Planning 11.1.2.2 Release, it was found that the position of a Cross Dimensional reference would not only affect the calculation, but also seemingly corrupt the Run Time Prompt (RTP) used in the Rule. This ‘invalid’ position of the Cross Dimension reference, would Validate and Deploy fine, but errored when run. The error complained about the RTP. The worst part was the corrupted RTP, also affected other Calc Manager Rules that used the same RTP.
There were 2 ways to fix the corrupted RTP after the fact.1. Run a Application Refresh.
2. Open the Rule and edit and re-save the RTP.
Sample code that passed validation, but corrupted the RTP cause the Rule not to run:
Acct123 = @SUMMRANGE(@CURRMBR({RTP_Acct}
Sample code that worked:
Acct123 = @SUMMRANGE(&CurrYr->@CURRMBR({
Solution:
1. Corrupted RTP can be fixed by a Refresh or Edit and Save of the RTP.
2. Position of Cross Dimensional reference in a Calc Mgr Rule can affect the RTP, thus preventing the rule from running.
The Outline Load utility can be used to load data, import/export metadata for standard dimensions and user defined dimensions, attributes, UDAs, exchange rates, Smart Lists, and planning unit hierarchies.
Command Line Parameters
The following command line parameters are available for the Outline Load utility. After running the utility, you can verify the results by reviewing the exception file and log file. If no errors are reported in the log file, you can then access the imported metadata and data in the application. It is not necessary to restart the application server.
OutlineLoad [-f:passwordFile] [/S:server] /A:application /U:userName [/M] [/E:outputFileName] [/I:inputFileName /D[U]:loadDimensionName| /DA:attributeDimensionName:baseDimensionName] [/TR] [/N] [[/R] [/U]] [/C] [/F] [/K] [/X:exceptionFileName] [L:logFileName] [/DX:HSP_Rates] [/DS:HSP_SMARTLISTS] [/8] [/?]
NOTE:
|
|
Parameter
|
Description
|
[-f:passwordFile]
|
Optional: If an encrypted password file is set up, use as the first parameter in the command line to read the password from the full file path and name specified in passwordFile.
|
/S:server
|
Server on which the application resides; if not specified, localhost is used.
|
/A:application
|
Name of the Planning application to which you are importing.
|
/U:userName
|
User name with which to log on to the application.
|
/M
|
Generate fully qualified header records for loadable dimensions in the application.
Use /-M if you do not want to display this information (default).
|
/E:outputFileName
|
Exports the dimension specified with the /D switch to the specified output file. (When exporting planning unit hierarchies, the file is in the format defined for importing planning unit hierarchies.)
|
/I:inputFileName
|
Name of the load file that contains a header record and data records in .CSV format. You must also specify a data load dimension (/D).
|
/D:loadDimensionName
|
Dimension to be loaded, whose member fields correspond to the header record in the load file. You must also specify a load file (/I) or the planning unit hierarchy to be exported with the /E switch.
See the following rows to load user-defined dimensions and attributes using /DU, /DA[T], /DAN, /DAB, and /DAD.
|
/DU:userDefinedLoadDimensionName
|
User-defined dimension to be loaded; a dimension with this name will be created if it does not exist.
|
/DA[T]:attributeLoadDimensionName:baseDimensionName
|
Text attribute dimension to be loaded; an attribute dimension with this name, bound to the base dimension, will be created if it does not exist.
|
/DAN:attributeLoadDimensionName:baseDimensionName
|
Numeric attribute dimension to be loaded; an attribute dimension with this name, bound to the base dimension, will be created if it does not exist.
|
/DAB:attributeLoadDimensionName:baseDimensionName
|
Boolean attribute dimension to be loaded; an attribute dimension with this name, bound to the base dimension, will be created if it does not exist.
|
/DAD:attributeLoadDimensionName:baseDimensionName
|
Date attribute dimension to be loaded; an attribute dimension with this name, bound to the base dimension, will be created if it does not exist.
|
/TR
|
Load data when driver members are specified in the .CSV file in the Driver Members column. All members except the driver member must be specified in the Point-of-View column. With / TR, you can load one value per row in the .CSV file.
|
/N
|
Perform a “dry run” by parsing the load file without loading data or metadata. Use /-N (or do not specify the /N parameter) to parse the load file while loading data and metadata (default).
|
/O
|
Maintain the order of members in the load file when loading, with the exception of UDAs (default).
Use /-O to ignore the order of members in the load file when loading.
|
/H
|
Order input records in parent-child order, with the exception of UDAs (default). Use /-H to load input records as they appear in the load file; this option is faster and uses less memory.
|
/R
|
Delete all members of the load dimension before performing the load. Use /-R (or do not specify the /R parameter) to keep all members of the load dimension (default). See also /U.
Note: Use caution with /R; this option removes attribute bindings and approvals states.
|
/U
|
Delete all planning units with the/R option, or display an error if members in planning units would be deleted. Use /-U (or do not specify the /U parameter) to prevent deleting members in planning units (default).
Use /U with /R to enable deleting started planning units and deleting all members in the dimension specified in the .CSV load file.
|
/T
|
Inherit unspecified plan type settings from the parent when adding new members (default). Use /-T to force explicit setting of plan type settings for the member.
|
/C
|
Perform a cube refresh after the metadata load. Use /-C if you do not want to perform a cube refresh (default). See also /F.
|
/F
|
Create security filters when refreshing with the /C option. Use /-F if you do not want to refresh security filters (default).
(This option does not provision users to the application; it only creates security filters for users that currently exist. Users can be provisioned to applications using other methods.) For this option to take effect, /C must also be specified.
|
/K
|
Lock the load dimension before loading (default), recommended. Use /-K if you do not want to lock the dimension (not recommended unless you are using /N).
|
/X:exceptionFileName
|
Specify the file that will contain exceptions that occur during the load. (If no file name is specified, the information is written to a file called stderr.)
|
/L:logFileName
|
Specify the file that will contain status and informational messages. (If no file name is specified, the information is written to a file called stdout.)
|
/DX:HSP_Rates
|
Load the HSP_Rates dimension and create exchange rate tables if they do not exist.
|
/DS:HSP_SMARTLISTS
|
Load the Smart Lists dimension and Smart List dimension entries.
|
/8
|
Use /8 if the file being exported will contain Unicode characters.
|
/?
|
Display usage text.
|
Loading Metadata
Metadata for Classic Hyperion Planning applications can be loaded or exported for Account, Period, Year, Scenario, Version, Currency, Entity, user-defined dimensions, attributes, UDAs, and Smart Lists. Values can also be loaded for exchange rates. However, because exchange rate values are loaded into the Planning relational tables, not directly into Essbase, the procedure for loading metadata still applies.
When loading metadata, the comma delimited metadata file (.csv) must contain a header record that lists the dimension, such as Entity, and the member properties used by subsequent metadata records.
For Entity, you can specify which entity to load, a default alias, additional aliases, Data Storage, UDA, Currency and any other property that is associated to the Entity dimension.
Header records are case sensitive. They can appear in any order, but must be spelled and punctuated exactly as the utility expects. I recommend running an export on the dimension that you want to get the headers.
The utility loads one record at a time. If a record fails to load, a message is written to the exception file, and the load process resumes with the next record.
Metadata for Classic Hyperion Planning applications can be loaded or exported for Account, Period, Year, Scenario, Version, Currency, Entity, user-defined dimensions, attributes, UDAs, and Smart Lists. Values can also be loaded for exchange rates. However, because exchange rate values are loaded into the Planning relational tables, not directly into Essbase, the procedure for loading metadata still applies.
When loading metadata, the comma delimited metadata file (.csv) must contain a header record that lists the dimension, such as Entity, and the member properties used by subsequent metadata records.
For Entity, you can specify which entity to load, a default alias, additional aliases, Data Storage, UDA, Currency and any other property that is associated to the Entity dimension.
Header records are case sensitive. They can appear in any order, but must be spelled and punctuated exactly as the utility expects. I recommend running an export on the dimension that you want to get the headers.
The utility loads one record at a time. If a record fails to load, a message is written to the exception file, and the load process resumes with the next record.
- When new members are added, unspecified properties assume the default value or inherit the parent member's property as appropriate.
- If a member exists and no property value is specified, the property is left as is.
Here are some examples for exporting/importing metadata:
1. Export the Entity dimension members from the requested Planning “Forecast” application.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /U:admin /M /D:Entity /E:D:\Files\Extracts\EntityMbrs.txt /L:D:\Logs\EntityMbrs.log /X:D:\Logs\EntityMbrs_Excptns.log
Output Sample:
Entity, Parent, Alias: Default, Alias: OCAT, Valid For Consolidations, Data Storage, Two Pass Calculation, Description, Formula, UDA, Smart List, Data Type, Base Currency, Plan Type (Budget), Aggregation (Budget), Plan Type (Employee), Aggregation (Employee), Plan Type (Plan), Aggregation (Plan)
TotDept, Entity, Total Department, , false, store, false, , , , , unspecified, USD, true, +, true, +, true, ~
Operations, TotDept, , , false, store, false, , , , , unspecified, USD, true, +, true, +, true, ~
2. Import the Entity dimension metadata members into the requested Hyperion Planning “Forecast” application.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /U:admin /M /D:Entity /I:D:\Files\Metadata\EntityMbrs.txt /L:D:\Logs\EntityMbrs.log /X:D:\Logs\EntityMbrs_Excptns.log
Input Sample:
Entity, Parent, Alias: Default, Data Storage, Data Type, Aggregation (Budget), Aggregation (Employee), Aggregation (Plan)
Dept_001, Div_4004, Department 001, store, , +, +, +
Dept_002, Div_4004, Department 002, store, , +, +, +
Dept_003, Div_4004, Department 003, store, , +, +, +
1. Export the Entity dimension members from the requested Planning “Forecast” application.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /U:admin /M /D:Entity /E:D:\Files\Extracts\EntityMbrs.txt /L:D:\Logs\EntityMbrs.log /X:D:\Logs\EntityMbrs_Excptns.log
Output Sample:
Entity, Parent, Alias: Default, Alias: OCAT, Valid For Consolidations, Data Storage, Two Pass Calculation, Description, Formula, UDA, Smart List, Data Type, Base Currency, Plan Type (Budget), Aggregation (Budget), Plan Type (Employee), Aggregation (Employee), Plan Type (Plan), Aggregation (Plan)
TotDept, Entity, Total Department, , false, store, false, , , , , unspecified, USD, true, +, true, +, true, ~
Operations, TotDept, , , false, store, false, , , , , unspecified, USD, true, +, true, +, true, ~
2. Import the Entity dimension metadata members into the requested Hyperion Planning “Forecast” application.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /U:admin /M /D:Entity /I:D:\Files\Metadata\EntityMbrs.txt /L:D:\Logs\EntityMbrs.log /X:D:\Logs\EntityMbrs_Excptns.log
Input Sample:
Entity, Parent, Alias: Default, Data Storage, Data Type, Aggregation (Budget), Aggregation (Employee), Aggregation (Plan)
Dept_001, Div_4004, Department 001, store, , +, +, +
Dept_002, Div_4004, Department 002, store, , +, +, +
Dept_003, Div_4004, Department 003, store, , +, +, +
Planning Results:
Entity
|__ Div_4004
|__ Dept_001 (Alias: Department 001)
|__ Dept_002 (Alias: Department 002)
|__ Dept_003 (Alias: Department 003)
3. Import the numeric attribute dimension and values, and associate them with the Entity dimension. An attribute dimension will be created if it does not exist, but no assignment is made of attribute values to base numbers.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /M /I:D:\Files\Metadata\EntAttrib.csv /DAN:NumericAttrib:Entity /L: D:\Logs\EntAttrib.log /X:D:\Logs\EntAttrib_Excptns.log
Input Sample:
NumericAttrib,Parent
1,One
2,Two
3,Three
4,Four
4. Import UDAs. The UDA is loaded and associated with a dimension, but it is not assigned to any member in the dimension.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /M /I:D:\Files\Metadata\Ent_UDA.csv /D:UDA /L:D;\Logs\Ent_UDA.log /X:D:\Logs\Ent_UDA_Excptns.log
Input Sample:
Dimension,UDA
Entity,CostCenter
Entity,Management
Loading Data
When loading data with the Outline Load utility, there are two ways to specify driver members. You can load to driver members that are specified on the Hyperion Planning Data Load Administration page, or you can specify driver members in a comma delimited (.csv) load file and run the utility with the /TR option.
If you load data with the /TR option, the comma delimited (.csv) file must list the driver member and all other members under the Point-of-View column, regardless of their location on the data form.
For example, if Jan or Descendants (YearTotal) and are columns in a data form, they must be specified in the Point-of-View column. When using /TR, you can load one value per row in the .CSV file. You can include multiple rows, but you can specify only one data value per row.
I personally would not use the Outline Load utility to load data using the /TR option. It is much easier to use EAS or MaxL scripts to load data directly to Essbase.
If you want to load data into Planning the following parameters must be set:
DIRECT_DATA_LOAD -- system property enables data to be loaded directly to Essbase. In the current release, this property is set to true by default, and data is loaded directly into Essbase. To prevent data from being loaded directly into Essbase, set DIRECT_DATA_LOAD to false.
DATA_LOAD_PATH – The location and name that will be used for the generated data and rules files, for example, D:/Files\Extracts/Forecast.txt.
Ensure that these properties are set in the System Properties tab in the Manage Properties page.
|__ Div_4004
|__ Dept_001 (Alias: Department 001)
|__ Dept_002 (Alias: Department 002)
|__ Dept_003 (Alias: Department 003)
3. Import the numeric attribute dimension and values, and associate them with the Entity dimension. An attribute dimension will be created if it does not exist, but no assignment is made of attribute values to base numbers.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /M /I:D:\Files\Metadata\EntAttrib.csv /DAN:NumericAttrib:Entity /L: D:\Logs\EntAttrib.log /X:D:\Logs\EntAttrib_Excptns.log
Input Sample:
NumericAttrib,Parent
1,One
2,Two
3,Three
4,Four
4. Import UDAs. The UDA is loaded and associated with a dimension, but it is not assigned to any member in the dimension.
OutlineLoad -f:D:\Files\PswdFile.txt /A:Forecast /M /I:D:\Files\Metadata\Ent_UDA.csv /D:UDA /L:D;\Logs\Ent_UDA.log /X:D:\Logs\Ent_UDA_Excptns.log
Input Sample:
Dimension,UDA
Entity,CostCenter
Entity,Management
Loading Data
When loading data with the Outline Load utility, there are two ways to specify driver members. You can load to driver members that are specified on the Hyperion Planning Data Load Administration page, or you can specify driver members in a comma delimited (.csv) load file and run the utility with the /TR option.
If you load data with the /TR option, the comma delimited (.csv) file must list the driver member and all other members under the Point-of-View column, regardless of their location on the data form.
For example, if Jan or Descendants (YearTotal) and are columns in a data form, they must be specified in the Point-of-View column. When using /TR, you can load one value per row in the .CSV file. You can include multiple rows, but you can specify only one data value per row.
I personally would not use the Outline Load utility to load data using the /TR option. It is much easier to use EAS or MaxL scripts to load data directly to Essbase.
If you want to load data into Planning the following parameters must be set:
DIRECT_DATA_LOAD -- system property enables data to be loaded directly to Essbase. In the current release, this property is set to true by default, and data is loaded directly into Essbase. To prevent data from being loaded directly into Essbase, set DIRECT_DATA_LOAD to false.
DATA_LOAD_PATH – The location and name that will be used for the generated data and rules files, for example, D:/Files\Extracts/Forecast.txt.
Ensure that these properties are set in the System Properties tab in the Manage Properties page.
