Datasets and data sources

A dataset is built based on at least one data source, and optional transformations and processed for visualisation in Charts.

You can retrieve data from the following data sources :

• OTQL queries

• Activities analytics data cube

• Collection volumes data cube

• Resources usage data cube

• Events ingestion monitoring data cube

Depending on the query you run and the transformations you apply, you can build different types of datasets. Here is a recap of which datasets are created from which data sources and transformations and the available visualisations for each.

Dataset	Created from	Compatible with
Single number	Queries OTQL @count and metrics directives Activities analytics without dimensions Collection volumes without dimensions Transformations None	Charts Metric Transformations ratio to-list
Key / value	Queries OTQL bucket directives Activities analytics queries with dimensions Collection volumes queries with dimensions Transformations to-list to-percentages index	Charts Pie Radar Bars Transformations to-percentages join index reduce
Key / value / buckets	Queries OTQL multi-level bucket directives Activities analytics queries with multiple dimensions Collection volumes queries with multiple dimensions Transformations None	Charts Pie Bars Transformations to-percentagesreduce
Key / values	Queries None Transformations join	Charts Bars Radar Transformations reduce

Single number datasets

Here is an example dataset with only one OTQL data source, that returns a number :

"dataset": {
    "type": "OTQL",
    "query_id": 666 // SELECT @count FROM UserPoint
}

You can build the same kind of dataset with a different data source, like activities analytics :

"dataset": {
    "type": "activities_analytics",
    "query_json":  { // This query returns the number of active users
        "dimensions": [],
        "metrics": [
            {
                "expression": "users"
            }
        ]
    }
}

Use this type of dataset in Metric charts to display a single number.

Key / value datasets

Queries in the preceding paragraph were only returning numbers, but you can build key / value datasets with more complex queries like OTQL bucket directives and activities analytics dimensions.

// key-value dataset built with an OTQL query
"dataset": {
    "type": "OTQL",
    "query_id": 666 // SELECT {gender @map} FROM UserProfile
}

// key-value dataset built with an activities analytics query
"dataset": {
    "type": "activities_analytics",
    "query_json":  { // This query returns the number of active users per channel
        "dimensions": [
            {"name": "channel_id"}
        ],
        "metrics": [
            {
                "expression": "users"
            }
        ]
    }
}

You can pass this kind of dataset in Bars, Pie and Radar charts to visualize the content.

Key / value datasets also come from transformations like to-list, to create a list from multiple numbers. You can note the series_title property that gives you control over the title that will be displayed in tooltips and legends.

"dataset": {
    "type": "to-list",
    "sources": [
        {
            "type": "OTQL",
            "query_id": "666",
            "series_title": "Female"
        },
        {
            "type": "OTQL",
            "query_id": "777",
            "series_title": "Male"
        }
    ]
}

Key / value / buckets datasets

You can go further by adding up to three levels of buckets in your dataset with multi-level bucket directives and activities analytics queries with multiple dimensions.

// key-value dataset built with an OTQL query
"dataset": {
    "type": "OTQL",
    "query_id": 666 // SELECT {cat1 @map{cat2 @map{cat3 @map}}} FROM UserProfile
}

// key-value dataset built with an activities analytics query
"dataset": {
    "type": "activities_analytics",
    "query_json":  { // Number of active users per day per channel
        "dimensions": [
            {"name": "date_yyyymmdd"}
            {"name": "channel_id"}
        ],
        "metrics": [
            {
                "expression": "users"
            }
        ]
    }
}

This can then be displayed with Bars and Pie charts, with drill down or multiple / stacking bars.

Key / values datasets

The join transformation with multiple key / value datasets with common keys creates a single dataset with multiple values associated with each key.

"dataset": {
    "type": "join",
    "sources": [
        {
            "type": "OTQL",
            "query_id": 777, // Select {interests @map} FROM UserPoint WHERE ...
            "series_title": "Group 1" 
        },
        {
            "type": "OTQL",
            "query_id": 666, // Select {interests @map} FROM UserPoint WHERE...
            "series_title": "Group 2"
        }
    ]
}

The two groups can be displayed together in Bars and Radar charts to efficienly compare their data.

Dataset JSON declaration

A dataset is formed with a tree of data sources and transformations chained.

"dataset": {
    "type": "transformation-name",
    "sources": [
        { 
            "type": "transformation-name",
            "sources": [
                {
                    // OTQL data source
                    "type": "OTQL", 
                    // ID of the OTQL query to call
                    "query_id": Int, 
                    // Optional. Title of the series for tooltips and legends
                    "series_title": String, 
                    // Optional. Datamart on which to run the query.
                    // Defaults to current datamart
                    "datamart_id": Int,
                    // Optional. To adapt the query to the current scope
                    // for example by adding current segment's query
                    // when dashboard is executed on a segment
                    // Defaults to TRUE
                    // COMING SOON
                    "adapt_to_scope": Boolean
                    // Optional. To run the query in a specific precision
                    // To be used when charts take too long to load and 
                    // a lower precision is accepted
                    // Defaults to FULL_PRECISION
                    "precision": "FULL_PRECISION" | "LOWER_PRECISION" | "MEDIUM_PRECISION"
                }
            ]
        },
        {
            "type": "activities_analytics",
             // JSON representation of the activities analytics query
            "query_json": Object, 
            // Optional. Title of the series for tooltips and legends
            "series_title": String, 
            // Optional. Datamart on which to run the query.
            // Defaults to current datamart
            "datamart_id": Int,
            // Optional. To adapt the query to the current scope
            // for example by only selecting activities of users 
            // that were in the segment while doing it
            // when dashboard is executed on a segment
            // Defaults to TRUE
            // COMING SOON
            "adapt_to_scope": Boolean
        },
        {
            "type": "collection_volumes",
             // JSON representation of the activities analytics query
            "query_json": Object, 
            // Optional. Title of the series for tooltips and legends
            "series_title": String 
        },
        {
            "type": "resources_usage",
             // JSON representation of the activities analytics query
            "query_json": Object, 
            // Optional. Title of the series for tooltips and legends
            "series_title": String 
        },
        {
            "type": "data_ingestion",
             // JSON representation of the activities analytics query
            "query_json": Object, 
            // Optional. Title of the series for tooltips and legends
            "series_title": String 
        },
        {
            "type": "data_file",
            // URI of the JSON data file containing data
            // Format "mics://data_file/tenants/1426/dashboard-1.json"
            "uri": String,
            // Path of the property in the JSON that should be used as dataset
            // This allows you to have multiple datasets in the same JSON file
            // Should use the JSONPath syntax. See https://jsonpath.com/
            // For example, "$[0].components[1].component.data"
            "JSON_path": String,
            // Optional. Title of the series for tooltips and legends
            "series_title": String
      }
    ]
}

• To learn about OTQL queries, go to OTQL queries.

• To learn about activities analytics queries, go to Activities analytics queries.

• To learn about collection volumes queries, go to Collection volumes.

• For a list of available transformations, see Transformations.

series_title property

All data sources have a series_title property. This is useful when combining multiple sources together to set the title associated with each source. This will be reflected in tooltips and legends. Here is an example of a Datamart and a Segment data sources combined together.

"dataset": {
    "type": "join",
    "sources": [
        {
            "type": "OTQL",
            "query_id": 777, // Select {interests @map} FROM UserPoint WHERE ...
            "series_title": "Segment" 
        },
        {
            "type": "OTQL",
            "query_id": 666, // Select {interests @map} FROM UserPoint WHERE...
            "series_title": "Datamart",
            "adapt_to_scope": false
        }
    ]
}

datamart_id property

All data sources have a datamart_id property allowing you to specify the datamart on which to run the query. It defaults to current datamart. This allows you to bring data for an other datamart or to create a dashboard at the community level that aggragates data from sub organisations.

The user loading the dashboard should have the permissions to query the specified datamart or the chart will throw an error for this user.

adapt_to_scope property

By defaults, all data sources will try to adapt to the page on they are executed, with the adapt_to_scope property set to TRUE.

The goal is to :

• Filter data for the current segment when a dashboard is displayed on a segments page

• Filter data based on the current query when a dashboard is displayed on a builder.

For OTQL data sources :

• On home scopes, nothing is changed and the query is run as is.

• On segments scopes, the current segment's query is added at the end of the OTQL query. That means that only OTQL queries FROM UserPoint will adapt to the scope.

• On builders scopes, the current query selected in the builder is added at the end of the OTQL query. That means that only OTQL queries FROM UserPoint will adapt to the scope.

For activities analytics data sources :

• On home and builders scopes, nothing changes and the query is run as is.

• On segments scopes, activities are filters so that only those of users that were in the segment while having the activity will be kept.

If the dashboard is meant to be displayed on segments, only build OTQL queries FROM UserPoint and activities analytics queries unless you want to retrieve data for the whole datamart.

If the dashboard is meant to be displayed on builders, only build OTQL queries FROM UserPoint unless you want to retrieve data for the whole datamart.