Compare Personal Data Versus Team’s Benchmarks Under Security Restrictions

[intapiuser]

Description

A common visualization is a comparison between personal KPIs and the rest of the team KPI averages. 

The main challenge is when applying a security configuration, the other users’ data is filtered out

Example

•  A typical Elasticube for sales to track performance. A security configuration is set on the SalesRep field taken from "DIM Sales Rep" as seen in Picture 1:

 Picture 1: Elasticube Model Example

• Data sample for the Sales fact table can be seen in Picture 2.
• When security configuration is applied for Sales Rep named “Justine Marriner” the rest with the data will be filtered out. Indicators such as “Total Sales Average” will present a NULL value.

Picture 2: FACT Sales data sample

Solution

Based on:

• Changes in the EC Model
• Security Configuration
• Formula Design

1. Changes in the EC Model

• Adding a Security Key in the Sales Reps dimension table
• “Sales Rep Name” - One type will be serving our need to filtering the personal data
• “All Sales Reps” - The other type will be serving all rest of the Sales Reps

The dimension table will be:

Picture 3: DIM Sales Rep data sample

• This can be created by the following SQL for a custom DIM Sales Rep table:

Picture 4: SQL for DIM Sales Rep

The finalized Elasticube will be:

Picture 5: Final Elasticube model which includes the new DIM Sales Rep

2. Security Configuration

• For example, for Justin Marriener user we will set the following security configuration:

Field: [DIM Sales Rep].Security Key = [Justin Marriener] , [All Sales Reps]

3. Formula Design

A common widget requirement is an column chart the shows the difference between the current user and the rest of his team:

Picture 6: Total Sales Average Amount Comparison widget

• “All Sales Reps”

Filter by the “All Sales Reps” value for both of the calculations:

 Picture 7: Formula for “All Sales Rep”

• “Me”

Filter out (Exclude) the “All Sales Reps” value for both of the calculations:

Picture 8: Formula for “Me” calculation

• All But me

Picture 9: Formula for “All But Me”

To review the finalized solution, please log in as one of the users (As seen in the example,
“Gates Townsend”).