Over the last few years the integration between Oracle Business Intelligence Enterprise Edition (OBIEE) and Essbase has been undergoing continuous evolution. The progression is aimed at making both analytical products more compatible as they are complimentary. Security may be a challenge, however, when it comes to implementing data-level security on OBIEE reports that source data from Essbase cubes. The challenge is mainly due to the fact that there are different means to achieve data-level security on OBIEE reports sourced from Essbase, depending on the type of installation and security requirements. In this blog, I will navigate through the different methods of how to do this and reference instructions to achieve each method.
The above high level decision diagram is a guide to know which method is most suitable. I present three different methods. Following are key questions that are crucial in selecting the most suitable method:
- What type of OBIEE and Essbase installations are involved? Was Essbase part of a single OBIEE install or a standalone install?
OBIEE and Essbase can be installed in either one of 2 ways:
- A single install of OBIEE which includes Essbase as an option during the OBIEE installation. This is only possible with OBIEE version 11.1.1.7 or later and offers the best form of integration between OBIEE and Essbase. If Essbase’s sole purpose is to support reporting out of OBIEE, this is the recommended type of install. In this case, Method 1 is the most suitable way of applying data-level security.
- The other type of install is the traditional install where each of OBIEE and Essbase are separately installed. If you installed OBIEE on a version prior to 11.1.1.7.1, you are on this type of install. It is very possible that you are using OBIEE 11.1.1.7 or later and still be using separate installs of OBIEE and Essbase for many reasons that are not really the topic for this blog. If you have a case of separate installs, then you need to investigate further with the second question below to know if Method 2 or 3 is most suitable.
- Are there filters defined within EPM Shared Services that need to be leveraged for the purpose of data-level security on OBIEE reports?
Essbase may already have security filters defined in Shared Services. If the requirement is to leverage the same native Essbase authorization in terms of data filter assignments, then Method 2 is the way to go.
Otherwise, if the Essbase native security filters need to be bypassed and redefined for OBIEE reporting purposes, then Method 3 is most appropriate.
Method 1: Using Enterprise Manager to define Essbase filters against Application Roles and Policies
This is only possible if you installed Essbase together with OBIEE from the OBIEE installation wizard. With this method, you use Enterprise Manager to define Essbase security policies against application roles. For detailed instructions on how to implement this method, refer to the Oracle Fusion Middleware System Administrator’s Guide for OBIEE 11g and more specifically the section titled: “Configuring Data-Level Security Using Essbase Filters”.
Method 2: Leveraging Shared Services for Essbase Security by Implementing Single Sign-On (SSO)
When SSO is chosen in the Essbase connection pool of the OBIEE physical layer, whatever security is defined in Shared Services for the authenticated user, still applies even when that user logs in from OBIEE. The pre-requisite for this method is that both Essbase and OBIEE authentication are configured against the same LDAP server such as Active Directory or Oracle Identify Manager (OID). Detailed instructions on how to achieve this are available in the Oracle Fusion Middleware System Administrator’s Guide for OBIEE 11g and more specifically in the section: “Configuring Oracle Business Intelligence to Use Hyperion SSO Tokens when Communicating with Essbase, Hyperion Financial Management, and EPM Workspace”.
Method 3: Using a Shared Essbase Login and Applying OBIEE Repository Application Role Filters
This method bypasses native Essbase Shared Services security filters by allowing OBIEE to connect to Essbase via a shared login that has access to all cube data. The aim here is to allow the OBIEE repository to determine what security filters get applied to each cube per application role. This concept is very similar to how row-level security works with relational sources. One advantage is that all row-level security for OBIEE reporting is centralized within the OBIEE repository, irrespective of whether data is sourced from a relational source or an Essbase cube. Another thing to keep in mind though is that more complicated security filters that utilize an OR operator may have adverse effects on the performance of the MDX queries that get automatically generated by OBIEE. These security filters are defined in the normal way inside the OBIEE repository Security Manager using Data Filters against Application Roles.
Do you have any experience or tips to provide for apply row level security with MS Sql server analysis Cubes being the data source?
Donna,
With SQL Server Cubes, applying row level security is doable following an approach similar to that described in Method 3. It basically entails connecting with a common (all access) user to SQL Server and leverage OBIEE metadata to define security filters. These filters are define in the RPD Security Manager using Data Filters.