BI information security

Peter Scott

Nov 16, 2007 • 2 min read

These days there are a wealth of restrictions or regulations (SOX, UK Data Protection Act to name but two) on data access and coupled with the simple commercial need to hide sensitive information that means unfettered access to data warehouse data is a thing of the past. Fortunately products such as OBI EE (and SE-One) have ways to implement information security. For example; the web interface to OBI can prevent users from accessing specific folders and dashboards, the BI administration tool can control access to subject areas and even down to individual measures and data levels, and the Security Manager can assign data filters to users or groups to prevent access certain data items - and that is without considering what we can do by using database level security to audit and control row level access.

But sometimes interesting data access challenges come up. One organisation I know of sells sales information from their retail data warehouse back to their suppliers, most suppliers who opt-in to the scheme go the budget route - they get a monthly extract that shows their product sales by store (value and quantity) and the contribution of the sale made to total sales of products in the same category - so if the supplier supplied 'Blogger Bleach' with is in my bleach product category as one of their products they would get a row per store that gave the quantity and value of Blogger Bleach and the proportion of bleach (the product category) sales by quantity and value. But some customers pay more for premium access to the more detailed analysis. Here they are given access to the web based query tool (and for this customer it is not OBI EE) and allowed to drill down on detail or navigate to more aggregated data. Of course, commercial restrictions apply - the supplier can not see any information that identifies:

Individual customers - but they can see customer segment
Sales of individual products not supplied by the supplier
Product Category aggregated sales for categories not supplied

Fortunately, supplier is an attribute of product (and each product has only one possible supplier) and product category the hierarchy parent of product. We can then build a table or view over the denormalised product dimension table to contain the distinct product categories supplied by each supplier (one row per product category per supplier id) and use that as join condition on the product dimension table; for a given supplier this will restrict us to seeing only products belonging to product categories supplied. We then hide the identity of the products not supplied by the supplier (and also the other suppliers' names) by using case statements on the selection clauses generated by the query tool.

For a tool like OBI EE Answers it is a simple proposition to give suppliers their own subject area so they can not access data they would never be permitted to see. Supply an initialisation block that stores the supplier ID (which may or not be the same as their user name) as a session variable to mask the product selection on the products they supply and restrict the product categories by using the supplied category table (just in case they omit selecting a product).

Sign up for more like this.