If you have used Smart Pushes, you have undoubtedly run into security issues.  The PBCS development team is working on a way to bypass this, but there is no release date.  If you haven’t run into this, you probably have and didn’t know it.  Here is the issue.  Dimensional security is typically setup so that users only have access to write to specific accounts.  For example, they have access to update units and cost per unit, but not revenue because revenue is calculated by multiplying units and cost per unit.  The problem is that a Smart Push won’t push revenue because the user doesn’t have access to write to that account.  Said another way, when users update the components of revenue, the revenue itself doesn’t get pushed to the reporting cube because the user doesn’t have write access.  Here in lies the problem, as revenue won’t get updated until an administrator runs a Data Map.

I understand why Oracle did this, but with no ability to override it, it creates issues with Smart Pushes and real time reporting.

Before I continue, I want to thank Pete over at Essbase Down Under for this brilliant idea.  Enter stage left, valid intersections!

What Are Valid Intersections?

Normally, valid intersections are used to link drop down menus.  For example, once Honda is selected in the first menu, the second menu only shows the models related to Honda, like Accord, Civic, and Prelude.  If you know what a Prelude is, you have been doing this as long as I have!  Valid intersections can also limit what users have access to write too, like the rows a user has access to write to in a grid.  If valid intersections are setup on account and accounts are in the rows of a Data Form, the grid will show all the accounts, but the invalid accounts are greyed out and not editable.  Since this isn’t truly security, when the Smart Push is executed, the user has the access to push calculated accounts.

Why Not Cherry Pick Rows As Read Only?

Sure, rows can be set to read only.  If you are thinking about going down this path, beware!  There are some huge issues with this approach.

1. As hierarchies change, forms will have to be manually changed.  By singling out specific rows, it means administrators lose the benefit of functions, like level 0 descendants.  So, rows will have to be manually maintained every time metadata changes.
2. Since this is a form setting, users who run ad-hoc retrieves can still write data to these accounts.

Valid Intersections In Action

You might be thinking, yeah, but valid intersections also use level 0 descendants?  They do, but just like in forms, exclusions can be applied.

In the example below, the rows/accounts with the check marks have been setup to be valid with the working scenarios.

In this example, the dimensional security is set to write for all accounts on this form.  As visible below, the dimensional security is ignored.  Users have the access to the accounts needed, but can’t edit those that aren’t setup as valid intersections (selected in the valid intersections setup above).

When the form is saved, and the Smart Push executes, the Smart Push uses dimensional security.  All the data pushes since the users have write access to all accounts!

What About Ad-hoc?

Don’t worry, users can’t change data on the invalid intersections.  Well, sort of.  The rows for the invalid accounts are still greyed out.  Here is where it gets blurry.  Users can still edit the data in those cells, the ones that are grey.  Although this might be confusing to a user, it is ignored on submission.  Below shows the accounts in the above form, but in an ad-hoc.  Notice what happens when the first two rows are changed.  The first row is saved, but the second is not.  It returns to its original value.

Finishing Up

I wish there was a parameter in the Smart Pushes (Groovy or not) that allowed the form developer to set the permissions to use admin privileges.  Until this gets figured out with Oracle, this is a great way to fix the problem.  Everybody that uses Smart Push will run into this.  Setup dimensional security to write to all accounts.  Setup valid intersections for the accounts that users need to be able to edit.  And wallah!

Working with Jake Turrell always benefits me in many ways.  Jake found a fantastic way to minimize the effort it takes to create test accounts for testing and training Planning users.  You no longer have to create new multiple accounts.

“During the testing phase of most Planning implementations, developers need to create test user accounts.  I typically create at least one test user for each security group so I can verify that the correct access has been assigned.  With an on-premises Hyperion Planning implementation, this is easy – simply create user ID’s in the Shared Services native directory.  With PBCS, creating bulk test ID’s can be difficult, as each user ID requires a unique e-mail address.  If you need 50 test users, should you create 50 fake/temporary e-mail accounts?  Luckily the answer is no.”

Check out how here.

About Jake

Jake Turrell is a Hyperion Architect and Oracle Ace Associate with over 20 years of experience implementing Enterprise Performance Management solutions. Jake’s technology career began in the early 90’s as a Financial Systems intern at Dell in Austin, Texas, administering IMRS Micro Control (the DOS-based predecessor to Hyperion Enterprise). After working at Dell, Jake joined Ernst & Young’s Management Consulting practice where he worked with a variety of technologies. He later returned to the Hyperion world and joined a boutique Hyperion consulting firm in Dallas, Texas.
Jake has spent the last 17 years implementing Hyperion Planning and Essbase solutions for a variety of clients across multiple industries. Certified in both Hyperion Planning and Essbase, Jake holds a BBA from the University of Texas at Austin.

Creating security filters and assigning them to different users/groups can be a time consuming process, especially if it is done manually. Luckily, there are some simple MaxL statements that can be used to significantly expedite the process. Here are the 3 that I’ve found to be most useful:

• Create Filter
• Alter Filter
• Grant Filter

Create Filter:

The MEMBER-EXPRESSION must be enclosed in single quotation marks. It can be a comma-separated list as well (this also pertains to the Alter Filter syntax). Notice in the example below how commas are used to separate 3 different dimensions (Year, Measures, & Product) in the create filter syntax:

• create filter Sample.Basic.filter1 read on ‘@IDescendants(“Year”), @IDescendants(“Measures”), @IDescendants(Product”)’;

For the FILTER-NAME portion, the application and database must be included preceding the filter name. This syntax will be used for Create, Alter, & Grant.

After running the batch, open EAS to verify that the filter was created correctly (I’ve included a generic version of my batch & MaxL files at the end of this post in case they may be helpful). Right click on the database and select Edit->Filters:

A list of all filters in the database will appear:

Select edit and the member specification assigned to the filter will pop up. All 3 dimensions that are outlined in the MaxL command should be accounted for:

Many times, the filter will need to be updated after it has been created. There is also a command line function for that…

Alter Filter:

For this example, we’ll add another dimension into the filter. Let’s add read access for @IDescendants(“East”). Here’s an example of the Alter Filter syntax:

• alter filter Sample.Basic.filter1 add read on ‘@IDescendants(“East”)’;

After running the batch file, the filter now reflects the change that was made:

Now that the filter is built, it can be assigned to a user, group, or multiples of both using the Grant Filter command line function. However, prior to assigning a filter to a user/group, the user/group must be provisioned to have filter access to the application. This is done through Shared Services. We’ll use “Test_User1” as a sample user. Right click on “Test_User1” and select Provision:

Expand down on the Sample application until Filter appears. Highlight “Filter” and bring it across to the right side of the screen:

The selected roles should display “Filter” under Sample:

Click Save. Now, “Test_User1” is provisioned for the Sample application and the filter can be applied using the Grant Filter MaxL command.

Grant Filter:

Example of the Grant Filter syntax:

• grant filter Sample.Basic.filter1 to Test_User1;

To verify that “filter1” has been granted to “Test_User1”, head back to Shared Services and right click on Sample->Assign Access Control:

Select “User Name” from the dropdown menu in the top left and click search. Highlight “Test_User1” and click the right arrow to bring the user to the Selected box on the right. Click Next:

“Test_User1” has been granted “filter1” and the user’s access should reflect this change:

Batch File:

call MaxlPath “MaxL File Path” Sample Basic userID password ServerId filter_log

MaxL File:

login $3 $4 on $5;

spool on to “Log File Path”;

create filter Sample.Basic.filter1 read on ‘@IDescendants(“Year”), @IDescendants(“Measures”), @IDescendants(“Product”)’;

alter filter sample.basic.filter1 add read on ‘@IDescendants(“East”)’;

grant filter Sample.Basic.filter1 to Test_User1;

logout;

spool off;

exit;

To take a deeper dive into the filter functionality, or to clarify any issues, check out the Essbase Technical Reference:

https://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref.pdf

In Hyperion 11.1.2.1, there is a change in how security is deployed. If you are having an issue deploying Planning security with Essbase adhoc access, and the user can’t

1. Access Essbase Adhoc
2. Access FR reports using an Essbase connection
3. Access Essbase directly

you are not alone. This is not classified as a bug, but it sure can cause a lot of frustration. If you have a user that needs access to Essbase directly, the user can’t be associated to ONLY Planning applications. For them to get access to Essbase, even to access the Planning application, they must have security to a native Essbase application (a NON Planning application).

From the Oracle Doc ID 1328741.1

SYMPTOMS

Trying to add the “Essbase” application access type to a Planning user in Shared Services so the user can access native Essbase applications using the Excel Add-in. Shared Services confirms the update when saved but when checking the user again, it only has “Planning” access. This also happens when trying to use the MaxL command to modify the application access type.

CAUSE

Starting in EPM v11.1.2, a user’s type (application access type) cannot be changed by Maxl, the EAS console or manually via Shared services console. The type is automatically assigned based on the roles that the user has.

• If a user has a role on a Planning application only, then that user is treated as a Planning user.
• If the user has a role on a Essbase application only, then the user is treated as an Essbase user.
• If the user has roles on both Planning and Essbase applications the user is treated as a Planning and

Essbase user.

SOLUTION

In order for a Planning user to access native Essbase applications in the Excel Add-in, the user will need to be given access to a native Essbase application. For example, assign the Planning user “Read” access to the Demo application.