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!

Terminology Primer – Goals, Objectives, Measures, Metrics, Key Performance Indicators (KPIs), Meters

The essential purpose of business intelligence is to provide information that will allow people throughout an organization to make informed decisions relevant to their business processes and responsibilities.  This post is a simple terminology primer that describes the meaning of a few terms commonly used in business intelligence, and explain how they relate to each other, and their relevance to supporting the overall goals of an organization.

Goals are a business’ desired outcomes. They are typically around growth, cost savings, innovation, improvement in efficiency, the company’s workforce, and the competition, but may include a lot of other things.
Goals help a company to stay focused by providing team members within a company with an aim to work towards.

Objectives are specific strategies and steps that a business needs to take to achieve the goals that have set. These objectives are usually specific and measurable.  Success toward achieving objectives usually indicates progress toward achieving goals.
Objectives are sometimes referred to as Critical Success Factors because they are critical to the success of achieving the goals.

Measures are numeric representations of various transactions that occur through various business processes. For example, when the company makes a sale (during the sales process), some measures that are generated in that transaction are:  sales amount, discount amount, number of items sold, and number of items discounted.  Then from this, other measures can be determined, such as total sales for all customers, total number of items sold, total number of sales for each customer, and so on.  Measures are numeric and therefore can have mathematical calculations performed on them – such as sum, avg., min, max, etc. – to generate metrics.

Metrics are calculations derived from one or more measures. For example, as mentioned in an example above, you may have the measures “Discount Amount” and “Number of Items Discounted”, and you may use these measures to calculate a metric of “Average Discount Amount per Discounted Item” or “Average Discount Amount per Sale”.  As another example, you may simply add up all the Discount Amounts over a specific time period, such as month, to get a “Total Discount Amount by Month” metric.

Key Performance Indicators (KPIs) are metrics that measure how well a company is doing toward their objectives. Companies will have hundreds or thousands of metrics, but there will be a few key ones that the executive team wants to keep a close eye on for the overall company or divisions, and other managers will want to keep an eye on KPIs relevant to their respective departments.  Those key metrics are the KPIs. All KPIs are metrics, but not all metrics are KPIs.

Meters are a group of metrics that collectively provide a broader, overall view of a subject area.
For example, you may have individual metrics for Sales to Date, Sales in Pipeline, Number/Value of Expiring Contracts, Avg. Time to Close Sales, etc. Putting these all together in a Meter presents the user with a lot of related information that provides a broad, overall picture of sales (and loss of sales) which would allow for analysis such as determining the chances of meeting sales targets. The Meter in this example could be called “Sales Forecast” for example.

Thanks for reading!

How to conditionally set Interaction (Action Links) on a column in OBIEE 11g

This post shows an example of how to conditionally set Interaction (Action Links) on a column.

Most times, when you set “Interaction” on a column, you set it for the column under all conditions – this is the default.  However, it is possible to conditionally set “Interaction” on a column – so that the Interaction link is conditionally available.

Let’s look at an example …

You have a pivot table displaying Values by day range as shown below.  Interaction is set on the measure value, and therefore, a user can click on any of the measure values (in any column and any row) to drill/navigate to a more detailed report for example. 

(Note that all the measure values are “blue” indicating that they are hyperlinks allowing navigation to some other BI content).

Let’s now assume that you only want to make the Interaction available for the measure values associated with “Value A”.  Therefore, the row in your pivot table associated with “Value A” will have measure values that are clickable (hyperlinks), while the “Value B” row values will not.  In other words, you only want to allow drill down/navigation on “Value A” data.

To do this, from your analysis, edit the column properties, and go to the Interaction tab, and Edit the relevant Action Link.


Change the “Show Link” property from “Always” to “Conditionally”.


Then enter the condition(s) in the Conditions box. Such as in our example, we want to show/activate the action link only for “Value A” values.  So, choose the column, and then select “Value A” (and of course, do not select “Value B”).


Now when you go back to your report, action links will only be available on the measure values for “Value A” row.

(Note that only the measure values associated with “Value A” are “blue” indicating that they are hyperlinks allowing navigation to some other BI content; while “Value B” values are black indicating that they are not clickable.)

You can add more conditions and more complex conditions as necessary. One thought that came to mind … I could even see this being used to conditionally set Action Links based on users – allowing some users to be able to click the action links while others just see the data without being able to click and navigate to other detail.

Adobe Flash 11.8.800.168 causing graphs/charts not to show in Internet Explorer 8

There is an issue with Internet Explorer 8 and Adobe Flash browser plugin that causes graphs/charts not to show up in OBIEE 11g.  This is not the same as the issue that can be resolved by setting the thousand place separator property. 

The versions that have the issue are Adobe Flash 11.8.800.168 and IE8.  The same Adobe release works fine with IE9 and also works great with Firefox.  So users on those browser versions would not have been affected.

The good news is … Adobe released an emergency release within 3 days of this problem release.  The new version is 11.8.800.174.  After downloading and installing this version, we were able to see all graphs/charts.   

You can access the latest and previous Adobe Flash versions here …

Good luck.

Oracle releases PeopleSoft 9.2 – HR, Financials, and SCM

Oracle has released PeopleSoft 9.2 – including HR, Financials and Supply Chain Management. 

You can read the PeopleSoft 9.2 Press Release here, and check out the PeopleSoft 9.2 Data Sheet here.

The release includes over 1000 new features, functions and enhancements.

As for something specifically related to BI/Reporting, here is a clip from the press release:

“PeopleSoft applications take advantage of new real-time, multi-dimensional decision support analysis capabilities enabled by PeopleTools 8.53 with over 60 prebuilt self-service Pivot Grids delivered in release 9.2.”

This is a trend – more reporting/analysis functionality is being built into or integrated into the ERP systems (as opposed to having separate systems that users access separately to get their jobs done).

Enjoy reading.



Oracle Peoplesoft Accounts Payable Data model and dataflow

This is a simplified Peoplesoft Accounts Payable data flow and data model. The purpose is to provide a basic understanding of the key tables and how data flows through the module.  Understanding the key tables and the data flow is helpful when creating metrics and reports in tools such as OBIEE or when working with OBIA Financial Analytics, and particularly, with the Accounts Payable dashboard, reports and metrics.

When a voucher is created, the information is recorded in the VOUCHER, VOUCHER_LINE and DISTRIB_LINE tables. 

When payments are created for the vouchers, the information is recorded in the PAYMENT_TBL and PYMNT_VCHR_XREF tables.  The PYMNT_VCHR_XREF table provides a bridge between voucher and payment tables.

When a voucher is posted and when a payment is posted, accounting entries are generated and recorded in VCHR_ACCTG_LINE.

Payments are of course made to vendors.  Vendor primary information is stored in the VENDOR table, but there are many more vendor related tables.


Accounts Payable key table descriptions

VOUCHER AP Voucher Header Table. This Table provides the Invoice Header Information and the Accounting Information as displayed on the Voucher. Budget Checking Exceptions can also be identified on this table.
VOUCHER_LINE AP Voucher Line Table. This Table provides the individual line information for each invoice including description, quantity, unit of measure, unit amount, and extended amount. You can have multiple VOUCHER_LINE to each VOUCHER.
DISTRIB_LINE AP Voucher Distribution Table. This Table provides the Invoice accounting line distribution detail. Budget Checking Exceptions can also be identified on this table. You can have multiple DISTRIB_LINE to each VOUCHER_LINE.
VCHR_ACCTG_LINE AP Accounting Entries. This Table contains all transactions that are journal generated.
PYMNT_VCHR_XREF Voucher Scheduled Payment Table. This Table is often used as a link between the Voucher and the Payment Tables.
PAYMENT_TBL AP Disbursements Table. The Primary Table that the Payments are recorded against at time of the PayCycle Manager.
PYMNT_XREF_VW AP Payment Voucher Information. The View is designed to link the Voucher ID with the Payment Reference.
VENDOR Vendor Header Table. This Table represents the high-level Header information related to a specific vendor.
VENDOR_ADDR Vendor Address Table
VNDR_ADDR_PHN Vendor Address Phone Table
VENDOR_LOC Vendor Location Table
VENDOR_CNTCT Vendor Contact Detail Table
VENDOR_PAY Vendor Payment Defaults
VENDOR_WTHD Vendor Withholding Table
VENDOR_WTHD_JUR Vendor Withholding Jurisdiction Table