Showing results for 
Search instead for 
Did you mean: 
Community Team Member
Community Team Member
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
  •  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
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”).
Version history
Last update:
‎03-02-2023 08:35 AM
Updated by:
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: