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.
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?
Who is in my groups?
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:
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:
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.
** 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.
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).
Upload and Import the Zipped Shared Services Files
From within Shared Services, right-click on the File System. Select Upload.
Browse to the zipped Shared Services files. Select Finish, then OK.
Next, expand the file within the Shared Services File System. Select the Groups.
Select Import. Ensure that the groups were created successfully and that the users were added as expected.
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.)
Re-zip the Shared Services LCM files, upload the file and import the Planning Assigned Roles.
At this point, all of the groups should be provisioned as Planners.
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:
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.
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:
Oracle\Middleware\user_projects\epmsystem1\Planning\planning1
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:
US_Read,US,READ,@IDESCENDANTS
We can now craft our full import file using this same format.
In Excel, we can build a file like this:
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:
Oracle\Middleware\user_projects\epmsystem1\Planning\planning1
Upload the security with the following command:
ImportSecurity.cmd “SB_BSO,admin,SL_COMMA,0,”
Check the ImportSecurity.log in the following directory:
Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\planning
Look for something like this:
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:
- EPM Security Maintenance is centralized in a single workbook.
- EPM Security is self-documenting. (It’s all in the Excel workbook.)
- The EPM Security Workbook makes a good security backup.
- The EPM Security Workbook can be versioned and archived.
- Security migrations are simplified because the same files can be imported across multiple environments.
- Importing files is infinitely faster than creating security manually.
A copy of the workbook used in these examples is available here.
