Planning 11.1.2.2.300 Outline Load Utility Enhancements

I noticed that the recent patch release of planning 11.1.2.2.300 includes some additional functionality for the outline load utility that is worth going through.

With each release the outline load utility seems to gain extra functionality and it has just grown from strength to strength since its first appearance in 11.1.1.0

It is a utility that I know has made consultants lives much easier, it is simple to use and is now packed with functionality.

The enhancements in this patch release are

• Import metadata and data from a relational data source
 

• Optimize command lines by storing command line arguments in a command properties file. For example, if you use a command properties file to run the same commands on multiple applications, you need only change a parameter in the command line for each import. This allows you to bundle switches for a common application. It also makes command lines shorter and easier to manage, and enhances readability and ease of use.
 

• Export data export to a flat CSV file

If you are going to patch planning then make sure you go through the readme in detail as it can be quite painful if you are on a distributed environment and it also requires patching other products first plus additional ADF patching.

Today I am going to go through using the properties file and importing metadata from a relational source, in the next blog I will cover the remaining enhancements.

In previous versions of Hyperion before the days of the Shared Services registry most of the configuration settings were held in properties file, a properties file (.properties) basically allows you to store key-value pairs which are separated either with a colon (key:value) or an equals sign (Key=value) and these pairs are then read by the calling application.

For example in the outline load utility world these could be /U:username or /S:servername

You are not restricted to only using pairs in the file as the other parameter switches can be used as well such as /N /O /C etc.

Before this release all of the these parameters had to be included in the command line and depending on the number of parameters it could look messy and if you have many scripts a lot of replication was being exercised.

Now there is a new parameter available /CP: commandPropertieFileName which designates a properties file to use when the outline load utility executes:

OutlineLoad.cmd /CP:E:\PLAN_SCRIPTS\metaload.properties   

By using the above example when the outline load utility start its will look for metaload.properties in E:\PLAN_SCRIPTS

Instead of having to write this information to the command line it is read from the easier to read and manageable properties file.

It is also possible to override the values in the properties file by including them in the command line.

OutlineLoad.cmd /CP:E:\PLAN_SCRIPTS\metaload.properties /D:Entity

/D:Entity which defines the entity dimension to load to takes precedence over /D:Account in the properties file.

I did notice that it doesn’t look possible to include -f:passwordFile parameter in the properties file and had to be included in the command line.

Right on to the main event and loading metadata into a planning application from a relational source, I know this new functionality will be music to the ears for lots of consultants because in many cases the source can be relational and up to now a SQL download and formatted file would have to be produced before using the utility.

There a quite a lot of new parameters available for the relational functionality and I will cover the ones that you are likely to use when loading metadata.

Here is an extension to above metaload.properties file which includes the parameters to run a SQL query against a relational source to load metadata to a planning application.

/IR:RelationalConnectionPropertiesFileName 

Just like the /CP parameter for including a properties file there is also one available just for the connectional information to a relational database, it is possible to use the same properties file as the one for other parameters like I have used:

/IR:E:/PLAN_SCRIPTS/metaload.properties

This will read metaload.properties for the source relational database information.

/RIQ:inputQueryOrKey

This can either the SQL query to be run or it can be used to designate a key which will hold the query.

/RIQ: ACCOUNT_SQL

So in my example the SQL query to be executed is held in key ACCOUNT_SQL

You may be asking why not just put the SQL directly in the /RIQ value, well you may have multiple SQL statements for different metadata load and by just updating /RIQ you call the required one, if you use /RIQ in the command line and have all the keys in the properties file then it is simple call different queries and looks much tidier.

KEY=SQL

The key relates to the key defined in /RIQ and SQL is the query that will be run, so in my example

The key is ACCOUNT_SQL and the SQL that will be executed is

SELECT ACCOUNT as "Account",PARENT as "Parent",alias_default as "Alias: Default",data_storage as "Data Storage" FROM PLAN_ACCOUNT ORDER BY idno

For my example this generates the following records to be loaded as metadata into the account dimension:

The query must return column header names to exactly match the properties required for planning.

/RIC: catalog

For Oracle I don't believe it matters what you specify as the value, for SQL Server it should be the database name.

/RID: driver

The parameter is the JDBC driver that will be used for the connection to the relational database.

For Oracle use:  /RID:oracle.jdbc.OracleDriver
For SQL Server:  /RID:weblogic.jdbc.sqlserver.SQLServerDriver

/RIR:url

The parameter is the JDBC URL to be used for the connection to the relational database.

For Oracle the format is: jdbc:oracle:thin:@[DB_SERVER_NAME]:DB_PORT:DB_SID

So in my example that equates to /RIR:jdbc:oracle:thin:@[fusion11]:1521:FUSION

For SQL Server the format is:
jdbc:weblogic:sqlserver://[DB_SERVER_NAME]:DB_PORT

An example being
/RIR: jdbc:weblogic:sqlserver://[fusion11]:1433

/RIU:username

Nice and simple this is the user name to connect to the relational database.

/RIP:password

The password for the database connection, this is unencrypted the first time it is used in a properties file.

Once the outline load utility has been run it will update the properties file and encrypt the password.

If you don’t want to use a properties file then all these parameters can be entered directly into the command line.

So let’s give it a go

The Account dimension currently contains no members.

The outline load utility is executed passing in the parameters to the encrypted password file and the properties file to use.

The utility reads the properties file and checks whether the database connection password is encrypted and as it is not encrypted it updates the file with an encrypted value.

The utility reads through the rest of the properties and then merges them with the ones currently in the command line before submitting them.

The output in the log provides further detailed information.

Property file arguments: /RIU:PLANSTAGE /D:Account /RIR:jdbc:oracle:thin:@[fusion11]:1521:FUSION /RIP:TOzyauMwe2gtUQ9tjidf1Zq2pCA8iroN4i7HQxssFKUaogr16fi+WKmHFTD8NIIs /RIQ:ACCOUNT_SQL /X:E:/PLAN_SCRIPTS/Logs/accld.err /A:PLANDEMO /S:FUSION11 /L:E:/PLAN_SCRIPTS/Logs/accld.log /U:epmadmin /RIC:FUSION_CONN /RID:oracle.jdbc.OracleDriver /IR:E:/PLAN_SCRIPTS/metaload.properties

Command line arguments: /CP:E:\PLAN_SCRIPTS\metaload.properties

Submitted (merged) command line: /RIU:PLANSTAGE /D:Account /RIR:jdbc:oracle:thin:@[fusion11]:1521:FUSION /RIP:TOzyauMwe2gtUQ9tjidf1Zq2pCA8iroN4i7HQxssFKUaogr16fi+WKmHFTD8NIIs /RIQ:ACCOUNT_SQL /X:E:/PLAN_SCRIPTS/Logs/accld.err /A:PLANDEMO /S:FUSION11 /L:E:/PLAN_SCRIPTS/Logs/accld.log /U:epmadmin /RIC:FUSION_CONN /RID:oracle.jdbc.OracleDriver /IR:E:/PLAN_SCRIPTS/metaload.properties

Successfully logged into "PLANDEMO" application, Release 11.122, Adapter Interface Version 5, Workforce supported and not enabled, CapEx not supported and not enabled, CSS Version 3

A query was located in the Command Properties File "E:\PLAN_SCRIPTS\metaload.properties" that corresponded to the key passed on the Input Query switch (/RIQ) "ACCOUNT_SQL" so it's corresponding value will be executed as a query: "SELECT ACCOUNT,PARENT as "Parent",alias_default as "Alias: Default",data_storage as "Data Storage" FROM PLAN_ACCOUNT ORDER BY idno"

Attempting to make input rdb connection with the following properties: catalog: FUSION_CONN, driver: oracle.jdbc.OracleDriver, url: jdbc:oracle:thin:@[fusion11]:1521:FUSION, userName: PLANSTAGE

Source RDB "FUSION_CONN" on jdbc:oracle:thin:@[fusion11]:1521:FUSION connected to successfully.

Connection to input RDB made successfully.

[Mon Sep 17 22:24:26 BST 2012]Header record fields: ACCOUNT, Parent, Alias: Default, Data Storage

[Mon Sep 17 22:24:26 BST 2012]Located and using "Account" dimension for loading data in "PLANDEMO" application.

[Mon Sep 17 22:24:26 BST 2012]Load dimension "Account" has been unlocked successfully.

[Mon Sep 17 22:24:26 BST 2012]A cube refresh operation will not be performed.

[Mon Sep 17 22:24:26 BST 2012]Create security filters operation will not be performed.

[Mon Sep 17 22:24:26 BST 2012]Examine the Essbase log files for status if Essbase data was loaded.

[Mon Sep 17 22:24:26 BST 2012]Planning Outline data store load process finished. 317 data records were read, 317 data records were processed, 317 were accepted for loading (verify actual load with Essbase log files), 0 were rejected.

All the 317 data records were read, processed and loaded successfully to the planning application.

Back in the planning application you can see all the metadata has been loaded.

There is one more parameter that I have not mentioned and that is /IRA, if this is used then the database connection information to the connected planning application is used.

This allows queries to be run against tables in the connected planning application database and does not require the /RIQ, /RIC, /RID, /RIR, /RIU, /RIP parameters.

Using the new functionality will also allow you to query the planning tables directly to return metadata, if this is something you are looking to do then it is definitely worth having a look at the Cameron Lackpour’s blog as he has kindly spent quite a lot of time covering this area in a number of his posts.

In the next blog I will cover the rest of the new functionality.