Advertisements

Implementing Reports-To data-level security in Oracle BI (OBIEE)

In a previous post, Implementing data-level security in Oracle BI (OBIEE), I described data-level security and how to implement it in Oracle Business Intelligence (OBIEE).  In this post I will describe a special type of data-level security, called Reports-To security, and how to implement it in OBI.

For Reports-To data-level security, we want to secure data in such a way that we allow a user access only to data for his/her direct and indirect reports. In other words, each user will be able to see data only for people that are below him/her in the organization hierarchical chain.

Take a look at this example diagram:

ReportsTo_Security_Org_Position_Hier

If Reports-To security is applied to this example, Position# 303 would only be able to see information for Position# 409; and Position# 305 would only be able to see information for Position#’s 410, 411, 412; and a final example, Position# 201 would be able to see the information for Position#’s 303, 304, 305, 306, and 409, 410, 411, 412.

I use “Position” as the driving entity in the hierarchy instead of “Employee” because there are times when a position is vacant (no employee) and so it’s better to use the position which will always have a value.  However, you can use Employee if that works better in your scenario or if that’s what your data supports.

Let’s move on to how to implement this type of security.  The steps are similar to the steps in a previous post, Implementing data-level security in Oracle BI (OBIEE), but with some key differences.  (Refer to that post for some of the more detailed steps not reiterated in this post.)

First, build a Reports-To data table and create the necessary ETL to ensure that it remains correct and up-to-date.  This table will contain each position (employee/user) and what position (employee) they report to. The data for this table will likely come from your HR system (such as PeopleSoft, Oracle EBS, SAP, Workday, home-grown system, etc.) that contains all the position and employee data.  Using the Organization Position Hierarchy diagram example, the table (REPORTS_TO_DATA) may look something like this:

REPORTS_TO_DATA

Next, create a Session Initialization Block (Init Block) with row-wise Initialization that will be used to get the list of all positions that report to the position of the current user and store them in a defined Target Variable.  If you log in, the Init Block will generate the list with all the positions (or employees) that report to you; and when Jane logs in, the Init Block will generate the list of all the positions (or employees) that report to her.

An important component of the SQL in the Init Block is that it needs to be recursive, because for each person, it needs to retrieve their direct reports, and then retrieve the people reporting to their direct reports, and so on down the line.  Using the above Organization Position Hierarchy diagram example, when the user in Position 202 logs in, the SQL needs to retrieve the positions reporting to 202 (which are 307 & 308), and then recursively retrieve the positions reporting to 307 and 308, and so on. The Target Variable used for storing the values in this example is: REPORTS_TO_POSITIONS

The Init Block, its SQL, and variable definition may look something like this:

Reports_To_Position_InitBlock2

 

Then finally, we need to create the data filters on the appropriate data sets (that need to be secured) using the variable containing the “list of positions” reporting to the current user (REPORT_TO_POSITIONS variable).  The needs to be done for each role that will access the reports that need to be secured by Reports-To security.

REPORTS_TO_Data_Filter

After this is all set, then Reports-To Security will be in effect for the filtered data sets and the reports that use them.

If you need to make it such that each user can only see data for his or her direct reports, the SQL can be modified to remove the recursion, and just return the direct report positions.

One final point … as you would with all changes, but particularly with solutions involving sensitive data, test your solution thoroughly – including making sure to perform both positive and negative testing.

Thanks for reading!

 

Advertisements

Implementing data-level security in Oracle BI (OBIEE)

Data Level Security involves securing the data available in an application in such a way that each user will see only the data that he/she is authorized to see, resulting in each user possibly seeing different results on the same report.   In this post I will describe how to implement data-level security in Oracle Business Intelligence (OBIEE).

Let’s use an example to describe data-level security.  Each user of the BI system works in or is assigned to a particular Business Unit.  Each user is allowed to see only the data for his or her assigned Business Unit.

In our example, the below table lists the 4 users and the Business Unit that each of them works in or is assigned to, and therefore, should have access to.  We will call this the USER_TO_BUSINESSUNIT table.
DataLevelSecurity_UsersBUs

Jane and Xing should only be able to see data for Business Unit BU2000, Bill should be able to access data for both BU3000 and BU4000, and Venkat should be able to access data for BU4000.

Now, we will use the below table as the example data set that we need to secure with the Business Unit data-level security.  We will call this table TRANSACTION_DATA.
DataLevelSecurity_AllData

When data-level security is applied …

Jane and Xing will be able to access/see the following data:
DataLevelSecurity_BU2000

Bill will able to access/see the following data:
DataLevelSecurity_BU3000_and_BU4000

And Venkat will be able to access/see the following data:
DataLevelSecurity_BU4000

So, now let’s move on to how to implement data-level security in OBI to achieve what was described above.

First, ensure that the USER_TO_BUSINESSUNIT table data is correct and up-to-date, and that there is an ETL in place or some other method of keeping that data updated. You want to ensure that if and when a user’s Business Unit changes, it is reflected in this table so that the user will have access to the appropriate data.

Next, create a Session Initialization Block with row-wise Initialization that will be used to get the list of Business Units that a user has access to.

Open the RPD -> Manage -> Variables
ManageVariables

In the Variable Manager -> Action -> New -> Session -> Initialization Block

This needs to be a “Session” Init block so that it will run each time a user logs in, and gets that user’s list of Business Units; and it needs to be row-wise because some users will have more than 1 value returned.

New_Session_InitBlock

In the Session Variable Initialization Block Dialog, enter a Name for the Init Block.

Then click Edit Data Source
InitBlockDialog

In the Data Source dialog, enter the SQL to get the Business Units for the current logged in user.  Click OK when done which closes this window and brings you back to the Session Variable Initialization Block Dialog.

InitBlockSQL

Click Edit Data Target in the Session Variable Initialization Block Dialog.

Enter your Variable name and check “Row-wise initialization”. As mentioned above, we need to select row-wise because our Init Block SQL may return more than 1 value for some users.   For example, when Bill in our example above data logs in, the Initialization Block will return values BU3000 and BU4000, and store them in the Target Variable, “BUSINESS_UNIT”.

You may also check “Use caching” to store the values in cache. Click OK when done.

SessionInitBlock_RowWiseTargetVariable
Then click OK to save the Init Block.

InitBlock_SetupComplete

Next, apply data filter(s) to the appropriate data set(s) for the appropriate role(s) using the Target Variable above.  You may have role(s) specifically used for data-level security and will need to apply it there, but if not, you will need to apply the filters in each role that has access to the datasets/dashboards/reports that you want to apply data-level security to.

Manage -> Identity
ManageIdentity

Go to the Application Roles tab, and select the Application Role to which you would like to apply the data-level security.  In the APplication Role dialog, click Permissions.
IdentityManager_ApplicationRole

In the Permissions dialog, select the layer and data table that you want to apply the data security to, and then enter the appropriate filter.  In this example, you are filtering by BUSINESS_UNIT.  This will cause the data to be filtered to only include each users’ Business Units.
DataFilter

Save your changes.  You have now applied data-level security.  This is what will happen now:

User logs in -> Init Block runs and selects the Business Units associated with the user’s User ID -> Init Block assigns value(s) to the variable BUSINESS_UNIT -> if the user is a member of a role that has data security applied to -and- the user visits the report -> the data filter will be triggered/run -> User only sees data for the Business Units the user is allowed to see.

Look out for my upcoming post on implementing a special type of data-level security: Reports-To Data Level Security.

Thanks for reading!

How to generate detailed Oracle BI (OBIEE) Repository Documentation

In this post, I will show the steps for using the OBIEE “Repository Documentation” utility to generate repository (RPD) lineage information.  I will also provide a couple example of how this documentation (output file) can be used.

To access and run the Repository Documentation utility,  from the BI Admin Tool menu, select Tools -> Utilities.

biadmintool_menu_tools_utilities

From the Utilities dialog, select “Repository Documentation”, and click “Execute…”

utilitiesdialog

In the “Save As” dialog, select the destination and enter the name you would like for the output file.

saverepositorydocumentationdialog

When it finishes, it will generate the output csv file.  Note  – this will likely be a large file.  It will contain all your repository objects.

obieerepositoryoutputfile

The RPD documentation file will contain the following columns:
Subject Area, Presentation Table, Presentation Column, Description – Presentation Column, Business Model, Derived logical table, Derived logical column, Description – Derived Logical Column, Expression, Logical Table, Logical Column, Description – Logical Column, Logical Table Source, Expression, Initialization Block, Variable, Database, Physical Catalog, Physical Schema, Physical Table, Alias, Physical Column, Description – Physical Column

You can use this file to quickly track lineage from physical sources to the logical columns to the presentation columns and identify all the logic and variables in between.
You can also use it to identify where and how much a specified table, column, variable, etc. is used which will help you to identify dependencies and know the effect of making changes or deleting elements.

Development, Data Governance, and Quality Assurance teams may find this information useful in this format.

Disallow online RPD updates in OBIEE

You may want to disable online updates on your OBIEE RPD for performance reasons or because you have a specific development process that prohibits online updates.

To disallow online RPD updates, do the following:
Log into Enterprise Manager. Navigate the tree menu to Business Intelligence -> coreapplication.  Click tabs “Capacity Management”, and “Performance”.

Under the RPD Updates section, check the box for “Disallow RPD updates”.

disallowRPD_updates

This will prevent online RPD updates for all.

If you want to allow a select group of people to have access to perform online updates, such as a lead developer or administrator, then don’t do the above, but instead provide Administrator role to those that should have the access, and remove it from those that should not (and give them BI Author role for example instead).

 

“The connection has failed” Error when trying to Import Metadata into OBIEE

If you get the error “The connection has failed” when you try to Import Metadata into the RPD, this post may help you to resolve it.

The solution is to: Create an Environment Variable called TNS_ADMIN and set its value to the directory of your tnsnames.ora file.
The TNS_ADMIN variable tells Oracle Client where to find the tnsnames.ora file which contains your data source details.

In case you need the details:
Click the Windows Start menu –> Right-Click on Computer –> select Properties
Then click on “Advanced system settings” on the left.
Advanced_System_Settings

Click the “Environment Variables” button.
Then in the Environment Variables window, click New.
Enter the details for the TNS_ADMIN variable.  The value needs to be the path to your tnsnames.ora file, typically located at [ORACLE_HOME]\network\admin. The path will look something like the value shown below (it depends on where Oracle is installed on your system).
TNS_ADMIN_Environment_Variable

 Hope this helps.

Components of Oracle Business Intelligence Applications (OBIA)

The Oracle Business Intelligence Applications (OBIA) is made up of a number of components that are brought together to create a great prebuilt BI solution.  The components can be categorized into 4 major components.

1. Prebuilt reports and dashboard content + Embedded dashboard/report building tool
This prebuilt content is contained in the Oracle BI Presentation Services Catalog, and some of the content is built on the Oracle BI Repository metadata.
The tools include Dashboard Editor and Answers.

2. Prebuilt metadata content (Oracle BI Server Repository) + Administration Tool      
This metadata content is contained in the Oracle Business Intelligence Applications repository file (EnterpriseBusinessAnalytics.rpd).
This content is built and administered using the BI Administration Tool, and is built from the metadata in the OBAW.

3. Oracle Business Analytics Warehouse
The prebuilt data warehouse that holds data extracted, transformed, and loaded from the transactional sources.  The OBAW contains best-practice star-schemas and conforming dimensions.

4. Prebuilt ETL processes and tools
Prebuilt Informatica content + Embedded Informatica ETL Tool
+ Prebuilt DAC metadata repository files + Embedded DAC Tool
Informatica is a third-party application that performs the extract, transform, and load operations for the Data Warehouse.  The Informatica content includes Extract-Transform-Load (ETL) repository objects, such as mappings, sessions, and workflows, and is contained in the Informatica repository file (Oracle_BI_DW_Base.rep).
The DAC is a tool that is used for setup, configuration, administration, and monitoring of data warehouse processes.  The DAC content includes repository objects such as tables, subject areas, execution plans, and tasks, and is contained in XML files.
These tools and processes together extract data from sources, such as Oracle EBS or PeopleSoft, and load the data into the OBAW.

OBIEE data source types and data retrieval methods

OBIEE is capable of connecting to and retrieving data from a variety of data sources.  The type of data sources that OBIEE can connect to are OLTP, OLAP, Data Warehouses (ROLAP), and Files.

  • OLTP databases – these include the normalized-design databases including ERP, CRM and other LOB systems.
    – The relational databases supported are: Oracle databases, Microsoft SQL Server, IBM DB2, and Teradata Warehouse.
    – And the ERP/CRM sources supported are: Oracle E-Business Suite, Oracle Peoplesoft, Oracle Siebel CRM, Oracle JD Edwards, and SAP.  Note: any ERP/CRM system running on one the databases mentioned above can be supported, but those mentioned here are special ERP/CRM sources.
  • OLAP databases – these include dimensional-databases including applications based on dimensional databses.
    – The OLAP datases supported are: Oracle Essbase, Oracle OLAP, Microsoft Analysis Services, and SAP Netweaver BI.
    – And the OLAP applications sources supported are: Oracle Hyperion Planning and Oracle Hyperion Financial Management.
  • Dimensionally-modeled data warehouses – these are relational databases designed with a star-schema / dimensional model, on one of the 4 supported relational databases mentioned above.
  • Files – Microsoft Excel, XML files, Flat files.

The data retrieval methods used to connect to these sources are:

  • OLTP – SQL
  • OLAP – MDX
  • Data Warehouse – SQL
  • Files – ODBC

OBIEE has the ability to connect to multiple of these data sources at the same time, and the data sources can be of the same or different types.  So, for example, an OBIEE Server can source data from an Oracle 11g Data Warehouse, and from an Oracle Essbase 11g OLAP cube at the same time, and join the data together for user consumption.
Similarly, file datasources can also be added to provide additional information, for example from an external source, and joined to data from the other sources mentioned above.  This “joining” of data is handled by the OBIEE BI Repository and BI Server.

To the end user accessing the data from a front-end tool (Analysis Editor / Answers), it seems like a single data source. That is one of the features that makes OBIEE such a great tool particularly for heterogeneous database environments.