This article demonstrates how to generate and maintain leaf-level member security settings based on attribute dimension within a global Hyperion Planning application, using only ODI and Hyperion Planning application metadata repository information.
By Ricardo Giampaoli
and Rodrigo Radtke
Abstract
In some Hyperion Planning projects, security becomes so complex that it requires more than just granting some security groups access to the high-level members of the dimensions. Global companies must often create multiple planning applications to meet the requirements of diverse regions. But what happens when the business requires a single application, with a single plan type, that contains cost centers from different regions across the entity’s hierarchy? Or when that data is restricted according to the region’s security group using only one attribute dimension? Or when each user needs to see aggregated values correctly for their region only?
This article demonstrates how to generate and maintain leaf-level member security settings based on attribute dimension within a global Hyperion Planning application, using only ODI and Hyperion Planning application metadata repository information.
Introduction: The Case Study
RUM (Revenue under Management) was a global Hyperion Planning application in which cost centers were split by cubes (one cube per region: AMER, APJ, EMEA and WWOPS). To leverage its forecast process cycles, the business wanted to change the application so it would contain all cost centers from all regions (22,000+) in the same cube.
Security should be granted so that users could see only the cost centers of their region, identified by an attribute dimension called "Support Geography." In addition, the aggregation at the parent level should respect user access, which means that, if we have three children under a parent, each one from a different region, a user with access to, say, the AMER region should see only the AMER amount at the parent-level rollup.
While there is no doubt that Hyperion Planning security is robust and extremely flexible-the tool does enable administrators to control access at the member level-it does not allow attribute dimensions to be used as filters in the security configuration. This limitation demands the creation of custom security processes using some external tool and/or an ImportUtility tool.
The problem is how to create a process that can handle a quickly changing environment and that is easy to maintain. Some companies generate security files using Microsoft Excel, but in large Hyperion environments, which can contain more than 22,000 cost centers and more than 70 security groups, it gets a little hard to maintain such a file.
Aggregation Solution
Figure 1: Wrong Aggregation
The business requirements state that the rollup aggregation should respect the users' region. Since the Entity dimension contains cost centers from different regions under the same parent, a solution to split the data across the cube should be created to prevent wrong data rollup, as it can be seen in Figure 1. For Global users, the rollup would be correct, but for an EMEA user, with access only to EMEA cost centers, the rollup should be $1000 instead of $3000.
In order to fix that, a Regions dimension was created to segregate the data into different slices. Figure 2 shows what this Regions dimension looks like in the Hyperion Planning application.
Figure 2: Region Dimension
This dimension makes it possible to split the data of each region inside the cube and to see all the regions' data together under the "Total Regions" member. Since an EMEA user will always insert data in the EMEA cost centers and in the "Emea Region" member (the same applies to the other region users), a simple data aggregation in the Entity dimension will result in the right rollup to the parent level. For Global users, all data will be available at the "Total Regions" member, since it is a Dynamic Calc member. Figure 3, below, shows what a retrieve looks like in this architecture:
Figure 3: Correct Aggregation
The security setting is simple in this dimension. All groups that should have access to a specific region are granted member access to that region’s member (e.g., EMEA users will have access to the “Emea Region” member). This way, all regional users will be able to see only their own region member. For global users, an access to the “Total Region” member and its descendants would suffice.
Global users generally do not insert data into the application, they just use the “Total Region” member to read what the regional users inserted. If a Global user needs to insert data into the application, he would select the region to which that cost center belongs (as Figure 3 demonstrates).
The challenge: to create the security settings in the Entity dimension using an attribute dimension as filter, knowing that under any parent we could have multiple cost centers from different regions of the globe.
Security Solution
The second business requirement states that users should see only the cost centers that belong to their region. The Support Geography attribute dimension defines to which region that cost center belongs, but Hyperion Planning does not have a proper security filter to use with those attributes. This compels us to create a custom solution, the core piece of which is right under the Hyperion Planning application—more specifically, in its metadata repository. Querying the Hyperion Planning application repository, we can build the entire security from bottom up and create a security file or even populate the security tables with the correct granted security using any type of rule imaginable. This is a totally dynamic solution, meaning almost zero maintenance work.
To make this process work, the following information is needed:
- The Entity members and their Support Geography attribute
- The Support Geography hierarchy to find out to which region that attribute belongs
- The existing security groups that must be granted access
Some assumptions are needed for the security group’s creation:
First, the security group’s name must include the region to which it belongs, since it will be used to match the Support Geography attribute with the correspondent security groups (Figure 4).
Figure 4: Security Groups
Support Geography attributes are associated with the leaf members of the Entity dimension. The top-level member of Support Geography also contains the region to which that attribute member belongs, as can be seen in Figure 5.
Figure 5: Support Geography
Second, the security group needs to be initially granted at Generation 1 and 2 of the Entity hierarchy, since theywill be used as an allocation guide for the rest of the Entity dimension. Generation 1 will be the all-channel access security groups restricted by region, and Generation 2 will be the security groups that have access only to a specific Business Segment under a specific region (Figure 6).
Figure 6: Entity Dimension
Third, the security access flag must be set as “member,” because security access will be granted member-by- member for all security groups that have a region in their name. No other security access flag can be used since that could wrongly give access to undesirable cost centers.
Access mode granted for Generation 1 and 2 will be used to discover which security groups must be spread throughout the Entity dimension. This will allow the query to retrieve the main lines and build the desired security (i.e., what channel/business segment/region that user will have access to). Figure 7 illustrates this setup:
Figure 7: Security Configuration
Cost Center 223281 is associated with the SUPP_Netherlands Support Geography attribute. This attribute belongs to SUPP_EMEA, which means that all security groups that exist in Generation 1 and 2 of Entity with the EMEA region in their name should be spread, with access flag set to “member,” from this cost center up to its parents.
Planning Repository Overview
After this initial setup of dimensions, attributes and security groups at the Hyperion Planning application, the custom security SQL can be built. All the necessary information regarding metadata is stored at the Hyperion Planning repository.
When an application is created in Hyperion Planning, a schema in a database is needed. This schema will contain 138 tables (version 11.1.2.3) with all the information about that application, including configurations, forms, users, groups and, more important, the metadata.
Metadata is "data about data." In our case, it is all the members, attributes and properties in all the dimensions of a specific Planning application.
This is what is refreshed to Essbase when a database refresh is executed.
Just seven tables are needed to accomplish the goal of this article:
- Three tables to get the groups/users and the current security settings
- Three tables for the attribute hierarchy
- One table to get the object’s metadata information
Security Tables
Hyperion Planning stores all its security in only three tables:
- HSP_USERS: Used if an user is assigned directly to an object in Planning
- HSP_GROUP: Used if a group is assigned directly to an object in Planning
- HSP_ACCESS_CONTROL: Used to associate a user or group to an object and also to inform which access it will be assigned and if this access will be spread to its children or only to it
The structure of these tables is as follows:
|
Column Name
|
Description
|
|
USER_ID
|
The user id created after a user login or assigned to any object in Hyperion planning.
|
|
SID
|
The native or external directory ID
|
Table 1: HSP_USER Description
|
Column Name
|
Description
|
|
GROUP_ID
|
The group id that is created after a user that belongs to a group login or a group is assigned to any object in Hyperion planning.
|
|
SID
|
The native or external directory ID
|
Table 2: HSP_GROUP Description
|
Column Name
|
Description
|
|
USER_ID
|
The user or the group id that is created after a group or a user is assigned to any object in Hyperion planning.
|
|
OBJECT_ID
|
The ID of the object that has been granted the security
|
|
ACCESS_MODE
|
The type of access that a user or a group can have on an object:
|
|
1 = Read
|
3 = ReadWrite
|
-1 = Deny
|
|
FLAGS
|
Essbase access flag, to determine if a user or a group has access only to that object or to the hierarchy below it:
|
|
0
Member
|
5
@Children
|
6
@IChildren
|
8
@Descendants
|
9
@IDescendants
|
Table 3: HSP_ACCESS_CONTROL Description
Attribute Tables
Attributes are defined using three tables:
- HSP_ATTRIBUTE_DIM: Stores all attribute dimensions
- HSP_ATTRIBUTE_MEMBER: Holds all attribute members stored in Planning
- HSP_MEMBER_TO_ATTRIBUTE: Joins the attributes with the members in a Dimension
|
Column Name
|
Description
|
|
ATTR_ID
|
Attribute dimension ID
|
|
DIM_ID
|
The ID of the dimension with which the attribute is associated
|
Table 4: HSP_ATTRIBUTE_DIM Description
|
Column Name
|
Description
|
|
ATTR_MEM_ID
|
Attribute member ID
|
|
ATTR_ID
|
Attribute dimension ID
|
Table 5: HSP_ATTRIBUTE_MEMBER Description
|
Column Name
|
Description
|
|
MEMBER_ID
|
ID of the member that has been assigned an attribute
|
|
ATTR_ID
|
Attribute dimension ID
|
|
ATTR_MEM_ID
|
Attribute member ID
|
Table 6: HSP_MEMBER_TO_ATTRIBUTE Description
Object Table
Planning objects are defined using one table:
- HSP_OBJECT: Contains information of all Planning objects as well the parent member relationship used to create all metadata structure. Central table used as source of all metadata in Planning.
|
Column Name
|
Description
|
|
OBJECT_ID
|
Object ID for all objects in planning
|
|
OBJECT_NAME
|
Stores all metadata description in Planning (e.g., Alias, Members)
|
|
OBJECT_TYPE
|
Type of the Object (e.g., Entity, Account, Attribute)
|
|
PARENT_ID
|
Parent ID of the object used to build the relationship with OBJECT_ID
|
|
GENERATION
|
Inform with member’s generation
|
|
HAS_CHILDREN
|
Inform if the member has a child member or not
|
Table 7: HSP_OBJECT Description (Only the columns used in this solution are displayed)
The HSP_OBJECT is the starting point of every metadata query to be created because all the metadata in planning is stored here. This includes the dimensions, forms, attributes, security users and groups, workflow, etc. In addition, this is the table where all hierarchies can be rebuilt using the Parent Child relationship.
Building the Security SQL
Entity Hierarchy
This paper’s detailed explanation around the custom security SQL creation is divided into steps, each containing a small part of the SQL logic that, in the end, will be merged into a single SQL that will return all the necessary information. It all starts with Entity Hierarchy SQL (Figure 8), where all members from the Entity dimension are retrieved along with their associated Support Geography attribute. This is the foundation of the security query and it will be used to spread the Support Geography attribute contained in the Entity leaf member to their parents.
Figure 8: Planning Repository Entry
To accomplish this, the Parent Child relationship in the HSP_OBJECT table will be used to recreate the hierarchy. The Oracle SQL language has a CONNECT BY function that is very useful for this situation.
CONNECT BY specifies the relationship between the hierarchy’s parent rows and child rows. In hierarchical queries, the expression PRIOR must be specified.
PRIOR is most commonly used when comparing column values with the equality operator. PRIOR causes Oracle to use the value of the parent row in the column.
CONNECT BY has some other expressions that increase its functionality. One of the most used is the START WITH expression. START WITH specifies the root row(s) of the hierarchy.
The traditional use of CONNECT BY is to create “Top Down” queries, which means that the starting point is a higher member of the hierarchy and goes down to the leaf members.
Since the process needs to spread the Support geography attributes from the leaf members to their parents, it needs to use a “Bottom Up” approach, meaning that it will start from the leaf members up to the higher parents and, depending on the outline design, it may cause CONNECT_BY to loop indefinitely.
Use of the NOCYCLE expression prevents this. The NOCYCLE parameter instructs the Oracle Database to return rows from a query even if a CONNECT BY LOOP status exists in the data. In other words, if the process does not use this expression, an infinite loop will be created, returning an error.
Because the CONNECT BY command was “inverted,” the SQL needs to use all leaf members from the Entity dimension instead of the top-level member in START WITH; therefore, a sub-query was created to filter only OBJECT_TYPE equal to 33 (Entity type ID) and HAS_CHILDREN = 0 to get only the leaf members.
A bottom-up approach was used because the Entity leaf member must be identified for each Entity parent that exists in the application. To accomplish that, the CONNECT_BY_ROOT command was used. CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When a column is qualified with this operator, Oracle returns the column value using data from the root row. Since the SQL is intentionally “inverted,” it will return the leaf member for each row, allowing the process to identify all the parents that belong to each leaf member, spreading the leaf attribute member through the hierarchy.
This will allow the process to join this query to the attribute dimension query and find out what region that leaf member and its parents belong to. Figure 9 shows the results:
Figure 9: Planning Repository Entity Query Result
Support Geography Hierarchy
Since the Region name is contained at the top-level member of the Support Geography attribute dimension, this information must be spread through the hierarchy, to identify the region of all attribute members (Figure 10).
Figure 10: Planning Security Attribute Query
This will be achieved using the CONNECT_BY_ROOT and CONNECT BY in a “top down” mode, having the START WITH command use the top- level member of the Support Geography attribute dimension instead of its leaf-level attribute members.
Figure 11 demonstrates the result of this query for the “SUPP_Netherlands” attribute member. Its region, and that of all its parents, is EMEA.
Figure 11: Planning Security Attribute Query Results<</b>/p>
Join 1: Entity + Support Geography
Since the Entity query has the ATTR_MEM_ID spread to all hierarchy, identifying the region of each Entity member is just a matter of joining this same column in the Support Geography query. Figure 12 shows that Entity 223281 has an Attribute member called “SUPP_Netherlands” that belongs to EMEA region. This also means that the entire hierarchy for member 223281 belongs to EMEA region.
It is worth mentioning that some parents will have multiple associated regions; that is a correct statement because this information will be used in a join later with all security groups, associating all the necessary region groups to each Entity member.
Figure 12: Planning Repository Entity and Attribute Join
Users/Groups Security
The next step is to figure out which Security Groups will be spread to which Entity members. To achieve that, the HSP_ACCESS_CONTROL table will be queried to retrieve all security that exists in Generation 1 and 2 of the Entity hierarchy.
It is import to filter only the FLAGS = 0 to return only the security that was set as MEMBER. This query will get all the security information related to Generation 1 and 2 of the Entity dimension, plus all the information regarding its access mode, if it is read/write/deny.
Figure 13: Planning Repository Groups Query
Figure 14 shows the results from the query in figure 13.
Figure 14: Planning Repository Groups Result
Join 2: Adding Security Groups
In Join 1, the regions of all the Entity members were identified. The Security query brought all the security group’s information, including its name, which contains the region to which this security group belongs. Join 2 is a “like” between the security group name and the Entity region name (Figure 15).
The security in Generation 1 of Entity is intended for users that have access to all business units, but restricted to its region. The security in Generation 2 restricts the region and also a specific business unit. At this point, this SQL has only the join between the security group and its region, which does not respect the business unit restriction. This will be fixed later with the last join of this query.
Figure 15: Planning Repository Entity and Groups Join
Generation 1 and 2 Parent Query
To implement the business unit restriction, another query will be used to bring Generation 1 and 2 members and their children. The CONNECT BY PRIOR will be used in a top-down style from the Entity Channel member to its leaf members.
But this is not enough to create the business unit filter, since it is represented by Generation 2 of Entity. In order to achieve this, SYS_CONNECT_BY_PATH function will be used. SYS_CONNECT_BY_PATH is valid only in hierarchical queries and returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition (in this case the pipe (“|”) character) (Figure 16).
Figure 16: SYS_CONNECT_BY_PATH Example
The path result contains each member’s complete hierarchy in one column. This needs to be split in two columns (Generation 1 and 2) and, for that, the REGEXP_SUBSTR function will be used. REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. This allows the strings between the pipe (“|”) characters to be found. The string after the first pipe returns Generation 1 and the string after the second pipe returns Generation 2.
Figure 17: Parents Query
Figure 18, below, demonstrates the results of Figure 17 using member 223281 as example:
Figure 18: Planning Repository Generation Query Results
Join 3: Putting Everything Together
Joining the last query with the previous one will allow the process to create the missing business unit restriction by creating a relationship between the security groups contained at the Generation 1 and 2 of Entity and its members. Since the security groups are set at Generation 1 and 2 of Entity, the join must consider both parent columns using an OR relationship. The final link is created, joining the Entity members’ ID contained in both queries (Figure 19).
This concludes the security query, which now contains all information to dynamically create any Hyperion Planning security based on any attribute dimension. In this paper, the security was based on Generation 1 and 2 because that was enough for the existing business requirements. If a more detailed Entity security setup is needed, it is just a matter of increasing the generation numbers in the SQL query as much as needed, since all the rest will continue the same.
Figure 19: Planning Repository All Together
Why ODI?
Now that the security SQL is ready to be used, it’s time to decide how to actually implement it and which tool should be used for this effort. Several tools could handle this SQL, but Oracle Data Integrator (ODI) was selected for the following reasons:
- Full flexible development platform
- Modify Knowledge Modules (KMs)and procedures to create dynamic processes
- Virtually accepts any existing technology
- Complete execution platform
- Built in security (only key users can use it)
- Easy to use
- Automatize, schedule and control jobs
- Complete log information
ODI 11g can be used to integrate several Enterprise Performance Management (EPM) tools—and especially Hyperion Planning applications, where its knowledge models can be used to maintain metadata and to load and extract data from its Essbase cubes. But the main reason to use ODI is to take advantage of its flexibility to customize its code. Its knowledge models and procedures can be tweaked to allow the creation of dynamic process with them. And because ODI is Java-based, it virtually accepts any existing technology out there—if a technology can be accessed using Java, ODI will also be able to access and manipulate it.
Regarding its execution platform, ODI comes as a complete package containing built-in security, which may also be integrated with a corporate external authentication method such as LDAP. ODI is extremely easy and intuitive to use; it may automatize, schedule and control an entire range of jobs and comes with complete log information regarding jobs execution (e.g., execution times, number of data manipulated in a certain step, complete job status, etc.). In sum, with the right architecture in place, ODI can be used as a full, flexible, development and execution platform.
In large Hyperion Planning architectures, ODI is used for the complete set of processes that manipulates data around the applications (e.g., automatization of metadata maintenance cycle, admin tasks like security updates, backups, database optimizations, inbound and outbound data jobs). There is only one detail that should be observed from all this: for now, only ODI version 11 can be used, since ODI version 12 does not yet support Hyperion Planning/Essbase.
Solution Design Choices
With ODI chosen as the development/execution tool for this solution, there is a need to decide how to develop this solution and apply the security SQL to the Hyperion Planning application. There are two ways to accomplish that:
- Generate a Secfile and run a command line at the end of the ODI process to load it into Planning (using ImportSecurity utility)
- Insert the security directly into the HSP_ACCESS_CONTROL table
Both solutions have pros and cons; Table 8, below, compares them:
|
ImportSecurity
|
Insert into Repository
|
|
No clear control (clear all or nothing)
|
Clear any type of security based on any rule (delete clause + repository)
|
|
No service restart
|
Service restart
|
|
No repository manipulation
|
Repository manipulation
|
Table 8: Import Security vs. Repository Manipulation
Solution 1: ImportSecurity utility
This solution uses the existing ImportSecurity utility that comes with Hyperion Planning to read a SecFile.txt (produced by the ODI process) and load it into the Hyperion Planning application. This very simple utility is used through an OS command line passing some basic parameters as input, as shown in Table 9:
ImportSecurity.cmd [-f:passwordFile] “appname,username,[delimiter],[RUN_SILENT],[SL_CLEARALL]”
|
Parameter
|
Description
|
|
[-f:passwordFile]
|
Optional: If an encrypted password file is set up, use as the first parameter in the command line to read the password from the full file path and name specified in passwordFile.
|
|
appname
|
Name of the planning application to which you are importing access permissions.
|
|
username
|
Planning administrator user name.
|
|
delimiter
|
Optional: SL_TAB, SL_COMMA, SL_PIPE, SL_SPACE, SL_COLON, SL_SEMI-COLON. If no delimiter is specified, comma is the default.
|
|
RUN_SILENT
|
Optional: Execute the utility silently (default) or with progress messages. Specify 0 for messages, or 1 for no messages.
|
|
[SL_CLEARALL]
|
Optional: Clear existing access permissions when importing new access permissions. Must be in UPPER CASE.
|
Table 9: ImportSecurity Definition
Note that the SecFile.tx is not mentioned anywhere in the ImportSecurity command line. This file must be saved at the Hyperion Planning bin directory in order to make it work. If Hyperion Planning was installed to the default location, the SecFile.txt will be located at this path: HYPERION_HOME/products/Planning/bin. Besides that, SecFile.txt is just a text-delimited file, with all the security that needs to be added to the Hyperion Planning application. Although it is simple, it must respect the following format in order to be correctly imported to Hyperion Planning (Table 10):
|
Item
|
Description
|
|
Username or group name
|
The name of a user or group defined in Shared Services Console.
|
|
Artifact name
|
The named artifact for the imported access permissions (e.g., the member, data form, task list, folder, or Calculation Manager business rule).
|
|
Access permissions
|
Read, ReadWrite, or None. If there are duplicate lines for a user/member combination, the line with ReadWrite access takes precedence.
|
|
Essbase access flags
|
@CHILDREN, @ICHILDREN, @DESCENDANTS, @IDESCENDANTS and MEMBER.
|
|
Artifact type
|
For artifacts other than members, use the artifact type identifier to distinguish which artifact you are importing security for.
|
Table 10: ImportSecurity SecFile Definition
One of the cons of loading the security through the ImportSecurity utility is that it does not have a good clear control; it contains only the option to clear all the security or nothing, which forces us to choose from two design options:
- Clear all existing Hyperion Planning security first and then load all the security definition from SecFile.txt (including both the old and the new definitions). The SQL presented in this paper returns the entire Entity security, so a new SQL query must be created to retrieve all the existing security that is not related to Entity.
- The security information is not cleared and the new security settings are added over the existing one, which may lead to some undesired old security in the application (e.g., when a cost center changes its Support Geography attribute, it will maintain the old Security Group). In this case, a SQL query must be created to discover the old undesired access; the result is emailed to an Admin and the undesired access is removed manually from the Hyperion Planning application.
The first option seems much more straightforward and requires no manual intervention by the Hyperion Planning admin. However, depending on how big/complex the application’s security, it may take a long time to refresh the Essbase security filters. Before making this decision, consider the current Hyperion Planning architecture.
Solution 1.1: ImportSecurity utility without SL_CLEARALL option
If SL_CLEARALL option is not an option and manual deletion of the old undesired security settings sounds feasible, two ODI interfaces will need to be created.
The first interface will load only the new security to SecFile.txt. This is easily done by creating two datasets inside an ODI interface, as shown in Figure 20:
Figure 20: ODI Interface to Create SecFile.txt
The first dataset contains the entire desired Entity security, which is represented by a temporary table called T$_HP_SECURITY; from now on, every time this table name is referenced in this paper, it is assumed that it contains the result of the queries shown in the first part of this paper. The T$_HP_SECURITY will be used in a minus operation against the security that already exists in Hyperion Planning related to the Entity, which is represented here by the OBJECT_TYPE = 33 filter at the ODI interface.
The target data store contains four columns that represent the necessary layout for the SecFile.txt file. The ARTIFACT_TYPE column was omitted here because MEMBER type is the onlyone used for this process (and it is the default when you leave it blank in the file). Some basic mappings were done in the ODI interface—decoding from Hyperion Planning codes to the codes accepted by the ImportSecurity utility—so ACCESS_PERMISSION 1 becomes Read, 3 becomes Write, and so on. There is also similar decoding for the ESSBASE_ACCESS_FLAG column. Once the job is executed and the file is loaded, it will contain only the new security, so it is just a matter of using this file to import to Hyperion Planning without the SL_CLEARALL parameter.
The second interface inverts the datasets orders, as seen in Figure 21:
Figure 21: ODI Interface to Load Old Security
The first dataset will read from what currently exists in Hyperion Planning, filtering only OBJECT_TYPE = 33 (which stands for Entity( and FLAGS equal to 0 (which stands for MEMBERS security type). The second dataset gets the data from the temporary security table containing the security that must be added to the Hyperion Planning application. When the first dataset is used in a minus operation with the second dataset, the query result will return all the old/undesired security that should not exist in the application anymore.
The target data store in this case can be in any format; in this example, for convenience, it’s in the same format as the SecFile.txt. Now we have only to create an ODI procedure that sends emails, attach this file to the email and send it to a Hyperion Planning admin to delete the security manually for those cases that will be showing in the file.
Solution 1.2: ImportSecurity Utility with SL_CLEARALL Option
If the SL_CLEARALL option is used, all the security settings from Hyperion Planning must be cleared first and then reloaded, both the new Entity security plus everything that already existed in the Hyperion Planning application that was not related to Entity or to the MEMBER security type. Just one interface needs to be created, but it will be slightly different from the other ones from Solution 1.1. This interface will perform a union operation on both datasets instead of a minus operation, as seen in Figure 22:
Figure 22: ODI Interface to Create SecFile.txt with Clear All Option
The first dataset is the temporary security table that contains only MEMBER security type for Entity dimension. The second dataset will be all the security on Hyperion Planning application that is not related to Entity (OBJECT_TYPE <> 33) and everything that is not a MEMBER security type (FLAGS <> 0).
This filter must be added because all security related to dimensions other than Entity is required here, as well as all the security settings from the Entity dimension that may eventually exist and that are not related to the MEMBER security type (i.e., Idecendants, Ichildren, etc.). The target table will be the same SecFile.txt data store that was shown before, with the same mappings to convert from Hyperion Planning codes to ImportSecurity utility codes.
Solution 1: Final procedure
Figure 23 shows the final ODI procedure for Solution 1, independently of the use of the CLEARALL option:
Figure 23: ODI Procedure to Run ImportSecurity Utility
This ODI procedure calls the ImportSecurity utility command, passing the necessary input parameters to it. The connection information from the “Command on Source” tab is set to our Hyperion Planning application. The ”Command on Source” and “Command on Target” tabs are heavily used in ODI since they allow the use of information set in the Source tab into a command in the Target tab, allowing a huge coding flexibility.
The Source tab can also be used similarly as a PL/SQL cursor, where a SQL query can be added on the Source tab, which may return N number of rows; for each one of those rows, ODI will execute whatever is in the Target tab.
Note that two ODI variables were used in this solution: #PLANNING_BIN_PATH indicates where the ImportSecurity utility is located under the Hyperion Planning install folder, and #PASSWORD_FILE indicates the full path where the password file is stored. Remember, this password file is necessary in this particular case because it is an automated process and there will be no human intervention to type the password at the prompt line. Also, an ODI option was added to select whether SL_CLEARALL was going to be used or not, so this procedure may be used in both approaches demonstrated in this paper.
Solution 2: Manipulate Hyperion Planning Repository
The second solution manipulates the HSP_ACCESS_CONTROL table directly using SQL. Although this option is easier, much more flexible and more powerful than the ImportUtility (the official tool for manipulating Hyperion Planning security), Oracle does not support repository manipulation. This needs to be taken into consideration before selecting which solution to implement.
For reasons of simplicity and flexibility, an ODI procedure with two steps inside of it was chosen over ODI interfaces. The logic in the queries is very similar to what was used in Solution 1, as can be seen in Figure 24:
Figure 24: ODI Procedure to Load Security into Planning Repository
The first step deletes from HSP_ACCESS_CONTROL all security related to the Entity dimension with FLAGS = 0 that do not exist in the temporary security table. This will remove all old undesired access that should no longer be in the application.
The second step just inserts into HSP_ACCESS_CONTROL everything from the temporary security table that does not yet exist in HSP_ACCESS_CONTROL. Performing those two steps will guarantee that the Hyperion Planning application is in sync with the necessary security settings—but a restart of Hyperion Planning Services is needed, as showed in Figure 25:
- SC \\PLANNING_SERVER STOP HYS9Planning
- Wait
- SC \\PLANNING_SERVER START HYS9Planning
- Wait
Figure 25: ODI Package to Restart Planning Service (Windows)
This is a very simple way to restart the Hyperion Planning services using ODI. This example was done in Windows and just calls a Service Controller command to stop and start the HYS9Planning service. Between each command there is a “Wait” ODI object with an arbitrary number of seconds for it to wait for the services to go down and to go up again. There are other much better/smarter ways to do it, depending on which Operational System the Hyperion Planning application is running; since it is not within the scope of this paper to show all possible ways to do it, it was decided to show only the simpler one.
Figure 26: ODI Package to Dynamic load Security in Hyperion Planning
Figure 26 shows one example of the final ODI package that will be created: a very small but powerful package that allows automatic maintenance of the Planning Security settings. For example, this package can be scheduled to be executed from time to time with proper user access and all the benefits of the ODI execution platform.
Conclusion
The Hyperion Planning repository is a magical resource that allows us to do everything that we cannot do directly in the tool. With the ODI development flexibility, a completely new dimension of what we can do inside Planning is revealed.
This article shows how to create security dynamically in Hyperion Planning, using only its repository and ODI in a simple architecture. This is just a small part of what we can do using both the Planning repository and ODI.
More examples can be found at DevEpm.com, a blog that does not just cover how to do something, but also explains why, when, and the where these solutions and techniques should be used.
About the Authors
Ricardo Giampaoli has been working in the IT environment for 19 years, the last nine as an EPM consultant. A certified professional on Hyperion Planning, Essbase, OBIEE and ODI, Ricardo works on a number of different projects with a wide variety of Oracle tools, giving him a broad vision of how the tools can be put together to increase their potential. Ricardo has presented at the 2013, 2014, and 2015 Kscope events, leading sessions on how to improve Hyperion using ODI. He is an Oracle ACE Associate and a tireless promoter of Oracle’s EPM tools. Ricsrdo holds a Masters Degree in Administration.
Rodrigo Radtke is a computer engineer experienced in software development—especially in BI for Finance space. Rodrigo has spoken at Kscope13, Kscope14 and Kscope15 sessions about ODI and Hyperion Planning usage. He is a certified professional on Oracle Data Integrator, Oracle SQL Expert and Java (SCJP and SCWCD). Currently he works as a Software Development Consultant at Dell and in several other personal projects, all of them involving ODI and EPM tools.
This article was first published in the ODTUG Technical Journal.
This article represents the expertise, findings, and opinions of the authors. It has been published by Oracle in this space as part of a larger effort to encourage the exchange of such information within this Community, and to promote evaluation and commentary by peers. This article has not been reviewed by the relevant Oracle product team for compliance with Oracle's standards and practices, and its publication should not be interpreted as an endorsement by Oracle of the statements expressed therein.