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:


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:


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:



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.


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!



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.

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.

When data-level security is applied …

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

Bill will able to access/see the following data:

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

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

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.


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

Then click Edit Data Source

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.


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.

Then click OK to save the Init Block.


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

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.

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.

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!

Upgrading OBIEE 11g to OBIEE 12c – First thing to ensure

Our team is currently in the process of upgrading our OBIEE 11g environments to OBIEE 12c. I have been gathering information about the process and will be sharing information on our experience as we progress.

I wanted to point of the first thing you want to ensure before planning/starting the upgrade from 11g to 12c – this may save you a little time. Or if you have already started, and encountered an error relating to … catalog version is not supported … then this post might be helpful.

You can upgrade the OBIEE catalog from OBIEE or OBIEE to OBIEE 12c. This should upgrade without any major issues.
But you may unexpectedly run into the above problem if you had upgraded the OBIEE 11g catalog using patch sets and had not run the full catalog upgrade. This results in the catalog being used as an or environment, but the version stored in the catalog is still older (such as or
Then when you try to upgrade from OBIEE 11g to OBIEE 12c, you get the error because the catalog is still technically not yet on an approved version for upgrade.

To resolve this, you need to run a full catalog upgrade on the OBIEE 11g catalog. This involves modifying the instanceconfig.xml file as follows:

Change the value of the UpgradeAndExit parameter from “false” to “true” as shown in the example below.


Restart the presentation services.
After this is complete, edit the files again and change “true” back to “false“, and restart the presentation services again.

You should now be able to upgrade your catalog to an OBIEE 12c version.

I hope this helps. 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.


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


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


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.


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.

Creating a Custom Landing Page or Custom Home Page for your OBIEE / OBIA environment

Your organization may want to have a custom home page or landing page for your OBIEE or OBIA environment.  (I will use the term “Landing Page” going forward to not confuse it with the OBIEE delivered “Home Page”).  When users log in, they need to be automatically taken to this custom landing page instead of to the delivered OBIEE Home Page.

This post describes some of the reasons you may want a custom landing page, the content that could be on the page, how to automatically navigate users to the page, and security associated with the page.

Why would you want to create a Custom Landing Page?  The reasons will vary by organization, but these could be some of the reasons:

  1. Deliver the look and feel that your company or users desire.
  2. Allow for a place that serves as a central location for the content you want to emphasize, in the way you want to display it.
  3. Provide a central place for messages of any kind for your users.

What content will be on this Custom Landing Page?  Some of the possibilities are:

  1. Create a page with your custom logos, images, and colors that are in line with your company’s or department’s branding.
  2. A section with messages for your user community. This information could include things such as:
    1. The date/time of the last data load?
    2. The sources of the information displayed on your dashboards
    3. Information about recent dashboard releases
    4. Upcoming downtime
    5. Upcoming events such as user training events
    6. Action needed by the user community
  3. A section that lists links to useful resources, such as:
    1. user’s guides or tutorials
    2. dashboard and report glossary
    3. analysis/report request forms
    4. Security/Access Request forms
    5. general OBI information
  4. A section with Contact Information – containing information about who, what, when, how to contact people for help or information, or how to submit new requests for data/analyses/reports, maybe by functional area, etc.
  5. An area to display your company’s or division’s top key performance indicators (KPIs). These should be limited to just a few – I would say not more than 5 – and they should be relevant company-wide or “OBI user community-wide”.
  6. Links to dashboards. You may create an area or areas of links to various dashboards. Your dashboard list may include many of your dashboards or just a select few that you know are frequently used or that you want to emphasize.

All users that are authorized to use the OBI system will have access to this page.  So, maybe BI Consumer role will be provided access.

However, you will need to set security on the sections containing links to dashboards to allow access only to those authorized for the each set of dashboards.

Once your custom landing page is ready, you will then need to set it as the default page for users (or a subset of users).  To do this you will need to create an initialization block that sets the PORTALPATH built-in OBI variable to point to the new landing page dashboard page.

One final note … you can have multiple custom landing pages if you desire, for example, a different page for each division or a different page for each major group of users.  You would then need to set the PORTALPATH variable based on the user’s profile.

Good luck with your custom landing page project.

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”.


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).


OBIEE Performance Tuning

This post describes a few tips and things to keep in mind for OBIEE Performance Tuning.

Be Proactive when possible
The need to performance tune can be proactive (tune before a major issue arises) or reactive (tune after a problem is reported by users for example).  It is best to be proactive – so performance tuning should be built into your OBIEE maintenance schedule. For example, OBIEE’s Usage Tracking functionality should be used regularly to identify reports whose performance can be improved and then performance steps should be carried out on the worst performers.

Iterative Process – change one thing or set of things at a time
One of the first things to keep in mind is that performance tuning is an iterative process.  And there is typically no one silver bullet that will resolve all your performance problems.  You may need to analyze and make changes to multiple parts of the system, but you want to make the changes methodically.  It is best to change one parameter or setting at the same time (or one related set of parameters).  Adjust and test the settings for that one parameter/setting (or set of parameters) before moving on to another.  If you change too much at one time, you may have a difficulty determining what is helping from what is hurting your efforts.

Fix user complaints first, worst performers next, and then the next bad performers down the list
Another thing to keep in mind, tune what users are reporting first, then tune the worst problems second, then move on to the next.

Team Effort – problem could be anywhere along the technology stack
Performance problems could be anywhere along the technology stack:
• Database
• Server
• Network
Due to that span of technology, performance tuning is a team effort.  OBIEE Admins and Developers, DBAs, and ETL Developers can all be key to solving performance issues.
Logs from all components may need to be reviewed depending on the scenario.

Try to isolate or narrow-down the source of the problem
For example, run the report SQL directly on the database and see if you have the same problem. If there is no issue when run directly on that the database, then you have eliminated the database as the problem.
Determine if other applications have been also been experiencing slowness which could indicate the possibility of a network problem.

If your users have reported an issue, then you need to get as much details as possible about the performance problems they are experiencing.  When did this start happening?  Is it just one report or many?  Is it localized to one business area or multiple?  Is it all the time or sometimes?  Knowing this will help you to know where to focus.

Other questions to ask as you try to identify the source of the problem include but not limited to:
Has anything changed?  If reports were running fine, but are now slow, the first thing to ask is …
When the issue start?  Determining exactly when it started might be helpful when correlating with other system or company activity)
What has changed recently?  Has there been any system changes, data changes, database updates, network changes, etc. (even if they seem unrelated)?  For example, rolling into a new calendar year will cause new “Year” value(s) to be included in the data and can impact performance if statistics are not gathered.
Is there a possibility that an index was dropped and not recreated as expected?

Use OBIEE’s Usage Tracking information to analyze specific reports, analyze long running reports, or frequently run reports.  You will want to capture and analyze the SQL from these reports to determine what can be done to improve their performance.

DBAs can monitor the system in real-time, use various tools, or review logs for information that can be helpful in the tuning effort.  Tools such as Oracle Enterprise Manager (EM) or SQL Tuning Advisor can be used to identify, analyze and tune high-load SQL.
OBIEE Usage Tracking can also be used to identify high-load SQL.
Without getting into much detail, these are some database features that could be used to help improve performance:
• Gather Statistics
• Results Cache database feature
• Partitioning

The System Admins can monitor the server resources to determine if there is an issue there.
• Use fast disk for the OBIEE cache and/or temporary files.


OBIEE-specific performance tuning tips

• OBIEE Caching
Are the tables being used set to cacheable?
Is caching turned on at the application level?
You may consider seeding the cache daily.
CACHE Settings:
o ——————-

• Use Aggregation: Aggregate data when applicable
o You can use Aggregate tables or materialized views to realize this benefit.
o Aggregate Fact tables and corresponding Aggregate Dimensions.
o Make sure aggregation rules are applied to Fact table measures.
o Don’t necessarily merge all measures into a single fact.

• Joins and Indexes
o Do not create unnecessary joins.
o Verify that the joins on the tables being investigated are appropriate.
o Performance Indexing could be helpful.  Again, this is an iterative process.

• Prompts and Filters
o Use LOV tables to drive prompt values when possible, instead of building prompts from large transactional data tables.
o Force filter selection / entry by making prompt values required.  Do not allow open ended run of reports.

• Filter out unneeded data.  If there is a significant amount of data that is not being used in one or more tables (especially if they are frequently used), then that data should be filtered out by the ETL before it gets joined in SQL, and then has to be filtered out in the RPD or at the report level.

• Enter the “Number of Elements at this level” value in the logical level in hierarchies.
• Also ensure that all logical level keys are unique.

• Avoid function in the where clause when possible.

• Be careful of sub-queries.

• Check out the features of the OBIEE Performance Monitor
http://server:port/analytics/saw.dll?Perfmon  (enter your OBI server and port)

• When possible, do comparison analysis to determine for example, why is this report running fine, but this other seemingly similar report is not.

• Use fast disk for the OBIEE cache and/or temporary files.

Sometimes a complete overhaul might be required
Review the users’ workflow and determine if new and improved queries can be written or if the number of queries can be reduced.
Present information from a summary level first, and then provide increasing levels of details as requested by users through drill down or navigation.  Basically, present detailed information only when necessary, and minimize the amount of detail provided at a time by filtering on user selections.

Oracle’s OBIEE Performance Tuning Guide
Apply recommendations from the “Best Practices Guide for Infrastructure Tuning Oracle® Business Intelligence Enterprise Edition 11g Release”.  I would recommend applying 1 – 3 changes or set of changes at a time; don’t apply everything at the same time because if there is a problem, it will be more difficult to determine which change caused it.