Queries
Transformations
None
|Charts
Transformations
| | Key / value |Queries
Transformations
|Charts
Transformations
to-percentages join index reduce
Queries
OTQL multi-level bucket directives Transformations
None
|Charts
Transformations
| | Key / values |Queries
None
Transformations
|Charts
Transformations
| ## Single number datasets Here is an example dataset with only one [OTQL](/querying-your-data/otql-queries.md) data source, that returns a number : ```json "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](/querying-your-data/activities-analytics-queries.md) : ```json5 "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](/dashboards/charts.md#metric-charts) 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](/querying-your-data/otql-queries.md#aggregation-operations) and [activities analytics dimensions](/resources/data-cubes/creating-a-report.md#dimensions). ```json5 // 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](/dashboards/charts.md#bars-charts), [Pie](/dashboards/charts.md#pie-charts) and [Radar](/dashboards/charts.md#radar-charts) 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. ```json5 "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](/querying-your-data/otql-queries.md#aggregation-operations) and [activities analytics queries with multiple dimensions](/resources/data-cubes/creating-a-report.md#dimensions). ```json5 // 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](/dashboards/charts.md#bars-charts) and [Pie](/dashboards/charts.md#pie-charts) 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. ```json5 "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](/dashboards/charts.md#bars-charts) and [Radar](/dashboards/charts.md#radar-charts) charts to efficienly compare their data.  ## Dataset JSON declaration A dataset is formed with a tree of data sources and transformations chained. ```json5 "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 } ] } ``` {% hint style="info" %} * To learn about OTQL queries, go to [OTQL queries](/querying-your-data/otql-queries.md). * To learn about activities analytics queries, go to [Activities analytics queries](/querying-your-data/activities-analytics-queries.md). * To learn about collection volumes queries, go to [Collection volumes](/advanced-usages/platform-monitoring/collection-volumes.md). * For a list of available transformations, see [Transformations](/dashboards/transformations.md). {% endhint %} ### `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. ```json5 "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.** {% hint style="info" %} 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. {% endhint %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://developer.mediarithmics.io/dashboards/datasets-and-data-sources.md?ask=