Compare Personal Data Versus Team’s Benchmarks Under Security Restrictions
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”).
“Gates Townsend”).
Updated 03-02-2023
