KScope17 Session: Complex Data Security Made Easy in Oracle BI

Perficient is exhibiting and presenting this week at KScope17 in San Antonio, TX.  I delivered my presentation yesterday on modeling complex data security in Oracle BI.  Here is the abstract for my presentation:

Learn how to build a position based security model to enable a position can view all it’s direct reports’ marketing/sales performance; learn how to build team based security model to enable a manager/supervisor to view it’s team’s performance on leads/opportunities; learn how to build org hierarchy based security model to view restricted data by organization; learn how to use views to optimize the data security performance in OBIEE; learn how to configure the data security for the reports.

I went through a very typical use case that marketing organizations use to secure analytics reports of who gets to see what.

The typical challenge for such data security requirements is how we can model the data security to apply the m:m relationship to Star schemas where is typical of m:1 or 1:m relationship. The ERD for the use case looks like this:

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

And the two Stars used for my presentation look like this:

  • Opportunity Team Fact – A fact where capture the m:m relationship between opportunities and positions.
  • Opportunity Fact – A fact where captures only at the opportunity level, no references are available for sales person, position and position hierarchy dimensions

Applying salesperson and position hierarchy data filters for the Opportunity Team fact appears to be very straightforward; but when it comes to the Opportunity fact, it becomes a challenge. In my presentation, I went through four different data security approaches in Oracle BI, and their pros and cons.

  1. Dimension Data Filter
    Dimensions can be directly applied to fact table as data filters. It’s typically used in where dimensions for data filtering are available and supported in the dimensional model.  For example, such dimension data filters can be applied to the Opportunity Team fact.
  2. Reverse Star Schema
    Dimensions required for data filtering are not directly available in its own Star schema, but can be indirectly available through another Star schema via a common dimension.  For example, such dimensions resulted from reverse star schema through the Opportunity dimension can be applied to the Opportunity fact.  Since the reverse star schema is a logical implementation, it can be applied to the Opportunity Team fact as well.
  3. Row-wise Session Variable
    While reserve star schema might help to bring in the additional dimensions for data filters, but it might also have a performance problem for a large data volume because of its m:m cartesian join in the actual database query.  The performance issue can be alleviated by using session variables defined as row-wise initialization to pre-filter the data elements that are visible to the logged in user and used in the security data filters.  This approach can be applied to both Opportunity Team and Opportunity facts.
  4. Opaque View
    Instead of using session variables defined as row-wise initialization, we can create opaque views to pre-filter the data elements that are visible to the logged in user.  Since views work the same as table, they can be used to model the data security in the data model level.  Opaque views will always result in a unique list of data elements so that they can be modeled as dimensions and directly joined to the fact, or snow-flake the dimension, and used for security data filters.

As far as a best practice, I would suggest to address the data security in the data model level using the opaque view approach, to model the data security correctly in the first place and have flexibility to adapt any new data security or change in the future.

About the Author

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up