Using Strategy OLAP Services, it is possible to apply security filters at the view report level. For information about general rules that apply when security filters are applied on view reports based on Intelligent Cubes, refer to the following Strategy Knowledge Base document:
KB30696: Overview of security filter application to view reports based on Intelligent Cubes in MicroStrategy OLAP Services
This document discusses a specific case regarding how security filters are applied when there are complex metrics in the Intelligent Cube definition. Complex metrics here refer to metrics which are defined using multiple metrics. Common examples of such metrics are:
The ground rule in such cases is that the complex metric is displayed on a view report if and only if all the metrics in the Intelligent Cube individually fulfill the security filter conditions. In other words, a logical AND operator is applied between all the individual metrics which are used in the definition of a complex metric.
The examples below are based on the Strategy Tutorial project and discuss how security filters are applied for the three types of complex metrics mentioned above. For the purpose of this example, consider that the view report is executed by a user who has a security filter defined as:
Region = {Mid-Atlantic, Northeast}, bottom level = Region
Compound Metrics:
Consider an Intelligent Cube with attributes Region and Call Center and 3 metrics: M1, M2 and M3
M1 = Sum(Revenue) {~, Region} - Revenue metric calculated at the Region level
M2 = Sum(Cost) {~, Region} - Cost metric calculated at the Region level
M3 = M1+M2 - Compound metric
If the security filter user executes a view report which has all the objects in the Intelligent Cube, all the metrics will be shown to the user (for the regions to which the user has access as defined by the security filter). This is because metrics M1, M2 are at the Region level and the security filter application is also at the Region level. Hence, it is considered safe to display M3 (the compound metric) to the user. The user would see data as shown below:
As seen from the cube subsetting instructions given below, the compound metric is also retrieved along with the other 2 metrics and the security filter for Region is applied in the WHERE clause.
Sql
select region@ID,
region@desc,
call center@id,
call center@desc,
M01,
M02,
M03
from Cube_1
where region@ID in (2, 1)
Nested Metrics:
Consider an Intelligent Cube with attributes Region and Call Center and 2 metrics: M1 and M2.
M1 = Sum(Revenue) {~, Region} - Revenue metric calculated at the Region level
M2 = Avg(Sum(Revenue) {~ , Region} ) {~ , Call Center} - The inner metric is calculated at the Region level and the outer average metric is calculated at the Call Center level.
If the security filter user executes a view report which has all the objects in the Intelligent Cube, as shown below, only the metric M1 will be shown to the user (for the regions to which the user has access as defined by the security filter). This is because the metric M2 is calculated at the Call Center level which is outside the scope (because the bottom range of the security filter is Region) of the security filter.
As seen from the cube subsetting instructions below, the metric M2 is not retrieved.
Sql
select region@ID,
region@desc,
call center@id,
call center@desc,
M01
from Cube_1
where region@ID in (2, 1)
Metrics with Apply functions
Consider an Intelligent Cube with attributes Region and Call Center and Metrics: M1, M2 and M3
M1 = Sum(Revenue){~} - Revenue metric at report level
M2 = Sum(Revenue) {~, Region} - Revenue metric at Region level
M3 = ApplySimple("CASE WHEN #1 > 0 THEN #0 ELSE #1 END ", M2, M1) - Apply simple metric using metrics M1 and M2. If metric M1 is greater than 0, the result is M2.
If the security filter user executes a view report which has all the objects in the Intelligent Cube, as shown below, only the metric M2 will be shown to the user (for the regions to which the user has access as defined by the security filter). This is because metric M1 which is defined at the report level also includes Call Center, which is outside the scope of the security filter. For metric M3, even though the metric always evaluates to M2 (which is safe to display to the security filter user), because metric M1 is used in the evaluation, it is not displayed to the user.
