Remove Dimensions From Planning LCM Extracts

Problem

I am currently working with a client that is updating a planning application and one of the changes is to remove a dimension.  After the new application was setup and the hierarchies were modified to meet the objectives, migrating artifacts was the next step.  As many of you know, if you try to migrate web forms and composite forms, they will error during the migration due to the additional dimension in the LCM file.  It wouldn’t be a huge deal to edit a few XML files, but when there are hundreds of them, it is extremely time consuming (and boring, which is what drove me to create this solution).

Assumptions

To fully understand this article, a basic understanding of XML is recommended.  The example below assumes an LCM extract was run on a Planning application and it will be used to migrate the forms to the same application without a CustomerSegment dimension.  It is also assumed that the LCM extract has been downloaded and decompressed.

Solution

I have been learning and implementing PowerShell scripts for the last 6 months and am overwhelmed by how easy it is to complete complex tasks.  So, PowerShell was my choice to modify these XML files in bulk.

It would be great to write some long article on how smart this solution is and overwhelm you with my whit, but there is not much too it.  A few lines of PowerShell will loop through all the files and remove the XML tags related to a predefined dimension.  So, let’s get to it.

Step 1 – Understand The XML

There are two folders of files we will look to.  Forms are under the plan type and the composite forms are under the global artifacts.  Both of these are located inside the resource folder.  If there are composite forms that hold the dimension in question as a shared dimension, both will need to be impacted.  Scripts will be included to update both of these areas.

Inside each of the web form files will be a tag for each dimension, and it will vary in location based on whether the dimension is in the POV, page, column, or row.  In this particular example, the CustomerSegment dimension is in the POV section.  What we want to accomplish is removing the <dimension/> tag where the name attribute is equal to CustomerSegment.

For the composite forms, the XML tag is slightly different, although the concept is the same.  The tag in composite form XML files is <sharedDimension/> and the attribute is dimension, rather than name.

Step 2 – Breaking Down the PowerShell

The first piece of the script is just setting some environment variables so the script can be changed quickly so that it can be used wherever and whenever it is needed.  The first variable is the path of the Data Forms folder to be executed on.  The second is the dimension to be removed.

# Identify the source of the Data Forms folder and the dimension to be removed
# List all files, recursively, that exist in the path above
$files = Get-ChildItem $lcmSourceDir -Recurse | 
where {$_.Attributes -notmatch 'Directory'} |

The next piece of the script is recursing through the folder and storing the files in an array.  There is a where statement to exclude directories so the code only executes on files.

# List all files, recursively, that exist in the path above
$files = Get-ChildItem $lcmSourceDir -Recurse | 
where {$_.Attributes -notmatch 'Directory'} |
Step 3 – Removing The Unwanted Dimension

The last section of the script does most of the work.  This will loop through each file in the $files array and

  1. Opens the file
  2. Loops through all tags and deletes any <dimension/> tag with a name attribute with a value equal to the $dimName variable
  3. Saves the file
# Loop through the files and find an XML tag equal to the dimension to be removed
Foreach-Object {

$xml = Get-Content $_.FullName
$node = $xml.SelectNodes(“//dimension”) |
Where-Object {$_.name -eq $dimName} | ForEach-Object {
# Remove each node from its parent
[void][/void]$_.ParentNode.RemoveChild($_)
}
$xml.save($_.FullName)
Write-Host “($_.FullName) updated.”
}

Executing The Logic On Composite Forms

The above concepts are exactly the same to apply the same logic on composite forms files in the LCM.  If this is compared to the script applied to the web forms files, there are three differences.

  1. The node, or XML tag, that needs to be removed is called sharedDimension, not dimension. (highlighted in red)
  2. The attribute is not name in this instance, but is called dimension.  (highlighted in red)
  3. We have added a counter to identify whether the file has the dimension to be removed and only saves the file if it was altered.  (highlighted in green)
The Script
$lcmSourceDir = "Z:\Downloads\KG04\HP-SanPlan\resource\Global Artifacts\Composite Forms"
$dimName = "CustomerSegment"
# List all files
$files = Get-ChildItem $lcmSourceDir -Recurse | where {$_.Attributes -notmatch 'Directory'} |
# Remove CustomerSegment
Foreach-Object {
  # Reset a counter to 0 - used later when files is saved
  $fileCount = 0

$xml = Get-Content $_.FullName
$node = $xml.SelectNodes(“//sharedDimension“) | Where-Object {$_.dimension -eq $dimName}  | ForEach-Object {
#Increase the counter for each file that matches the criteria
    $fileCount++
# Remove each node from its parent
[void][/void]$_.ParentNode.RemoveChild($_)
}
# If the dimension was found in the file, save the updated contents.
  if($fileCount -ge 1) {
$xml.save($_.FullName)
Write-Host “$_.FullName updated.”
    }
}

Summary

The first script may need to be run on multiple plan types, but the results is an identical folder structure with altered files that have the identified dimension removed.  This can be zipped and uploaded to Shared Services and used to migrate the forms to the application that has the dimension removed.

The scripts above can be copied and pasted into PowerShell, or the code can be Downloaded.




Adding a New Custom Dimension to HFM

Unlike a Planning application, adding a new custom dimension to an HFM application (after it has been deployed) requires a few extra steps to ensure a successful deployment. This post will provide step by step instructions on how to successfully complete the process.

Initially, I attempted to deploy my HFM application without following the steps that have been outlined in the post below. I received 8 warnings upon validation of the application prior to deployment:

In order to deploy the HFM application, we will need to log on to the server and stop some processes and services. Once you’re logged onto the HFM server, open up the task manager to begin shutting down a few key processes. You will need to right click and stop CASSecurity.exe, HsxServer.exe, & all of the HsvDataSource.exe processes:

Next, open up Services on the HFM server:

Right click and select to stop Oracle Hyperion Financial Management – Management Service (EPMA):

Return to the Task Manager and stop DMElistener.exe & HsxService.exe:

Now you can deploy the HFM application with the new custom dimension included. Check the job console to see that the deployment was successful:

Once deployment is complete, head back to the HFM server to start the Oracle Hyperion Financial Management – Management Service (EPMA) service to ensure that everything is back up & running.




Calc Manager: Fixing Corrupt Rules & Rulesets

I was recently testing out the performance of rulesets that were attached to forms, and ran into an issue that had me scratching my head. Certain rulesets were not running or even appearing on forms that they were attached to in the Planning application. Turns out, the rules had been corrupted in the transfer between Calc Manager and the Planning application

The issue first appeared when looking at a Planning form. Prior to the testing, I had attached a ruleset to the Brokerage and Commission form. However, when looking at the form, the ruleset was nowhere to be found:

However, with a little digging, I was able to verify that there is indeed a ruleset attached to the form:

So, where is the breakdown happening?

From the Planning application, let’s take a look at the Business Rules to see if we can gather any more information:

Notice that some of the rulesets say “None” instead of displaying the play button. Broke_Comm, our missing ruleset, is displaying “None”:

It looks like we found the issue. These rules have been corrupted and will not launch in the Planning application. That would explain why we were not seeing the rulesets on the forms. Some maintenance is required to get the rules back up and running. Here are the steps I took to fix the issue:

Open up Calc Manager and select deployment view:

Expand the “To Be Deployed” folder and uncheck all of the rules (this list of rules should match up with the corrupted rules – rules displaying “None” in the Launch column – from the Planning app):

Right click on the application in deployment view and select Deploy:

In Planning, verify that there are no longer any non-launchable rules:

Next, head back to Calc Manager and check all of the rules that are under the “To Be Deployed” folder. Right click on the application and deploy. Navigate back to the Business Rules tab in the Planning application, where all of the rules should now be launchable:

Looking back at the Brokerage Commission form, the Ruleset that is attached is now displayed:

Note: This works most of the time, but sometimes following the above steps will not bring back all of the corrupted rules/rulesets. When this happens, I’ve found that the easiest solution is to:

  1. Take an LCM backup of Calc Manager in Shared Services
  2. Delete the rules that are corrupt
  3. Import the rules from the backup that was just taken
  4. Navigate to Calc Manager->Deployment View and Deploy all of the newly imported rules

This second option might take a little bit longer than the first solution outlined, but it will clean up your rules/rulesets and get them back to performing as expected.




Building A Planning Application Using EPMA (11.1.2.3)

This post covers the basics of building a Planning app. Building a Planning application can be a straightforward process; however, there are some pitfalls to watch out for. One or two mistakes can lead to some major headaches in trying to decipher what went wrong. Whether a beginner or a seasoned vet looking for a quick refresher, this guide will outline the steps necessary to successfully create/deploy a Planning application.

First, open up the dimension library, and then click File, New, and Application. This will take you to the first screen in the Application setup: Application Type

 

 

A. Application Type

There are 3 sections that make up the first screen

  • Application Information
  • Planning
  • Calendar

We will tackle them one at a time…

Application Information:

  1. In this first section, give a name for your application, as well as select Planning from the type dropdown. Giving the application a description is optional
  2. Since we are creating an application using dimensions from the EPMA library, leave “Create Blank Application” & “Auto Create Local Dimensions” unchecked

Planning:

  1. Select the application type (typically this will be General), and select the default currency for the application. You can select “Use Multiple Currencies” but this is not the most effective way of going about it. For more detailed insight on the utilization of multiple currencies in a Planning app, check out the following article:
  2. Select and name the Plan Types that will be in the application. From the screenshot below, the application will have 3 plan types (Plan1, Plan2, Plan3)

 

Calendar:

  1. Monthly, Quarterly, and Yearly application setup.
    • Base Time Period: This can be 12 months, Quarters, or Custom (if you need a lower level of detail such as weeks)
    • Fiscal Year First Month/Fiscal Year Start Date: Set the first month of the fiscal year as well as if it is the same calendar year or the previous calendar year
    • Weekly Distribution: There are 4 options from this dropdown (Even, 445, 454, 544)
  2. CAUTION: For the following section, both the Period and Year dimensions should be renamed to “Periods” & “Years” if Dynamic Time Series is going be turned on for the application (M-T-D, Q-T-D, Y-T-D, etc). If the dimensions are named Year and Period, Planning will not be able to differentiate between the dimensions and the dynamic time series members, causing errors.
  3. Name the dimension “Years” and select the first fiscal year/number of fiscal years. The number of years ranges from 1-100.

After completing all of the above settings, click next to move on to the next screen, Dimension Selection:

 

B. Dimension Selection

This screen will have sections for the required, custom, as well as other dimensions for the application. First, the screen will auto populate whatever dimensions are already in the EPMA library. In this example, Entity, Account, and Alias are already filled in. The remaining dimensions (Version & Scenario) need to be populated via the dropdown menu:

These dimensions will be added as local dimensions to the application. Give the dimensions a name and make sure that the type matches the dimension that is being updated:

After setting the required dimensions, the custom dimensions can be selected in the next section. By clicking on the “[Select]” dropdown, the rest of the dimensions from EPMA will be available for selection in the application (note: your dropdown menu will not display the same selection as in this screenshot, as these dimensions had to be created in the Shared Library first):

If there are any Attributes, Smart Lists, or UDA’s that need to be added, the “Other Dimensions” section is the place to do that. Notice that there are 3 different Attributes to choose from in our Test application (I created these in the Shared Library beforehand as an example).

Reviewing the dimensions, there are 4 new local dimensions (Version, Scenario, Years, & Periods), 3 custom dimensions (Customer, Product, & BusinessUnit), & 3 other dimensions (Attribute, SmartList, & UDA). Click Next to go to the Application Settings screen.

 

C. Application Settings

  1. The entire dimensionality of the new application is available to view/edit. Exclude/add members to the hierarchy as necessary. Make sure that all the dimensions are accounted for.
  2. Properties of the application: This is a review of the application settings from the first screen. Double check that base time period, fiscal start year, and fiscal year first month are set. Also, make sure that the default alias table is set.

After making those changes, click Validate. A list of errors and warnings will populate below. These errors and warnings need to be taken care of before clicking Finish. There are a wide variety of errors that may be encountered such as duplicate members, duplicate aliases, invalid members, invalid plan types, etc. depending on your application. After fixing an error, click validate again and the error will disappear from the list.

Once there are no more errors, the application is ready to be deployed. Click Finish and go to the Application Library and right click, then deploy the application.




Essbase Database Restructures

Changes to an Essbase outline cause changes to the Essbase index and data files, regardless of the method (Essbase Administration Services, Hyperion Planning database refreshes, or from a script).

Changes that require restructuring the database are time-consuming (unless data is discarded before restructuring).  Understanding the types of restructures and what causes them can help database owners more effectively manage the impacts to users.

TYPES OF RESTRUCTURES

Essbase initiates an implicit restructure after an outline is changed, whether done with the outline editor, through an automated build, or some other fashion like a Hyperion Planning database refresh.  The type of restructure that is performed depends on the type of changes made to the outline.

DENSE RESTRUCTURE:  If a member of a dense dimension is moved, deleted, or added, Essbase restructures the blocks in the data files and creates new data files. When Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks. Empty blocks are not removed. Essbase marks all restructured blocks as dirty, so after a dense restructure you must recalculate the database. Dense restructuring, the most time-consuming of the restructures, can take a long time to complete for large databases.

SPARSE RESTRUCTURE:  If a member of a sparse dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the time required depends on the index size.

Sparse restructures are typically fast, but depend on the size of the index file(s).  Sparse restructures are faster than dense restructures.

OUTLINE ONLY:  If a change affects only the database outline, Essbase does not restructure the index or data files. Member name changes, creation of aliases, and dynamic calculation formula changes are examples of changes that affect only the database outline.

Outline restructures are very quick and typically take seconds.

Explicit restructures occur when a user requests a restructure to occur.  This can be done in Essbase Administration Services or via Maxl (and EssCmd for those of you who still use it) and forces a full restructure (see dense restructure above).  It is worth noting that this also removes empty blocks.

CALCULATING IMPLICATIONS AFTER RESTRUCTURES

When a restructure occurs, every block that is impacted is tagged as dirty.  If Intelligent Calculations are used in the environment, they don’t provide any value when a dense restructure occurs as all blocks will be calculated.  When member names or formulas are changed, the block is not tagged as dirty.

WHAT DICTATES THE RESTRUCTURE TYPE

The following outline changes will force a dense restructure, which is the most time- consuming restructure.

DENSE AND SPARSE

  • Defining a regular dense dimension member as dynamic calc
  • Defining a sparse dimension regular member as dynamic calc or dynamic calc and store
  • Defining a dense dimension dynamic calc member as regular member
  • Adding, deleting, or moving dense dimension dynamic calc and store members
  • Changing dense-sparse properties [Calc Required]
  • Changing a label only property [Calc Required]
  • Changing a shared member property [Calc Required]
  • Changing the order of dimensions [Calc Required]

DENSE (DATA FILES)

  • Deleting members from a dense dimension  [Calc Required]
  • Adding members to a dense dimension
  • Defining a dense dynamic calc member as dynamic calc and store member

SPARSE (INDEX)

  • Adding members to a sparse dimension
  • Moving members (excluding shared members) in a sparse dimension
  • Defining a dense dynamic calc member as dynamic calc and store
  • Adding, deleting, or moving a sparse dimension dynamic calc member
  • Adding, deleting, or moving a sparse dimension dynamic calc and store member
  • Adding, deleting, or moving a dense dimension dynamic calc member
  • Changing the order of two sparse dimensions

NO RESTRUCTURE OCCURS

  • Deleting members of a sparse dimension [Calc Required]
  • Deleting members of an attribute dimension
  • Deleting shared members from a sparse or dense dimension [Calc Required]
  • Adding members to an attribute dimension
  • Adding shared members to a sparse or dense dimension
  • Moving a member in an attribute dimension
  • Renaming a member
  • Changing a member formula [Calc Required]
  • Defining a sparse dynamic calc member as dynamic calc and store member
  • Defining a dense or sparse dynamic calc and store member as dynamic calc
  • Defining a regular dense dimension member as dynamic calc and store
  • Defining a sparse dimension dynamic calc and store member or dynamic calc member as regular member
  • Defining a dense dimension dynamic calc and store member as regular member
  • Changing properties other than dense-sparse, label, or shared [Calc Required]
  • Changing the order of an attribute dimension
  • Creating, deleting, clearing, renaming, or coping an alias table
  • Importing an alias table
  • Setting a member alias
  • Changing the case-sensitive setting
  • Naming a level or generation
  • Creating, changing, or deleting a UDA

WHAT DOES THIS MEAN

Understanding this can help users and administrators manage applications to better meet the needs of all those involved.  When designing an application, knowledge of this topic can be instrumental in the success of the application.  Here are some things to keep in mind.

  • When updating an outline or refreshing a planning application, it may be faster to export level 0 (or input level) data, clear the data, perform the update, and reload/aggregate the export when  changes cause a dense restructure.
  • For dimensions that are updated frequently, it may be beneficial to define those dimensions as sparse.  Changes to sparse dimensions typically require only restructures to the index file(s), which are much faster.
  • If frequent changes are required, enabling incremental restructuring may make sense.  Using this defers dense restructures.  The Essbase restructure happens on a block by block basis, and occurs the first time the data block is used.  The cost is that calculations will cause restructures for all the blocks included and the calculation performance will degrade.
  • Setting the isolation level to committed access may increase memory and time requirements for database restructure.  Consider setting the isolation level to uncommitted access before a database restructure.
  • If multiple people have access to change the outline, outline logging may be useful.  This can be turned on by adding OUTLINECHANGELOG = TRUE in the essbase.cfg.
  • Monitoring progress of a restructure is possible when access to the server is granted.  Both sparse and dense restructures create temporary files that mirror the index and data files.  Data exists in the .pag files while indexes are stored in .ind files.  As the restructure occurs, there are equivalent files for each (pan for data files and inn for index files).  In total, the restructure should decrease the size of the ind and pag files, but the pan and inn files can be used for a general idea of the percent of completion.