...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
Table of Contents
| Table of Contents |
|---|
Introduction
Virtual Members provide a powerful way to build advanced queries, measures and KPIs.
Virtual Members allow you to produce dynamic members for a dimension based on a scripting language.
Scripting Guidelines
...
- When creating the expression, you must type everything into a single line, do not use enter to create a new line.
- Field names must start with "@".
- If the field has a space in its name, the space char (" ") must be replaced by the "_SPACE_" keyword.
For example, a field called "DATE OPEN" would be written as @DATE_SPACE_OPEN. - Same applies to the dash ("-") char, it must be replaced by the "_DASH_" keyword.
For example, a field called "RefQuery-Price" would be written as @RefQuery_DASH_Price. - Similarly for a field with a dot("."), it needs to be replaced by "_DOT_" characters. For example, a field "Value.Keyword" needs to be written as Value_DOT_Keyword".
- Mind the parenthesis while creating complex expressions.
- There must be a space before and after the operator. For example, "if(@name1>0)" is an invalid expression. The format should be "if(@name1 > 0)".
Creating a Virtual Member
Virtual Members Page
- You can access the Virtual Members configuration page through the KPI Admin screen (1):
.png?version=1&modificationDate=1620122381195&cacheVersion=1&api=v2)
- The screen will display all Virtual Members you have previously saved (3).
- From there, you can either add a new Virtual Member (2) or edit an existing Virtual Member (4).
- By doing so, the Virtual Member Details pop-up will be shown.
Virtual Members Details Pop-up
...
- Member #1: Will match all rows of the index where the field “TOTAL_TRANSFERS” is less or equal zero.
- Member #2: Will match all rows where “TOTAL_TRANSFERS” is between 1 and 30.
- Member #3: Will match all rows where “TOTAL_TRANSFERS” is between 31 and 60.
Grid Column - Return (3): This is where you can see what Member Value will be returned if the condition is met. In the example above, if we were to select all the members of this Virtual member (DimSample3) in a cube report, the result would be:.png?version=1&modificationDate=1620122380089&cacheVersion=1&api=v2)
| Info |
|---|
| Notice that the members “Option 0”, “Option B” and “Option C” are wrapped in a tag called “#MemberValue<value>#”. It will only work if you are using this notation and the page will give you an error with you do not do this. |
Grid Column - Member Label (4): This is where you can see what Member Label will be visible on the eMite dashboards. Note: The Member Label can be different from the Member Value (see Example 1 below)
| Info |
|---|
| There are also edit and delete buttons next to each Virtual Member condition and buttons to test your virtual member conditions depending on the type of aggregation (COUNT, AVG, SUM, MAX, MIN). Please also make sure to click save to save the Virtual Member. |
Virtual Member Examples
Example 1: Combining First and Last Names (Combining 2 text fields)
An eMite data source contains the following set of data: .png?version=1&modificationDate=1620122382058&cacheVersion=1&api=v2)
In order to combine the fields “FIRST_NAME” and “LAST_NAME”, you can create a single condition in a new Virtual Member as follows:
- Condition: if(@FIRST_NAME is null && @LAST_NAME is null)
- Return: return #MemberValue=<@FIRST_NAME + ' ' + @LAST_NAME>#
- Member Label: First and Last names
.png?version=1&modificationDate=1620122380615&cacheVersion=1&api=v2)
Once tested and saved, this Virtual Member will look like the following in a Cube Report:.png?version=1&modificationDate=1620122380004&cacheVersion=1&api=v2)
| Info |
|---|
| The example above shows the Member Label available for selection (1), is different from the Member Values returned (2). |
Example 2: Grouping First Names starting with “A” and “B”
Next, we can add more complexity by including two new virtual member conditions that will select names starting with “A” and “B”:
.png?version=1&modificationDate=1620122381766&cacheVersion=1&api=v2)
Once tested and saved, this Virtual Member will look like the following in a Cube Report:.png?version=1&modificationDate=1620122380481&cacheVersion=1&api=v2)
Example 3: Performing Calculations
An eMite data source contains the following set of data:
.png?version=1&modificationDate=1620122382126&cacheVersion=1&api=v2)
We want to find out the ratio of TOTAL_TRANSFERS vs GROUP_TRANSFERS and filter the output into the following 3 ranges. i.e.
- Less than or equal to 1
- Less than or equal to 2
- Greater than 2
The Virtual Members conditions will be defined as follows:
.png?version=1&modificationDate=1620122381496&cacheVersion=1&api=v2)
Once tested and saved, this Virtual Member will look like the following in a Cube Report:
.png?version=1&modificationDate=1620122381840&cacheVersion=1&api=v2)
| Info |
|---|
| The labels 1, 2 and 3 shown in the screenshot above are the numeric values defined at the "return" column of the grid. |
As all the values are numbers, it means that you can also use the whole algorithm written for “DimSample2” as a calculated measure:
.png?version=1&modificationDate=1620122380406&cacheVersion=1&api=v2)
You can then combine this calculated measure with another dimension:
.png?version=1&modificationDate=1620122381631&cacheVersion=1&api=v2)
Example 4: Reporting on Null Values
A common requirement is to select the data that is not there. Here is a brief example:
.png?version=1&modificationDate=1620122380820&cacheVersion=1&api=v2)
Using the logic above, we will be able to filter all rows where the “Organization” field contains a null value:
.png?version=1&modificationDate=1620122381424&cacheVersion=1&api=v2)
Example 5: Virtual Members using Regex expressions
Virtual Members Definition is built on a language called Painless which is a built-in script language of Elasticsearch that allows users to create advanced queries. Painless now supports Regex and here is an example:
In the Virtual Member above, we have 2 conditions:
- Select an ASSIGNEE that contains an “A” letter in it.
- Select an ASSIGNEE that contains a “B” letter in it.
Once tested and saved, this Virtual Member will look like the following in a Cube Report:
.png?version=1&modificationDate=1620122381698&cacheVersion=1&api=v2)
Example 6: Calculating Time Spans
Date fields are probably the most used data type in any given dashboard as it helps us to make sense of data over a period of time. Calculating a time interval is a very common use case that can be supported using Virtual Members.
Assume a data source has two date columns, called “OPEN_DATE” and “CLOSED_DATE”.
The requirement is to calculate the hourly difference between these two date fields and bucket the result into 4 distinct buckets:
- Hour diff Greater or Equal to 1.
- Hour diff Greater or Equal to 24.
- Hour diff Greater or Equal to 48.
- Hour diff Greater than 72.
...
- if ( (@CLOSED_DATE - @OPEN_DATE)/3600000.0 >= 1 )
- else if ( (@CLOSED_DATE - @ OPEN_DATE)/3600000.0 >= 24 )
- else if ( (@CLOSED_DATE - @ OPEN_DATE)/3600000.0 >= 48 )
- else if ( (@CLOSED_DATE - @ OPEN_DATE)/3600000.0 > 72 )
After testing and saving it, the members will be available to be used in any dashboard.
- The concept is pretty much the same for Minutes, Seconds and even Days difference:
- Minutes Diffif ( (@CLOSED_DATE - @ OPEN_DATE)/(3600000.0/60) >= 1 )
- Seconds Diffif ( (@CLOSED_DATE - @ OPEN_DATE)/(1000.0) >= 1 )
- Day Diffif ( (@CLOSED_DATE - @ OPEN_DATE)/(3600000.0*24) >= 1 )
| Info |
|---|
| Null values might have an impact when performing these calculations, it really depends on how the data source is structured. If you want to evaluate it, you can append the check "@Field != null" into the Virtual Member condition. |
Example 7: Calculating an SLA to exclude Weekends and Non-Working Hours
If you need to exclude Weekends and Non-Working Hours outside of 8am-5pm, then we could define a virtual member condition like below:
if( (doc['CLOSED_DATE'].date.dayOfWeek != 6 && doc['CLOSED_DATE'].date.dayOfWeek != 7) && (doc['CLOSED_DATE'].date.hourOfDay >= 8 && doc['CLOSED_DATE'].date.hourOfDay < 18))
| Info |
|---|
| Please note that the column is mentioned with a doc preceding it in square brackets so that the script will be executed in Elastic Search directly. |
Example 8: Using Virtual Members to Group Queues
...
| Table of Contents | ||||
|---|---|---|---|---|
|
...
Overview
Virtual Members provide a powerful way to build advanced queries, measures, and KPIs. It allows you to produce dynamic members for a dimension based on a scripting language. It also would allow you to automatically generate matching KPIs for the conditions set for the Virtual Members.
This page aims to provide you with easy-to-follow steps when working with Virtual members based on common use cases outlined in this page. For any other use cases pertinent to Virtual Members, please reach out to the eMite Consulting Team or your eMite account executive for further guidance.
...
Scripting Guidelines
General rules to follow when creating Virtual Members:
When creating the expression, you must type everything into a single line. Do not use Enter to create a new line.
Field names must start with "@".
There are some special characters in field names which must be replaced by keywords:
The space character (" ") must be replaced by the "_SPACE_" keyword. For example, a field called DATE OPEN is written as @DATE_SPACE_OPEN.
The dash character ("-") must be replaced by the "_DASH_" keyword. For example, a field called RefQuery-Price is written as @RefQuery_DASH_Price.
The dot character (".") must be replaced by the "_DOT_" keyword. For example, a field "Value.Keyword" is written as @Value_DOT_Keyword.
Mind the parenthesis while creating complex expressions.
There must be a space before and after the operator. For example, "if(@name1>0)" is an invalid expression. The format should be "if(@name1 > 0)".
You may also check out Elastic’s Painless Scripting Guide here.
...
Creating a Virtual Member
Go to KPI Admin screen to access the Virtual Members configuration page. All Virtual Members previously saved will display on the screen.
Click the Add new record button.
To edit an existing Virtual Member, click on its edit button.
...
The Virtual Member Details will display.
Virtual Members Details Pop-up
The following screenshot is an example of a fully defined Virtual Member.
...
Field Name | Description | ||
|---|---|---|---|
Name | What the Virtual Member will be called. Keep this name in mind when linking it with a dimension. | ||
Description | Brief description of the Virtual Member | ||
Connection, Cube, Dimension | Used to select the dimension that you want to link the Virtual Member with | ||
Condition | Contains the logical conditions for each Virtual Member. In the example above, there are 3 conditions defined:
| ||
Return | Contains the Member Value that will be returned if the condition is met. In the example above, if we were to select all the members of this Virtual member (DimSample3) in a cube report, the result would be:
| ||
Member Label | Contains the Member Label that will be visible on the eMite dashboards. Note: The Member Label can be different from the Member Value (see Example 1 below).
|
...
Common Use Cases
Grouping Queues
In this example, we will look at how Virtual Members can be used to group queues based on characters within the queue names.
1. Fill out the Name field with what you want the new Virtual Member to be displayed as.
| Note |
|---|
The name you give the virtual member will also be displayed as the dimension when creating your cube report later. Please keep your users in mind. |
...
2. Select the appropriate index within which this should appear with the Cubes drop down. In this example, QueueHistory index is selected.
3. Since we want this to be a new Dimension, leave the Dimensions box blank and tick Create a new dimension.
For the purpose of this example, we are using the following expression:
| Code Block |
|---|
if(@QueueName != null && doc['QueueName'].value.startsWith('SC')) |
This will check that the value of the QueueName field is not null, and check for any which start with the letters “SC”, as shown in the image below:
...
4. Click Save. Once saved, this will create the new Virtual Member and also create the new Dimension.
5. Click the Count button to check the returned values. This will give you an indication of the number of times this string has been encountered.
...
You can replace “SC” with the first few characters of your own naming conventions to suit your unique needs.
Now you can create a standard cube report using this new dimension as shown below:
...
You can verify the data by linking the cube to the drillthrough datablock as shown below:
...
To group queue names which include specific characters:
This can be achieved by following the above steps and substituting the condition with the following:
| Code Block |
|---|
if(@QueueName != null && doc['QueueName'].value.contains('EMEA')) |
We have replaced value.StartsWith, with value.contains.
In this example, we used the expression to ensure that the field is not null and to find all field content that contain the text “EMEA”. This text will be specific to your requirements.
The value member will look like this:
...
There are some syntax differences between a text type field and a keyword type field for Virtual Member.
For a text field using comma analyser in Elasticsearch, ie QueueName
Elasticsearch will store the field as array and sort the array items in alphabetical order.
doc['QueueName'].value will return the first item in the array.
doc['QueueName'].values will return the whole array.
To do exact match with the array items, use doc['QueueName'].values.contains('searchterm') only when one of the array item equal to searchterm will match it.
To do wildcard match with the array items, first convert the array to string. Use doc['QueueName'].values.toString().contains('searchterm'). All the array items that contain searchterm will match it.
...
Reporting on Null Values
A common requirement is to select the data that is not there. Here is a brief example:
...
Using the logic above, we will be able to filter all rows where the “Organization” field contains a null value:
...
Aliasing DNIS/ANI
In this example, we will look at how Virtual Members can be used to
...
To group queue names which start with specific characters:
alias DNIS/ANI based on characters within the DNIS/ANI.
1. Fill out the Name field with what you want the new
...
Virtual Member to be displayed as.
| Note |
|---|
The name you give the virtual member will also be displayed as the dimension when creating your cube report later |
...
. Please keep your users in mind. |
...
2. Select the appropriate index within which this should appear with the Cubes drop down
...
. In this example
...
, QueueHistory index is selected.
...
3. Since we want this to be a new dimension, leave the Dimensions box blank and tick
...
Create a new dimension.
For the
...
purpose of this example, we are using the following expression:
| Code Block |
|---|
...
...
if( |
...
@DNIS != null && doc[' |
...
DNIS'].value.startsWith(' |
...
DNIS number')) |
This will check that the value of the QueueName field is not null, and
...
check for any which start with the letters
...
“SC”, as shown in the image below:
...
4. Click Save. Once saved, this will create the new
...
Virtual Member and also create the new
...
Dimension.
...
5. Click the Count button to check the returned values
...
. This will give you an indication of the number of times this string has been encountered.
...
You can replace
...
“SC” with the first few characters of your own naming conventions to suit your unique needs.
Now you can create standard cube report using this new dimension as shown below:
...
You can verify the data by linking the cube to the
...
drillthrough datablock as shown below:
...
To group queue names which include specific characters:
This can be achieved by following the above steps and substituting the condition with the following:
| Code Block |
|---|
...
...
if(@QueueName != null && doc['QueueName'].value.contains('EMEA')) |
We have replaced value.StartsWith, with value.contains.
In this example, we
...
used the expression to ensure that the field is not null and
...
to find all field content that contain the text “EMEA”. This text will be specific to your requirements.
...
The value member will look like this:
...
...
There are some syntax differences between a text type field and a keyword type field for
...
Virtual Member.
For a text field using comma
...
analyzer in
...
Elasticsearch, ie QueueName
...
Elasticsearch will store the field as array, and sort the array items in alphabetical order.
doc['QueueName'].value will return the first item in the array.
doc['QueueName'].values will return the whole array.
To do exact match with the array items, use doc['QueueName'].values.contains('searchterm')
...
only when one of the array item equal to searchterm will match it.
To do wildcard match with the array items, first convert the array to string.
...
Use doc['QueueName'].values.toString().contains('searchterm')
...
. All the array items that contain searchterm will match it.
...
Testing
...
Virtual Members
When adding a new Virtual Member Definition,
...
there are buttons on the bottom of the
...
Virtual Members Details page.
...
Once you're done creating a new Virtual Member definition, you
...
can test if the aggregation will work on an actual dashboard.
...
In this very example, the axis generated will be exclusively numeric (items in yellow in the last picture). That means that you
...
can test all basic measures.
In contrast, if one or more axis (return column) have text in it, you
...
can only
...
use the count measure.
If the test is successful, a dialog box similar to the below is shown:
...
Otherwise, a popup with an error will be displayed.
...
Additional Reference Information
Virtual Members definitions are built on a language called Painless which is a built-in scripting language of Elasticsearch.
Most of the time, you'll be dealing with string comparisons to evaluate a condition, i.e.
...
If (XXX is true), then it returns something 'YYY'.
XXX and YYY are usually string/text, so you can use the below references for any type of functions around string and text manipulation:
You may also check more Elastic’s Painless Scripting Guide here.
...