Row Level Security In Business Intelligence

What is Row Level Security in Business Intelligence?

Row level security enables the implementation of “Who Sees What” in Business Intelligence. It enables users to control the access to rows in the DataBase tables based on authorization. This Row Level Security can be implemented at different places in the Business Intelligence components. Let’s see the example below with data to understand it better.

From the above data – Brad is associated with the “West” region. So Brad should be able to see data that belongs to “West” region only. Other data should not be rendered when data is presented to Brad. After slicing off the “West” region data for Brad, he would see the below-highlighted data only.

Different approaches and Pros-Cons of each implementation

Row Level security can be implemented in BI using Visualisation Tool, OLAP feature or Data Driven. Let’s see what are different Pros and Cons of each one on different scenarios.

Using Visualisation Tool – (Overall Complexity – Tedious)

Pros – This is directly in the visualization tool and the IT cycle can be avoided.

Cons –

1. Needs to be implemented explicitly across all Dashboards/Reports.
2. Multiple visualization tools will duplicate the efforts.

OLAP Feature – (Overall Complexity – Tedious)

Pros –

1. Needs to be put up explicitly in the OLAP database. Here there would be a lot of options to implement. E.g. Optimistic Security (Allow only those specified), Pessimistic (Allow everything except those specified).
2. Security can be provided on one or more dimensional data.
3. However different consumer visualization tools it might be – security will be implemented.

Cons – Every time there is a change – the OLAP database needs to be processed and it might be a time-consuming process.

Data Driven – (Overall Complexity – Easy)

Pros –

1. Source Application data can be used in implementing security.
2. So whenever there is a change in the source data – it will be automatically pushed to the BI module and the change will take effect.
3. Only Single Row needs to be added in Security Dimension.

Cons –

1. Each consumer should implement this security and it doesn’t get implemented by its own.
2. If this is not implemented properly – it might even blow up the data and wrong results would be seen on the visualization too.
3. Security might be based on more than one Dimension and it becomes a tedious task.

Using Visualisation Tool – (Overall Complexity – Tedious)

Pros – This is directly in the visualization tool and the IT cycle can be avoided.

Cons –

1. Needs to be implemented manually across all Dashboards/Reports.
2. Multiple visualization tools will duplicate the efforts

OLAP Feature – (Overall Complexity – Tedious)

Pros – Needs to be put up in the OLAP database. Here there would be a lot of options to implement. E.g. Optimistic security (Allow only those specified), Pessimistic (Allow everything except those specified).

Cons – Every time there is change – the OLAP database needs to be processed and it might be a time-consuming process

Data Driven – (Overall Complexity – Easy)

Pros –

1. Source Application data can be used in implementing security.
2. So whenever there is a change in the source data – it will be automatically pushed to the BI module and the change will take effect.
3. Only Single Row needs to be added in Security Dimension.

Cons –

1. Each consumer should implement this security and it doesn’t get implemented by its own.
2. If this is not implemented properly – it might even blow up the data and wrong results would be seen on the visualization.
3. Security might be based on more than one Dimension and it becomes a tedious task.

Using Visualisation Tool – (Recommendation – No)
Needs to be implemented explicitly across all the Dashboards/Reports (In case of a new or modification of the existing user)

OLAP Feature – (Recommendation – No)

Needs to put up explicitly in the OLAP DB

Data Driven – (Recommendation – Yes)

By simply adding or modifying the existing row in the DB

Using Visualisation Tool – (Overall Complexity – Easy)

Availability of tool feature so no need to maintain any security table

OLAP Feature – (Overall Complexity – Easy)

Availability of tool feature so no need to maintain any security table

Data Driven – (Overall Complexity – Difficult)

If Security implemented for multiple Dimension then more tables needs to be maintained

Using Visualisation Tool – (Efforts – Low)

Due to Availability of the tool feature – It takes less time to add, modify and provide access to the user

OLAP Feature – (Efforts – Low)

Due to Availability of the tool feature – It takes less time to add, modify and provide access to user

Data Driven – (Efforts – High)

Write a code to create security dimension and maintain the same

Using Visualisation Tool – (Good)

It will slice the data at the tool level and so its little slower

OLAP Feature – (Excellent)

It will slice the data at the OLAP level and so faster by leveraging on database engine capabilities

Data Driven – (Excellent)

It will slice the data at the DB level and so faster by leveraging on database engine capabilities