Author: jturrell

EPM Pro Tip: Build & Maintain Security in Excel

Agatha Christie once argued against the notion that necessity is the mother of invention. She said, “Invention, in my opinion, arises directly from idleness, possibly also from laziness – to save oneself trouble”. This is the guiding principle I follow when designing and building security models for Hyperion Planning and Essbase. So how can a bit of laziness take the “trouble” out of EPM security? By leveraging Excel and letting formulas do the heavy lifting. This is an approach I’ve been using for over a decade, and I’m certain I’m not the only one. Although some of the tools have changed (think LCM), the general approach has been the same.

Step 1: Create a user-friendly, table-like input worksheet where administrators can enter security information.

Step 2: Create separate “export” worksheets for each of the basic components of EPM security (Shared Services, Essbase, Planning, etc.).

Step 3: Take the content from the input worksheet(s) and link it to the “export” worksheets with formulas, placing everything into the correct format.

Step 4: Import security & check for errors.


Show & Tell

Let’s look at a simple example. Keep in mind that based upon your particular requirements, this approach can have a lot of variations. For example, you may not be able to create your own groups, because your organization prefers groups to be maintained in Active Directory. In our example, we’ll use native Shared Services users and groups. The same principles apply . . . you’ll just have to tweak things a bit.

In our example, our administrator has to maintain security in a Hyperion Planning application and a native Essbase reporting cube. The applications both have the same users, and they have similar security requirements. We’ll use my Starbucks Hyperion Planning demo. It contains all of the global Starbucks, Teavana and Seattle’s Best Coffee locations. (The locations are real, but everything else is made up . . .) We’ll create groups for the 12 main countries/regions in which Starbucks operates.

9-29-2015 9-00-25 PM

In the examples below, an Excel workbook will be highlighted.  The full workbook can be downloaded here.

 The Simple Input Worksheet

In our “simple input worksheet”, we will need some basic information. We’ll need to know:

  • What are the groups with access to the EPM products?
  • What members can these groups access?
  • What is the level of access?
  • Who is in the groups?

Here is what our simple input worksheets look like. In our example, we will create 24 groups (one read and one write for each country/region). We will also add one test user to each group. We will assume that all groups have access to both Planning and Essbase.

What are my groups and what can they access?

9-30-2015 12-48-33 AM

Who is in my groups?

9-29-2015 9-22-59 PM

The Lazy Bit

Now that we have collected the basic information we need, we can begin to create our outputs. These are the files that we will import into Shared Services, Essbase and Planning to actually build our EPM security.

We’ll start by creating an LCM import file that creates our groups. However, we may not know how to format this file. How do we get around this? Follow these steps:

  • Manually create one native group in Shared Services.
  • Manually add a user to the group.
  • Manually provision the group.

I provisioned my sample group like this:


Next we need to export the security we just created manually in Shared Services. This will give us an idea of what the Shared Services import files should look like.

Here’s what we will export from Shared Services in LCM:

9-29-2015 10-03-25 PM

Note that even groups and users that were created in Active Directory will be exported when the “Native Directory” above is selected, assuming they have been provisioned.

Select the Export button. Once the export is complete, browse to the file under the File System. Right-click on the file and select Download. Save the file to your desktop and unzip it. The following file structure will be available:

9-29-2015 10-12-00 PM

If we edit the Groups.csv file, we can immediately see the format of the file we need to create that will ultimately be used to create the new native groups in Shared Services. In this file we see the group that was previously created manually and we see the test user that was assigned to the group.

9-29-2015 10-14-08 PM

** Note – Leave the internal_id above blank when creating the new import file.

** Note – If you’re using Active Directory users or groups, your providers will be slightly different.

Create the Shared Services Group Import File

Using the same basic format as our exported Groups.csv file, we can create a new file (within our EPM Security Workbook). This file will create our groups and associate our users with the groups.

It’s important to note that many of the cells below are populated with simple formulas that reference the Groups_and_Entities or the Groups_and_Users worksheet. Note the formula circled below.

9-29-2015 11-01-23 PM

This file can either be saved as the Groups.csv file (in the previously unzipped folder structure), or the administrator can copy and paste the results into the Groups.csv file (the one previously exported from Shared Services). Then, simply zip the files back up (starting at the top of the LCM file structure).

9-29-2015 11-05-44 PM

Upload and Import the Zipped Shared Services Files

From within Shared Services, right-click on the File System. Select Upload.

9-29-2015 11-08-05 PM

Browse to the zipped Shared Services files. Select Finish, then OK.

Next, expand the file within the Shared Services File System. Select the Groups.

9-29-2015 11-11-55 PM

Select Import. Ensure that the groups were created successfully and that the users were added as expected.

9-29-2015 11-16-52 PM

Provision the Groups

Now that the groups are imported and the users have been added to the groups, it’s time to provision the groups. Navigate to the Assigned Roles directory in the previously exported LCM file.

2015_09_29_SS_Security\HSS-Shared Services\resource\Native Directory\Assigned Roles\Planning

Modify the Planning application file to provision each of your groups, again, using formulas to reference the groups in your Groups_and_Entities tab. Pay special attention to the role numbers. (This is why we create a Group and User manually first, and then export the file. This way, we can copy items such as the role number – something we otherwise would probably not know.)

9-29-2015 11-24-10 PM

Re-zip the Shared Services LCM files, upload the file and import the Planning Assigned Roles.

9-29-2015 11-29-49 PM

At this point, all of the groups should be provisioned as Planners.

9-29-2015 11-34-37 PM

Repeat this process and provision the groups for Essbase and FR (based upon the files previously exported containing our manually created test group). Groups must be provisioned in Shared Services for Essbase and Planning before application-specific security can be applied.

Create Essbase Filters and Grant Them to Groups

Woo hoo! At this point, we’re finished with Shared Services and LCM. All of the groups have been created. Users have been added to the groups. All of the groups have been provisioned. And all of this has been done by importing files built based upon simple formulas.

Next we need to create our Essbase filters. For this, we will use Excel formulas to craft MaxL statements. We can paste these statements directly from Excel into the MaxL command prompt.

Let’s start with the basic MaxL that we want in our Security workbook. The MaxL syntax can be found here.


Note the common practice of locking down a dimension with “no_access” and then selectively opening it up with read or write lines.

If we paste the MaxL into a MaxL command line, we’ll see that the filter is created and granted to the specified group. But what if we want to create these commands in bulk in Excel? We simply use formulas with the concatenate function.

Using Excel formulas, we can craft the following file:

9-30-2015 12-57-21 AM

The MaxL above is broken up into four columns. These columns have the following formulas:

Column A: =CONCATENATE(“create or replace filter SB_ESB.SB_ESB.f_”, Groups_and_Users!A3)

Column B: =CONCATENATE(” no_access on ‘@IDESCENDANTS(“”Location””)’, “)

Column C: =IF(RIGHT(A3, 4) = “Read”,CONCATENATE(” read on ‘@IDESCENDANTS(“””, Groups_and_Entities!B3, “””)’; “),CONCATENATE(” write on ‘@IDESCENDANTS(“””, Groups_and_Entities!B3, “””)’; “))

Column D: =CONCATENATE(“grant filter SB_ESB.SB_ESB.’f_”, Groups_and_Entities!A3, “‘ to ‘”, Groups_and_Entities!A3, “‘;”)

Note the use of spaces and quotes in the formulas above. Also note that by placing the words “read” or “write” in the actual group names, we can reference this in the formulas when building the filters, thus granting either read or write access.

Once our formulas are sorted, we can simply paste the MaxL directly from Excel into a command prompt. Our filters are immediately created and granted to all of our groups. Subsequent changes are as easy as a copy/paste into MaxL.

9-30-2015 12-54-20 AM

Create a Hyperion Planning Security Import File

The last step in our process is to create the file to import security into our Hyperion Planning application. Again, we will use Excel and basic formulas. Like LCM, it can be helpful to add a minimal amount of security to Hyperion Planning manually, and then export a sample secfile.txt file. This can be helpful when determining the format of the import file.

To export a Planning security file, navigate to the Planning directory on the server:


Execute the following command (update the app, user ID and file location):

ExportSecurity.cmd /A=SB_BSO,/U=admin,/TO_FILE=C:\\temp\\SecFile

Review the security export file.

Our test security export contains the one piece of security information we assigned manually in Hyperion Planning:


We can now craft our full import file using this same format.

In Excel, we can build a file like this:

9-30-2015 1-37-40 AM

Here is the Excel formula that concatenates this information from the Groups_and_Entities tab:

=CONCATENATE(Groups_and_Entities!A3, “,”, Groups_and_Entities!B3, “,”, Groups_and_Entities!D3, “,”, Groups_and_Entities!C3)

We can then copy the contents of this file into the secfile.txt in the planning folder. Unfortunately, the ImportSecurity utility must generally be run from the server, in the following directory:


Upload the security with the following command:

ImportSecurity.cmd “SB_BSO,admin,SL_COMMA,0,”

Check the ImportSecurity.log in the following directory:


Look for something like this:

9-30-2015 1-40-01 AM

In Summary . . .

Building security manually is a tedious, time-wasting exercise. And in the end, administrators are left with a manual process to maintain. By centralizing security development in an Excel workbook, developers can create exports that feed the various EPM components. While these exports require different formats for LCM, Essbase and Planning, the mechanics that produce the files are similar. Developers can use basic Excel formulas to craft files into the correct import file formats. Then import the files.

This approach has several benefits:

  1. EPM Security Maintenance is centralized in a single workbook.
  2. EPM Security is self-documenting. (It’s all in the Excel workbook.)
  3. The EPM Security Workbook makes a good security backup.
  4. The EPM Security Workbook can be versioned and archived.
  5. Security migrations are simplified because the same files can be imported across multiple environments.
  6. Importing files is infinitely faster than creating security manually.

A copy of the workbook used in these examples is available here.

Essbase Pro Tip: Build & Maintain Partitions in a Text Editor

Partitions in Essbase are a bit like views in a relational database. They allow the results of a query in one cube to be available in another cube. This data may be “materialized”, or physically stored in both the source and target. These are referred to as replicated partitions. Or the partition may simply provide a “window” from one cube into another. These are referred to as transparent partitions. A third type of partition, linked, isn’t really a partition at all . . . it’s more of a navigational aid. It’s important to note that linked partitions are deprecated as of Essbase That’s a nice way for Oracle to say “stop using linked partitions even though the option is still available”.

Embrace the Command-Line

Most Essbase professionals (myself included) learned to build partitions in EAS. It has a nice tabbed GUI that walks users through the various steps:

  • Type (Replicated, Transparent or Linked)
  • Connection (Source & Target Connection Info)
  • Areas (Source & Target Details)
  • Mappings (How to Handle Specific Differences Between Source & Target)
  • Validation (Is everything correct?)

There’s nothing “wrong” with this GUI . . . in the same way that there’s nothing “wrong” with training wheels. But for those of us that are comfortable in a command-line environment, a text file with good ole MaxL is just more straight-forward.

Why Ditch the GUI?

  1. MaxL is safer.
  2. MaxL is faster.
  3. MaxL is easier.
  4. MaxL is repeatable and migratable.

Have you ever “lost” a partition? Maybe it became orphaned and was no longer editable or repairable in EAS? It’s a frustrating experience, and unfortunately there are a variety of reasons partitions can head south. Building and maintaining partitions in a text editor using MaxL creates a tidy little backup. Re-creating the partition or migrating it to another environment is a snap. Simply paste some text into the MaxL command-line and you’ve got a partition!

And speaking of migrations, it’s important to note that LCM cannot currently migrate partitions (as of version The LCM Guide recommends using MaxL for partition migrations. I recommend taking this a step further. Create and maintain your partitions in MaxL in the first place.

Show me the MaxL!

The typical MaxL statement for creating a partition can be broken down into seven sections:

  1. Login (Source)
  2. Create Command
  3. Source Area Definition
  4. Login (Target)
  5. Target Area Definition
  6. Mappings
  7. Logout

In the example below, I’m partitioning data from a Planning application (BSO) called “SrcApp”, into an ASO reporting cube called “TgtApp”. The Planning app (source), has a Version dimension, but the target does not. The target has a “Profit Center” and “Cost Center” dimension, however the source does not.


You may have noticed that there are no area alias “nicknames” in the statement above, and there is only a single area definition on the source and target. Multiple areas are allowed, and mappings at the bottom of the statement can be area-specific. But I usually find that one area definition (and global mappings) gets the job done.

Here’s what you should expect to see when the partition is pasted into MaxL:


Review your results, looking for errors, warnings and in particular, cell-count mismatches.

Final Pointers

Here are some tips that may help when creating your partitions in MaxL:

  • Enclose area definitions in single quotes.
  • Separate members and functions within area definitions with commas.
  • Do not separate mapping lines with commas.
  • The first set of parenthesis in a mapping statement represents the source.
  • The second set of parenthesis in a mapping statement represents the target.
  • Map a member to or from a null value when a dimension in the source does not exist in the target (or vice-versa). In the example above, “Final Version” is mapped to “”, because there is no Version dimension in the target cube.
  • End the entire statement with a semi-colon.
  • Do not store passwords in MaxL files unless they’re encrypted.
  • If you’re using Essbase clustering, make sure you login to the cluster name when you create your partition, and not the server name.
  • Create partitions with service accounts, preferably ones with passwords that do not expire.

Hyperion Planning Patch

I’m on a team that recently decided to patch a client environment, upgrading Hyperion Planning from the .501 patch to the .700 patch.  We quickly started having deployment issues related to unknown members in member formulas.  This was with a “classic” Hyperion Planning application, not an EPMA app.

We soon realized that we had issues with member formulas that referenced members with names that included a “%” sign.  An example is below:


The screenshot above represents a brand-new formula.  It hadn’t been saved, and it hadn’t been validated.  (As you have probably guessed, these two actions led to issues.)

Upon validating (or saving) the formula, an error was introduced.  Interestingly, the validation returned with a “successful” message.


The number 25 was appended to the “%”.  Subsequent validations added another “25”.  The application repository was clean – formulas there were correct.  LCM exports also looked good.  But saving formulas suddenly got risky.  This application had some very, very complex member formulas.

My immediate reaction was to start updating member names and formulas, replacing “%” with “Pct”.  This would normally get the job done, however this particular application had a LOT of members with “%” in the name.  Some of these members were referenced in FDMEE maps . . . and reports . . . and business rules . . . and partitions.  Renaming was going to be a pain.  Luckily we had another unpatched environment where development could move forward while we worked the SR.  This leads us to a conversation regarding patch best practices:

  • Read the Readme file!  It details known issues and fixes addressed by the patch.
  • Patch a Development or Sandbox environment first.
  • Have a back-out plan.
  • If you have apps in Production, make sure you plan for a thorough regression test.
  • Schedule your patches for “low-disruption” time periods.  (Don’t patch right before year-end close or go-live.)
  • If you can, wait for others to patch first.  (The best testers are strangers who don’t bill your project.)