1

Batch Scripts: Creating XML files for Runtime Prompts

|

When automating Business Rules through batch scripts, an XML file is needed to state the runtime prompts. This is how the batch script will know which members to run the business rule for. Is there an easy way to create these files?

Luckily, it is fairly simple to create these XML files and reference them from the batch script. First, to create an XML file directly from a Planning application, go to Tools -> Business Rules:

Once the business rule page opens, use the 2 dropdown menus at the top of the page to narrow down the list of available business rules:

Select the relevant business rule, and click on the launch button on the right side of the window. In this example, I want to create an XML file for the business rule, “CurrConvAdmin”:

This rule has 3 runtime prompts (Scenario, Year, & Version). Select the 3 members for the prompts and click “Create runtime prompt values file” in the bottom right of the pop-up window:

The following screen will appear, confirming that the file was created successfully:

Now, we need to go and find where the XML file was saved so that we can reference it in our batch file. From the Foundation server (or the server that Planning is on), go to the following path:

D:\Oracle\Middleware\user_projects oundation1\Planning\planning1\RTP

All of the XML files will be created under your username within the RTP folder:

Right click to edit the file, and notice that all 3 runtime prompts are accounted for in the file:

In the batch command itself, the XML file is referenced as follows:

Here is the default syntax for referencing a business rule via batch scripts:

CalcMgrCmdLineLauncher.cmd [-f:passwordFile] /A:appname /U:username /D:database [/R:business rule name | /S:business ruleset name] /F:runtime prompts file [/validate]

And here is the full documentation on the business rule syntax from Oracle: http://docs.oracle.com/cd/E1723601/epm.1112/hpadmin/frameset.htm?ch06s09s05.html

I’ve noticed that it is best to create the XML files directly from Planning, rather than trying to create them manually. When manually created, the batch command won’t always recognize the format of the XML file, even if it looks the same to what is created via Planning. It only takes a couple of extra minutes, but will save you from some headaches down the line.



Change Application Maintenance Mode via Command Line

|

Patch Set Update: 11.1.2.1.600 offers a welcome utility

If you have ever tried to automate the state of a Hyperion Planning applications’ Application Maintenance Mode, you found it difficult. The only way to accomplish this was to run a SQL Update on the repository table, and for this to take effect, the Planning service had to be restarted.

If you are unfamiliar with the Application Maintenance Mode setting, it is found in Administration/Application/Settings menu. Changing this setting from All Users to Administrators, locks out planners from using the application. It is typically used when changes are made to hierarchies, web forms, system settings, security, and during deploys, to keep users out while changes are being introduced.

Patch Set 11.1.2.1.600, and the corresponding patch release for 11.1.2.2, introduces a new utility that allows administrators to change this setting from a command line. YEAH, it can now be automated without restarting Planning!

Without Further Adieu

MaintenanceMode.cmd (or MaintenanceMode.sh in UNIX) is found in the <EPM_PLANNING_INSTANCE> directory. The following parameters can be passed, separated by commas.

  • /A=app – Application name (required)
  • /U=user – Name of the administrator executing the utility (required)
  • /P=password – The administrator’s password (required)
  • /LL=loginLevel – [ALL_USERS|ADMINISTRATORS|OWNER]

ALL_USERS – All users can log on or continue working with the application.

ADMINISTRATORS – Only other administrators can log on. Other users are forced off and prevented from logging on until the parameter is reset to ALL_USERS.

OWNER – Only the application owner can log on. All other users are prevented from logging on. If they are currently logged on, they are forced off the system until the option is reset to All_USERS or ADMINISTRATORS. Only the application owner can restrict other administrators from using the application.

  • /DEBUG=[true|false] – Specify whether to run the utility in debug mode. The default is false. (optional)
  • /HELP=Y – View the utility syntax online (optional)

Example

MaintenanceMode.cmd /A=app1,/U=admin,/P=password,/LL=ADMINISTRATORS

MaintenanceMode.cmd /A=app1,/U=admin,/P=password,/LL=ALL_USERS

 



Managing More With Less Doesn’t Have To Be Impossible

|

 

We will always be asked to do more with less. Finance is asked to produce more and better analytics with less people. Sales people are asked to produce more in a weakening economy with less marketing dollars, and yes, groups that manage Essbase environments are asked to produce and manage more data/applications with shrinking resources.

Back in the Day

In a prior life, I used to manage a group responsible for managing the Essbase environment used to produce all the reporting for the group. It generated about 70% of the revenue for Bank One (now Chase). We delivered all the reporting, budgeting, and forecasting applications. It included nearly 2 TB of data (pre ASO) on four servers that included more than 50 databases. All the typical technologies were employed. A large number of filters existed to maintain security. Many of the applications were linked together with several types of partitions. Data was loaded daily, weekly, and monthly. SQL Server was used for all the ETL processes, and we completed the development and performed all the maintenance with four people.

The only way the group could be effective in developing and enhancing applications, was to eliminate our effort spent on typical production activities. With the number of applications and the frequency they were updated (daily, weekly, or monthly), communicating this information to the more than 250 users was also a large time commitment.

The Solution

We built custom applications using the Essbase API to not only automate the tasks, but also notify the appropriate person if there was an error. This included everything from data loads, application builds, ETL processing, nightly data exports, repetitive calculations, and every other aspect of the maintenance. We even automated the validation of the data during the load process. Data loaded to the ETL layer was compared to the ETL Export. After the data was loaded to the Essbase application, we automated Excel data retrieves and compared them back to the ETL data exports. We effectively eliminated any effort it took to maintain the environment unless an error occurred that required attention. This was the ONLY way we could keep our heads above water.

We chose the API because it is so robust. It has most of the Maxl functions. It introduces the ability to check for errors at any point in the process, and can take the appropriate steps to resolve. No manual intervention was required. The same application can interact with the ETL layer, send text messages or pages, email administrators and users, and update web pages with statuses that the users can see (like the state of the load process, calculation status, etc.).

This solution may be overkill for very small implementations of one or two applications. But, don’t underestimate its importance in medium to large-scale operations. It minimizes costs, reduces errors, provides a better user experience, and minimizes delays in new development.

I highly recommend investigating how this would work for your group. Although I used the Visual Basic API, there are also libraries for C and Java.