Get Aggregated Data Out of Visier

Learn how to perform an aggregate query using the Data Model API and Data Query API.

Introduction

The Data Model API and Data Query API provide you with the ability to discover and inspect the data model objects within your solution and perform aggregation and list queries to retrieve data from Visier.

In this tutorial, you will learn how to retrieve information out of Visier in an aggregate form. We will first walk you through how to navigate the Data Model API to find the required information to perform an aggregate query using the Data Query API, and then show you how to form an aggregate query.

To demonstrate we'll take you through a hypothetical scenario where we use the Data Model API and Data Query API to answer a business question.

You will learn how to:

• Call the Data Model API to retrieve available objects
• Perform an aggregate query

Prerequisites

Before beginning this tutorial, you will need:

• A Visier account, API key, and security token. See API Authentication and Get Started with Data Query API.

• An API client such as Postman. See Use Postman to Call Visier APIs.

• Foundational understanding of Visier's data model.
• Basic understanding of the relationship between object types.

Scenario

We've been presented with a business goal to achieve 40% representation of women in managerial positions in our organization by 2023. Before we start planning, we need to understand the current representation of women managers.

Steps

Determine what information we want

To get started, let's determine what information we want to get from our Visier solution based on our business goal or question. This will help us narrow down which object types we need to retrieve from the data model and what specific IDs we need to gather for our query.

Knowing our goal is to understand the current representation of women managers in our organization, we want to retrieve the follow object information:

• A metric that will show us how many employees we currently have
• A dimension that will group our results by gender so we can compare how many women managers we have to other groups
• A selection concept that will filter the employee population to managers

Request and retrieve metric objects

Let's send our first request to retrieve the list of all metrics to find the relevant metric that measures how many active employees we have.

GET https://{vanity_name}.api.visier.io/v1/data/model/metrics

In each request in this tutorial we will replace the {vanity_name} with our tenant name jupiter and include the API key and security token as a header.

1
2
3
curl -X GET --url 'https://jupiter.api.visier.io/v1/data/model/metrics' \
-H 'apikey:12345'
-H 'Cookie:VisierASIDToken=abc123'

The response will return a list of all metrics which includes the following fields:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
    "metrics": [
        {
            "id": "employeeCount",
            "displayName": "Headcount",
            "description": "The number of employees in the organization.",
            "dataStartDate": "1522627200000",
            "dataEndDate": "1604102400000",
            "analyticObject": "Employee"
        },
        {
            "id": "employeeTenureAverage",
            "displayName": "Tenure",
            "description": "The time in years that employees have worked for the organization.",
            "dataStartDate": "1522627200000",
            "dataEndDate": "1604102400000",
            "analyticObject": "Employee"
        },
        {
            "id": "exitAverageTenure",
            "displayName": "Employee Exit Average Tenure",
            "description": "The average tenure, in years, of employees who exited the organization during the period.",
            "dataStartDate": "1514764800000",
            "dataEndDate": "1622419200001",
            "analyticObject": "Employee_Exit"
        }
    ]
}

By looking through the list of metrics, we find a metric with a description that matches what we want to measure, “the number of employees in our organization”.

Line 4 shows that the ID for the metric is employeeCount, the display name is Headcount, and the metric is based on the analytic object Employee.

Now that we have this information, we need to copy it since it will be used to make additional requests to the data model, as well as in our query. Let's copy the metric ID employeeCount as well as the analytic object Employee.

Request and retrieve dimension objects

Next, we want to find out which dimensions we can group our employees by, specifically gender. We would use this request:

GET https://{vanity_name}.api.visier.io/v1/data/model/analytic-objects/{analyticObjectId}/dimensions

To retrieve this information our request needs to specify the analytic object we want to retrieve dimensions for, in this case Employee.

GET https://jupiter.api.visier.io/v1/data/model/analytic-objects/Employee/dimensions

The response will return a list of all employee dimensions which includes the following fields:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
{
    "dimensions": [
        {
            "id": "Pay_Level",
            "displayName": "Job Pay Level",
            "description": "The pay level or pay grade of the specified job."
        },
        {
            "id": "Performance_Rating",
            "displayName": "Performance Rating",
            "description": "The last performance evaluation of this person."
        },
        {
            "id": "Gender",
            "displayName": "Gender",
            "description": "The gender that the employee identifies with (e.g., woman, man, non-binary).",
            "levels": [
                {
                    "id": "(All)",
                    "displayName": "Level 0"
                    "depth": 0
                },
                {
                    "id": "Gender",
                    "displayName": "Gender",
                    "depth": 1
                }
        }
    ]
}

Looking through the list of dimensions and their descriptions, we were able to find a dimension that groups our employees by gender. Line 14 shows that the ID and display name for the dimension is Gender, and that the dimension has levels associated with it.

The (All) level ID is a summary level and the Gender level ID will include values for each gender. Since we want to retrieve values for each gender we will be using the Gender level ID in our query.

Let's copy the dimension ID Gender as well as the level ID Gender as we will be using these in our query.

Request and retrieve selection concept objects

Next, we need to retrieve available selection concepts to see if we have one that filters employee data by managers.

GET https://{vanity_name}.api.visier.io/v1/data/model/analytic-objects/{analyticObjectId}/selection-concepts

This request will be very similar to the last where we need to include the analytic object we want to retrieve selection concepts for which is Employee.

GET https://jupiter.api.visier.io/v1/data/model/analytic-objects/Employee/selection-concepts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
{
    "selectionConcepts": [
        {
            "id": "isManager",
            "displayName": "Manager",
            "description": "An employee who is a manager."
        },
        {
            "id": "isMale",
            "displayName": "Male",
            "description": "A person whose gender is male."
        },
        {
            "id": "isTopTalent",
            "displayName": "Top Talent",
            "description": "Employees identified as top talent."
        }
    ]
}

After looking through the list of selection concepts, we were able to find a selection concept that filters employee data by managers. Line 4 shows that the ID for the selection concept is isManager. Let's copy the selection concept ID to use in our query.

Form the aggregate query

Now that we've gathered the required information from the data model, we can perform an aggregation query that shows us how many women employees we have compared to other genders, and how many of those employees are managers.

We will include the following IDs in the body of our query:

• Metric ID: employeeCount
• Dimension ID: Gender
• Dimension level ID: Gender
• Selection concept ID: isManager
• Analytic object ID: Employee

When forming our query we also need to specify the time interval. In order to understand the current representation of female managers, we've determined it would be helpful to look at the trend for the past 6 months.

Our request will specify the following:

• intervalPeriodType, this will be set to MONTH.
• intervalCount, this will be set to 6 to retrieve values for 6 months.
• fromDateTime, this will be set to the current date, 2021-01-01

Keep this is mind when working with the time interval:

• The default TimeDirection is backwards which means the data in the response will move backwards from the specified fromDateTime.
• The fromDateTime is exclusive. In the below request, the fromDateTime is set at January 1, 2021 to retrieve values up to and including the last day of December.

View sample JSON request

Copy

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
POST https://{vanity_name}.api.visier.io/v1/data/query/aggregate

{
  "query": {
    "source": {
      "metric": "{metricId}"
    },
    "axes": [
      {
        "dimensionLevelSelection": {
          "dimension" : {
            "name": "{dimensionId}",
            "qualifyingPath": "{analyticObjectId}"
          },
          "levelIds": [
            "{levelId}"
          ]
        }
      }
    ],
    "filters": [
        {
            "selectionConcept": {
                "name": "{selectionConceptId}",
                "qualifyingPath": "{analyticObjectId}"
            }
        }
    ],
    "timeIntervals": {
      "fromDateTime": "{YYYY-MM-DD}",
      "intervalPeriodType": "{intervalType}",
      "intervalCount": {number}
    }
  }
}

The complete request will look like the following:

POST https://{vanity_name}.api.visier.io/v1/data/query/aggregate

{
  "query": {
    "source": {
      "metric": "employeeCount"
    },
    "axes": [
      {
        "dimensionLevelSelection": {
          "dimension" : {
            "name": "Gender",
            "qualifyingPath": "Employee"
          },
          "levelIds": [
            "Gender"
          ]
        }
      }
    ],
    "filters": [
        {
            "selectionConcept": {
                "name": "isManager",
                "qualifyingPath": "Employee"
            }
        }
    ],
    "timeIntervals": {
      "fromDateTime": "2021-01-01",
      "intervalPeriodType": "MONTH",
      "intervalCount": 6
    }
  }
}

View sample SQL-Like request

Copy

1
2
3
4
5
POST https://{vanity_name}.api.visier.io/v1/data/query/sql

{
    "query": "SELECT employeeCount() AS Count, level(Gender, 'Gender') AS Gender FROM Employee WHERE Visier_Time IN periods(date(\"2021-01-01\"), 6, period(1, Month)) AND isManager = TRUE"
}

Explore the aggregate response

You can define the response as JSON or CSV. The default response format is JSON, however since we are retrieving multiple data points, let's use the Accept header value text/csv.

The response will return six months of manager headcount values by gender up to January 1, 2021 in an easy to read format.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Measures,DateInRange,Gender
56.0,2020-08-01T00:00:00.000Z,
146.0,2020-08-01T00:00:00.000Z,Female
151.0,2020-09-01T00:00:00.000Z,Male
150.0,2020-09-01T00:00:00.000Z,Female
150.0,2020-10-01T00:00:00.000Z,Male
157.0,2020-12-01T00:00:00.000Z,Male
155.0,2020-11-01T00:00:00.000Z,Male
59.0,2021-01-01T00:00:00.000Z,
60.0,2020-11-01T00:00:00.000Z,
63.0,2020-12-01T00:00:00.000Z,
150.0,2021-01-01T00:00:00.000Z,Female
149.0,2020-10-01T00:00:00.000Z,Female
58.0,2020-10-01T00:00:00.000Z,
147.0,2020-12-01T00:00:00.000Z,Female
148.0,2020-11-01T00:00:00.000Z,Female
154.0,2021-01-01T00:00:00.000Z,Male
156.0,2020-08-01T00:00:00.000Z,Male
56.0,2020-09-01T00:00:00.000Z,

Now you know how to navigate the Data Model API in order to find the required information to perform an aggregate query using the Data Query API.

As a result of our query we were able to get aggregated values that showed us six months of manager headcount data and its distribution among different genders for our organization. We can take this information and use it when planning how many hires we need by when in order to reach our goal.

Next, take a look at this tutorial that shows you how to Get a List of Records Out of Visier.

Resources

• API Reference
• Data Query API Code Samples