Get a List of Records Out of Visier

Learn how to perform a list query using the Data Model API and Data Query API.

Introduction

This tutorial will walk you through how to perform a list query that provides you with the records that make up a population.

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 a list query

Prerequisites

Before beginning this tutorial, you will need:

Scenario

In the Get Aggregated Data Out of Visier tutorial, we performed an aggregate query that showed the current representation of women managers in our organization. Now that we have this data, we want to get a list of individual manager information to share with our Diversity, Equity & Inclusion Committee.

Steps

Determine what information we want

Before performing our list query, we need to 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 list query.

We've been asked to gather and share the following information about the managers from our aggregate query with the Diversity, Equity & Inclusion Committee:

  • Full name
  • Job name
  • Tenure
  • Age
  • Gender

Because our list query is focused on managers, it will include the same metric ID employeeCount and selection concept ID isManager that we gathered from the data model and used in our aggregate query. However, we need to explore the data model to find additional object information based on the list above.

Knowing that the information we want to retrieve are employee properties, we need to find specific property IDs that match each one.

Request and retrieve employee properties

Let's send a request to retrieve a list of properties.

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

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.

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

Copy
cURL sample request: Retrieve a list of Employee properties
curl -X GET --url 'https://jupiter.api.visier.io/v1/data/model/analytic-objects/Employee/properties' \
-H 'apikey:12345'
-H 'Cookie:VisierASIDToken=abc123'

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

Field

Description

id

The unique ID of the property.

displayName

The localized display name of the property.

description

The localized description of the property.

dataType

The data type of the property, such as Categorical, HourDuration, or Ratio.

primitiveDataType

The primitive data type of the property, such as Number, String, or Boolean.
Copy
Sample response: A list of all properties for the Employee analytic object
{
    "properties": [
        {
            "id": "Employee.Age",
            "displayName": "Age",
            "description": "The current age of the employee.",
            "dataType": "YearDuration",
            "primitiveDataType": "Number"
        },
        {
            "id": "Employee.Birth_Date",
            "displayName": "Birth Date",
            "description": "The employee's date of birth.",
            "dataType": "Date",
            "primitiveDataType": "Date"
        },
        {
            "id": "Employee.Full_Name",
            "displayName": "Full Name",
            "description": "The employee's first and last name.",
            "dataType": "String",
            "primitiveDataType": "String"
        },
        {
            "id": "Employee.Gender",
            "displayName": "Gender",
            "description": "The gender that the employee identifies with (e.g., woman, man, non-binary).",
            "dataType": "String",
            "primitiveDataType": "String"
        },
        {
            "id": "Employee.Hourly_Rate",
            "displayName": "Hourly Rate",
            "description": "The dollar value of base pay per hour.",
            "dataType": "Currency",
            "primitiveDataType": "Number"
        },
        {
            "id": "Employee.Job_Name",
            "displayName": "Job Name",
            "description": "The group of jobs involving similar responsibilities and qualifications that the employee’s job belongs to.",
            "dataType": "String",
            "primitiveDataType": "String"
        },
        {
            "id": "Employee.Last_Promotion_Date",
            "displayName": "Last Promotion Date",
            "description": "The most recent date on which the employee received a promotion.",
            "dataType": "Date",
            "primitiveDataType": "Date"
        },
        {
            "id": "Employee.Tenure",
            "displayName": "Tenure",
            "description": "The number of months the employee has been with the organization.",
            "dataType": "MonthDuration",
            "primitiveDataType": "Number"
        }
    ]
}

By looking through the list of properties, we find matching descriptions based on what we are looking for and copy the IDs.

  • Line 4 shows a property ID Employee.Age that will provide us with the employee's age.
  • Line 18 shows a property ID Employee.Full_Name that will provide us with the employee's full name.
  • Line 25 shows a property ID Employee.Gender that will provide us with the employee's gender.
  • Line 39 shows a property ID Employee.Job_Name that will provide us with the employee's job name.
  • Line 53 shows a property ID Employee.Tenure that will provide us with how long the employee has been with the organization.

Form the list query

Now that we've gathered the property IDs from the data model, we can perform a list query to get individual manager records.

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

  • Metric ID: employeeCount
  • Selection concept ID: isManager
  • Property IDs:
    • Employee.Age
    • Employee.Full_Name
    • Employee.Gender
    • Employee.Job_Name
    • Employee.Tenure

Our list query will include the following fields:

  • Source defines the source data to query, in our case it will be the employeeCount metric.
  • Columns define the detailed information we want to retrieve. We will add a column for each employee property. For example, we will include the property ID for employeeAge in a column which will return the current age of the employee.
  • Filters will include specific data points within a population, in our case we will include isManager to filter only managers in our list.
  • Time interval specifies the time interval for the data we want to retrieve. Our request will specify the following:
    • intervalPeriodType, this will be set to MONTH.
    • intervalPeriodCount, this will be set to 1 to retrieve values for 1 month.
    • 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.
Copy 
POST https://{vanity_name}.api.visier.io/v1/data/query/list

{
    "source": {
        "metric": "{metricId}"
    },
    "columns": [
        {
            "columnDefinition": {
                "property": {
                    "name": "{propertyId}",
                    "qualifyingPath": "{analyticObjectId}"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "{propertyId}",
                    "qualifyingPath": "{analyticObjectId}"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "{propertyId}",
                    "qualifyingPath": "{analyticObjectId}"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "{propertyId}",
                    "qualifyingPath": "{analyticObjectId}"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "{propertyId}",
                    "qualifyingPath": "{analyticObjectId}"
                }
            }
        }
    ],
    "filters": [
        {
            "selectionConcept": {
                "name": "{selectionConceptId}",
                "qualifyingPath": "{analyticObjectId}"
            }
        }
    ],
    "timeInterval": {
        "fromDateTime": "{YYYY-MM-DD}",
        "intervalPeriodType": "{intervalType}",
        "intervalPeriodCount": {number}
    }
}

The complete request will look like the following:

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

{
    "source": {
        "metric": "employeeCount"
    },
    "columns": [
        {
            "columnDefinition": {
                "property": {
                    "name": "Employee.Full_Name",
                    "qualifyingPath": "Employee"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "Employee.Job_Name",
                    "qualifyingPath": "Employee"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "Employee.Tenure",
                    "qualifyingPath": "Employee"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "Employee.Age",
                    "qualifyingPath": "Employee"
                }
            }
        },
        {
            "columnDefinition": {
                "property": {
                    "name": "Employee.Gender",
                    "qualifyingPath": "Employee"
                }
            }
        }
    ],
    "filters": [
        {
            "selectionConcept": {
                "name": "isManager",
                "qualifyingPath": "Employee"
            }
        }
    ],
    "timeInterval": {
        "fromDateTime": "2021-01-01",
        "intervalPeriodType": "MONTH",
        "intervalPeriodCount": 1
    }
}
Copy
Sample
POST https://{vanity_name}.api.visier.io/v1/data/query/sql

{
    "query": "SELECT Employee.Full_Name, Job_name, Employee.Tenure, Employee.Age, Gender FROM Employee WHERE Visier_Time BETWEEN date(\"2020-12-01\") AND date(\"2021-01-01\") AND isManager = TRUE"
}

Explore the list response

You can define the response as JSON, JSON Lines, and CSV. The default response format is JSON, however since we are performing a list query, let's use the Accept header value text/csv to retrieve the response in an easy to read format.

The response below shows us a list of managers for 1 month up to January 1, 2021. It includes:

  • Full name
  • Job name
  • Tenure
  • Age
  • Gender
Copy
Text/CSV sample response: A list of manager employees' full names, job names, tenure, age, and gender for 1 month up to January 1, 2021. 
Employee.Full_Name,Employee.Job_Name,Employee.Tenure,Employee.Age,Employee.Gender
Aldo Justin Cantu,Product Development Manager,22,44,Male
Dalton Salem Martinez,Plant Operations Analyst,60,24,Male
Holden Avery Shields,Senior Recruiter,78,32,Male
Earnest Lyle Kelly,Plant Logistics Manager,56,34,Male
Josh Bruno Spencer,Customer Support Manager,2,47,Unknown
Marian Bridgette Atkins,Assistant Plant Manager,108,29,Female
Alberto Lewis Erickson,Plant Supervisor,25,32,Male
Carissa Kendall Sainz,Assistant Plant Manager,97,44,Female
Joann Amelie Cabrera,Sales Development Representative,12,28,Female
Tauria Lola Buchanan,Product Development Vice President,27,51,Unknown
Bluebell Catalina Mendez,Sales Regional Director,86,53,Female
Justina Yuki Hurst,Plant Supervisor,106,39,Unknown
Cashel Jaxon Shimizu,Sales Operations Director,4,57,Male
Jaylin Davian Tokuda,Plant Manager,132,34,Male
Stephan Justice Villa,Sales Operations Manager,1,46,Male
Francesco Quincy Thurman,Information Security Manager,30,42,Male
Keenan Marcell Pascual,Customer Support Regional Director,27,46,Male
Betty Kira Fife,Product Marketing Director,45,44,Female
Cade Bobbi Diaz,Product Development Manager,69,59,Male
Enoch Darin Merino,Senior Recruiter,131,29,Unknown
Juno Alexis Gimenez,Plant Supervisor,51,43,Unknown
Aspen Cheyenne Russell,Senior Marketing Specialist,27,38,Female
Precious Juliana Perez,Product Director,62,56,Female
Mustafa Enrique Sorenson,Customer Support Manager,8,43,Male
Earnest Draven Gilbert,Recruiter,140,34,Male
Clarence Walker Bader,Product Marketing Director,76,47,Male
Analicia Brooklynn Patel,Product Marketing Director,118,45,Female
Brady Glenn Pedroza,Product Development Manager,15,38,Male
Miles Oberon Allison,Senior Sales Executive,52,27,Male
Darin Spencer Clayton,Software Development Manager,22,42,Male
Tulip Ricky Huerta,HR Services Director,30,48,Unknown
Aaliya Nadia Krueger,Sales Operations Director,26,44,Female
Robin Kalina Salib,Assistant Plant Manager,123,32,Female
Rachael Dakota Tyler,Maintenance Engineer,16,41,Female
Theodore Corbin Roby,Finance Director,5,64,Male
Kelvin Yasiel Rubio,Senior Marketing Specialist,120,40,Male
Lizbeth Annie Maez,Customer Support Manager,3,29,Female
Jaiden Toni Richter,Plant Operations Analyst,95,31,Female
Carolyn Magdalena Vega,Senior Sales Executive,20,32,Unknown
Chace Kazu Malone,Staffing Manager,40,50,Male
Marlee Collette Monsalve,Assistant Plant Manager,96,38,Female
Michelle Clarissa Porter,Senior Maintenance Engineer,25,33,Female
Hudson Marcia Dean,Product Development Vice President,27,55,Female
Michael Elizabella Harrell,Product Marketing Director,16,57,Female
Marigold Gretchen Cody,Senior Marketing Specialist,26,35,Female
Killian Everest Medrano,Plant Supervisor,30,44,Male
Peony Jawa Hebert,Product Development Vice President,27,58,Female
Audra Priscilla Gilbert,Customer Support Vice President,69,56,Female
Wendy Leanne Sellers,Sales Development Representative,20,33,Female
Alyna Theodora Moyer,Maintenance Engineer,152,34,Female
Simon Mauricio Foster,Customer Support Manager,7,37,Male
Dayanara Joann Gardner,Vice President Product Marketing,10,60,Unknown
Mauricio Heaven Watts,Senior Product Marketer,135,37,Male
Elliana Stacie Soto,Product Development Manager,22,45,Female
Blakely Dana Jones,Maintenance Engineer,125,30,Male
Abbygail Jennie Iniguez,Information Security Specialist,5,29,Female
Juana Evelyn Plunkett,Plant Supervisor,105,44,Unknown
Jackie Alexander Olsen,Sales Operations Manager,150,32,Male
Stephan Daragh Hubert,Product Marketing Director,16,57,Male
Leroy Aksel Passmore,Maintenance Engineer,109,33,Male
Katelyn Lorna Gonzales,Vice President Product Marketing,14,46,Unknown
Gabrielle India Palmer,Senior Marketing Specialist,5,40,Female
Titiana Abiah Alford,Customer Support Manager,12,40,Unknown
Cristopher Zackery Molina,Customer Support Team Lead,8,32,Male
Jose Porter Peterson,Sales Operations Director,23,53,Male
Pasquale Robbie Oconnell,Assistant Plant Manager,24,35,Male
Pedro Kenta Barron,Sales Regional Director,29,59,Male
Maria Paula Carla Montiel,Marketing Specialist,10,29,Female
Morris Rafael Jorgensen,Plant Manager,90,46,Male
Ezra Derrick Cardenas,Vice President Product Management,107,54,Male
Edmund Sky Newton,IT Project Office Director,42,45,Male
Terence Herman Gillette,Customer Support Manager,121,45,Male
Ciara Tiana Carlin,Senior Recruiter,143,39,Female
Albia Annia Martin,Assistant Plant Manager,117,42,Female
Alessandra Constanza Bishop,Assistant Plant Manager,1,37,Female
Reid Kermit Vela,Finance Vice President,150,52,Male
Kevin Clark Asato,Product Development Manager,61,45,Male
Tyler Charly Byrne,Senior Marketing Specialist,5,40,Unknown
Gerald Zebulon Benitez,Sales Regional Director,29,53,Male
Jun Vijay Camacho,Customer Support Manager,24,35,Male
Irma Zillah Wilcox,Learning and Development Manager,1,54,Female
Glenda Beverly Salazar,Assistant Plant Manager,2,47,Female
Christine Kelly Yasui,Senior Recruiter,66,34,Female
Joshua Colton Montes,Product Development Manager,23,56,Male
Maurice Seth Oliver,Vice President Product Marketing,25,56,Unknown
Lionel Alexandro Reich,Sales Regional Director,138,64,Male
Amir Victor Sakamoto,Finance Director,14,47,Male
Davion Juan Felipe Stephens,Customer Support Team Lead,28,31,Male
Mathew Simeon Copeland,Marketing Manager,22,43,Unknown
Zayra Lauri Montgomery,Marketing Manager,26,43,Female
Jason Alan Lester,Customer Support Manager,28,32,Male
Diamond Janine Costa,Assistant Plant Manager,2,45,Female
Michaela Liz Loyola,Marketing Vice President,54,48,Female
Patty Noa Cortez,Maintenance Engineer,87,31,Female
Marcus Yuuki Zimmerman,Assistant GM,26,64,Male
Jared Travis Mula,Sales Operations Manager,133,38,Male
Robbie Yousif Estrella,Sales Operations Director,4,44,Male
Simeon Orlando Lozano,Operations Director,20,47,Male
Addison Jonquil Henning,Staffing Manager,136,54,Female
...

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

As a result of our query we were able to get a list of detailed manager information to share with our Diversity, Equity & Inclusion Committee.

Resources