# Holistics Docs (4.0) > Documentation for Holistics - Self-service BI Platform This file contains all documentation content in a single document following the llmstxt.org standard. ## Holistics API ## Introduction At Holistics, we treat API interface as a first-class citizen. Our goal is to allow you to flexibly integrate Holistics with your workflow as much as possible. We do this by trying to design an extensive, easy to use API coupled with readily available libraries. :::tip OpenAPI Specification Download the [OpenAPI spec (YAML)](/api/v2.yml) to use with API clients, code generators, or AI agents. ::: :::caution **Holistics API version does not correlate with Holistics feature version**. For example, Holistics API v2 can support operations on Holistics 2.0, 2.7 and 3.0. The latest API version is API v2. API v1 and below is considered legacy API. Legacy API is functional but is not in active development/ maintenance anymore. ::: ## Use cases These are some of the most common things teams reach for the API to do. Programmatically export CSV or Excel data from any dashboard or chart, so you can pass live numbers to your other applications. Kick off data schedule jobs from your own workflow, on your own triggers, instead of waiting for the next scheduled run. List, fetch, clone, and manage dashboards. Set up email report delivery to your customers when a new account onboards. Provision and deprovision users programmatically to keep access in sync with your own systems. :::info Our API is currently work-in-progress, so some of the use cases/features might not be available. If you have an API use case you want to support/prioritize, please reach out to our Support. ::: ## Data Center Regions API data is limited to specific regions. You must use the correct subdomain for your data center region. | Region | API Base URL | |--------|--------------| | Asia-Pacific (APAC) | `https://secure.holistics.io/api/v2` | | Europe (EU) | `https://eu.holistics.io/api/v2` | | United States (US) | `https://us.holistics.io/api/v2` | :::tip Finding your region Check the URL when you log into Holistics: - `secure.holistics.io` → APAC - `eu.holistics.io` → EU - `us.holistics.io` → US See [Data Centers](/docs/security-compliance/data-centers#how-do-i-know-which-data-center-im-on) for more details. ::: ## Authentication All API requests require authentication using an API key passed in the `X-Holistics-Key` header. ```bash curl -X GET \ -H "Accept: application/json" \ -H "Content-Type: application/json" \ -H "X-Holistics-Key: YOUR_API_KEY" \ https://secure.holistics.io/api/v2/users/me ``` For step-by-step instructions on enabling API access, generating your API key, and making your first call, see the [Getting Started guide](/api/v2/getting-started). --- ## Jobs API ## What is Job? Every time a user starts executing something in Holistics, a Job is created. It is then picked up and executed by Holistics background workers. Learn more about our [jobs queue mechanism](/docs/jobs/queues-and-workers) here. ## Get Job info ``` GET /jobs/:job_id.json ``` Parameters: - `job_id`: Integer. Job ID Response: ```json { "id": 1138, "status": "success", "created_at": "2018-08-24T08:19:19.596Z", "source_id": 3, "source_type": "DataImport", "user_id": 3, "start_time": "2018-08-24T08:19:20.670Z", "end_time": "2018-08-24T08:19:20.924Z", "tenant_id": 2, "duration": 0.253676, "cancellable": true, "source_method": "execute" } ``` ## Last run jobs Retrieve list of jobs last run. ``` GET /jobs/last_run_jobs.json ``` Parameters: - source_type: Possible values: 'DataImport', 'DataTransform', 'EmailSchedule', 'ScheduleCache' - ids: IDs of the jobs' sources. These sources must have the same type specified in param `source_type` Response: ```json { "3": { "id": 1138, "status": "success", "start_time": "2018-08-24T08:19:20Z", "end_time": "2018-08-24T08:19:20Z", "created_at": "2018-08-24T08:19:19Z" } } ``` --- ## Group API :::caution Warning This is a **legacy API**. It is still functional but is **not in active development/ maintenance** anymore. ::: These API endpoints allows the admin to manage their groups programmatically. ## How to use these API endpoints ### Obtain API Key Before using the API, please obtain the API key by following the [Getting Started guide](/api/v2/getting-started). ### Send API requests to Holistics To use these APIs, simply append Holistics' host URL `secure.holistics.io` with your chosen endpoint. For example, to call the API [Get all users in a tenant with full information](/api/v1/user-api#get-all-users-in-a-tenant-with-full-information), you will have to use **GET** request with this URL: `secure.holistics.io/user.json`. ## Get all groups in a tenant **Sample request:** ``` GET /groups.json ``` **Sample response:** ```json [ { "id": 1, "name": "Singapore", "num_user": 2 }, { "id": 2, "name": "Vietnam", "num_user": 2 }, { "id": 3, "name": "Indonesia", "num_user": 0 } ] ``` ## Create a new group **Sample request:** ``` POST /groups.json ``` **Sample body request:** ```json { "group":{ "name" : "Test" } } ``` **Sample success response:** ```json { "id": 1, "name": "Test", "created_at": "2018-09-07T09:18:43.742Z", "updated_at": "2018-09-07T09:18:43.742Z", "tenant_id": 5 } ``` ## Update information of an existing group **Sample request:** ``` PUT /groups/group_id.json ``` **Permitted params:** - **name:** The name of your group **Sample body request:** ```json { "group":{ "name" : "New Admin Group" } } ``` ## Delete an existing group **Sample request:** ``` DELETE /groups/group_id.json ``` ## Add a user into a group **Sample request:** ``` PUT /groups/group_id/user/user_id ``` Sample success response: ```json { "status": "OK" } ``` ## Remove a user from a group **Sample request:** ``` DELETE /groups/34/user/454 ``` **Sample success response:** ```json { "status": "OK" } ``` --- ## User API :::caution Warning This is a **legacy API**. It is still functional but is **not in active development/ maintenance** anymore. ::: These API endpoints allows the admin to manage their users programmatically. ## How to use these API endpoints ### Obtain API Key Before using the API, please obtain the API key by following the [Getting Started guide](/api/v2/getting-started). ### Send API requests to Holistics To use these APIs, simply append Holistics' host URL `secure.holistics.io` with your chosen endpoint. For example, to call the API [Get all users in a tenant with full information](#get-all-users-in-a-tenant-with-full-information), you will have to use **GET** request with this URL: `secure.holistics.io/user.json`. ## Get all users in a tenant with full information **Sample request:** ``` GET /users.json ``` **Sample response:** ``` [ { "id": 1, "name": "Analyst", "email": "analyst@holistics.io", "role": "analyst", "initials": "An", "is_deleted": false, "is_activated": true, "has_authentication_token": true, "groups": [ { "id": 33, "name": "Capital", "created_at": "2015-06-29T03:22:14.842Z", "updated_at": "2015-06-29T03:22:14.842Z", "tenant_id": 5 } ], "allow_authentication_token": true, "current_sign_in_at": "2018-10-23T03:35:47.353Z", "last_sign_in_at": "2018-10-22T03:21:52.393Z" }, { "id": 2, "name": "Business User", "email": "business@holistics.io", "role": "business", "initials": "Bu", "is_deleted": false, "is_activated": true, "has_authentication_token": false, "groups": [], "allow_authentication_token": false, "current_sign_in_at": "2018-10-21T03:35:47.353Z", "last_sign_in_at": "2018-10-20T03:21:52.393Z" }, ] ``` :::note - **current_sign_in_at:** latest sign-in timestamp. - **last_sign_in_at:** previous sign-in timestamp (before the current_sign_in_at time). - **allow_authentication_token** is used to determine whether a user **is allowed** for API access (only Admin can update this field for a specific user). - **has_authentication_token** is used for determining whether a user **has already generated** an API access key (this flag will be turned to false if the Revoke Authentication Token call is requested). - A **Tenant** is the organization that is using Holistics (e.g. Grab). ::: ## Invite a new user to Holistics **Sample request:** ``` POST /users/invite.json ``` **Parameters:** - **name:** user's full name - **email:** user's email address - **role:** user role. At Holistics, there are 4 roles: admin, analyst, explorer, viewer.To learn more about the role system, visit [User Roles](/docs/admin/user-roles). - **message:** The invitation message that will be sent to the invitee. **Sample request body:** ``` { "name": "Test", "email": "tester@tenant.com", "role": "user", "message": "Hey, let's join MyCompany workspace on Holistics" } ``` **Sample success response:** ``` { "status": "ok" } ``` **Sample error response when a user already exists in Holistics's database:** ``` { "errors": [ "Email already existed" ] } ``` You will need to use the [Resend Invitation API](#resend-invitation-to-user) instead. ## Resend invitation to user **Sample request:** ``` POST /users/user_id/resend_invite.json ``` **Sample success response:** ```json { "status": "ok" } ``` ## Soft-delete a user **Sample request:** ``` DELETE /users/user_id.json ``` **Sample success response:** ```json { "status": "ok" } ``` ## Restore a deleted user **Sample request:** ``` POST /users/restore.json ``` **Sample request body:** ```json { "id": 560 } ``` **Sample error response when attempt to restore a non-deleted user:** ```json { "errors": [ "User is not deleted" ] } ``` ## Allow/ Revoke a user's API access **Sample request:** ``` PATCH /users/user_id.json ``` **Sample body request:** ```json { "allow_authentication_token": true } ``` **Sample success response:** ```json { "status": "ok" } ``` ## Revoke Authentication Token from a user This API is used to revoke generated Authentication Tokens. Consider using this API if: - You are worried that the current token may have been leaked. - You want to enforce a token refresh for security reasons. After their token has been revoked, the user would need to re-generate a new token by following the [Getting Started guide](/api/v2/getting-started). **Sample request:** ``` POST /users/user_id/revoke_authentication_token.json ``` **Sample success response:** ```json { "status": "ok" } ``` ## Check whether email address is already used for a user in Holistics **Sample request:** ``` GET /users/check_holistics_user.json/?email={email_address} ``` **Sample success response:** ```json { "is_already_user": true } ``` ## Change user role in Holistics **Required params:** - **user_id** - **user:** an object that contains the needed information for changing user role - **role (string):** can be `admin`, `analyst` or `user` - **remove_groups (boolean):** false by default. If the params are set to true, then the user's groups will also be removed after the role is changed. **Sample request:** ``` POST /users/change_user_role.json ``` **Sample body request:** ```json { id: 1, user: { role: "analyst", remove_groups: true } } ``` ## Find user by email address **Sample request:** ``` GET /users/get_user.json/?email={email_address} ``` **Sample success response:** ```json { "id": 1, "name": "Business User", "email": "business@holistics.io", "role": "business", "title": null } ``` --- ## Create Data Alerts using API ## Introduction In Holistics, you can set up a system that sends you automatic notifications when data meets certain criteria, allowing you to make timely and strategic business decisions. This concept is called [**Data Alert**](/docs/delivery/data-alert). Holistics offers a [set of public API endpoints](/api/v2/reference/data-alerts-create) allowing clients to work with Data Alert. This tutorial shows you how to programmatically create these data alerts using the API. ## The Use Case Assuming you are an e-commerce company that wants to track the number and status of orders placed on your platform. You can set up data alerts that trigger notifications when certain conditions are met, such as: 1. **A number of orders placed in a specific time period exceed a certain threshold.** 2. **A certain percentage of orders are canceled or marked as "canceled".** You can receive these alerts through email or Slack, and use this information to quickly respond to any issues or to make informed decisions about adjusting inventory or shipping processes. Let's walk through how we can create a data alert to **track your number of orders using Holistics API**. ## High-level Approach We will use the API to create email data alerts. Specifically, we'll send a POST request to [Holistics create data alert API](/api/v2/reference/data-alerts-create). Before that, we need to prepare the following: - A dashboard widget with relevant report - Holistics API Key: We need to setup the API key. Refer to the [Getting Started guide](/api/v2/getting-started) for setup instructions. ## How to Create a Data Alert via API ### Step 1: Preparing the Dashboard Widget You will need to create a dashboard widget containing the data of interest to you. In this example, I used this simple report that counts the total number of orders. ![Dashboard widget example](https://cdn.holistics.io/docs/dashboards/dashboard.png) ### Step 2: Making the API Call We'll be using the following [endpoint](/api/v2/reference/data-alerts-create): ``` POST https://secure.holistics.io/api/v2/data_alerts ``` There are some notable configuration options in this request: - `schedule`: Specify how frequently the email schedule should be - `viz_conditions`: Specify what alert condition should be checked on the report result. By configuring this, we can customize the alert for each alerting use case. - `dest`: Specify configurations regarding the alert delivery destination, e.g. the recipients' information. - `dynamic_filter_presets`: Specify what dynamic filters should be applied to the report. By configuring this, we can customize the report for each recipient. Below is a sample request body. Refer to [Holistics API docs](/api/v2/reference/data-alerts-create) for more information on these fields. ![Sample request body](https://cdn.holistics.io/docs/data-alert/samplereq.png) #### Dest field - `dest`: Specify configurations regarding recipients' information - `title` (string): Note that Holistics support [dynamic variables](/docs/delivery/email-schedules#data-schedule-dynamic-variables-support) in email title. For example, the title `Sales report for {{$today}}` sent on 29/09/2021 will be rendered as `Sales report for 2021-09-29 Wed`. - `type`: This tells Holistics you want to receive notification via email: `EmailDest` or Slack: `SlackDest` - If you choose `SlackDest`, you will need to [get the id and the name of your slack channel](https://help.socialintents.com/article/148-how-to-find-your-slack-team-id-and-slack-channel-id) for the `slack_channels` field #### Viz conditions - `viz_conditions`: Coming to the core of the alert, the condition to set data alert - `field_path`: This is used to extract from a specific data model - `field_name`: field name of the field in the data model you want to apply the condition. - `model_id`: The id of the data model associated with the `field_name` - You can get the `field_name` and `model_id` by following these steps: - From the widget id, use [Get a dashboard widget](/api/v2/reference/dashboard-widgets-get) (include_report=true) then extract `query_report.viz_settings.fields`, you can find the field you want to add condition then extract `path_hash` to get `model_id` and `field_name` - `aggregation`: The aggregation that will be applied on the field when processing the condition. - `transformation`: The transformation that will be applied on the field when processing the condition. - `condition`: be applied on the Data Modeling layer to filter your data. - Example: you have a field `id` and want to check whether the number of it is greater than 35000 ```json { "field_path": { "field_name": "id", "model_id": 1 }, "aggregation": "count", "transformation": null, "condition": { "operator": "greater_than", "modifier": null, "values": [35000] } } ``` #### Schedule `schedule`: This field is used to specify the schedule for your email schedule. `repeat` (string): You will need to input a [crontab expression](https://crontab.guru/) to let Holistics know how frequently you want your email schedule to run. #### Dashboard Filters `dynamic-filter-presets` is where you define your filter presets. `dynamic_filter_id`: To get your `filter_id`, send a request to [Dashboards#Get API](/api/v2/reference/dashboards-get) to get the dashboard info. `preset_conditions`: Let's go through our example to better illustrate this field. We want to set a value for the filter by setting a preset condition. **A preset condition consists of an operator and a list of values.** Holistics supports various operators. In this case, we want to use operator `is` and set values to `customer_id`. By this, we mean the filter's default value equals exactly to `customer_id`. Finally, we also want to set up another dynamic filter preset to filter data to the whole of last week. Fortunately, Holistics allows a list of dynamic filter presets. For the Date Range filter, we similarly want to use operator `is` and set values to `"last 7 days"`. Our final `dynamic_filter_presets` object will look like this: ```js dynamic_filter_presets = [ { "dynamic_filter_id": MY_CUSTOMER_FILTER_ID, "preset_condition": { "operator": "is", "values": [ customer_id ] } }, { "dynamic_filter_id": MY_DATE_RANGE_FILTER_ID, "preset_condition": { "operator": "is", "values": [ "last 7 days" ] } } ] ``` ### Step 3: Verify to see if your data alert is created The request should now be ready. After sending it to Holistics, the server will respond with a [HTTP status response code](https://developer.mozilla.org/en-US/docs/Web/HTTP/Status) to indicate whether the request was successful / failed. Lastly, be sure to head over to the Holistics page to see if your data alert is created. ![Data alert confirmation](https://cdn.holistics.io/docs/data-alert/doublecheck.png) ## Example Script An example script to send requests written in Ruby is available here: ```ruby # install the neccessary dependencies for this script to work require "bundler/inline" require "net/http" require "json" gemfile do source "https://rubygems.org" gem "net-http" end # this the Holisitics API endpoint that we need to send our POST request to DATA_ALERT_ENDPOINT = URI("https://secure.holistics.io/api/v2/data_alerts") # Step 1: Create a POST request to the endpoint below request = Net::HTTP::Post.new(DATA_ALERT_ENDPOINT) # Step 2: Provide your authentication information request["X-Holistics-Key"] = "mysecretAPIkey" # Step 2.1: This line helps the system know that we are sending a JSON request request.content_type = "application/json" # Step 3: Provide information for your data alert in the request body request.body = { "data_alert": { "title": "Tracking number of orders", "source_id": 41, "source_type": "DashboardWidget", # Conditions for the Alert to be Triggered "viz_conditions": [ { "field_path": { "field_name": "id", "model_id": 12 }, "aggregation": "count", "transformation": null, "condition": { "operator": "greater_than", "modifier": null, "values": [35000] } } ], # Destination for the Alert "dest": { "type": "EmailDest", "title": "The number of orders has just exceeded than 35000", "recipients": [ "admin@domain.com" ], "options": { "body_text": null } }, # Schedule for the Alert to be Triggered "schedule": { # Repeat Everyday at 7:00 AM "repeat": "0 7 * * *", "paused": false }, # Dynamic Filter Presets for the Alert "dynamic_filter_presets": [ { "preset_condition": { "operator": "is", "modifier": null, "values": ["VN", "SG"], }, "dynamic_filter_id": 1 }, { "preset_condition": { "operator": "is", "modifier": null, "values": [ "Male" ], }, "dynamic_filter_id": 2 } ] } }.to_json # Step 4: Send the request to Holistics! response = Net::HTTP.start( DATA_ALERT_ENDPOINT.hostname, DATA_ALERT_ENDPOINT.port, :use_ssl => DATA_ALERT_ENDPOINT.scheme == "https", ) do |https| https.request(request) end # Optional: Output the result into the terminal puts [response.code,response.message].join(' ') ``` --- ## Create Email Schedules using API ## Introduction In Holistics, you can set up schedules sending reports or dashboards to a group of recipients via email, Slack, SFTP, or Azure Blob. This concept is called **Data Schedule**. Holistics offers a [set of public API endpoints](/api/v2/reference/data-schedules) allowing clients to work with Data Schedule. This tutorial shows you how to use the API to create these data schedules programmatically. ## The Use Case Suppose you are a B2B SaaS company that has a feature to email usage statistics to your customers every week. You want to set it up such that: - When a new customer onboards to your platform, you want **Holistics to send a weekly email report** to that customer. - The weekly email reports **should be customised to display information regarding the usage data of that customer only** Let's walk through how we can solve this problem using Holistics API. ## High-level Approach We will use the API to create email reports. Specifically, we'll send a POST request to [Holistics' create email schedule API](/api/v2/reference/data-schedules-create). Before that, we need to prepare the following: - A dashboard with relevant reports. This dashboard will be emailed to the customer. - The dashboard should have a "Customer" filter to filter down the data for a particular customer. - Holistics API Key: We need to setup the API key. Refer to the [Getting Started guide](/api/v2/getting-started) for setup instructions. ## Step 1: Preparing the Dashboard You will need to create a master dashboard that contains the reports you want to send to the customers. In this example, we have a `Customer Usage Data` dashboard which showcases the statistics of jobs that Holistics customers execute in our platform. We also have two filters in this dashboard, `Date Range` and `Customer`. - The **Customer ID filter** accepts a customer ID, then passes it to the report within the dashboard. The report then replaces the ID with its placeholder in a prepared SQL query, reruns the query, and fetches only data that belongs to that customer. In other words, this filter helps **filter down to the usage data of that customer only**. - Similarly, the **Date Range filter**'s value accepts `"last 7 days"`, `"last 14 days"`, `"last 30 days"`, or `"any time"`. This filter helps **filter usage data by a date range**. Note that this filter can be optional. If you are not sure on how to create dashboards and filters, refer to [Dashboard Docs](/docs/dashboards). ![Dashboard to visualize customer usage data](https://cdn.holistics.io/docs/guides/data-schedule-API/pic1.png) ## Step 2: Making the API Call We'll be using the following [endpoint](/api/v2/reference/data-schedules-create): ``` POST https://secure.holistics.io/api/v2/data_schedules ``` There are some notable configuration options in this request: - `schedule`: Specify how frequent the email schedule should be - `dynamic_filter_presets`: Specify what dynamic filters should be applied to the reports. By configuring this, we can customise the report for each recipient. - `dest`: Specify configurations regarding recipients' information. - Remember to set `dest.type` to "EmailDest" Below is a sample request body. Refer to [Holistics API docs](/api/v2/reference/data-schedules-create) for more information on these fields. ![Sample Request Body](https://cdn.holistics.io/docs/guides/data-schedule-API/samplereq.png) :::info 💡 Most of the fields are straightforward which means that you can fill them out by reading our API docs. Refer to this section below for notes on trickier fields. ::: ### Common fields - `id` (integer): This is used to uniquely identified your object. Holistics automatically generates an id for you if you don't specify this. - `source_id` (integer): This tells Holistics which dashboard you want to send email to. To determine this value, view your dashboard link. For example, the link [`https://secure.holistics.io/dashboards/v3/16076-demo-my-beautiful-dashboard`](https://secure.holistics.io/dashboards/v3/16076-demo-my-beautiful-dashboard) will have the `source_id` of 16076. - `dest`: Specify the information regarding the recipients - `title` (string): Note that Holistics support [dynamic variables](/docs/delivery/email-schedules#data-schedule-dynamic-variables-support) in email title. For example, the title `Sales report for {{$today}}` sent on 29/09/2021 will be rendered as `Sales report for 2021-09-29 Wed`. ### Schedule frequency `schedule` is used to specify the schedule for your email schedule. `repeat` (string): You will need to input a `crontab expression` here to let Holistics how frequent you want to your email schedule to run. ### Dynamic filter values `dynamic-filter-presets` is where you define your filter presets here. `dynamic_filter_id` (integer): To get your `filter_id`, send a GET request to [Dashboards#Get API](/api/v2/reference/dashboards-get) to get the dashboard info. `preset_conditions` (object): Let's go through our example to better illustrate this field. We want to set a value for the filter by setting a preset condition. **A preset condition consists of an operator and a list of values.** Holistics supports various operators and provides several examples in the [Data Schedules API](/api/v2/reference/data-schedules-create). In this case, we want to use operator `is` and set values to `customer_id`. By this, we mean the filter's default value equals exactly to `customer_id`. Finally, we also want to set up another dynamic filter preset to filter data to the whole of last week. Fortunately, Holistics allows a list of dynamic filter presets. For the Date Range filter, we similarly want to use operator `is` and set values to `"last 7 days"`. Our final `dynamic_filter_presets` object will look like this. ```js dynamic_filter_presets = [ { "dynamic_filter_id": MY_CUSTOMER_FILTER_ID, "preset_condition": { "operator": "is", "values": [ customer_id ] } }, { "dynamic_filter_id": MY_DATE_RANGE_FILTER_ID, "preset_condition": { "operator": "is", "values": [ "last 7 days" ] } } ] ``` ## Step 3: Double-check to see if your email schedule is created The request should now be ready. After sending it to Holistics, the server will respond with a [HTTP status response code](https://developer.mozilla.org/en-US/docs/Web/HTTP/Status) to indicate whether the request was successful / failed. Lastly, be sure to head over to Holistics page to see if your email schedule is created. ![Screenshot of the email schedule creation confirmation](https://cdn.holistics.io/docs/guides/data-schedule-API/double-check.png) ## Example script An example script to send requests written in Ruby is available here: ```ruby # install the neccessary dependencies for this script to work require "bundler/inline" require "net/http" require "json" gemfile do source "https://rubygems.org" gem "net-http" end # this the Holisitics API endpoint that we need to send our POST request to EMAIL_SCHEDULE_ENDPOINT = URI("https://secure.holistics.io/api/v2/data_schedules") # Step 1: Create a POST request to the endpoint below request = Net::HTTP::Post.new(EMAIL_SCHEDULE_ENDPOINT) # Step 2: Provide your authentication information request["X-Holistics-Key"] = "mysecretAPIkey" # Step 2.1: This line helps the system know that we are sending a JSON request request.content_type = "application/json" # Step 3: Provide information for your email schedule in the request body request.body = { "data_schedule": { "source_type": "Dashboard", "source_id": 16076, "schedule": { "repeat": "* * * * *", "paused": false, }, "dest": { "type": "EmailDest", "title": "Sale report for {{$today}}", "recipients": [ "abc@gmail.com", ], }, }, }.to_json # Step 4: Send the request to Holistics! response = Net::HTTP.start( EMAIL_SCHEDULE_ENDPOINT.hostname, EMAIL_SCHEDULE_ENDPOINT.port, :use_ssl => EMAIL_SCHEDULE_ENDPOINT.scheme == "https", ) do |https| https.request(request) end # Optional: Output the result into the terminal puts [response.code,response.message].join(' ') ``` --- ## Error Handling ## HTTP Status Codes | Code | Description | |------|-------------| | 200 | **OK** - Request succeeded | | 400 | **Bad Request** - Missing parameter or invalid request structure | | 401 | **Unauthorized** - Invalid or missing API key | | 403 | **Forbidden** - API key owner lacks permission for this action | | 404 | **Not Found** - Resource doesn't exist | | 422 | **Unprocessable Entity** - Semantic errors in parameters | | 429 | **Too Many Requests** - Rate limit exceeded (see [Rate Limiting](/api/v2/rate-limiting)) | | 500 | **Internal Error** - Server-side issue (rare) | ## Error Types All errors follow a consistent structure with a `type` field indicating the error category: | Error Type | Description | |------------|-------------| | `BaseError` | Base schema for all error types | | `RecordError` | Error when saving a record | | `InvalidParameterError` | One or more parameters are missing or invalid | | `AuthError` | Authentication failed (missing or expired token) | | `MaintenanceError` | Holistics is in maintenance mode | | `SubscriptionError` | Subscription lacks permission for this resource | | `PermissionDeniedError` | API key owner lacks access to this resource | | `InternalHolisticsError` | Internal error - provide `debug id` to support | | `InvalidOperationError` | Action blocked by system constraints | ## Error Response Format All error responses follow this structure: ```json { "error": { "type": "InvalidParameterError", "message": "Parameter 'source_id' is required", "debug_id": "abc123xyz" } } ``` ## Handling Errors **Best practices:** 1. **Check status codes first** - Use HTTP status to determine error category 2. **Parse error type** - Use the `type` field for specific error handling 3. **Log debug IDs** - For `InternalHolisticsError`, save the `debug_id` for support requests 4. **Validate inputs** - Prevent `InvalidParameterError` by validating before sending **Example error handling (Python):** ```python response = requests.post(url, headers=headers, json=data) if response.status_code == 200: return response.json() elif response.status_code == 401: raise Exception("Invalid API key") elif response.status_code == 403: raise Exception("Permission denied") elif response.status_code == 422: error = response.json().get('error', {}) raise Exception(f"Validation error: {error.get('message')}") elif response.status_code == 429: # Handle rate limiting pass else: error = response.json().get('error', {}) raise Exception(f"API error: {error.get('message')} (debug_id: {error.get('debug_id')})") ``` --- ## API FAQs ## How do I submit array query parameters? When a query parameter accepts an array, use the `[]` syntax: 1. Append `[]` to the parameter name (e.g., `ids` becomes `ids[]`) 2. Include each array value as a separate parameter **Example:** ``` GET /endpoint?ids[]=1&ids[]=2&ids[]=3&names[]=Alice&names[]=Bob&limit=10 ``` This submits: - `ids` as `[1, 2, 3]` - `names` as `["Alice", "Bob"]` - `limit` as `10` **In code (Python):** ```python params = { 'ids[]': [1, 2, 3], 'names[]': ['Alice', 'Bob'], 'limit': 10 } response = requests.get(url, headers=headers, params=params) ``` ## How do I find my data center region? Check the URL when you log into Holistics: | URL | Region | API Base URL | |-----|--------|--------------| | `secure.holistics.io` | Asia-Pacific (APAC) | `https://secure.holistics.io/api/v2` | | `eu.holistics.io` | Europe (EU) | `https://eu.holistics.io/api/v2` | | `us.holistics.io` | United States (US) | `https://us.holistics.io/api/v2` | See [Data Centers](/docs/security-compliance/data-centers#how-do-i-know-which-data-center-im-on) for more details. ## How do I get my API key? 1. Ask your admin to enable API access for your account in User Management 2. Go to [User Settings](https://secure.holistics.io/users/settings) 3. Generate a new API key See [Authentication](/api/#authentication) for detailed steps. ## What's the difference between API v1 and v2? API v2 is the current version with full support. API v1 is legacy and no longer actively maintained. If you're using API v1, see [Migrating V1 to V2](/api/v2/migrating-v1-to-v2) for upgrade guidance. ## How do I handle async operations? Some API operations (like exports) run asynchronously. These endpoints return a job ID that you poll for status: 1. Submit the request → Get `job_id` 2. Poll `GET /jobs/{job_id}` until status is `success` or `failure` 3. Retrieve the result See [Export Widget Data](/api/v2/get-data) for a complete example. --- ## Export Widget Data via API ## Introduction This short tutorial shows you how to use the Holistics APIs to get report data in raw tabular form (CSV, Excel). This tutorial uses Ruby, but you can easily use any other language for it. You can also refer to the [full source code here](https://github.com/holistics/api/blob/main/ruby/report_export.rb). :::info We'll be working on client libraries wrapper around our API. Once done, using the APIs will be simpler by just making a few function calls. ::: ## Mechanism Since Holistics uses a async job queuing system to process job, you can't make a single API call to retrieve the results. We need to submit an 'export request', then wait for the export job to finish, and then make an API call to download the results. **API Endpoints used:** - [Export a Dashboard Widget](/api/v2/reference/dashboard-widgets-submit-export) - [Download an exported file](/api/v2/reference/exports-download) ## Steps Let's go through the steps here. ### 1. Setting Up API Key Please see [guide](/api/) to set up and retrieve your API key. ### 2. Initial code structure To make things simple and reusable, we'll wrap our code around a `HolisticsAPI` class. We'll also use the [httprb](https://github.com/httprb/http) gem to handle making HTTP calls. ```ruby require 'http' class HolisticsAPI def initialize(api_key, host: 'secure.holistics.io') @api_key = api_key @api_url = "https://#{host}/api/v2" @http = HTTP.headers({'X-Holistics-Key' => @api_key}) end end ``` ### 3. (Optional) Get Filters ID for your Dashboard Export If you want to include **Filters** in your export, you will need to get their Filter IDs. Please follow these steps to obtain them: **1. Get your Dashboard ID** The Dashboard ID can be retrieved by looking at its URL in the browser. In this sample URL below, the Dashboard ID would be 16076. ![Dashboard ID in the URL](https://cdn.holistics.io/docs/dynamic-filter/dashboard.png) **2. Get Filter ID** Supposed that your dashboard has a sets of filters like the one below. Let's get the ID of the **Date filter** to include it in our export. ![Dashboard filters](https://cdn.holistics.io/docs/dynamic-filter/filters.png) We will use [Get Dashboard API](/api/v2/reference/dashboards-get) for this purpose. Let's call the API with the Dashboard ID from step 1. ```ruby curl --location --request GET 'https://secure.holistics.io/api/v2/dashboards/{your_dashboard_id}' \ --header 'X-Holistics-Key: your_API_key' \ --header 'Content-Type: application/json' \ ``` The response would be quite lengthy, but you just need to find the **dynamic_filters** field to get the **Filter ID**. ![API response with dynamic_filters field](https://cdn.holistics.io/docs/dynamic-filter/api-response.png) You can then use this Filter ID in the next step. ### 4. Submit widget export request Make sure you have the DashboardWidget ID in hand. The widget ID can be retrieved by opening up the widget in the dashboard, and look at the `_e` parameter in the URL. For example, 4175 is the widget ID of the below. ``` https://secure.holistics.io/dashboards/v3/12345-some-dashboard/?_e=4175 ``` :::info (Optional) Include filter conditions in your export If you wish to include a filter condition in your export, first refer to [step 3](#3-optional-get-filters-id-for-your-dashboard-export) to get your desired **Filter ID**. Then, append **dashboard_filter_conditions** in your request body. - **dynamic_filter_id** is the Filter ID from step 3. - **condition:** - **operator:** refer to [Data Schedules API](/api/v2/reference/data-schedules-create) for all available operators. - **values:** is an array of strings or integers that go with the operator. For example, assuming that you have completed step 3, to apply a **Date Filter** that filters data from 2 months ago to the export, simply include this snippet to your request. ```ruby { "dashboard_filter_conditions": [ { "dynamic_filter_id": 2335, "condition": { "operator": "matches", "values": [ "2 months ago" ] } } ] } ``` ::: Then we make the call to submit widget export: ```ruby class HolisticsAPI # ... # output: 'csv' or 'excel' def submit_report(widget_id, output: 'csv') url = @api_url + "/dashboard_widgets/" + widget_id.to_s + "/submit_export" response = @http.post(url, json: {output: output}) res = JSON.parse(response.to_s) if response.code == 200 res['job']['id'] else raise StandardError.new(res['message']) end end end ``` If successful, this method returns the job ID of the job created. ### 5. Waiting for job to complete The job will the go to the queue system waiting to be processed. This method below will continuously poll the job's metadata until it is either success, or failure. ```ruby class HolisticsAPI # ... def wait_for_job_status(job_id) url = @api_url + "/jobs/" + job_id.to_s while true do response = @http.get(url) res = JSON.parse(response.to_s) raise StandardError.new(res['message']) if response.code != 200 status = res['job']['status'] puts "===> status: #{status}" unless ['created', 'running', 'queued'].include?(status) return status end # Wait a while before pinging again sleep 2 end end end ``` ### 6. Downloading the results Once the job finishes, we make one final API call to ```ruby class HolisticsAPI # ... def download_export(job_id) url = @api_url + "/exports/download" response = @http.follow.get(url, params: {job_id: job_id}) raise StandardError.new(JSON.parse(response.to_s['message'])) if response.code != 200 response.to_s end end ``` ### 7. Putting things together Once the above class is defined, let's put in a short code to perform all 3 steps to get the data. ```ruby API_KEY = 'your_api_key' WIDGET_ID = 1234 # your widget api = HolisticsAPI.new(API_KEY) job_id = api.submit_report(WIDGET_ID) puts "===> job_id: #{job_id}" job_status = api.wait_for_job_status(job_id) puts "===> job_status: #{job_status}" if job_status == 'success' csv_data = api.download_export(job_id) puts csv_data # your CSV-formatted data here! end ``` ### 7. Profit! Save the data into CSV, convert them into array, or feed them to other applications. The potentials are limitless! --- ## Getting Started with Holistics API This guide walks you through setting up API access and making your first API call. By the end, you'll have a working example that retrieves your user information. ## Prerequisites - A Holistics account with admin access (to enable API access for users) - Basic familiarity with making HTTP requests (curl, Postman, or any programming language) ## Step 1: Enable API Access Before a user can use the API, an admin must grant them API access. 1. Go to **Settings** > **User Management** 2. Edit the user who needs API access 3. Check the **Allow API access** checkbox 4. Save changes ## Step 2: Generate Your API Key Once API access is enabled, generate your personal API key: 1. Go to [User Settings](https://secure.holistics.io/users/settings) 2. Scroll to the **API Keys** section 3. Click **Generate New Key** 4. Copy and securely store your API key ![Create a new API key in User Settings](https://cdn.holistics.io/docs/api/new_api_key.png) :::warning Keep your API key secure Your API key grants full access to your Holistics account. Never share it publicly or commit it to version control. ::: ## Step 3: Find Your API Base URL Holistics has multiple data center regions. Use the base URL that matches your region: | Region | API Base URL | |--------|--------------| | Asia-Pacific (APAC) | `https://secure.holistics.io/api/v2` | | Europe (EU) | `https://eu.holistics.io/api/v2` | | United States (US) | `https://us.holistics.io/api/v2` | :::tip Finding your region Check the URL when you log into Holistics: - `secure.holistics.io` → APAC - `eu.holistics.io` → EU - `us.holistics.io` → US ::: ## Step 4: Make Your First API Call Let's verify everything works by fetching your user information: ```bash curl -X GET \ -H "Accept: application/json" \ -H "Content-Type: application/json" \ -H "X-Holistics-Key: YOUR_API_KEY" \ https://secure.holistics.io/api/v2/users/me ``` Replace `YOUR_API_KEY` with your actual API key and adjust the base URL for your region. A successful response looks like: ```json { "user": { "id": 12345, "email": "you@example.com", "name": "Your Name", ... } } ``` ## Understanding Async Jobs Many Holistics API operations (exports, schedule executions, etc.) run asynchronously. Instead of returning results immediately, these endpoints return a **job ID** that you poll until completion. The typical pattern is: 1. **Submit a request** → Returns a `job_id` 2. **Poll the job status** → Check `/jobs/{job_id}` until status is `success` or `failure` 3. **Retrieve the result** → Use the job ID to download results or get output For example, exporting dashboard data works like this: ``` POST /dashboard_widgets/{id}/submit_export → { "job": { "id": 123 } } GET /jobs/123 → { "job": { "status": "success" } } GET /exports/download?job_id=123 → CSV/Excel data ``` See the [Export Widget Data](/api/v2/get-data) tutorial for a complete working example. ## Next Steps Now that you're set up, explore these tutorials: - [Export Widget Data](/api/v2/get-data) - Download dashboard data as CSV/Excel - [Create Email Schedules](/api/v2/create-data-schedule) - Automate report delivery - [Create Data Alerts](/api/v2/create-data-alert) - Set up automated alerts - [Query Datasets](/api/v2/query-data) - Run queries against your datasets Or browse the [API Reference](/api/v2/reference/jobs) for all available endpoints. --- ## Migrating API V1 to API V2 ## Introduction This documentation will guide you through the process of transitioning from API V1 to the new API V2. It will also provide information about the migration plan and outline what you can expect in our new API version. Our primary goal is to assist you in achieving a smooth and successful transition to Holistics’s latest API version. ## Why it is time to move to API V2 For the past few months, we have considered API V1 as our legacy API. While it is still functional, it is not actively maintained. Meanwhile, we have been developing API V2 as a more powerful, flexible, and well-structured version of V1. Now that API V2 has surpassed its predecessor in both functionality and experience, we are deprecating V1 and are here to assist you in migrating your applications to V2. ## Our deprecation plan for API V1 :::info The entire API V1 will cease to function by **November 20th, 2023**. ::: This deprecation includes all functionalities available in these two V1 kits: - [Users API](/api/v1/user-api) - [Group API](/api/v1/group-api) Be assured that we have supported all of these functionalities API in V2. Next steps, you will need to manually update your applications that are utilizing V1 to switch them to V2. ## Equivalent functions between API V1 & API V2 API V1 API V2 Changes [User] Get all users in a tenant with full information GET - List users Groups are in a different list, not included in each user V2 applies pagination, you have to query page by page V2 has user counter information [User] Invite a new user to Holistics POST - Invite Multiple Users V2 allows inviting multiple emails with additional user settings, including API usage, group IDs, and data export permissions [User] Resend invitation to user POST - Resend invitation to user We introduced inviting multiple users API in API V2 In order to make invite multiple users and resend invitation user consistent, we return the job info of created job [User] Soft-delete a user DEL - Delete a user [User] Restore a deleted user POST - Restore a deleted user [User] Allow/ Revoke a user's API access PUT - Update user By setting attribute: allow_authentication_token: boolean [User] Revoke Authentication Token from a user PUT - Update user (Upcoming) [User] Check whether email address is already used for a user in Holistics GET - Check whether an email address is already used for a user in your Holistics workspace [User] Change user role in Holistics PUT - Update user By setting attribute: “role”: enum remove_groups: passing empty group_ids [Group] Get all groups in a tenant GET - List Groups V2 applies pagination, you have to query page by page V2 includes all users’s id instead of num_user in V1 V2 supports include_users [Group] Create a new group POST - Create Group Response’s structure change [Group] Update information of an existing group PUT - Update a Group Now included user_ids in groups [Group] Delete an existing group DEL - Delete a Group [Group] Add a user into a group POST - Add User to Group [Group] Remove a user from a group POST - Remove User from Group ## FAQ ### How will the migration affect existing applications that are utilizing API V1? - We expect no interference in your automation workflow after the migration since all functionalities of V1 are completely covered by V2 - However, since there will be some differences in the data schema (or response structure) between the two versions, you would need to follow our documents to set up precisely ### Should I do anything prior to this migration? We suggest you check which applications will be affected by such an update, and inform your team that they will not be available during that time. ### What do I do if I encounter unexpected issues? We are committed to providing support throughout the migration. If you encounter any problems, please reach out to us at support@holistics.io for assistance. --- ## Query Datasets via API This guide shows you how to query your metrics and dimensions programmatically. You can either execute queries and get results as JSON, or retrieve the generated SQL to run elsewhere. For background on why this matters, see [Open Semantic Layer](/docs/open-semantic-layer). ## How it works You specify which dimensions and metrics you want. Holistics executes the query against your data warehouse and returns the aggregated results. The API handles all the complexity: joining tables, applying aggregations, respecting relationships, and enforcing permissions. Your external application just receives clean, governed data. ## Quick start ### 1. Get your API key Go to **User Settings** in Holistics and generate an API key. You'll include this key in every API request using the `X-Holistics-Key` header. ### 2. Find your dataset ID Your metrics and dimensions are organized in datasets. To query them, you'll need the dataset ID. **Option A: From the URL** Navigate to your dataset in Holistics. The ID is in the URL: ``` https://secure.holistics.io/data_sets/12345/explore ^^^^^ Dataset ID ``` **Option B: Via API** List all datasets you have access to: ``` GET /api/v2/data_sets ``` ### 3. Build and send your query Build a query specifying the dimensions and metrics you want, then POST it to one of two endpoints: | Endpoint | Returns | |----------|---------| | `/data_sets/{id}/submit_query` | Executes the query and returns data as JSON | | `/data_sets/{id}/generate_sql` | Returns the generated SQL | Both endpoints accept the same request body: ```json { "query": { "dimensions": [{"field": "model_name.field_name"}], "metrics": [{"field": "metric_name"}], "filters": [ { "field": "model_name.field_name", "operator": "operator_name", "values": [...] } ] } } ``` - **dimensions**: Fields to group by (e.g., `countries.name`, `products.category`) - **metrics**: Aggregated measures to calculate (e.g., `gmv`, `order_count`) - **filters**: Apply filter to your query (e.g., `countries.continent_name is Asia`)
Example: curl ```bash curl -X POST \ -H "X-Holistics-Key: YOUR_API_KEY" \ -H "Content-Type: application/json" \ -d '{ "query": { "dimensions": [{"field": "ecommerce_countries.name"}], "metrics": [{"field": "gmv"}] } }' \ https://secure.holistics.io/api/v2/data_sets/12345/submit_query ```
Example: Python ```python response = requests.post( "https://secure.holistics.io/api/v2/data_sets/12345/submit_query", headers={ "X-Holistics-Key": "YOUR_API_KEY", "Content-Type": "application/json" }, json={ "query": { "dimensions": [{"field": "ecommerce_countries.name"}], "metrics": [{"field": "gmv"}] } } ) response.raise_for_status() # Raises an HTTPError for bad responses (4xx or 5xx) data = response.json() ```
### 4. Get results The response format depends on which endpoint you called. #### submit_query response Returns the query results as JSON: ```json { "fields": ["Country Name", "GMV (Gross Merchandise Value)"], "values": [ ["United States", "1341997"], ["India", "1181929"], ["Australia", "1241734"] ], "meta": {"page": 1, "page_size": -1, "num_rows": 10} } ``` #### generate_sql response Returns the generated SQL from the API call. Use this to audit query logic, debug joins, or run the SQL directly in your data warehouse. ```json { "sql": "SELECT \"ecommerce_countries\".\"name\", SUM(\"order_items\".\"quantity\" * \"order_items\".\"price\") AS \"gmv\" FROM ..." } ``` ## API reference ### Endpoints | Endpoint | Method | Description | |----------|--------|-------------| | `/data_sets` | GET | List available datasets | | `/data_sets/{id}` | GET | Get available dimensions and metrics | | `/data_sets/{id}/submit_query` | POST | Query data and return results | | `/data_sets/{id}/generate_sql` | POST | Get generated SQL without executing | ### Query object | Field | Type | Description | |-------|------|-------------| | `dimensions` | array | Fields to group by: `[{"field": "model.field_name"}]` | | `metrics` | array | Measures to calculate: `[{"field": "metric_name"}]` | | `filters` | array | Conditions to filter data | | `limit` | number | Maximum rows to return | ### Query examples #### Basic query Query GMV by country: ```json { "query": { "dimensions": [{"field": "countries.name"}], "metrics": [{"field": "gmv"}] } } ``` #### Multiple dimensions Break down by country and product category: ```json { "query": { "dimensions": [ {"field": "countries.name"}, {"field": "products.category"} ], "metrics": [ {"field": "gmv"}, {"field": "order_count"} ] } } ``` #### Filter by value Only include orders from specific countries: ```json { "query": { "dimensions": [{"field": "countries.name"}], "metrics": [{"field": "gmv"}], "filters": [ { "field": "countries.name", "operator": "is", "value": ["United States", "United Kingdom"] } ] } } ``` #### Filter by date range Query data for a specific time period: ```json { "query": { "dimensions": [{"field": "orders.created_date"}], "metrics": [{"field": "gmv"}], "filters": [ { "field": "orders.created_date", "operator": "between", "value": ["2025-01-01", "2025-12-31"] } ] } } ``` #### Limit results Get top 10 results: ```json { "query": { "dimensions": [{"field": "products.name"}], "metrics": [{"field": "gmv"}], "limit": 10 } } ``` #### Filter operators | Operator | Description | Example value | |----------|-------------|---------------| | `is` | Equals any of the values | `["USA", "UK"]` | | `is_not` | Not equal to any of the values | `["Unknown"]` | | `is_null` | Value is null | `[]` | | `not_null` | Value is not null | `[]` | | `greater_than` | Greater than | `[100]` | | `less_than` | Less than | `[1000]` | | `between` | Between two values (inclusive) | `["2025-01-01", "2025-12-31"]` | | `contains` | Contains substring | `["phone"]` | | `does_not_contain` | Does not contain substring | `["test"]` | | `starts_with` | Starts with substring | `["John"]` | | `ends_with` | Ends with substring | `[".com"]` | | `is_true` | Boolean is true | `[]` | | `is_false` | Boolean is false | `[]` | | `last` | Within last N periods | `[7, "day"]` | | `next` | Within next N periods | `[30, "day"]` | | `before` | Before a date | `["2025-01-01"]` | | `after` | After a date | `["2025-01-01"]` | | `matches` | Matches regex pattern | `["^test.*"]` | | `matches_user_attribute` | Matches current user's attribute | `["department"]` | ### Regional endpoints | Region | Base URL | |--------|----------| | APAC | `https://secure.holistics.io/api/v2` | | US | `https://us.holistics.io/api/v2` | | EU | `https://eu.holistics.io/api/v2` | :::tip Finding your region Check the URL when you log into Holistics (`secure.holistics.io` is APAC, `us.holistics.io` is US, `eu.holistics.io` is EU). ::: ### Authentication Include your API key in the `X-Holistics-Key` header with every request. See the [Quick start examples](#3-build-and-send-your-query) above for curl and Python implementations. ## Next steps - **Full API reference**: Explore all endpoints in the [API documentation](/api/) --- ## Rate Limiting To ensure service stability, API requests are rate-limited per user and endpoint. ## Rate Limit Levels | Level | Requests per Minute | Description | |-------|---------------------|-------------| | Level 1 | 120 | Standard API requests | | Level 2 | 60 | Resource-intensive requests | | Level 3 | 20 | Heavy server-load requests | ## Response Headers Every API response includes rate limit information in the headers: | Header | Description | |--------|-------------| | `RateLimit-Limit` | Your quota for this endpoint | | `RateLimit-Remaining` | Remaining requests until reset | | `RateLimit-Reset` | Time when the limit resets | | `Retry-After` | When to retry (only on 429 responses) | ## Handling Rate Limits If you exceed the limit, the API returns status code `429 Too Many Requests`. **Best practices:** 1. **Check headers** - Monitor `RateLimit-Remaining` to avoid hitting limits 2. **Implement backoff** - When you receive a 429, wait until `Retry-After` before retrying 3. **Batch requests** - Combine multiple operations where possible 4. **Cache responses** - Avoid repeated identical requests **Example retry logic (Python):** ```python def api_request_with_retry(url, headers, max_retries=3): for attempt in range(max_retries): response = requests.get(url, headers=headers) if response.status_code == 429: retry_after = int(response.headers.get('Retry-After', 60)) print(f"Rate limited. Waiting {retry_after} seconds...") time.sleep(retry_after) continue return response raise Exception("Max retries exceeded") ``` --- ## Delete a Conversation Delete a Conversation Request --- ## Get Chat Messages of a Conversation Get Chat Messages of a Conversation Request --- ## Get the latest insight for a conversation Get the latest insight for a conversation Request --- ## Get a Conversation Get a Conversation Request --- ## List conversations List conversations Request --- ## List native skills from the skills library List native skills from the skills library --- ## List available AI skills List available AI skills --- ## Chat with Holistics AI Chat with Holistics AI Request --- ## Generate insight for a conversation Generate insight for a conversation Request --- ## Update a conversation Update a conversation Request --- ## Publish the AML Project to Production Publish the AML Project to Production Request --- ## Validate AML Project Validate AML Project Request --- ## Delete a Dashboard Widget Delete a Dashboard Widget Request --- ## Get a Dashboard Widget Get a Dashboard Widget Request --- ## Export a Dashboard Widget See more details on how to export a widget at [Get Data](/api/v2/get-data). When the Job is finished, you can download the exported file using the [download export API](/api/v2/reference/exports-download). Request --- ## Dashboard Widgets Reporting Dashboard Widgets ```mdx-code-block ``` --- ## Build a Dashboard URL with preset filter states Build a Dashboard URL with preset filter states Request --- ## Clone a canvas dashboard Clone a canvas dashboard Request --- ## Delete a Dashboard Delete a Dashboard Request --- ## Get a Dashboard Get a Dashboard Request --- ## List all dashboards metadata List all dashboards metadata Request --- ## List Dashboards List Dashboards Request --- ## Preload a Dashboard Preload a Dashboard by executing its widgets (using the submitted filter conditions, or default filter conditions if not submitted) and storing the widgets' results into Holistics cache. Read more about [Data Caching](/docs/performance/data-caching). Request --- ## Dashboards Reporting Dashboards ```mdx-code-block ``` --- ## Create a Data Alert Create a Data Alert Request --- ## Delete a Data Alert Delete a Data Alert Request --- ## Get a Data Alert Get a Data Alert Request --- ## List Data Alerts List Data Alerts Request --- ## Execute a Test Data Alert Execute a Test Data Alert Request --- ## Execute a Data Alert Execute a Data Alert Request --- ## Update a Data Alert Update a Data Alert Request --- ## Data Alerts Send automatic notification to you and your team when the data meets certain conditions ```mdx-code-block ``` --- ## Validate data model persistence custom DDL Validate data model persistence custom DDL Request --- ## List Data Models List Data Models Request --- ## Create a Data Schedule Create a Data Schedule Request --- ## Delete a Data Schedule Delete a Data Schedule Request --- ## Get a Data Schedule Get a Data Schedule Request --- ## List Data Schedules List Data Schedules Request --- ## Execute a Test Data Schedule Execute a Test Data Schedule Request --- ## Execute a Data Schedule Execute a Data Schedule Request --- ## Update a Data Schedule Update a Data Schedule Request --- ## Data Schedules Data delivery using automated schedules ```mdx-code-block ``` --- ## Delete a Data Set We do not support deleting data set created from AML yet, try deleting it from Development. Send your request to support@holistics.io if you have any concern. Request --- ## Get a Data Set Get a Data Set Request --- ## Get a SQL of a query on a dataset Get a SQL of a query on a dataset Request --- ## Run query on a dataset Run query on a dataset Request --- ## Bust Exploration Cache (Beta) *This feature is currently in Beta.* Bust (invalidate) all exploration/report [cache](/docs/performance/data-caching) of the specified Data Source. After a successful busting, all explorations/reports running on the specified Data Source will return fresh data. Please note that this does not bust or trigger the Data Source's [Model Storages](/docs/storage-settings). Hence, even after calling this API, the explorations/reports can only be as fresh as the data persisted in the Model Storages. Request --- ## Delete a Data Source Delete a Data Source Request --- ## Get a Data Source Get a Data Source Request --- ## Update a Data Source Update a Data Source Request --- ## Upload Multiple dbt manifest.json Files Upload multiple dbt manifest.json files for a data source atomically. Files are staged synchronously (size-validated in the request); parsing and the sync run as a background job. Poll `/api/v2/reference/jobs-get` for completion. Limits: at most 5 files per request, 2O0 MB per file. Malformed JSON is reported via the job's failure status, not the HTTP response. Gated by the `data_source:dbt_multi_manifest_upload` feature toggle. Request --- ## Get object's downstream dependencies Get object's downstream dependencies Request --- ## Dependencies ## Downstream dependencies Get ids of objects which are dependent on the object you input. The response schema varies based on the input type and its current dependants. ```mdx-code-block ``` --- ## Development Development APIs ```mdx-code-block ``` --- ## Create a Shortened embed token Use this API to [shorten the embed token](/embedded/faqs#i-received-an-error-414-request-uri-too-large-error) if the URL exceeds the browser's size limit. Request --- ## Embed Embedded Analytics ```mdx-code-block ``` --- ## Download an exported file Download an exported file Request --- ## Exports ```mdx-code-block ``` --- ## Process GitHub App webhook events Endpoint for receiving webhook events from the GitHub App integration. Handles installation lifecycle events (install, uninstall, suspend, unsuspend), repository access changes, and repository events (pull requests, pushes). The request signature is validated via HMAC before processing. No authentication is required. Request --- ## Process GitHub webhook events Endpoint for receiving GitHub webhook events related to pull requests and push events. Used for AmlStudio PR workflow integration. Request --- ## Process GitLab webhook events Endpoint for receiving GitLab webhook events related to merge requests and push events. Used for AmlStudio PR workflow integration. Request --- ## Add a User to a Group Add a User to a Group Request --- ## Create a Group Create a Group Request --- ## Delete a Group Delete a Group Request --- ## Get a Group Get a Group Request --- ## List Groups List Groups Request --- ## Remove a User from a Group Remove a User from a Group Request --- ## Update a Group Update a Group Request --- ## Groups Groups management ```mdx-code-block ``` --- ## Holistics API(Reference) Using these APIs, you can programmatically work with Holistics - retrieve report data, trigger schedules, manage users, and more. For setup instructions, see the [Getting Started guide](/api/v2/getting-started). Security Scheme Type: apiKey Header parameter name: X-Holistics-Key OAuth 2.1 Bearer token authentication Security Scheme Type: http HTTP Authorization Scheme: bearer Contact Holistics Support: [support@holistics.io](mailto:support@holistics.io) --- ## List Job's queues List Job's queues --- ## Get Job's logs Get Job's logs Request --- ## Get Job result Fetch the result data of a Job. Notes: * To check job info and status, use [GET /jobs/\{id\}](/api/v2/reference/jobs-get). * To download the result of an export job, use [GET /exports/download](/api/v2/reference/exports-download). Request --- ## Get a Job Get a Job Request --- ## List Jobs List Jobs Request --- ## Jobs Async (asynchronous) operations are operations that are executed within a background Job. If an API endpoint triggers an async operation, it will return an AsyncResult response containing the background Job info. You can use the bellow APIs to poll for the updated Job status. ```mdx-code-block ``` --- ## Submit tool call Submit tool call Request --- ## List available tools List available tools --- ## Get a Query Report Get a Query Report Request --- ## Create a Shareable Link Create a Shareable Link Request --- ## Delete a Shareable Link Delete a Shareable Link Request --- ## Get a Shareable Link Get a Shareable Link Request --- ## List Shareable Links List Shareable Links Request --- ## Update a Shareable Link Update a Shareable Link Request --- ## List all production tags Use this API to list all tags that are available in your [production tags.aml file](/docs/find-organize/tags#create-tags). --- ## Get all AML objects that are recommended to be archived Return a list of AML dashboards and datasets which have not been viewed for more than 30 days --- ## Add tags to an object Add tags to an object Request --- ## Remove tags from an object Remove tags from an object Request --- ## Tagging System Tagging system for content organization. ```mdx-code-block ``` --- ## Compute User Attribute Values Compute User Attribute Values Request --- ## List User Attribute Entries List User Attribute Entries Request --- ## Upsert User Attribute Entries Upsert User Attribute Entries Request --- ## Create a User Attribute Create a User Attribute Request --- ## Delete a User Attribute Delete a User Attribute Request --- ## List User Attributes List [User Attributes](/docs/admin/user-attributes) including [System Attributes](/docs/admin/user-attributes#system-user-attributes) Request --- ## Update a User Attribute Update a User Attribute Request --- ## Check if email address is used Check if the specified email address is already used for a User in your Holistics workspace. Request --- ## Delete a User (soft-delete) Revoke a User from accessing and using Holistics. Note: The soft-deleted User will still retain ownership of their resources. You can contact support@holistics.io to request for an ownership transfer. Request --- ## Invite Users Invite Users Request --- ## List Users List Users Request --- ## Get currently authorized User Get currently authorized User --- ## Resend invitation to User Resend invitation to User Request --- ## Restore a Deleted User Restore a Deleted User Request --- ## Revoke User API Key Admin can revoke all users' tokens. Users can revoke their tokens. Request --- ## Update a User Update a User Request --- ## Users Users management ```mdx-code-block ``` --- ## Migrating to 4.0 :::info Note If you're using version 3.0 and wish to migrate to 4.0, please **[submit migration request here](https://form.jotform.com/231550219234044)**. ::: ## Introduction This document is designed to assist Holistics customers in understanding the process of migrating from version 3.0 to version 4.0. It provides guidance on the migration process, from planning to execution, and covers the changes that come with version 4.0. Our goal is to ensure a successful migration to the new version. ## What is Holistics 4.0? Holistics 4.0 is the latest version of our analytics platform, introducing a range of new concepts and features. It is designed for analytics leaders who **prioritize control, scalability, and collaboration** in their analytics processes. Your experience remains unchanged with Holistics 4.0. Non-technical business users continue to benefit from our self-service analytics capabilities, while data teams will now have the ability to define analytics logic using code. ## Holistics 3.0 vs 4.0 ### ✅ In comparison to its previous version (Holistics 3.0), Holistics 4.0 offers: - **Analytics-as-code**: Holistics 4.0 introduces AMQL (Analytics Modeling and Query Language), allowing users to do analytics work using Holistics's code languages. - **Git Version Control**: The new version utilizes Git for code management, allowing users to track changes, create branches, and review code for quality and collaboration. - **AI-Powered Analytics:** Natural language querying, AI-assisted data exploration among other things. - **dbt Integration**: Holistics 4.0 also features an integration with dbt transformations, which improves metadata consistency and reporting accuracy. - **Canvas Dashboard**: Holistics 4.0 introduces [Canvas Dashboards](/docs/dashboards/), enabling you to create versatile and interactive data presentations. Additionally, it supports version tracking through Git. - And so much more. See **[3.0 vs 4.0 Feature Comparison](/docs/product-versions/3.0-vs-4.0)** for a high-level comparison between features between 3.0 and the new 4.0. ### 🤔 Some other considerations before migrating to Holistics 4.0: - **[Deprecation of Data Imports](https://community.holistics.io/t/support-for-importing-data-from-non-sql-sources-has-ended/643):** Holistics 4.0 will not support ETL capabilities (Data Import). Please migrate all data import jobs out of your Holistics instance and delete any import operations before proceeding with the migration. We believe that there are already existing market solutions that effectively address this challenge. - **[Data Persistence Mechanism Change](/docs/persistence):** - In Holistics 4.0, **each execution of a persistence job generates new names for persisted tables**, unlike in Holistics 3.0 where tables maintained consistent naming. This is because persisted tables (in version 4.0) function as a caching layer to boost query performance and are not intended for direct reuse. - If you require consistent naming for your persisted tables, we recommend creating **[a view](/docs/persistence#types-of-persistence-config)** through our Holistics 4.0 persistence option. - **[Development Workflow Change](/docs/quickstart):** - In Holistics 4.0, **Dataset** development has been integrated into the *Modeling* layer and is developed alongside **Models**. - Both Models and Datasets now offer an additional code-based view, complementing the GUI view available in version 3.0. - Holistics 4.0 also introduces an extra publish workflow to push changes from the Modeling phase to Reporting/Production (while in 3.0 version, each changes will take effect on Reporting/Production immediately). - **[(Optional) Disable quick dashboards](/docs/dashboards#3-im-using-both-canvas-dashboards-and-quick-dashboards-is-the-older-version-going-to-be-deprecated-and-when)**: Holistics 4.0 supports both canvas dashboards and quick dashboards, but quick dashboards are legacy and won’t receive new features/improvements. We won’t automatically disable quick dashboards on your account. However, if you want to avoid confusion from having two dashboard types, you can request that quick dashboards be disabled for your tenant. - You can’t create new quick dashboards. - You can view, edit, and delete existing quick dashboards. ## Can I test out Holistics 4.0 before deciding to migrate? Yes! And we strongly encourage you to do so. There are a few ways you can test out Holistics 4.0. 1. **Request a demo call**: Our team will be delighted to provide you with a personalized demo session, addressing any questions you may have about the migration process. 2. **Use our [Public Demo Account](https://demo4.holistics.io/demo) (Holistics 4.0)** : You can access a sandbox account of a shared public instance to test out the platform as an admin (non-analyst) user. This allows you to experience the data modeling and dashboard creation process. 3. **Request a new private trial account**: You can sign up for a separate private trial account from our [trial sign-up page](https://holistics.io/request-trial). ## Is there a price difference between 3.0 and 4.0? Yes, with the release of Holistics 4.0, a different pricing model is introduced, which means your existing pricing plan will need to be replaced. To find out more about the pricing changes, submit a [migration request using this form](https://form.jotform.com/231550219234044) and our team will reach out with the relevant information. ## What will happen with version 3.0? Will I be forced to upgrade to 4.0 in the future? We do not assume all existing Holistics 3.0 customers will feel compelled to upgrade to Holistics 4.0, nor is it our priority to encourage them to do so. We currently do not plan to require all customers to upgrade to Holistics 4.0. Should this change, you will receive an official announcement via email. Your account manager will also personally reach out to guide you through the next steps. In the event of a mandatory upgrade, we will provide a 12-month notice to ensure a smooth transition for you. ## Will you still continue to build new features for version 3.0? Holistics 4.0 brings a new architecture that enables us to expand analytics capabilities beyond what is available in 3.0. While version 3.0 will continue to receive regular updates, it is worth noting that major feature developments will primarily be focused on Holistics 4.0. This is due to the fact that these developments leverage the new architecture, allowing us to deliver more features and functionalities. ## Migrating to 4.0 ### How can I request to migrate? To upgrade to the Holistics version 4.0, please complete this [form](https://form.jotform.com/231550219234044) below: ### Pre-migration Checklist - **Compatibility Check**: Ensure you are using Holistics 3.0 version. - To check which version you are on, please refer to this [documentation](/docs/product-versions/check-version). - **Clean up unsupported features** - Since Data Imports are no longer supported in Holistics 4.0, ensure that you have migrated all your import jobs out of Holistics before proceeding with the migration. - **Book a migration timeslot:** Schedule a migration date and time with Holistics team. - **Inform your team members:** Notify your team members to refrain from accessing Holistics during the migration process. ### During Migration - **Migration Duration**: The process may take up to 4 hours during Singapore office hours (GMT+8). Once the migration timeslot is confirmed, notify your users that Holistics will be unavailable during this period. - **Backup & Restore:** Your Holistics account will be backed up before the migration takes place. In the event of any issues, we can restore your account to its previous version. ### Post-migration Checklist - Verify that everything works correctly - What you should check: - Dataset Validation: Open each datasets and ensure they load properly. Verify that you can explore the datasets and perform necessary operations. - Dashboard Validation: Confirm your dashboards load correctly and display expected data. ## How will Holistics handle unexpected issues? In the event of any issues, Holistics has measures in place to address them: - If certain reports and dashboards do not load properly after the migration, manual fixes may be required. Our team will provide guidance and support to help resolve these issues. - Holistics is committed to providing dedicated support throughout and after the migration. If you encounter any problems with your reports and dashboards, reach out to us at **[support@holistics.io](mailto:support@holistics.io)** for assistance. ## FAQs ### 1. Will my dashboards' URLs change? No, the URLs of your dashboards will remain the same as before the migration. ### 2. How will the migration affect existing data connections and integrations with external systems? The migration process will not have any impact on your existing data connections and integrations with external systems. They will continue to function as they did in the previous version. ### 3. Will there be any changes to the data access permissions or user roles after the migration? No, the data access permissions and user roles will remain unchanged from the previous version. You will have the same level of control and access as before. ### 4. What about all of my existing scheduled and embedded jobs? Your existing scheduled and embedded jobs will remain unaffected. However, Data Import model jobs will be removed. ### 5. How will this migration affect my embedded dashboards? While we anticipate a smooth transition for most embedded dashboards, there is a possibility of encountering errors if the migration script misses some configurations from the original dashboards. In such cases, you may need to manually resolve any issues with the original dashboard. --- ## AML Overview ## Introduction **AML (Analytical Modeling Language)** is a declarative language used to define data semantic models and other analytical objects in Holistics, like datasets and dashboards. It allows users to add more meaning to data without modifying the underlying structure, and make use of Holistics's powerful **Analytical Query Language (AQL)** to perform complex reports. :::note AML vs AQL **AML** is how you *model* your business — the structure, types, and reusable logic of your semantic layer. **[AQL](/as-code/aql/)** is how you *query* that model — the expression language that turns it into composable metrics and reports. This page covers AML; see the [AQL docs](/as-code/aql/) for querying. ::: ## Why AML We strongly believe in the vision of **analytics as-code** as the future of BI and analytics. However, in our opinion, there is not an adequate existing analytics-as-code language. While imperative languages like Python and R are powerful for complex logic, they are often overkill for defining semantic models. We advocate for a **declarative language** that is more suitable for defining semantic models and analytical objects. Unfortunately, existing declarative languages such as XML, JSON, and YAML have their limitations, including excessive **verbosity, ambiguity, and a lack of type checking**. Although LookML is a strong candidate, it is proprietary and only compatible with Looker. AML treats your analytics logic as **code, not configuration**. Three properties make this concrete: - **First-class, typed objects** — models, dimensions, measures, metrics, and datasets are real objects with defined types, not SQL strings embedded in YAML. You can reference, pass around, and compose them. - **Static type checking** — a built-in type system validates your model as you write it. Errors surface in the IDE at authoring time, not at query time in a broken dashboard. - **Reusable constructs** — [Constant](/reference/aml/constant), [Func](/reference/aml/func), [Module](/reference/aml/module), [Extend](/reference/aml/extend), and [Partial](/reference/aml/partial) let you factor out and compose logic instead of copy-pasting it. Together these make a model **maintainable**: less code, no drift, and changes that propagate from a single definition. This is what we mean by a language that is both **programmable** and **maintainable** — designed from the ground up for reusability, extensive parameterization, and modularization, so data teams can scale their analytical logic effortlessly as needs evolve. ### Static types as a feedback loop for AI Because AML is statically typed, the compiler checks every edit against the model immediately, whether a person writes it or an AI agent generates it. When an agent produces an invalid reference or a type mismatch, the compiler flags it at once, rather than letting the error surface later as a wrong number on a dashboard. This tight author-to-check loop is what makes agentic development on the semantic layer safe to trust. ## Design principles * **Declarative**: AML is designed for data analysts to declaratively specify the data semantic models and logic, not how to build those imperatively. * **Friendly yet rigorous syntax**: AML syntax is crafted with data analysts in mind, drawing inspiration from familiar languages like JSON, YAML, and JavaScript. It strikes a balance between being strict and well-defined, which promotes clarity and minimizes ambiguity in data and configuration expressions. * **Static type checking**: AML features a robust built-in static type system that provides instant feedback, enhancing the developer experience with top-notch smart auto-completion and template suggestions directly within the IDE. Both humans and AI agents catch mistakes at authoring time. * **Composability and Reusability**: Every component of the language is designed for reuse and composition, utilizing features such as [Constant](/reference/aml/constant), [Function](/reference/aml/func), [Module](/reference/aml/module), [Extend](/reference/aml/extend) — so analytics logic scales across a team without duplication or drift. ## Using AML in Holistics AML are written in `..aml` files in the [Development workspace](/docs/development/aml-studio#supported-files). Currently the following objects are defined using AML: - [Model](/reference/aml/model): `model_name.model.aml` - [Dataset](/reference/aml/dataset): `dataset_name.dataset.aml` - [Relationships](/reference/aml/relationship): `relationships.aml` - [Dashboard](/reference/aml/dashboard): `dashboard_name.page.aml` When users create a new object in the Modeling layer, base AML codes are automatically generated for the object. Users can extend on these bases either via the GUI, or manually write AML definitions (like dimension and measure definition, or model relationships...) which will also be reflected visually. ## Use cases highlights Some common reporting use cases that can be solved easily with AML and AQL includes: - [Cohort Retention](/as-code/aql/cookbook/aql-cohort-retention) - [Cumulative Metrics](/as-code/aql/cookbook/aql-cumulative-metrics) - [Nested Aggregation](/as-code/aql/cookbook/aql-nested-aggregation) - [Period comparison](/as-code/aql/cookbook/aql-period-comparison) - [Role-playing dimension](/docs/modeling/modeling-patterns/role-playing-dimensions) - ... Please visit our [Guides](/guides/) for more examples of how to leverage AML and AQL to answer your business questions. ## Quick start To quickly get used to AML, we suggest checking out the following pages: - [AML Examples](/reference/aml/examples) - [AML Model](/reference/aml/model) - [AML Dimension & Measure](/reference/aml/field) - [AML Dataset](/reference/aml/dataset) - [AML Dashboard](/reference/aml/dashboard) More documents can be found in the sidebar. --- ## AML Reusability Guide [AML](/reference/aml/) provides [several features](/as-code/aml/reusability-overview#features) to reuse code and logic across different objects, improving maintainability and efficiency in analytics development. Almost _anything_ created in AML can be defined once and reused by any other AML object. Here are some reusability use cases that you may be interested in: * [Create a chart once and use it in multiple dashboards](/docs/canvas-dashboard/reusable-components) * [Create part of a dashboard and use it in multiple dashboards](/embedded/dashboard-templates) * [Define dimensions, metrics, calculations and re-use in multiple models](/as-code/aml/use-cases/extend-create-new-models-on-the-fly) * etc By reusing logic, you can avoid duplication, reduce errors, and make your code more modular and scalable. This page acts as a guide to provide an overview of the general steps needed to reuse logic in AML. ## The Reusability Process * Step 1: **Identify** the logic to be reused and the resulting object that will reuse said logic. * Step 2: **Extract** reused logic to const or Partial for later reference. * Step 3: **Refer** to the reused logic in the resulting object. * Step 4 (optional): **Extend** the reused object to further customize it. This is useful when you want to mostly reuse the whole object, but with some additional small modifications. * Step 5 (optional): **Parameterize** logic with Func: for cases when you need to customize the reused logic with different parameters. ## Step 1: Identify the logic to be reused In order to reuse logic in AML, you should first identify **the logic to be reused**, and the **resulting object that reuse said logic**. Examples of reused logic include: * [SQL and AQL code snippets](/as-code/aml/use-cases/const-reuse-sql-definitions), reused in metrics and dimensions' definitions * Dataset's metrics, [reused in other datasets](/as-code/aml/use-cases/extend-metric-store) * Model's dimensions, reused in other models * Dashboard's blocks and themes, [reused in other dashboards](/docs/canvas-dashboard/reusable-components) * etc. ## Step 2: Extract the reused logic to const or Partial Top level objects in AML with explicit types (e.g. DataModel, Dataset, Dashboard, etc.) are already **AML constants** and ready to be reused, thus **no further extraction** is needed. An object's properties (Dashboard's blocks, Model's dimensions, etc.) are also ready to be **reused individually** (see [step 3](/as-code/aml/reusability-guide#step-3-refer-to-the-reused-object-or-its-properties) for details). There are 3 cases when extracting reused logic is necessary: * For reusing primitive values such as numbers, strings, etc. * For reusing non-top level objects in AML such as `VizBlock`, you can extract them to a const object. * For reusing parts of an object, you can extract them to a [Partial](/reference/aml/extend#reuse-extended-logic-with-aml-partials) object. **Primitive values** can be extracted to const objects. For example: ```aml title="Extracting primitive values to const" const my_const = 123 const my_string = "Hello, World!" ``` Non-top level objects are sub-objects that are part of another object, such as a `VizBlock` or `PageTheme` in a `Dashboard`. **Non-top level objects with explicit types** can be extracted to a const object. ```aml title="Extracting a dashboard block to const" // Equivalent: const my_block = VizBlock {...} VizBlock my_block { // details omitted... } ``` **Multiple properties of objects** (such as dataset's metrics, data model's fields) can be extracted together, at once, to a [Partial](/reference/aml/extend#reuse-extended-logic-with-aml-partials) object. Note that **any subset of an object**'s properties can be put in a partial. ```aml title="Extracting a group of dataset metrics to a PartialDataset" PartialDataset my_dataset { description: "This is a dataset that contains metrics a and b" metric a { // details omitted... } metric b { // details omitted... } } ``` ```aml title="Extracting a group of model dimensions to a PartialModel" PartialModel my_model { dimension a { // details omitted... } dimension b { // details omitted... } } ``` To reuse `Partial` objects, you can use `extend` function. Please refer to [step 4](/as-code/aml/reusability-guide#step-4-optional-extend-an-existing-object) for details. :::tip Use AML Module to group extracted objects Extracted objects can quickly clutter your project files, making it difficult to navigate your work. To improve organization, consider grouping extracted AML objects and functions into an [AML module](/reference/aml/module). An AML module is essentially a directory that serves as a container for these related items, helping you maintain a cleaner and more structured project. ::: ## Step 3: Refer to the reused object or its properties For **primitive values**, you can refer to them directly by their name, or use [string interpolation](/reference/aml/string-interpolation) feature. ```aml title="Refer to a const value" const my_const = 123 const color_blue = "#0000FF" PageTheme my_theme { background_color: color_blue } ``` ```aml title="Refer to a const value with string interpolation" const today = @sql current_date() ;; Model today_sales { description: "Sales data for today" query: @sql select * from sales where date = ${today} ;; // other details omitted... } ``` For full object with explicit names, you can refer to them directly just with the **name itself**. For **individual properties of objects**, you can refer to them by using the **object's name followed by a dot and the property name**. For examples: ```aml title="Refer to another model's dimension" Model original_model { dimension my_dimension { // details omitted... } } Model another_model { dimension another_dimension: original_model.dimension.my_dimension } ``` ```aml title="Refer to another dataset's metric" Dataset original_dataset { metric a { // details omitted... } } Dataset another_dataset { metric b: original_dataset.metric.a } ``` ```aml title="Refer to another dashboard's block" Dashboard original_dashboard { block my_block { // details omitted... } } Dashboard another_dashboard { block another_block: original_dashboard.block.my_block } ``` ## Step 4 (optional): Extend an existing object Refering to the reused object directly is useful when you want to reuse the whole object. In order to reuse an existing object with **some additional modifications**, you can use the `extend` function. This function can be applied on an analytics object to produce a new object that takes on the original properties, while merging in the additional properties. ```aml title="Extend an existing object" Model base_model { dimension a { // details omitted... } } Model another_model = base_model.extend({ dimension b { // details omitted... } }) ``` Another use case is to reuse a `Partial` object. You can use `extend` function to combine the `Partial` object with another object. ```aml title="Extend an existing object with Partial" PartialModel my_partial_model { dimension a { // details omitted... } } Model base_model { // details omitted... } Model another_model = base_model.extend(my_partial_model) ``` More details about `extend` function can be found in the [AML Extend](/reference/aml/extend) page. ## Step 5 (optional): Parameterize logic with Func When you need to customize the reused logic with different parameters, you can use `Func` to parameterize the logic. `Func` is a reusable block of code designed to perform a specific task. It can take parameters and return a value. ```aml title="Parameterize logic with Func" Func sum(x: Number, y: Number) { x + y } Func double(x: Number) { const multiple = 2 x * multiple } Func myvizBlockWithDataset(dataset: String) { // auto infer return type = VizBlock VizBlock { label: 'A pie chart' viz: PieChart { dataset: dataset // use the dataset parameter here instead of hard-coding legend: r(public_users.role) series { field { ref: r(public_users.id) aggregation: 'sum' } } } } } ``` ```aml title="Using Func in an object" Dashboard my_dashboard { // details omitted... block my_block: myvizBlockWithDataset('ecommerce') } ``` More details about `Func` can be found in the [AML Func](/reference/aml/func) page. ## Conclusion By following the steps above, you can effectively reuse logic in AML, making your analytics development process more efficient and maintainable. For more details on AML's reusability features, please refer to the [AML Reusability Overview](/as-code/aml/reusability-overview). --- ## AML Reusability Overview ## Introduction Analytics logic can become increasingly complex and difficult to maintain as more models, datasets, and dashboards are added. Analysts may end up reinventing the wheel, duplicating efforts and wasting time building similar functionality across different parts of the system. One of the key design goals of AML (Analytics Modeling Language) is to make the language highly reusable, addressing this common challenge. Unlike pure data serialization formats like XML, JSON, or YAML, AML provides the ability to **define reusable components directly within the language**. These reusability mechanisms enable analysts to factor out and abstract repeated logic, making it easy to share and reuse these components across different datasets and dashboards. This helps reduce duplication, improves maintainability, and increases overall efficiency in the analytics development process. ![AML Reusability Overview](https://cdn.holistics.io/product/aml-holistics-reusability-overview-20250120-555.png) ## Features Here are the list of reusability features that AML supports: * [AML Constant](/reference/aml/constant): An AML constant allows you to specify a value that can be reused throughout a project. * [AML Function](/reference/aml/func): An AML function is a reusable block of code designed to perform a specific task. * [AML Module](/reference/aml/module): An AML module is a directory containing related AML objects and functions together. * [AML Extend](/reference/aml/extend): AML Extend is a function that can be applied on an analytics object to produce a new object that takes on the original properties. * [AML Partial](/reference/aml/partial): AML Partial lets you extract shared logic (such as common dimensions or measures) into a reusable object that can be composed into multiple models. * [AML String Interpolation](/reference/aml/string-interpolation): String interpolation is a feature that allows embedding variable values directly into strings. * [AML If-else](/reference/aml/if-else): A control-flow expression that conditionally evaluates and returns values, enabling reusable definitions that branch on configuration. ## Understanding AML Extend AML Extend is one of the most powerful reusability features. It has three key properties: - **Inheritance**: When you edit properties in the base objects, these changes propagate to all extending objects. - **Additive**: You can add new properties to the extending objects that do not exist in the base objects. - **Overriding**: You can modify properties that the extending objects inherit from the base objects, without modifying the original properties. When you use `extend()`, AML follows these steps: 1. Clone the object that is being extended 2. Create a new extending object from the extending code 3. Merge and resolve any conflicts between these two objects. If a field is defined in both objects, AML always uses the version in the extending object (**overriding**) Example: ```aml Model users { label: 'Users' dimension id { ... } } Model activatedUsers = users.extend({ label: 'Activated users' dimension activated_at { ... } }) // Steps: // 1. Clone the "users" object into a new copy (users_cloned) // 2. Create a new extending object from the extending code block // { // label: 'Activated users' // dimension activated_at { ... } // } // 3. Merge users_cloned to the newly extending object. // The 'label' field will be overridden by the new value. // Final result: Model { label: 'Activated users' dimension id { ... } dimension activated_at { ... } } ``` ## Use Cases * [Implementing role-playing dimensions](/docs/modeling/modeling-patterns/role-playing-dimensions#approach-2-using-extend) * [Implement a metric store](/as-code/aml/use-cases/extend-metric-store) * [Expose different data for different teams](/as-code/aml/use-cases/extend-reuse-modify-expose-data) * [Reuse models but dynamically dimensions](/as-code/aml/use-cases/extend-reuse-modify-values) * [Compose new data from existing dimensions and measure](/as-code/aml/use-cases/extend-create-new-models-on-the-fly) * [Customize Client Dashboards](/embedded/dashboard-templates) ## Guide Please refer to the [AML Reusability Guide](/as-code/aml/reusability-guide) for detailed steps on how to utilize AML's reusability features. --- ## Build multiple pre-aggregates using AML Extend ## Introduction When setting up [Aggregate Awareness](/docs/aggregate-awareness/quick-start), it is a common need to create different PreAggregates for different _time granularities_ so that you can configure more _efficient_ persistence pipelines. For example: * A PreAggregate with time granularity `month` only needs to be persisted once a month. * While PreAggregate with time granularity `week` needs to be persisted once a week. To conveniently generate multiple PreAggregates for different time granularities, you can leverage AML Reusability!
## Without reusability Here's how you define it without reusability. ```aml title="movie_rating_analysis.dataset.aml" Dataset movie_rating_analysis { ... pre_aggregates: { aggr_movie_ratings_monthly: PreAggregate { dimension timestamp { for: r(public_ratings.timestamp) time_granularity: 'month' } measure highest_rating { for: r(public_ratings.rating) aggregation_type: 'max' } measure lowest_rating { for: r(public_ratings.rating) aggregation_type: 'min' } measure sum_rating { for: r(public_ratings.rating) aggregation_type: 'sum' } persistence: IncrementalPersistence { schema: 'persisted' incremental_column: 'timestamp' } }, aggr_movie_ratings_weekly: PreAggregate { dimension timestamp { for: r(public_ratings.timestamp) time_granularity: 'week' } measure highest_rating { for: r(public_ratings.rating) aggregation_type: 'max' } measure lowest_rating { for: r(public_ratings.rating) aggregation_type: 'min' } measure sum_rating { for: r(public_ratings.rating) aggregation_type: 'sum' } persistence: IncrementalPersistence { schema: 'persisted' incremental_column: 'timestamp' } }, aggr_movie_ratings_daily: PreAggregate { dimension timestamp { for: r(public_ratings.timestamp) time_granularity: 'day' } measure highest_rating { for: r(public_ratings.rating) aggregation_type: 'max' } measure lowest_rating { for: r(public_ratings.rating) aggregation_type: 'min' } measure sum_rating { for: r(public_ratings.rating) aggregation_type: 'sum' } persistence: IncrementalPersistence { schema: 'persisted' incremental_column: 'timestamp' } } } } ``` As shown in this example, we have to repeat many things: `persistence`, `highest_rating`, `lowest_rating`, `sum_rating`. * If we want to add more measures in the future, we have to add 3 times. * If we want to create more pre-aggregates in, for example, `year`, we again have to repeat almost everything. ## Refactoring using AML Extend Now let's refactor them for better reusability using [AML Extend](/reference/aml/extend). We can update the above codes using 2 steps: **Step 1**: Pick one PreAggregate (e.g. `aggr_movie_ratings_daily`) and turn it into a variable. :::info Notes * To declare a variable, you need to do it _outside_ of your `Dataset` declaration. * You can also declare this variable in a separate file! ::: ```aml title="movie_rating_analysis.dataset.aml" PreAggregate aggr_movie_ratings_daily { dimension timestamp { for: r(public_ratings.timestamp) time_granularity: 'day' } measure highest_rating { for: r(public_ratings.rating) aggregation_type: 'max' } measure lowest_rating { for: r(public_ratings.rating) aggregation_type: 'min' } measure sum_rating { for: r(public_ratings.rating) aggregation_type: 'sum' } persistence: IncrementalPersistence { schema: 'persisted' incremental_column: 'timestamp' } } Dataset movie_rating_analysis { ... } ``` **Step 2**: Create other PreAggregates by _extending_ the variable we just created. ```aml title="movie_rating_analysis.dataset.aml" Dataset movie_rating_analysis { ... pre_aggregates: { aggr_movie_ratings_monthly: aggr_movie_ratings_daily.extend({ dimension timestamp { for: r(public_ratings.timestamp) time_granularity: 'month' } }), aggr_movie_ratings_weekly: aggr_movie_ratings_daily.extend({ dimension timestamp { for: r(public_ratings.timestamp) time_granularity: 'week' } }), aggr_movie_ratings_daily: aggr_movie_ratings_daily } } ``` Just like that, we reduced 66 lines of code into 35 lines of code, making it **more maintainable** and **more readable** at the same time. [AML Extend](/reference/aml/extend) has made this so convenient! --- ## Define & reuse global SQL definitions Let's say that you define a dimension called `price_category` using SQL in one data model. As you work on other data models, you notice that the same SQL definition is also needed. With [AML Constant](/reference/aml/constant), you can declare the SQL definition as a const within a separate AML file, and then reuse it in any relevant data models in your AML project. This will help reduce code duplication, and eliminate the need to update multiple piece of code. ```aml // Define a SQL definition as const in a separate AML file // highlight-next-line // In reused_sql_defs.aml const price_category_sql = @sql select case when price >= 3000 then 'Expensive' else 'Cheap' end as price_category;; // highlight-next-line // In order.model.aml DataModel order { // ... table_name: 'ecommerce_orders' data_source_name: 'ecommerce' dimension price_category { label: "Price Category" type: "text" hidden: false // highlight-next-line definition: price_category_sql } } // highlight-next-line // In order_item.model.aml DataModel order_item { // ... table_name: 'ecommerce_order_items' data_source_name: 'ecommerce' dimension price_category { label: "Price Category" type: "text" hidden: false // highlight-next-line definition: price_category_sql } } ``` --- ## Create new models from existing dimensions and measures You can use [AML Extend](/reference/aml/extend) to define groups of reusable dimensions/measures, and combine them to create a new data model on the fly chaining extensions together. ```tsx // highlight-next-line PartialModel revenue_metrics { measure gmv { ... } measure mrr { ... } measure arr { ... } } // highlight-next-line PartialModel location { dimension city_name { ... } dimension country_code { ... } } // highlight-next-line PartialModel date { dimension created_at { ... } dimension updated_at { ... } } // Chaining multiple extends // highlight-next-line Model merchant_full = company .extend(revenue_metrics) .extend(location) .extend(date) // highlight-next-line Model merchant_secure = company .extend(location) .extend(date) ``` --- ## Implement Reusable Metric Store ## Overview With [AML Extend](/reference/aml/extend), you can define metrics once and reuse them across multiple datasets. This approach helps you: - Avoid duplicating logic for every new business question - Manage important business logic in one central place - Maintain a lean analytics codebase that's easier for new team members to learn ## Key Concepts Before diving into examples, understand these two approaches for organizing reusable metrics: 1. **Define metrics separately** - Metrics can be declared as standalone objects, making them independently reusable 2. **Group metrics with PartialDataset** - Related metrics can be organized into thematic groups using `PartialDataset` for easier management and reuse You can mix both approaches to build a flexible metric store that suits your team's needs. ## How to Reuse Metrics ### Approach 1: Define Metrics Separately You can define individual metrics as standalone objects and reference them when needed: ```aml title="metrics.aml" // Define standalone metrics Metric total_orders { label: "Total Orders" type: "number" description: "Total number of orders placed" definition: @aql count(ecommerce_orders.id);; } Metric gmv { label: "GMV (Gross Merchandise Value)" type: "number" description: "Total value of all orders before discount" definition: @aql ecommerce_order_items | sum(ecommerce_order_items.quantity * ecommerce_products.price);; format: "[\$\$]#,###0" } Metric revenue { label: "Total Revenue" type: "number" description: "Total revenue after applying commission rate" definition: @aql nmv * revenue_commission;; format: "[\$\$]#,###0" } Metric aov { label: "AOV (Average Order Value)" type: "number" description: "Average value per order" definition: @aql gmv / total_orders;; format: "[\$\$]#,###0" } ``` Then extend your dataset with individual metrics: ```aml title = "company.dataset.aml" Dataset company { // Base dataset definition // Omitted for brevity } // Add individual metrics to the dataset // highlight-next-line Dataset company_with_metrics = company.extend({ metric total_orders: total_orders metric gmv: gmv metric revenue: revenue }) ``` ### Approach 2: Group Related Metrics with PartialDataset For better organization, group related metrics into thematic `PartialDataset` objects. This makes it easier to reuse entire groups of metrics: ```aml title="metrics.aml" // Define individual metrics first Metric total_orders { label: "Total Orders" type: "number" definition: @aql count(ecommerce_orders.id);; } Metric total_delivered_orders { label: "Total Delivered Orders" type: "number" definition: @aql total_orders | where(ecommerce_orders.status is 'delivered');; } Metric total_cancelled_orders { label: "Total Cancelled Orders" type: "number" definition: @aql total_orders | where(ecommerce_orders.status is 'cancelled');; } Metric gmv { label: "GMV (Gross Merchandise Value)" type: "number" definition: @aql ecommerce_order_items | sum(ecommerce_order_items.quantity * ecommerce_products.price);; format: "[\$\$]#,###0" } Metric revenue { label: "Total Revenue" type: "number" definition: @aql nmv * revenue_commission;; format: "[\$\$]#,###0" } Metric aov { label: "AOV (Average Order Value)" type: "number" definition: @aql gmv / total_orders;; format: "[\$\$]#,###0" } // Group volume-related metrics // highlight-next-line PartialDataset volume_metrics { metric total_orders: total_orders metric total_delivered_orders: total_delivered_orders metric total_cancelled_orders: total_cancelled_orders } // Group revenue-related metrics // highlight-next-line PartialDataset revenue_metrics { metric gmv: gmv metric revenue: revenue metric aov: aov } ``` Now you can reuse these metric groups across different datasets: ```aml title="company.dataset.aml" Dataset company { // Omitted for brevity } // Extend with grouped metrics // highlight-next-line Dataset company_analytics = company .extend(volume_metrics) .extend(revenue_metrics) ``` ### Approach 3: Mix and Match - Chain Extensions You can combine both approaches, extending datasets with metric groups and individual metrics: ```aml title="store.dataset.aml" Dataset store { // Omitted for brevity } // Extend with metric groups and add custom metrics // highlight-next-line Dataset store_analytics = store .extend(volume_metrics) .extend(revenue_metrics) .extend({ // Add or override individual metrics metric store_specific_metric { label: "Store-Specific Metric" type: "number" definition: @aql gmv * (1 - store.discount_rate) } }) ``` ## Advanced: Parameterized Metrics with Functions When you need to reuse the same metric logic across different contexts (e.g., different regions, product lines, or data models), you can use [AML Functions](/reference/aml/func) to create parameterized metrics. ### The Problem: Duplicated Metric Logic Imagine you price products differently for US and UK markets using separate data models. Without parameterization, you'd duplicate the metric definition: ```aml // Without parameterization - duplicated logic PartialDataset regional_revenue_metrics { metric order_value_us { label: "Order Value (US)" type: "number" // highlight-next-line definition: @aql orders | sum(order_item.quantity * products_us.price) } metric order_value_uk { label: "Order Value (UK)" type: "number" // highlight-next-line definition: @aql orders | sum(order_item.quantity * products_uk.price) } } ``` Notice the formula is identical except for the product model reference. This duplication becomes harder to maintain as you add more regions. ### The Solution: Use Functions to Parameterize Metrics Instead, create a function that returns a `PartialDataset` with parameterized metrics: ```aml // Declare a function that accepts a product model as parameter // highlight-next-line Func getRevenueMetrics(product_model: Model) { PartialDataset revenue_metrics { metric order_value { label: "Order Value" type: "number" hidden: false // Use the product_model parameter in the definition // highlight-next-line definition: @aql orders | sum(order_item.quantity * ${product_model.name}.price) } metric gmv { label: "Gross Merchandise Value" type: "number" // highlight-next-line definition: @aql orders | sum(order_item.quantity * ${product_model.name}.price * (1 - orders.discount)) format: "[\$\$]#,###0" } } } ``` ### Reuse Parameterized Metrics Across Datasets Now you can reuse this metric logic for different regions by passing the appropriate product model: ```aml title="us_data.dataset.aml" Dataset us_market { // Base dataset definition // Omitted for brevity } // Extend with US product model // highlight-next-line Dataset us_market_with_revenue = us_market.extend(getRevenueMetrics(products_us)) // In uk_data.dataset.aml Dataset uk_market { // Base dataset definition // Omitted for brevity } // Extend with UK product model // highlight-next-line Dataset uk_market_with_revenue = uk_market.extend(getRevenueMetrics(products_uk)) ``` This approach ensures your metric logic stays consistent across regions while remaining flexible enough to adapt to different data models. ## Benefits of a Reusable Metric Store By implementing a metric store using the patterns above, you gain: 1. **Single Source of Truth** - Business logic defined once, used everywhere 2. **Easier Maintenance** - Update a metric definition in one place, all datasets benefit 3. **Consistency** - Same calculations across all reports and dashboards 4. **Scalability** - Add new metrics or metric groups without rewriting existing datasets 5. **Flexibility** - Mix and match metric groups, add custom metrics, or use parameterization for complex scenarios --- ## Expose different data to different teams You can use [AML Extend](/reference/aml/extend) to reuse the same model but hide certain fields for different purposes. Let’s say that you have a data model that represents employee information that you want to let the HR department and the Finance department use for different purposes. ```tsx Model employeeInfo { // Details of this dimension are omitted for brevity dimension employeeID {} dimension first_name {} dimension last_name {} dimension email {} dimension salary {} } ``` You can put this data model into a dataset and share that to both departments. However, the Finance department may be interested in certain dimensions, such as employee’s ID and salary, so that they can calculate relevant financial metrics. Exposing names/emails are not necessary, and can pose a security risk to your company. You can duplicate the data model into one for HR and one for Finance, but you’ll have to pay extra efforts to ensure consistency when introducing new dimensions or changing existing dimensions. With AML Extend, you can define the base model for employee information: ```tsx Model employeeInfo { // Details of this dimension are omitted for brevity dimension employeeID {} dimension first_name {} dimension last_name {} dimension email {} dimension salary {} } ``` Then you can extend the base model and define additional dimensions or metrics for HR team. ```tsx // highlight-next-line Model employeeInfoHR = employeeInfo.extend({ dimension employeeEngagementScore {} dimension recruited_from {} }) // Add this model into a dataset and then expose the dataset to HR team ``` You can also extend the base model and hide irrelevant fields for Finance team. ```tsx // highlight-next-line Model employeeInfoFinance = employeeInfo.extend({ // Note that you need to specify all properties dimension first_name { // other properties hidden: true } dimension last_name { // other properties hidden: true } dimension email { // other properties hidden: true } dimension performanceMetric {} }) // Add this model into a dataset and then expose the dataset to Finance team ``` --- ## Reuse models but dynamically modify dimensions You can use [AML Extend](/reference/aml/extend) to reuse the same model but modify its dimensions. Let's say that you have a product model that contains a description dimension, and you want to truncate this to the first 50 characters. You can use SQL to re-define the dimension definition in AML Extend like this: ```tsx Model product { // Details of this dimension are omitted for brevity dimension product_description { // Other propreties are omitted for brevity defnition: @sql {{ #SOURCE.product_description }};; } } // highlight-next-line Model productWithShortDescription = product.extend({ dimension product_description { definition: @sql left({{#SOURCE.product_description}}, 50);; } }) ``` --- ## AML vs YAML ## Introduction > "Aren't all YAML-based analytics modeling languages similar? Why did Holistics have to invent a new modeling language instead of just use YAML?" Most "Analytics as Code" solutions on the market use YAML as their underlying modeling markup syntax. While this is a popular choice among BI vendors, YAML remains a general-purpose markup language that poses many limitations when it comes to analytics development. This document explains the **limitations of YAML-based modeling language**, and how **AMQL addresses those limitations**. ## YAML is ambiguous and error-prone YAML is designed for human readability, **and it tries to be "helpful" by automatically interpreting values.** But this "helpfulness" creates a minefield of edge cases and inconsistencies. ### Inconsistent type parsing --- Consider this seemingly innocent example: ```yaml versions: - test # Return 'test' (string) - 10.5.1 # Return 10.5 (number) - 1e+5 # Return 10000 (scientific notation) config: enabled: yes # String or boolean? Depends on your parser! release: 2023-05-01 # String or date? ``` What actually gets parsed for `versions`? `["test", 10.5, 100000]` . The version number becomes a floating-point number, and the product code is interpreted as scientific notation, completely corrupting your intended data structure (all should be strings). Also, **the same YAML document parses differently** depending on which parser and version you use. Try parsing this identical YAML in Python vs Node.js. Python returns `True` (boolean) for the field `enabled`, whereas NodeJS returns `'yes'` (string). ### Whitespace indentation nightmares Furthermore, **YAML uses "whitespace indentation" to define nested structures**. This makes it really easy to cause error. Imagine working on a big YAML file and accidentally make a small spacing mistake in the code: ```yaml dimensions: - name: id label: "ID" - name: email label: "Email" ``` Can you spot where the error happens? Not very easy to do, especially with large files. These ambiguities turn simple mistakes into major debugging headaches, especially in large configuration files. ## AMQL provides clear, analytics-first syntax AMQL eliminates these ambiguities by design with a clean, explicit syntax: ```aml // users.model.aml Model users { type: 'table' label: 'Users' data_source_name: 'snowflakedb' table_name: 'public.users' dimension id { label: 'ID' type: 'number' definition: @sql {{#SOURCE.id}};; } dimension email { label: 'Email' type: 'text' definition: @sql {{#SOURCE.email}};; } measure user_count { type: 'number' label: 'Count Users' definition: @sql count({{#SOURCE.id}});; } } ``` Notice the difference: **analytics objects are first-class citizens** in AMQL. Models, dimensions, and measures are native language constructs with explicit types, not generic data structures left to interpretation. Furthermore, the curly bracket `{` syntax eliminates indentation ambiguity while creating a programming-friendly language that reads naturally and maps directly to analytics concepts. ## YAML is schemaless with no type system Perhaps the most fundamental limitation of YAML for analytics development is **its complete lack of a type system and schema validation**. This severely impacts both your analysts' productivity and your analytics projects' reliability, creating cascading problems that grow with your codebase. ### No enforcement of required fields YAML's schemaless nature means **there's no built-in way to define what fields are required**, **what types they should be**, or what relationships exist between different parts of your model: ```yaml # sales_dashboard.yaml dashboard: title: Sales Performance charts: - name: Revenue by Quarter metric: quarterly_revenue chart_type: bar - name: Conversion Rate metric: conversion_rate # highlight-next-line **# Oops! chart_type is missing, but YAML doesn't care** ``` Without schema validation, YAML **silently accepts missing required properties**. These issues only surface at runtime (typically after production deployment) and often manifest as silent corruptions where charts display convincing but entirely wrong data. ### No autocomplete and inline documentations Furthermore, YAML's schemaless nature creates a development experience that lacks two critical productivity features: **autocomplete and inline documentation**. Without code suggestions or inline docs, analysts must memorize property names, valid values, and structure details, or constantly refer to external documentation. ```yaml metrics: - name: revenue_growth time_grain: quater # Typo for "quarter" goes undetected # What are valid compare_methods? No way to know without external docs compare_method: precentage_change # Another typo that YAML happily accepts ``` This lack of guidance leads to countless wasted hours on trial-and-error, debugging typos, and hunting through documentation. ## AMQL has robust type system AML, on the other hand, is a strongly-typed language (think TypeScript but for analytics) with a powerful built-in static type system. This allows enhancing the developer experience with instant error feedback, smart auto-completion and template suggestions directly within the IDE. - **Smart autocomplete:** Get instant suggestions for valid attribute values - **Inline documentation** that explains the parameters without leaving your editor. - **Intelligent refactoring:** Go to definition, find all references, and track dependencies between dashboards, reports, and metrics - **Instant error feedback** catches mistakes as you type them - **Compile-time validation** that catches errors before deployment Here are a few examples: **Autocompletion within the analytics IDE:** the IDE auto-suggests suitable values for the "type" property of a dimension. **Inline docs** shown when hovering over code object **Instantly raise errors** when there's error (duplicate dimension). Raising error instantly when required attributes not declared **Jumps to the model's definition code** by control-clicking on name. ## YAML lacks programmability and reusability YAML's nature as a pure data serialization format means it completely lacks programmability features. This forces analytics teams into inefficient workflows and prevents the kind of code reuse that's standard in modern development. AML, on the other hand, is a fully programmable language that gives you variables, functions, and composition to build reusable analytics components. ### No variables, no functions, no abstractions Want to define a constant once and reuse it? Want to parameterize your logic? Want to create reusable analytics components? YAML offers no native way to do any of this: - No variables to store and reuse values - No functions to encapsulate and parameterize logic - No template mechanism for reusing structure - No inheritance or extension mechanisms - No conditional logic or control flow The result is analytics code that constantly violates the DRY (Don't Repeat Yourself) principle. Without programming constructs, YAML forces you to duplicate code for similar analytics components: ```yaml metrics: # Define a metric for daily active users - name: daily_active_users type: count_distinct sql: "SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date = CURRENT_DATE" # Want weekly active users? Copy-paste and modify - name: weekly_active_users type: count_distinct sql: "SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date >= CURRENT_DATE - 7" # Monthly active users? Copy-paste again - name: monthly_active_users description: "Users active in the last 30 days" type: count_distinct sql: "SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date >= CURRENT_DATE - 30" ``` This approach is not only tedious but dangerously error-prone: - When business logic changes (like how you define an "active" user), you must find and update every copy of the logic. - Also, you _can't build derived metrics_ like "daily active users in Europe" that reference and extend existing metric. Instead, you're forced to recreate the entire calculation from scratch. Here is the equivalent version in AML: ```aml {5,13,14,22,23,31,32} metric active_users { label: 'Active Users' type: 'number' definition: @aql count_distinct(events.user_id) ;; } metric daily_active_users { label: 'Daily Active Users' type: 'number' definition: @aql active_users | where(events.event_date matches @(today)) ;; } metric weekly_active_users { label: 'Weekly Active Users' type: 'number' definition: @aql active_users | where(events.event_date matches @(this week)) ;; } metric monthly_active_users { label: 'Monthly Active Users' type: 'number' definition: @aql active_users | where(events.event_date matches @(this month)) ;; } ``` When you want to change the definition of **active users** from count distinct of user id to something else, the derived metrics `daily_active_users`, `weekly_active_users`, and `monthly_active_users` automatically pick up the change. You don't have to find and update every copy of the logic. This code is more: - Composable - Reusable - Easy to read and maintain - Free of parsing ambiguities ### YAML + Jinja become a templating trap Backed into a corner by YAML's limitations, many platforms resort to bolting on external templating engines. DevOps tools like Ansible have grafted string-based templating systems like Jinja onto YAML. dbt, a popular data transformation tool, also added Jinja templating on top of their SQL-based transformation language. This is how adding Jinja templating to a YAML-based modeling language looks like: ```yaml # Data modeling with Jinja templating - a notorious pain point metrics: {% for period in ['daily', 'weekly', 'monthly'] %} {% set days = 1 if period == 'daily' else 7 if period == 'weekly' else 30 %} - name: {{ period }}_active_users description: "Users active in the last {{ days }} days" type: count_distinct sql: "SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date >= CURRENT_DATE - {{ days }}" {% endfor %} ``` While this approach might seem clever at first glance, it creates a fundamentally flawed hybrid that combines the worst aspects of both technologies: - **Multi-layer parsing nightmares**: The code must pass through multiple parsing layers (Jinja then YAML), creating cryptic errors that reference generated code rather than your source - **Runtime-only error detection**: Unlike proper programming languages, errors with undefined variables, type mismatches, and logic bugs only surface when templates execute, leaving you blind during development. This approach isn't programming-it's **string manipulation masquerading as logic**. Instead of coherent language constructs, you get a fragile chain of text processing that breaks in mysterious ways. The fact that teams resort to this approach highlights how desperately they need actual programming constructs that YAML simply cannot provide. ## AMQL is fully programmable and reusable AMQL takes a fundamentally different approach by being a **true programming language designed specifically for analytics**. Where YAML is a data format, AMQL gives you real programming constructs. ### Metric composition: build metrics from metrics Taking the `daily_active_users` metric as a base, we can easily build more metrics like `dau_in_europe` that reference and extend the original logic. ```aml {5,6,14} metric dau_in_europe { label: 'Daily Active Users in Europe' type: 'number' definition: @aql daily_active_users | where(users.country = 'Europe') ;; } metric dau_in_europe_pct { label: 'EU DAU % of Total' type: 'number' definition: @aql (dau_in_europe / daily_active_users) * 100 ;; } ``` If you want to change the logic of `active_users`, **do it once**, and `daily_active_users`, `dau_in_europe`, `dau_in_europe_pct` all pick it up automatically. ### Extend: build on existing models, don't rewrite them Need a model that's 90% the same as `events` but with extra fields? `extend` grafts new properties onto an existing object: ```aml {5,12} model user_events = events.extend({ dimension user_email { label: 'User Email' type: 'text' definition: @sql {{#SOURCE.users.email}};; } metric conversion_rate { label: 'Conversion Rate' type: 'number' definition: @aql (count_distinct(purchases.user_id) / count_distinct(user_id)) * 100 ;; } }) ``` In YAML, you'd copy the entire model and add your changes. In AMQL, you declare the delta. The compiler merges, preserves the original's integrity, and catches conflicts at build time. ### Func: parameterize logic, not strings When you need to generate similar analytics objects with different inputs, `Func` gives you typed, parameterized functions: ```aml {6-8} Func active_users_for(period: String, threshold: Number) { metric active_users_last_${period} { label: 'Active Users (last ${period}, min ${threshold})' type: 'number' definition: @aql active_users | where(events.event_date matches @(last ${period})) | where(events.session_count >= threshold) ;; } } active_users_for('7 days', 5) // active_users_last_7_days active_users_for('30 days', 10) // active_users_last_30_days ``` Compare to Jinja: no multi-layer parsing (Func → AML, not Jinja → YAML → SQL), no string concatenation bugs, and the IDE autocompletes parameter names because the language understands the function signature. ### Constants ```aml {5-9,16-17} const default_currency = 'USD' const high_value_threshold = 10000 const price_category_sql = @sql select case when price >= high_value_threshold then 'Expensive' else 'Cheap' end as price_category ;; metric high_value_orders { label: 'High-Value Orders' type: 'number' definition: @aql orders | where(orders.amount >= high_value_threshold and orders.currency = default_currency) ;; } ``` When the business redefines "high value" from \$10,000 to \$25,000, you change one constant. Every dashboard, every metric, every report that references it updates automatically. The code sample below ties it all together: models, datasets, and metrics for calculating user conversion rates across multiple time granularities, built from modular components and composed on the spot. ## Conclusion We're not the first to recognize YAML's limitations for analytics. Even Looker, one of the pioneers of analytics-as-code, eventually abandoned their YAML-based LookML 1.0 in favor of creating their own proprietary language (LookML 2.0). As Looker's [engineering team explained](https://web.archive.org/web/20220702090448/https://community.looker.com/lookml-5/new-lookml-why-3531?postid=6957#post6957) when announcing the change (see also [Google's migration guide](https://docs.cloud.google.com/looker/docs/best-practices/how-to-convert-yaml-to-new-lookml)): _"YAML has a lot of sharp edges... Building a parser with YAML wasn't sustainable... YAML is not a standard designed nor intended for data modeling."_ However, while Looker solved YAML's ambiguity and tooling issues, **LookML 2.0 remains essentially a markup language**. It lacks true programmability features like variables, functions, and composition. AMQL goes further by being a fully programmable language that enables the code reuse and composition that analytics teams desperately need. We designed AMQL to systematically address every limitation of YAML for analytics development. Where YAML fails due to its nature as a general-purpose data format, AMQL succeeds by being purpose-built for analytics from the ground up through three foundational principles: - **Clear, unambiguous syntax with first-class analytics concepts** that eliminate YAML's interpretation ambiguities - **Robust static type system** that enables intelligent IDE features like autocomplete, inline documentation, and instant error detection - **Native programmability** to enable true code reuse and composition without dangerous workarounds Your analysts deserve something better than YAML. ## tldr: AMQL vs YAML | **Category** | **YAML** | **AMQL** | | ------------------------ | ----------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------ | | **Type Safety** | ❌ Schemaless, no type checking❌ Silent errors at runtime❌ Ambiguous value interpretation | ✅ Strong static type system✅ Compile-time error detection✅ Explicit, unambiguous syntax | | **Developer Experience** | ❌ No autocomplete or inline docs❌ Manual trial-and-error workflow❌ Whitespace indentation errors | ✅ Smart autocomplete & suggestions✅ Inline documentation✅ Instant error feedback | | **Reusability** | ❌ No variables, functions, or abstractions❌ Forces code duplication❌ Dangerous templating workarounds | ✅ Variables, functions, and composition✅ True code reuse and modularity✅ Native programmability | | **Analytics Focus** | ❌ General-purpose data format❌ No understanding of analytics concepts | ✅ Purpose-built for analytics✅ First-class analytics objects | | **Parsing Consistency** | ❌ Different results across parsers❌ Version-dependent behavior❌ "Helpful" but unpredictable interpretation | ✅ Consistent, predictable parsing✅ Analytics-specific language constructs | **Bottom Line:** YAML is a data serialization format trying to be an analytics language. AMQL is a true programming language designed specifically for analytics development. --- ## Basic Concepts On this page, we will go through a few basic concepts that you will encounter throughout this document, give them a quick definition, and link to dedicated docs when necessary. ### Expression A “sentence” written in AQL code that, after being evaluated, will return something, like a table, a table column, or a scalar value. For more details, please check out the [Expressions](/reference/aql/expression) page. ### Model An abstract layer on top of your physical database tables that is defined in a `.model.aml` file in the AML modeling layer. A model contains information about the source type (table, or query), its dimensions, measures, and other meta-data like model documents, owners… For more details, please check out the [AML Data Model](/reference/aml/model) page. ### Iterate In AQL, “iterate” on a table means going through all rows of that table, and apply the same operation on each row. For example, in this transformation: ```aml orders | select(quantity_times_10: orders.quantity * 10) ``` Behind the scenes, AQL goes through each row of the `orders` table, get the values of the `quantity` field, multiply it with 10, and assign the new values to a new field called `quantity_times_10` ### Table’s origin model In AQL, when you apply transformations on a Table or a Field (like `select` or `filter`), it outputs a new Table or a Field object that still retains information about the first model that you used. For a more detailed explanation, please check out the [Origin Model](/as-code/amql/aql-concepts-origin) page. ### Cross-model reference This is one of the core features of AQL, that requires more reading to grasp. But in short, when you call `select`, `filter`, `sum`, or other functions that iterate on a `Table` with an origin model, you can reference other models that have relationships to that origin. ### Context Context encapsulates the necessary information to evaluate an expression, like the dimensions, relationships, and the filter condition that should be applied to the calculation… For a more detailed explanation, please check out the [Context](/as-code/aql/learn/metric-context) page. ### Types Type can be understood as the “blueprint”, or the “category” of an object in AQL. Depending on its type, an object can have certain properties. For more detailed explanation, please check out the [Types](/reference/aql/type-index) page. --- ## Data Modeling with AQL ## What is a data model > A data model is an abstract representation on top of a database table/SQL query that you may manipulate without directly affecting the underlying data. > > from _Holistics Documentation_ This definition is a bottom-up definition. From a top-down point of view, a data model represents a *Business Entity*. The fact that it's backed by a database table or a SQL Query is only an *implementation detail*. For example, every business has its own concept of *What a User is*. The job of a data model is to expose a concrete object representing that concept for Business Users to reason and interact with. One crucial aspect of understanding the data model is understanding what *Dimensions* and *Measures* are. ### Measure Measures are numeric values obtained by aggregating individual values, serving as key indicators (KPIs) for specific aspects or processes of a business. Using the context of the `user` model as an example, a measure would be something like `total_users`, `active_users`, `life_time_value`, etc. However, measures alone may lack meaning. How can we address questions about factors such as - "What led to the growth in Total Users this month"? - "Where did the new users come from?" - "Was it a result of a recent promotion?" This is where dimensions come in ### Dimension Dimensions are attributes of the model, and they give us the context to help us understand the meaning of the measures. For example, Total Users don't mean much as an isolated number Using the Sign Up At dimension, we can break Total Numbers down into the month level and have more context to understand what it means This gave us the context to understand: - What is the increase this month? - How is the increase compared to other months? - Which month has the highest spike in Registered Users? This type of analysis is called “slice-and-dice", meaning we chose an arbitrary set of dimensions to **break a measure down to the Level of Detail defined by the dimensions**. ## What is a relationship We have established that Data Model is a representation of a Business Entity, but Business Entities don't live in isolation. A User may have multiple Orders, an Order has multiple Order Items, and an Order Item is associated with a Product, etc. These relationships are crucial for analyzing data involving multiple entities In Holistics, when models are connected by relationships, we essentially treat them as a single large model. What this means is that the models are joined dynamically at run-time, while retaining the correct relationship between data. This approach enables end-users to focus on the business logic and effortlessly explore data without worrying about how the data is combined For example, since we have already created a relationship between `Users` and `Orders`, we can simply drag `Full Name` field from `Users` and `Order ID` from `Orders` into the exploration. As a result, a unique `Full Name` is associated with multiple `Order ID` values, just like how the relationship is defined which follows the underlying business logic. ## What is a dataset > In Holistics, a **Dataset** is a "container" holding several [data models](/docs/data-model) together so they can be explored together, and dictating which join path to be used in a particular analytics use case. Fundamentally, a dataset is a compilation of models and their relationships. They form a graph representing how the model (business entities) are related through the established relationships. With a clear understanding of the role and functionality of models and relationships, we can see that dataset is just a way to organize them to solve some particular analytics use cases ## From SQL to AQL SQL AMQL Dataset SQL doesn't inherently have a concept of datasets; data is queried from tables or views. A container holding multiple data models together for cohesive exploration. It dictates join paths for specific analytics use cases. Fundamentally, a dataset compiles models and their relationships into a graph, representing how business entities are related. Relationship Based on common fields from more than one table, often involving primary and foreign keys. JOIN and ON operators are used to connect 2 related tables within the query Based on common fields from more than one table. Relationships can be established within or separately from the datasets. Relationships enable users to treat connected models as a single large model and perform dynamic joining at runtime Measure An aggregation that is constructued from numerical fields and is included in SELECT clause Any expression that can return a single scalar value and can change based on the context it is used in. Dimension A table field which is included in SELECT and GROUP BY clause A dimension provides context to the measure, allowing it to be viewed from different angles and levels of detail --- ## Origin Model In AQL, when you apply transformations on a Table or a Field (like `select` or `filter`), it outputs a new Table or a Field object that still retains information about the first model that you used. For example, in the AML layer you have defined a model called `order_items`, and then you apply some transformations to it: ```aml Model order_items { dimension item_id {} dimension order_id dimension product_id {} dimension quantity {} } Model products { dimension id {} dimension price {} } ``` Imagine that you use SQL to query these two models: ```sql select oi.quantity * p.price as item_value from order_items oi left join products p on oi.product_id = p.id ``` After this SQL transformation, you will have a new table with only one column (`item_value`) and there is no information about `order_items` or `products` models. ```sql with order_items_transformed as ( select order_items.quantity * products.price as item_value from order_items left join products on order_items.product_id = products.id ) // Invalid select order_items.order_id from order_items_transformed ``` With AQL, the same transformation will also produce a new table with only a `value` column: ```aml // Output a new virtual 'order_items_transformed' table order_items | select(item_value: order_items.quantity * products.price) ``` However, the new table still retains the information about `order_items`. In this case, `order_items` is the origin model of the new `order_items_transformed` table. After the transformation, you can still access information of `order_items`: ```aml order_items | select(item_value: order_items.quantity * products.price) | select(order_items.order_id, order_items.item_id, item_value) ``` --- ## Design Principles ## Declarative and query-based language design - Similar to how Ansible and Terraform changed the way infrastructure is defined in code, we strongly believe in the superiority of declarative based language for expressing analytics semantic models - But defining semantic models by itself is not enough, we also have the need to have a query language ## Intuitive and easy to learn syntax - AML's declarative syntax is carefully designed to be familiar with data analysts who work with JSON and YAML data before, while avoiding ambiguity that plagues languages like YAML. - AQL's query syntax is designed to be familiar with analysts who has knowledge of SQL, while at the same time fixing the flaws of SQL such as lack of composability and extensibility ## Correctness - Holistics understands that trust in analytical logic is crucial for accurate decision-making. That's why AMQL includes a powerful type system that provides real-time feedback to analysts to help them avoid errors early. - In the future, AMQL will also include a built-in testing system that allows analytics logic to be properly tested before deploying to production. ## Developer experience - It is 2024 and data analysts should expect their analytics logic code to have excellent developer experience like their programming language counterparts with features such as: - Smart syntax highlighting - Auto-completion and suggestion - Automated refactoring - Real time feedback - AMQL is designed from the ground up to be able to support these features smoothly and effectively - Holistics Development workspace and Holistics Lite’s VSCode extension provides dual code-UI mode, which not only softens the learning curve but also makes the experience more intuitive ## Reusability - Using reusable code makes it significantly easier and quicker to create new analytics logic. Additionally, defining analytics logic in reusable code simplifies maintenance and updates, ensuring that reports and dashboards more likely to remain up-to-date. - Unlike pure data serialization formats such as XML, JSON or YAML, AMQL provides ability to define [constants](/reference/aml/constant), [functions](/reference/aml/func), [modules](/reference/aml/module) right inside the language. These mechanisms enable analysts to factor and abstract out repeated logic and reuse them across datasets and dashboards. - Unlike SQL, AQL allows you to save query fragments for later reuse. These fragments can then be combined to create new variations of existing queries when the need arises. - Holistics will soon provide a packaging system and a central repository that allows users to import reusable code published by others. This will enable cross-organization reusability of analytics logic. ## Performance - Ideally, the language should get out of the way of the analysts when they develop analytics logic, thus the performance of its toolchain is of utmost importance. - AMQL internal architecture is designed to be incremental, requiring minimal amount of computation needed to handle small changes that are inherent in typical, day-to-day development process. - At the same time, the processing performance of full projects is benchmarked and heavily optimized to minimize deployment time. ## Extensibility - AMQL's parser, type checker, and interpreter are going to be open sourced, enabling developers to create custom tools on top of them. - Unlike LookML which only supports Looker’s object, developer can declare their own types, effectively create their own domain specific objects --- ## What is AMQL? :::info AMQL is the analytical language powering Holistics 4.0. ::: ## What is AMQL? AMQL (Analytics Modeling and Querying Language) is the analytics-as-code language that powers Holistics' [expressive semantic layer](/docs/difference#the-differentiator-an-expressive-semantic-layer). Using AMQL, analysts define analytics logic in code (metrics, models, datasets, dashboards) and query that logic with composable, metric-centric expressions. AMQL is two inter-connected components: - **[AML (Analytics Modeling Language)](/reference/aml/)**: a declarative language for defining data semantic models, business metrics, datasets, dashboards, and relationships. AML is to analytics what Terraform is to infrastructure: a typed, reviewable, version-controllable description of what you want. - **[AQL (Analytics Query Language)](/as-code/aql/)**: a query language that operates on the semantic model defined in AML. AQL elevates **metrics to first-class composable objects**, so logic like period comparisons, cohort retention, ratios across grains, and nested aggregations stays inside the metric layer instead of leaking into SQL workarounds. Together, AML and AQL are why Holistics' semantic layer is **expressive enough for AI to reason from**. Instead of generating SQL against raw schema, [Holistics AI](/docs/ai) generates AQL against the governed semantic layer, using the same composable definitions humans already trust. See [Why Holistics AI is reliable](/docs/ai/architecture) for the full mechanism. AMQL is designed for data engineers, data analysts, analytics engineers, or anyone who works with analytics logic and wants **better reusability, composability, governance, and development productivity** than existing tools provide. ## Metric-centric Thinking AMQL follows “metrics-centric thinking” paradigm. AMQL allows users to query data at a higher abstraction level, closer to how business users operate. This makes AMQL more accessible to non-technical users compared to SQL. A query in AMQL revolves around metrics and dimensions. These metrics and dimensions are defined beforehand by analytics team. Check out [Metrics-centric Thinking](https://community.holistics.io/t/the-ideal-semantic-layer-and-metric-centric-paradigm-blog-post/1507) for a more detailed writeup. ## Why we built AMQL? :::tip Structural arguments - **[AML vs YAML](/as-code/amql/aml-vs-yaml)**: why we didn't use YAML configs (the route taken by dbt, Cube, MetricFlow, LookML 1.0). - **[AQL vs SQL](/as-code/aql/aql-vs-sql)**: why SQL strings can't be a metrics layer. ::: We strongly believe in the vision of **analytics as-code as the future of BI and analytics**. However, in our opinion, there is not an adequate existing analytics-as-code language. Firstly, we believe there is a need for a **declarative language to define semantic model**, similar to how Ansible and Terraform revolutionized the way infrastructure is defined in code. When we look at existing declarative languages, there are languages like XML, JSON or YAML but each of them has their own limitations. The most suitable language is LookML but it is proprietary and only works with Looker. Secondly, we also believe there is a need for a **new query language that can leverage the semantic model defined** in the first language, built to be concise, expressive while also suitable for adhoc analysis. We believe SQL is too low level as a language and solution like adding templating language (like dbt’s Jinja use) on top is just a band-aid. Thus, in order to realize this vision, we created AMQL to be a complete analytics-as-code language. ## Installation AMQL is currently only available in Holistics Cloud. Support for self-hosted installation will come in future versions. --- ## AQL Functions Cheatsheet Every AQL function in one place. Use this page to scan; jump to the dedicated reference page for full signatures, parameter details, and examples. See also: [Operators Cheatsheet](/reference/aql/operators) · [AQL Functions Overview](/reference/aql/function). ## Table Function | Function | Syntax | Purpose | | --- | --- | --- | | [`select`](/reference/aql/select) | `select(table, field1, [field2, ...])` | Returns a table containing only the specified fields. | | [`group`](/reference/aql/group) | `group(table, dimension1, [dimension2, ...])` | Returns a table grouped by one or more specified dimensions. | | [`filter`](/reference/aql/filter) | `filter(table, condition1, [condition2, ...])` | Returns a table containing only the rows that satisfy one or more specified conditions. | | [`unique`](/reference/aql/unique) | `unique(dimension1, [dimension2, ...])` | Returns a table with all unique combinations of the specified dimensions. | | [`top`](/reference/aql/top) | `top(n, dimension, by: metric, [logic])` | Returns the top N values of a specified dimension based on a metric. | | [`bottom`](/reference/aql/bottom) | `bottom(n, dimension, by: metric, [logic])` | Returns the bottom N values of a specified dimension based on a metric. | ## Condition Function | Function | Syntax | Purpose | | --- | --- | --- | | [`where`](/reference/aql/where) | `where(metric, condition1, [condition2, ...])` | Calculates a metric with specified conditions applied. | ## Aggregate Function | Function | Syntax | Purpose | | --- | --- | --- | | [`count`](/reference/aql/aggregator-functions#count) | `count([table], expression)` | Counts the total number of items in a group, excluding NULL values. | | [`count_if`](/reference/aql/aggregator-functions#count_if) | `count_if([table], condition)` | Counts the total rows from one table that satisfy the given condition. | | [`count_distinct`](/reference/aql/aggregator-functions#count_distinct) | `count_distinct([table], expression)` | Counts the total number of distinct items in a group, excluding NULL values. | | [`approx_count_distinct`](/reference/aql/aggregator-functions#approx_count_distinct) | `approx_count_distinct([table], expression)` | Counts the approximate number of distinct items in a group, excluding NULL values. | | [`average`](/reference/aql/aggregator-functions#average) | `average([table], expression)` | Calculates the average of values in a group, excluding NULL values. | | [`min`](/reference/aql/aggregator-functions#min) | `min([table], expression)` | Returns the item in the group with the smallest value, excluding NULL values. | | [`max`](/reference/aql/aggregator-functions#max) | `max([table], expression)` | Returns the item in the group with the largest value, excluding NULL values. | | [`sum`](/reference/aql/aggregator-functions#sum) | `sum([table], expression)` | Calculates the sum of values in the group, excluding NULL values. | | [`median`](/reference/aql/aggregator-functions#median) | `median([table], expression)` | Computes the median of the values in the group, excluding NULL values. | | [`stdev`](/reference/aql/aggregator-functions#stdev) | `stdev([table], expression)` | Computes the sample standard deviation of the values in the group, excluding NULL values. | | [`stdevp`](/reference/aql/aggregator-functions#stdevp) | `stdevp([table], expression)` | Computes the population standard deviation of the values in the group, excluding NULL values. | | [`var`](/reference/aql/aggregator-functions#var) | `var([table], expression)` | Returns the sample variance of the values in the group, excluding NULL values. | | [`varp`](/reference/aql/aggregator-functions#varp) | `varp([table], expression)` | Returns the population variance of the values in the group, excluding NULL values. | | [`string_agg`](/reference/aql/aggregator-functions#string_agg) | `string_agg([table], expression, [sep: _sep], [distinct: _distinct], [order: _order])` | Returns a text that is the concatenation of all values of the expression. | | [`corr`](/reference/aql/aggregator-functions#corr) | `corr(table, field1, field2)` | Returns the Pearson correlation coefficient of two number fields in the table. | | [`max_by`](/reference/aql/aggregator-functions#max_by) | `max_by(table, value, by)` | Returns the value of `value` from the row where `by` is maximum. | | [`min_by`](/reference/aql/aggregator-functions#min_by) | `min_by(table, value, by)` | Returns the value of `value` from the row where `by` is minimum. | | [`percentile_cont`](/reference/aql/aggregator-functions#percentile_cont) | `percentile_cont([table], expression, percentile)` | Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed. | | [`percentile_disc`](/reference/aql/aggregator-functions#percentile_disc) | `percentile_disc([table], expression, percentile)` | Returns the value at the given percentile of the sorted expression values. | ## Logical Function | Function | Syntax | Purpose | | --- | --- | --- | | [`case`](/reference/aql/logical-functions#case) | `case(when: condition, then: value, [when: condition_expr, then: value, ...], [else: value])` | Returns the value associated with the first condition that evaluates to true. | | [`and`](/reference/aql/logical-functions#and) | `and(condition, ...)` | Returns true only when all specified conditions are true. | | [`or`](/reference/aql/logical-functions#or) | `or(condition, ...)` | Returns true when at least one of the specified conditions is true. | | [`not`](/reference/aql/logical-functions#not) | `not(condition)` | Logical NOT takes a single truefalse expression and returns true when the expression is false. | ## Relationship Function | Function | Syntax | Purpose | | --- | --- | --- | | [`with_relationships`](/reference/aql/with_relationships) | `with_relationships(metric, relationship1, [relationship2, ...])` | Calculates a metric using specific relationships that are not active by default. | ## Level Of Detail Function | Function | Syntax | Purpose | | --- | --- | --- | | [`of_all`](/reference/aql/of_all) | `of_all(metric, [model, dimension, ...], [keep_filters: false])` | Returns a metric evaluated without certain dimensions or grains. | | [`keep_grains`](/reference/aql/keep) | `keep_grains(metric, [model, dimension, ...], [keep_filters: false])` | Calculates a metric only against the specified dimensions or grains, ignoring all other dimensions. | | [`dimensionalize`](/reference/aql/dimensionalize) | `dimensionalize(metric, [dimension, ...])` | Calculates a metric at a specific Level of Detail (LoD), regardless of the outer query context. | | [`percent_of_total`](/reference/aql/percent_of_total) | `percent_of_total(metric, total_type)` | Calculates the percentage of a metric relative to a specified total type. | ## Time-based Function | Function | Syntax | Purpose | | --- | --- | --- | | [`running_total`](/reference/aql/running_total) | `running_total(metric, [running_dimension, ...], [keep_filters: false])` | Calculates a running total of a metric along specified dimensions from the starting point to the current period. | | [`period_to_date`](/reference/aql/period_to_date) | `period_to_date(metric, date_part, date_dimension)` | Calculates a metric from the beginning of a specified time period (year, quarter, month, etc.) to the current date. | | [`exact_period`](/reference/aql/exact_period) | `exact_period(metric, time_dimension, time_range)` | Calculates a metric within a custom time period. | | [`relative_period`](/reference/aql/relative_period) | `relative_period(metric, time_dimension, offset)` | Calculates a metric in the active time range shifted by a specified interval. | | [`trailing_period`](/reference/aql/trailing_period) | `trailing_period(metric, date_dimension, period)` | Calculates a metric over a specific number of date periods up to the current period. | ## Window Function | Function | Syntax | Purpose | | --- | --- | --- | | [`rank`](/reference/aql/rank) | `rank(order: order_expr, ..., [partition: partition_expr, ...])` | Returns the rank of rows within a partition of a table. | | [`dense_rank`](/reference/aql/dense_rank) | `dense_rank(order: order_expr, ..., [partition: partition_expr, ...])` | Returns the rank of rows within a partition of a table. | | [`percent_rank`](/reference/aql/percent_rank) | `percent_rank(order: order_expr, ..., [partition: partition_expr, ...])` | Returns the relative percentile of a row within a partition of a table. | | [`ntile`](/reference/aql/ntile) | `ntile(ranks, order: order_expr, ..., [partition: partition_expr, ...])` | Divides the rows within a partition into a specified number of ranked groups. | | [`next`](/reference/aql/next) | `next(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])` | Returns the value from a following row at a specified offset relative to the current row. | | [`previous`](/reference/aql/previous) | `previous(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])` | Returns the value from a preceding row at a specified offset relative to the current row. | | [`first_value`](/reference/aql/first_value) | `first_value(expr, order: order_expr, ..., [partition: partition_expr, ...])` | Returns the value of an expression from the first row of the window frame. | | [`last_value`](/reference/aql/last_value) | `last_value(expr, order: order_expr, ..., [partition: partition_expr, ...])` | Returns the value of an expression from the last row of the window frame. | | [`nth_value`](/reference/aql/nth_value) | `nth_value(expr, index, order: order_expr, ..., [partition: partition_expr, ...])` | Returns the value of an expression from the Nth row of the window frame, where N is a positive integer. | | [`window_sum`](/reference/aql/window_sum) | `window_sum(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])` | Returns the sum of values in rows within a specified range relative to the current row. | | [`window_count`](/reference/aql/window_count) | `window_count(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])` | Returns the count of values in rows within a specified range relative to the current row. | | [`window_min`](/reference/aql/window_min) | `window_min(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])` | Returns the min of rows in a range relative to the current row. | | [`window_max`](/reference/aql/window_max) | `window_max(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])` | Returns the maximum value in rows within a specified range relative to the current row. | | [`window_avg`](/reference/aql/window_avg) | `window_avg(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])` | Returns the average of values in rows within a specified range relative to the current row. | | [`window_stdev`](/reference/aql/window_stdev) | `window_stdev(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])` | Returns the sample standard deviation of values in rows within a specified range relative to the current row. | | [`window_stdevp`](/reference/aql/window_stdevp) | `window_stdevp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])` | Returns the population standard deviation of values in rows within a specified range relative to the current row. | | [`window_var`](/reference/aql/window_var) | `window_var(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])` | Returns the sample variance of values in rows within a specified range relative to the current row. | | [`window_varp`](/reference/aql/window_varp) | `window_varp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])` | Returns the population variance of values in rows within a specified range relative to the current row. | ## Time Intelligence Function | Function | Syntax | Purpose | | --- | --- | --- | | [`epoch`](/reference/aql/time-intelligence-functions#epoch) | `epoch([datetime])` | Returns a Unix timestamp which is the number of seconds that have elapsed since '1970-01-01 00:00:00' UTC. | | [`day`](/reference/aql/time-intelligence-functions#day) | `day(datetime_dimension)` | Truncates a `datetime_dimension` value to the first day of the day (midnight). | | [`week`](/reference/aql/time-intelligence-functions#week) | `week(datetime_dimension)` | Truncates a `datetime_dimension` value to the first day of the week. | | [`month`](/reference/aql/time-intelligence-functions#month) | `month(datetime_dimension)` | Truncates a `datetime_dimension` value to the first day of the month. | | [`quarter`](/reference/aql/time-intelligence-functions#quarter) | `quarter(datetime_dimension)` | Truncates a `datetime_dimension` value to the first day of the quarter. | | [`year`](/reference/aql/time-intelligence-functions#year) | `year(datetime_dimension)` | Truncates a `datetime_dimension` value to the first day of the year. | | [`hour`](/reference/aql/time-intelligence-functions#hour) | `hour(datetime_dimension)` | Truncates a `datetime_dimension` value to the first minute of the hour. | | [`minute`](/reference/aql/time-intelligence-functions#minute) | `minute(datetime_dimension)` | Truncates a `datetime_dimension` value to the first second of the minute. | | [`date_trunc`](/reference/aql/time-intelligence-functions#date_trunc) | `date_trunc(datetime_dimension, datetime_part)` | Truncates a `datetime_dimension` value to the granularity of `datetime_part`. | | [`date_part`](/reference/aql/time-intelligence-functions#date_part) | `date_part(datetime_part, datetime)` | Extracts a specific numeric part from a date or datetime value. | | [`year_num`](/reference/aql/time-intelligence-functions#year_num) | `year_num(datetime)` | Extracts the numeric year from a datetime value. | | [`quarter_num`](/reference/aql/time-intelligence-functions#quarter_num) | `quarter_num(datetime)` | Extracts the quarter number from a datetime value. | | [`month_num`](/reference/aql/time-intelligence-functions#month_num) | `month_num(datetime)` | Extracts the month number from a datetime value. | | [`week_num`](/reference/aql/time-intelligence-functions#week_num) | `week_num(datetime)` | Extracts the week number from a datetime value. | | [`dow_num`](/reference/aql/time-intelligence-functions#dow_num) | `dow_num(datetime)` | Alias for dayofweek_num, extracts the day of week number from a datetime value. | | [`day_num`](/reference/aql/time-intelligence-functions#day_num) | `day_num(datetime)` | Extracts the day of month number from a datetime value. | | [`hour_num`](/reference/aql/time-intelligence-functions#hour_num) | `hour_num(datetime)` | Extracts the hour number from a datetime value. | | [`minute_num`](/reference/aql/time-intelligence-functions#minute_num) | `minute_num(datetime)` | Extracts the minute number from a datetime value. | | [`second_num`](/reference/aql/time-intelligence-functions#second_num) | `second_num(datetime)` | Extracts the second number from a datetime value. | | [`date_diff`](/reference/aql/time-intelligence-functions#date_diff) | `date_diff(datetime_part, start, end)` | Calculates the difference between two dates in the specified `datetime_part`. | | [`date_format`](/reference/aql/time-intelligence-functions#date_format) | `date_format(datetime, format)` | Formats a date according to the specified format string. | | [`from_unixtime`](/reference/aql/time-intelligence-functions#from_unixtime) | `from_unixtime(number)` | Converts a Unix timestamp (seconds since epoch) to a datetime value. | | [`last_day`](/reference/aql/time-intelligence-functions#last_day) | `last_day(datetime, date_part)` | Returns the last day of the period for a given date. | | [`age`](/reference/aql/time-intelligence-functions#age) | `age(datetime)` | Returns the age in years. | ## Null/Zero Handling Function | Function | Syntax | Purpose | | --- | --- | --- | | [`coalesce`](/reference/aql/null-and-zero-functions#coalesce) | `coalesce(val1, val2, ...)` | Returns the first non-null value in a list of expressions. | | [`nullif`](/reference/aql/null-and-zero-functions#nullif) | `nullif(val1, val2)` | Returns NULL if two expressions are equal, otherwise returns the first expression. | | [`safe_divide`](/reference/aql/null-and-zero-functions#safe_divide) | `safe_divide(dividend, divisor)` | Returns the division with a safe mechanism to handle division by zero. | ## Mathematical Function | Function | Syntax | Purpose | | --- | --- | --- | | [`abs`](/reference/aql/math-functions#abs) | `abs(number)` | Returns the absolute value of a number, removing any negative sign and returning the non-negative magnitude. | | [`sqrt`](/reference/aql/math-functions#sqrt) | `sqrt(number)` | Calculates the square root of a given number. | | [`ceil`](/reference/aql/math-functions#ceil) | `ceil(number)` | Returns the smallest integer greater than or equal to the given number. | | [`floor`](/reference/aql/math-functions#floor) | `floor(number)` | Returns the largest integer less than or equal to the given number. | | [`round`](/reference/aql/math-functions#round) | `round(number, [scale])` | Rounds a number to a specified number of decimal places. | | [`trunc`](/reference/aql/math-functions#trunc) | `trunc(number, [scale])` | Truncates a number to a specified number of decimal places, removing digits beyond the specified scale. | | [`exp`](/reference/aql/math-functions#exp) | `exp(number)` | Returns the value of the mathematical constant e (Euler's number) raised to the power of the given number. | | [`ln`](/reference/aql/math-functions#ln) | `ln(number)` | Calculates the natural logarithm (base e) of a given number. | | [`log10`](/reference/aql/math-functions#log10) | `log10(number)` | Calculates the base 10 logarithm of a given number. | | [`log2`](/reference/aql/math-functions#log2) | `log2(number)` | Calculates the base 2 logarithm of a given number. | | [`pow`](/reference/aql/math-functions#pow) | `pow(base, exponent)` | Raises a base number to the power of an exponent. | | [`mod`](/reference/aql/math-functions#mod) | `mod(dividend, divisor)` | Returns the remainder of a division operation. | | [`div`](/reference/aql/math-functions#div) | `div(dividend, divisor)` | Returns the integer quotient of a division operation. | | [`sign`](/reference/aql/math-functions#sign) | `sign(number)` | Returns the sign of a number: 1 for positive, -1 for negative, and 0 for zero. | | [`radians`](/reference/aql/math-functions#radians) | `radians(degrees)` | Converts degrees to radians. | | [`pi`](/reference/aql/math-functions#pi) | `pi()` | Returns the mathematical constant π (pi). | | [`acos`](/reference/aql/math-functions#acos) | `acos(number)` | Returns the arccosine (inverse cosine) of a number. | | [`asin`](/reference/aql/math-functions#asin) | `asin(number)` | Returns the arcsine (inverse sine) of a number. | | [`atan`](/reference/aql/math-functions#atan) | `atan(number)` | Returns the arctangent (inverse tangent) of a number. | | [`atan2`](/reference/aql/math-functions#atan2) | `atan2(y, x)` | Returns the two-argument arctangent, which computes the angle between the positive x-axis and the point given by the coordinates (x, y). | | [`cos`](/reference/aql/math-functions#cos) | `cos(number)` | Returns the cosine of a number. | | [`sin`](/reference/aql/math-functions#sin) | `sin(number)` | Returns the sine of a number. | | [`tan`](/reference/aql/math-functions#tan) | `tan(number)` | Returns the tangent of a number. | | [`cot`](/reference/aql/math-functions#cot) | `cot(number)` | Returns the cotangent of a number. | ## Miscellaneous Function | Function | Syntax | Purpose | | --- | --- | --- | | [`cast`](/reference/aql/miscellaneous-functions#cast) | `cast(expr, type)` | Returns the input value casted to the specified data type. | | [`concat`](/reference/aql/text-functions#concat) | `concat(text, [text ...])` | Returns the concatenated string of multiple strings. | | [`find`](/reference/aql/text-functions#find) | `find(text, substring)` | Returns the 1-based index of the first occurrence of a substring within a text string. | | [`left`](/reference/aql/text-functions#left) | `left(text, length)` | Returns the leftmost characters of a text string, up to the specified length. | | [`right`](/reference/aql/text-functions#right) | `right(text, length)` | Returns the rightmost characters of a text string, up to the specified length. | | [`mid`](/reference/aql/text-functions#mid) | `mid(text, start, length)` | Extracts a substring of a specified length from a text string, starting at a given position (1-based). | | [`len`](/reference/aql/text-functions#len) | `len(text)` | Returns the length of a text string (number of characters). | | [`lower`](/reference/aql/text-functions#lower) | `lower(text)` | Converts a text string to lowercase. | | [`upper`](/reference/aql/text-functions#upper) | `upper(text)` | Converts a text string to uppercase. | | [`trim`](/reference/aql/text-functions#trim) | `trim(text)` | Removes leading and trailing whitespace from a text string. | | [`ltrim`](/reference/aql/text-functions#ltrim) | `ltrim(text)` | Removes leading whitespace from a text string. | | [`rtrim`](/reference/aql/text-functions#rtrim) | `rtrim(text)` | Removes trailing whitespace from a text string. | | [`lpad`](/reference/aql/text-functions#lpad) | `lpad(text, length, pad_string)` | Pads the left side of a text string with a specified pad string until it reaches the specified length. | | [`rpad`](/reference/aql/text-functions#rpad) | `rpad(text, length, pad_string)` | Pads the right side of a text string with a specified pad string until it reaches the specified length. | | [`replace`](/reference/aql/text-functions#replace) | `replace(text, old_substring, new_substring)` | Replaces all occurrences of a substring within a text string with a new substring. | | [`split_part`](/reference/aql/text-functions#split_part) | `split_part(text, delimiter, part_number)` | Splits a text string into parts based on a delimiter and returns the specified part (1-based). | | [`regexp_extract`](/reference/aql/text-functions#regexp_extract) | `regexp_extract(text, regex, [occurrence], [group: _group], [flags: _flags])` | Extracts a substring from a text string that matches a regular expression pattern. | | [`regexp_like`](/reference/aql/text-functions#regexp_like) | `regexp_like(text, regex, [flags: _flags])` | Checks if a text string matches a regular expression pattern. | | [`regexp_replace`](/reference/aql/text-functions#regexp_replace) | `regexp_replace(text, regex, substitute, [flags: _flags])` | Replaces substrings in a text that match a regular expression pattern with a specified replacement text. | | [`is_at_level`](/reference/aql/miscellaneous-functions#is_at_level) | `is_at_level(dimension)` | Returns true if the specified dimension is active in the Level of Detail (LoD) context, else false. | ## AI Function | Function | Syntax | Purpose | | --- | --- | --- | | [`ai_complete`](/reference/aql/ai-functions#ai_complete) | `ai_complete(model, prompt)` | Queries an AI model with a text prompt and returns the generated response. | | [`ai_similarity`](/reference/aql/ai-functions#ai_similarity) | `ai_similarity(text1, text2)` | Calculates the semantic similarity between two text strings. | | [`ai_classify`](/reference/aql/ai-functions#ai_classify) | `ai_classify(text, category1, category2, ...categories)` | Classifies text into one of the provided categories using AI. | | [`ai_summarize`](/reference/aql/ai-functions#ai_summarize) | `ai_summarize(content)` | Generates a concise summary of the provided text content using AI. | ## SQL Passthrough Function | Function | Syntax | Purpose | | --- | --- | --- | | [`sql_text`](/reference/aql/sql-passthrough-functions#sql_text) | `sql_text('FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL function that returns a text/string value. | | [`sql_number`](/reference/aql/sql-passthrough-functions#sql_number) | `sql_number('FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL function that returns a numeric value. | | [`sql_datetime`](/reference/aql/sql-passthrough-functions#sql_datetime) | `sql_datetime('FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL function that returns a datetime value. | | [`sql_date`](/reference/aql/sql-passthrough-functions#sql_date) | `sql_date('FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL function that returns a date value. | | [`sql_truefalse`](/reference/aql/sql-passthrough-functions#sql_truefalse) | `sql_truefalse('FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL function that returns a boolean/truefalse value. | | [`agg_text`](/reference/aql/sql-passthrough-functions#agg_text) | `agg_text(table, 'FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL aggregate function that returns a text/string value. | | [`agg_number`](/reference/aql/sql-passthrough-functions#agg_number) | `agg_number(table, 'FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL aggregate function that returns a numeric value. | | [`agg_datetime`](/reference/aql/sql-passthrough-functions#agg_datetime) | `agg_datetime(table, 'FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL aggregate function that returns a datetime value. | | [`agg_date`](/reference/aql/sql-passthrough-functions#agg_date) | `agg_date(table, 'FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL aggregate function that returns a date value. | | [`agg_truefalse`](/reference/aql/sql-passthrough-functions#agg_truefalse) | `agg_truefalse(table, 'FUNCTION_NAME', param1, param2, ...)` | Calls a native SQL aggregate function that returns a boolean/truefalse value. | --- ## AQL Cheatsheet - Operators ## Text Operators ### Description A text value, represented as a string of characters. The "text" type can include any characters, including letters, numbers, symbols, and whitespace. ### Tags Operator, Condition ### Operators #### `==` ; `is` Equal to ```tsx title="Check if product name is Dandelion" products.name == 'Dandelion' products.name is 'Dandelion' ``` #### `!=` ; `is not` Not equal to ```tsx title="Check if product name is not Rock" products.name != 'Rock' products.name is not 'Rock' ``` #### `like` Match the pattern specified ```tsx title="Check if product name ends with Dan" products.name like '%Dan' ``` #### `not like` Not match the pattern specified ```tsx title="Check if product name does not end with Dan" products.name not like '%Dan' ``` #### `ilike` Match the pattern specified, case insensitive ```tsx title="Check if product name ends with dan, case insensitive" products.name ilike '%dan' ``` #### `not ilike` Match the pattern specified, case insensitive ```tsx title="Check if product name does not end with dan, case insensitive" products.name not ilike '%dan' ``` #### `is null` Include if the value is null ```tsx title="Check if product name is null" products.name is null ``` #### `is not null` Include if the value is not null ```tsx title="Check if product name is not null" products.name is not null ``` ### Learn more [Text Operators (AQL Doc)](/reference/aql/operator#text) ## List Operators ### Description Check if the field value is in the list. ### Tags Operator, Condition ### Operators #### `in` Include if the value is in the list ```tsx title="Check if product name is in the list" products.name in ['Dandelion', 'Rock'] ``` #### `not in` Include if the value is not in the list ```tsx title="Check if product name is not in the list" products.name not in ['Dandelion', 'Rock'] ``` ### Learn more [List Operators (AQL Doc)](/reference/aql/operator#list) ## Truefalse Operators ### Description A boolean value, represented as either "true" or "false". This is commonly used to represent logical values. For example, it can be used to represent the result of a comparison, the status of a switch, or the answer to a yes/no question. ### Tags Operator, Condition ### Operators #### `is` Equal to ```tsx title="Check if an order is paid" orders.is_paid is true ``` #### `is not` Not equal to ```tsx title="Check if an order is not paid" orders.is_paid is not true ``` #### `is null` Include if null ```tsx title="Check if the order payment status is unknown" orders.is_paid is null ``` #### `is not null` Include if not null ```tsx title="Check if the order payment status is known" orders.is_paid is not null ``` ### Learn more [Truefalse Operators (AQL Doc)](/reference/aql/operator#truefalse) ## Number Operators ### Description A numeric value, represented as an numeric, integer, float, or double. It can be used as dimension or measure type. The number type can include positive and negative values, as well as decimal points. ### Tags Operator, Condition ### Operators #### `==` ; `is` Equal to ```tsx title="Check if an order item discount is 50%" order_items.discount == 0.5 order_items.discount is 0.5 ``` #### `!=` ; `is not` Not equal to ```tsx title="Check if an order item discount is not 100%" order_items.discount != 1 order_items.discount is not 1 ``` #### `>` Greater than ```tsx title="Check if an order item discount is greater than 50%" order_items.discount > 0.5 ``` #### `>=` Greater than or equal to ```tsx title="Check if an order item discount is at least 50%" order_items.discount >= 0.5 ``` #### `<` Less than ```tsx title="Check if an order item discount is less than 50%" order_items.discount < 0.5 ``` #### `<=` Less than or equal to ```tsx title="Check if an order item discount is at most 50%" order_items.discount <= 0.5 ``` #### `is null` Include if null ```tsx title="Check if an order item discount is unknown" order_items.discount is null ``` #### `is not null` Include if not null ```tsx title="Check if an order item discount is known" order_items.discount is not null ``` #### `+` Add two numeric values ```tsx title="Add product cost and profit" products.cost + products.profit ``` #### `-` Subtract two numeric values ```tsx title="Subtract product cost from revenue" products.revenue - products.cost ``` #### `*` Multiply two numeric values ```tsx title="Multiply order quantity and product price" orders.quantity * products.price ``` #### `/` Divide two numeric values ```tsx title="Divide product profit by product cost" products.profit / products.cost ``` ### Learn more [Number Operators (AQL Doc)](/reference/aql/operator#number) ## Datetime Operators ### Description Right hand side of datetime operator takes [a datetime scalar type](/reference/aml/date-format) as input and always starts with @ token. Datetimes can be expressed in a fully supported format as @YYYY-MM-DD HH:MM:SS, in shorter variations like @YYYY-MM, or a relative datetime (relative to the current real world time) like @(last 7 days). ### Tags Operator, Condition ### Operators #### `==` Include data that equal to an absolute timestamp ```tsx title="Check if an order is created at 2022-01-01 00:00:00" orders.created_at == @2022 ``` ```tsx title="Check if an order is created at the first timestamp of the last 7 days" orders.created_at == @(last 7 days) ``` #### `is` ; `match` ; `matches` Include data that are in a time period ```tsx title="Check if an order is created in the period of the year 2022" orders.created_at is @2022 ``` ```tsx title="Check if an order is created in the period of the last 7 days" orders.created_at match @(last 7 days) ``` #### `!=` Include data that do not equal to an absolute timestamp ```tsx title="Check if an order is not created at 2022-01-01 00:00:00" orders.created_at != @2022-01 ``` #### `is not` Include data that are not in a time period ```tsx title="Check if an order is not created in the period of 2022-01" orders.created_at is not @2022-01 ``` #### `<` Include data that are before a specific time period ```tsx title="Check if an order is created before the period of the year 2022" orders.created_at < @2022 ``` #### `<=` Include data that are before or in a specific time period ```tsx title="Check if an order is created before or in the period of the year 2022" orders.created_at <= @2022 ``` #### `>` Include data that are after a specific time period ```tsx title="Check if an order is created after yesterday" orders.created_at > @(yesterday) ``` #### `>=` Include data that are after or in a specific time period ```tsx title="Check if an order is created after or on yesterday" orders.created_at >= @(yesterday) ``` #### `is null` Include if the value is null ```tsx title="Check if an order creation day is unknown" orders.created_at is null ``` #### `is not null` Include if the value is not null ```tsx title="Check if an order creation day is known" orders.created_at is not null ``` #### `+` Add an interval to a datetime ```tsx title="Add 3 months to the creation day" orders.created_at + interval(3 months) ``` ```tsx title="Substract 1 month to the creation day" orders.created_at + interval(-1 month) ``` #### `-` Subtract an interval to a datetime ```tsx title="Subtract 3 months to the creation day" orders.created_at - interval(3 months) ``` ```tsx title="Add 1 month to the creation day" orders.created_at - interval(-1 month) ``` ### Learn more [Datetime Operators (AQL Doc)](/reference/aql/operator#datetime) --- ## AQL vs SQL ## Introduction *"SQL is the universal language of data. Why did Holistics have to create a new query language instead of just using SQL?"* SQL is powerful, universal, and battle-tested. It's the right tool for ad-hoc data exploration, data transformation, and building data pipelines. AQL compiles to SQL under the hood. It's designed to **complement SQL, not replace it**. But SQL was designed in the 1970s for *querying tables*, not for *defining and composing business metrics*. When analytics teams try to build a reusable metrics layer using SQL, they run into fundamental limitations that no amount of CTEs or window functions can fix. This document explains the **limitations of SQL for metrics-based analytics**, and how **AQL addresses those limitations**. ## SQL buries business intent in mechanical complexity SQL requires you to solve a series of mechanical puzzles (which tables to join, what keys to use, what to GROUP BY) before you can express the actual business question. The intent gets buried under plumbing. ### Joins, GROUP BY, and ORDER BY for every query Consider a straightforward business question: *"What is revenue by product category?"* ```sql SELECT c.name AS category, SUM(oi.quantity * p.price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN categories c ON p.category_id = c.id GROUP BY c.name ORDER BY revenue DESC ``` Out of 7 lines, only one (`SUM(oi.quantity * p.price)`) is actual business logic. The rest is mechanical plumbing: 2 JOINs, a GROUP BY, and an ORDER BY. And if someone later wants revenue by *merchant* instead of *category*, they need to rewrite the query with different joins and different GROUP BY, even though the business logic hasn't changed at all. ### CTEs for anything non-trivial The moment your analysis goes beyond a single aggregation, SQL pushes you into CTEs and subqueries. Here's what it takes to answer *"How does each month's sales compare to the average?"*: ```sql WITH monthly_sales AS ( SELECT DATE_TRUNC('month', order_date) as month, SUM(amount) as sales FROM orders GROUP BY 1 ), avg_monthly AS ( SELECT AVG(sales) as avg_sales FROM monthly_sales ) SELECT month, sales, sales - (SELECT avg_sales FROM avg_monthly) as variance FROM monthly_sales ``` The business question is one sentence. The SQL is 14 lines across two CTEs. ## AQL expresses analytics intent directly AQL separates the *what* from the *how*. Relationships between tables are defined once in your dataset. Queries focus purely on what you want to know. Here's the same revenue-by-category question: ```aml // Relationships defined once in the dataset: no need to repeat in queries // relationship(order_items.product_id > products.id, true) // relationship(products.category_id > categories.id, true) metric revenue { definition: @aql sum(order_items, order_items.quantity * products.price) ;; } ``` No joins. The same `revenue` metric works whether you slice by category, merchant, country, or time period. Just change the dimension in your exploration. The metric definition stays the same. And the monthly variance? Three named, reusable components instead of a monolithic CTE query: ```aml metric sales { definition: @aql sum(orders.amount) ;; } metric avg_monthly_sales { definition: @aql orders | group(orders.created_at | month()) | select(sales) | avg(sales) ;; } metric sales_variance { definition: @aql sales - avg_monthly_sales ;; } ``` Each metric has a name, a clear definition, and can be reused independently. The pipe operator (`|`) chains operations so the logic reads like a sentence: *"take orders, group by month, calculate sales, then average them."* ## SQL metrics are trapped inside queries In SQL, a metric like "revenue" is just an expression (`SUM(amount)`) embedded inside a query. It has no name you can reference later, no way to compose it with other metrics, no single source of truth. When another team member needs a variant, they copy-paste and modify. ### The copy-paste problem ```sql -- Dashboard query 1: Total revenue SELECT SUM(oi.quantity * p.price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.id; -- Dashboard query 2: Delivered revenue (copy-paste + WHERE) SELECT SUM(oi.quantity * p.price) AS delivered_revenue FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN orders o ON oi.order_id = o.id WHERE o.status = 'delivered'; -- Dashboard query 3: Revenue per order (copy-paste + different aggregation) SELECT SUM(oi.quantity * p.price) / COUNT(DISTINCT oi.order_id) AS revenue_per_order FROM order_items oi JOIN products p ON oi.product_id = p.id; ``` The definition of "revenue" is now scattered across three queries. If the formula changes (say, you need to exclude refunds), you must hunt down and update every copy. Miss one, and your dashboards silently disagree with each other. ## AQL metrics are first-class, composable objects In AQL, metrics are named objects that can be referenced, filtered, and composed (just like functions in a programming language). ```aml // Single source of truth metric revenue { definition: @aql sum(order_items, order_items.quantity * products.price) ;; } // Filtered variants: references the original, not a copy metric delivered_revenue { definition: @aql revenue | where(orders.status == 'delivered') ;; } metric cancelled_revenue { definition: @aql revenue | where(orders.status == 'cancelled') ;; } // Derived metrics: composed from existing building blocks metric revenue_per_order { definition: @aql safe_divide(revenue, count(orders.id)) ;; } ``` When the definition of `revenue` changes, **all derived metrics update automatically**. This isn't string substitution. AQL understands the semantic structure. You build a library of metrics that grows more powerful over time, rather than a collection of isolated queries that drift apart. ## SQL lacks built-in analytics patterns Percent of total, year-over-year comparisons, and nested aggregations are bread-and-butter analytics operations. Yet in SQL, each one requires a verbose, error-prone pattern: window functions, self-joins, or deeply nested subqueries. These patterns are so common that they should be built-in primitives, not puzzles to solve from scratch every time. ### Percent of total *"What percentage of total revenue does each product contribute?"* ```sql SELECT p.name, SUM(oi.quantity * p.price) AS revenue, SUM(oi.quantity * p.price) * 100.0 / SUM(SUM(oi.quantity * p.price)) OVER () AS pct_of_total FROM order_items oi JOIN products p ON oi.product_id = p.id GROUP BY p.name ``` `SUM(SUM(...)) OVER ()`: a nested window function over an aggregate. Many analysts struggle to write this correctly, and it's nearly impossible to read at a glance. ### Year-over-year comparison *"What is the YoY revenue change by month?"* ```sql WITH monthly AS ( SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT curr.month, curr.revenue, prev.revenue AS prev_year_revenue, (curr.revenue - prev.revenue) * 100.0 / prev.revenue AS yoy_change FROM monthly curr LEFT JOIN monthly prev ON curr.month = prev.month + INTERVAL '1 year' ``` A self-join on a CTE with date arithmetic. Fragile and easy to get wrong. Want month-over-month too? Write it all again with a different interval. ### Nested aggregation *"What is the average number of new customers per month?"* ```sql SELECT AVG(monthly_count) FROM ( SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS monthly_count FROM users GROUP BY 1 ) sub ``` Even this relatively simple case requires a subquery. More complex nested aggregations quickly become deeply nested and unreadable. ## AQL has built-in analytical functions The same three questions in AQL: **Percent of total**: one line with `of_all()` ```aml metric pct_of_total { definition: @aql safe_divide(revenue * 100.0, revenue | of_all(products)) ;; } ``` `of_all(products)` is a declarative statement: *"calculate revenue ignoring the product dimension."* No window functions, no nested aggregates. **Year-over-year**: one function with `relative_period()` ```aml metric revenue_last_year { definition: @aql revenue | relative_period(orders.created_at, interval(-1 year)) ;; } metric yoy_change { definition: @aql safe_divide((revenue - revenue_last_year) * 100.0, revenue_last_year) ;; } ``` The same metric works at any time granularity: daily, monthly, quarterly. Want month-over-month? Just change `interval(-1 year)` to `interval(-1 month)`. **Nested aggregation**: a pipe chain that reads like a sentence ```aml metric avg_monthly_new_customers { definition: @aql users | group(users.created_at | month()) | select(monthly_count: count(users.id)) | avg(monthly_count) ;; } ``` *"Take users, group by month, count them, then average."* Two levels of aggregation in four lines, no subqueries. ## Conclusion SQL remains the right tool for ad-hoc data exploration and data transformation. But when it comes to building a scalable analytics layer (where metrics need to be defined once, reused everywhere, and composed into increasingly sophisticated analyses), SQL's limitations become structural barriers, not just inconveniences. We designed AQL to systematically address every limitation of SQL for metrics-based analytics. Where SQL falls short because it was designed for *querying tables*, AQL succeeds because it was designed for *defining and composing metrics*, built on three foundational principles: - **Intent over mechanics**: express analytics questions directly without specifying joins, GROUP BY, or query structure. Relationships are defined once; queries focus on *what* you want to know. - **Metrics as first-class objects**: metrics are named, reusable, composable building blocks with a single source of truth, not disposable expressions scattered across queries. - **Built-in analytics primitives**: common patterns like percent of total, time comparisons, and nested aggregations are single functions, not verbose SQL workarounds. Your analysts deserve something better than copy-pasting SQL. ## tldr: AQL vs SQL | **Category** | **SQL** | **AQL** | |---|---|---| | **Query Construction** | Must specify joins, GROUP BY, ORDER BY for every query. Business intent buried in mechanical plumbing. | Relationships defined once. Queries express only what you want to know. | | **Metric Reusability** | Metrics are anonymous expressions embedded in queries. Must copy-paste and modify for each use. | Metrics are named, first-class objects. Define once, reference and compose freely. | | **Metric Composition** | No native way to build metrics from other metrics. Each variant is a standalone query. | Pipe operator enables chaining: `revenue \| where(...)`, `revenue \| of_all(...)`. New metrics compose from existing ones. | | **Percent of Total** | Requires nested window functions: `SUM(SUM(...)) OVER ()` | Single function: `revenue \| of_all(dimension)` | | **Time Comparisons** | Self-joins on CTEs with date arithmetic. Must rewrite for each comparison period. | Single function: `revenue \| relative_period(date, interval(-1 year))` | | **Nested Aggregations** | Subqueries or CTEs for each aggregation level. Deep nesting becomes unreadable. | Pipe chain: `model \| group(...) \| select(...) \| aggregate()` | | **Single Source of Truth** | Metric definitions scattered across N queries. Changes require finding and updating all copies. | One definition; all derived metrics update automatically. | **Bottom Line:** SQL is a table query language trying to serve as a metrics layer. AQL is a metrics-first language designed specifically for analytics composition. --- ## AQL Best Practices This page collects practical advice for writing AQL well. Unlike [AQL Validation Rules](/as-code/aql/validation-rules) (which lists hard constraints), these are recommendations. Following them keeps your semantic layer maintainable as it grows. ## Model design **Define a primary key on every model** Not strictly required, but strongly recommended. A primary key lets AQL determine the grain of your data, drop unnecessary `GROUP BY` clauses, and support [Single Model Conditions](/reference/aql/where#single-model-condition). Without one, queries are less efficient and more prone to fan-out. ```aml // ✅ Good Model orders { dimension id { type: 'number' primary_key: true } } // ⚠️ Works, but no grain information Model orders { dimension id { type: 'number' } } ``` ## Metric design **Don't reference dimensions without aggregating them** A metric that returns a raw dimension value is fragile. It only works when that dimension is also in the explore, and end users typically expect a metric to be a single number. Always aggregate. ```aml // ✅ Good metric total_revenue { definition: @aql sum(orders.amount) ;; } // ❌ Bad: returns raw values, breaks when dimension isn't grouped metric total_revenue { definition: @aql orders.amount ;; } ``` **Don't hard-code time periods or dimensions into metric definitions** Metrics should adapt to whatever grouping the end user chooses. Hard-coding a `group(... | month())` inside the metric turns it into a *table* rather than a scalar and locks downstream reports into that grain. Use grouping inside a metric only when the grouping is essential to the calculation itself (e.g., nested aggregation). ```aml // ❌ Bad: returns a table of monthly values, not a single number metric monthly_sales { definition: @aql orders | group(orders.created_at | month()) | select(sum(orders.amount)) ;; } // ✅ Good: let the user pick the time grain in the report metric sales { definition: @aql sum(orders.amount) ;; } // ✅ Good: grouping is essential because we want avg-of-monthly-totals metric avg_monthly_sales { definition: @aql orders | group(orders.created_at | month()) | select(sum(orders.amount)) | avg() ;; } ``` **Reuse metrics instead of duplicating logic** Build complex metrics from simpler ones. One source of truth means one place to fix bugs. ```aml // ✅ Good metric revenue { definition: @aql sum(order_items.amount) ;; } metric revenue_last_month { definition: @aql revenue | relative_period(orders.created_at, interval(-1 month)) ;; } metric revenue_vs_last_month { definition: @aql revenue - revenue_last_month ;; } // ❌ Bad: duplicating the base logic metric revenue_vs_last_month { definition: @aql sum(order_items.amount) - (sum(order_items.amount) | relative_period(orders.created_at, interval(-1 month))) ;; } ``` **Document complex logic** Add a `description` for any metric whose definition isn't self-explanatory. Future you, and every downstream analyst, will thank you. ```aml metric customer_churn_rate { label: 'Customer Churn Rate' description: 'Percentage of customers who cancelled in the period' definition: @aql safe_divide( (count(users.id) | where(users.status == 'churned')) * 100, count(users.id) | where(users.created_at < first(users.period_param)) ) ;; } ``` ## Data quality **Use `safe_divide` instead of `/` for ratios** `safe_divide` returns null when the denominator is zero. Plain `/` raises a runtime error. ```aml // ✅ Good metric average_order_value { definition: @aql safe_divide(revenue, order_count) ;; } // ❌ Bad: explodes when order_count is 0 metric average_order_value { definition: @aql revenue / order_count ;; } ``` **Wrap counts in `coalesce` when "no rows matched" should mean zero** Unlike SQL, AQL returns null (not 0) when a filter matches no rows. If your visualization expects 0, use `coalesce`. ```aml // ✅ Good: returns 0 when no completed orders exist metric completed_orders { definition: @aql coalesce( count(orders.id) | where(orders.status == 'completed'), 0 ) ;; } // ❌ Bad: returns null, which charts handle inconsistently metric completed_orders { definition: @aql count(orders.id) | where(orders.status == 'completed') ;; } ``` ## Performance **Use PreAggregates for frequently accessed or expensive metrics** PreAggregates pre-compute and store aggregated data, dramatically reducing query latency. They're especially valuable for dashboard metrics over large fact tables, or for metrics that involve complex multi-step calculations. Start with [Aggregate Awareness](/docs/aggregate-awareness/quick-start) for the concept, then [Building Multiple PreAggregates](/as-code/aml/use-cases/build-multiple-pre-aggregates) for the patterns. ## Common pitfalls ### Don't pick a wrong source table when one is needed In most cases, AQL infers the source table from the expression and you don't need to specify it. When you *do* specify one (because the expression mixes models), make sure it matches the grain you're measuring. ```aml // ✅ Good: let AQL infer metric order_count { definition: @aql count(orders.id) ;; } // ✅ Good: explicit because fields span two models metric revenue { definition: @aql sum(order_items, order_items.quantity * products.price) ;; } // ❌ Bad: explicit source contradicts what's being counted metric order_count { definition: @aql count(order_items, orders.id) ;; // Counts order_items rows, so an order with 5 items is counted 5 times } ``` Rule of thumb: let AQL infer unless fields come from multiple models. When you do specify, the source table is the grain you're aggregating over. ### Don't assume null behaves like SQL AQL returns null (not 0) when a filter matches nothing. Plan for that with `coalesce` or `safe_divide` where it matters: see [Data quality](#data-quality) above. ## See also - [AQL Validation Rules](/as-code/aql/validation-rules): hard constraints - [Order of Operations](/as-code/aql/order-of-operations): when filters and metrics evaluate - [Cookbook](/as-code/aql/cookbook/metrics-by-example): worked examples of the patterns above --- ## Cohort Retention :::info Options to build a retention heatmap in Holistics We support multiple ways to build a retention heatmap. Here's a quick comparison to help you pick the right one: | Option | Custom colors | Auto color scale* | Maintenance | |--------|---------------|-------------------|-------------| | [Built-in, legacy Retention Heatmap](/docs/charts/cohort-retention) | No | Yes | Low -- built-in visualization | | **Pivot Table** with conditional formatting *(this page, recommended)* | Yes | Coming soon | Low -- built-in visualization | | [Dynamic Content Block](/docs/charts/dynamic-content-blocks/gallery/retention-heatmap) | Yes | Yes | Higher -- you maintain the HTML/CSS yourself | **(*) Auto color scale**: Color intensity adjusts automatically to the current data range. Without it, a heatmap built for a max of 1,000 will look washed out when a user's data only goes up to 80, or when a time filter reduces the range. ::: ## Introduction **Cohort Retention Analysis** analyzes the continued engagement of specific user groups over time, helping businesses understand and improve customer loyalty and long-term user activity. We will provide a step-by-step walkthrough on how to build a report in Holistics that demonstrates the classic cohort retention. ## Setup In order to gain insights into the lifetime value of groups of users who were acquired for each particular period of time, our objective is to define and analyze Acquisition Cohorts. For this tutorial, we will be using an e-commerce dataset that consists of two models: `orders` and `users`. Before diving into the implementation, let's take a quick look at our dataset setup. ```aml // orders.model.aml Model orders { ... dimension id {...} dimension user_id {} dimension created_at {} } // users.model.aml Model users { ... dimension id {...} dimension name {...} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [orders, users] relationships: [ relationship(orders.user_id > users.id, true) ] // Define the Cohort dimension acquisition_month_cohort { model: users type: 'date' label: 'Acquisition Month Cohort' // highlight-next-line definition: @aql min(orders.created_at | month()) | dimensionalize(users.id);; } // Calculate the Total Users are in this Cohort metric total_users { label: "Untitled metric" type: "number" // highlight-next-line definition: @aql count(users.id);; } // Define the Month Number dimension month_no { model: orders label: 'Month Number' type: 'number' // highlight-next-line definition: @aql date_diff('month', orders.cohort_month, orders.created_at | month());; } // How many users of the cohort are still active in consecutive months metric retention { label: 'Retention' type: 'number' // highlight-next-line definition: @aql (total_users*1.0) / (total_users | of_all(orders.month_no));; } } ``` _Sample data_ ## High-level flow 1. **Define Acquisition Cohort:** Determine the period and timeframe when each customer was acquired. This period could be daily, monthly, yearly, or any other suitable duration based on your business needs. 2. **Define the metric:** Define the metric you want to observe for each cohort 3. **Define the retention logic:** How many users of the cohort are still active in subsequent months ## Implementation
### 1. Define Acquisition Cohort Let’s define the acquisition cohort as the month that users made their first purchase. Since 1 user can make multiple purchases on different days, to find each user's first order date, obtain the earliest order created date (in the `Orders` model) and dimensionalize it by associating it with the User ID in the `Users` model. This can be done by using `min()` function and [`dimensionalize()`](/reference/aql/dimensionalize). ```aml Dataset e_commerce { (...) dimension acquisition_month_cohort { model: users type: 'date' label: 'Acquisition Month Cohort' // highlight-next-line definition: @aql min(orders.created_at | month()) | dimensionalize(users.id);; } } ``` ![Acquisition Cohort](https://cdn.holistics.io/product/aql-demo-dimensionalized-acquisition-20240724-157.png) ### 2. Define the Metrics (or the Cohort Size) You will then be able to find out how many users in the Acquisition Cohort. First, calculate the `Total Number of Users` by simply using `count()` function. ```aml Dataset e_commerce { (...) dimension acquisition_month_cohort {...} metric total_users { label: 'Total Users' type: 'number' // highlight-next-line definition: @aql count(users.id);; } } ``` And then, to find out how many users are in this Acquisition Cohort, you will use the combination of **dimension** `Acquisition Month Cohort` with the **metric** `Total Users` ![Total Users Cohort](https://cdn.holistics.io/product/aql-aql-total-users-cohort-20240722-151.png) ### 3. Define the retention logic :::info **What is considered retention**: * If first-time user A made an order on Week 1, and returns to buy more stuffs the next weeks, she is a returned user. * If user B also made an order on Week 1 and does not return the following weeks, she's a bounced user, that basically means you lose her as a user. ::: First, calculate the **Month since Acquisition** by using the [`date_diff()`](/reference/aql/time-intelligence-functions#date_diff) function. This dimension is necessary to determine how many users from a specific acquisition cohort return in subsequent months. ```aml Dataset e_commerce { (...) dimension acquisition_month_cohort {...} metric total_users {...} dimension month_no { model: orders label: 'Month Number' type: 'number' // highlight-next-line definition: @aql date_diff('month', users.acquisition_month_cohort, (orders.created_at | month()));; } } ``` This can be best illustrated by using Pivot Table where - The **Row** is the Dimension `Acquisition Month Cohort` - The **Column** is the Dimension `Month Number` - The **Values** is the Metric `Total Users` ![Retained Users](https://cdn.holistics.io/product/aql-aql-retained-users-20240723-153.png) `Month Number 0` represents the `Cohort Size`, which calculates the total number of users who made their first purchase in the `Acquisition Month Cohort`. `Month Numbers 1, 2, and 3` show how many users from that cohort continue to make purchases in the `subsequent months 1, 2, and 3`, respectively. Finally, create a `retention` metric to measure the [percentage of total](/as-code/aql/cookbook/aql-percent-of-total) users retained in each _acquisition cohort_ for each month. ```aml Dataset e_commerce { (...) dimension acquisition_month_cohort {...} metric total_users {...} dimension month_no {...} metric retention { label: 'Retention' type: 'number' // highlight-next-line definition: @aql (total_users*1.0) / (total_users | of_all(orders.month_no));; } } ``` ![Percent Cohort Retention](https://cdn.holistics.io/product/aql-pct-retention-20240723-155.png) --- We have covered all the foundational concepts required to calculate metrics for the acquisition cohort, and you are now equipped to create even more powerful cohort analysis reports to present to your stakeholders in Holistics. --- ## Cumulative Metrics ## Introduction In business analytics, aside from typical aggregation metrics, cumulative metric is an indispensable tool to understand the performance of your business over time. In this guide, we will try to answer the following questions about a fictional ecommerce business: - How does the company’s cumulative revenue look like over months? - How does the cumulative average order value of each category look like? We will work with the familiar `ecommerce` dataset, and use the `running_total()` function to answer these questions. ## Example 1: Cumulative Total Revenue over Months ### Setup In this example, the `ecommerce` dataset will contain only the `orders`, `order_items`, and `products` models. ```aml // orders.model.aml Model orders { ... dimension id {...} dimension user_id {...} dimension created_at {...} dimension order_month { label: 'Created At Month' type: 'date' definition: @aql date_trunc(orders.created_at, 'month') ;; } } // order_items.model.aml Model order_items.model.aml { ... dimension order_id {...} dimension product_id {...} dimension quantity {...} } // products.model.aml Model products { ... dimension id {...} dimension price {...} dimension category_id {...} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [orders, order_items, products] relationships: [ relationship(order_items.order_id > orders.id, true), relationship(order_items.product_id > products.id, true) ] metric revenue { label: 'Revenue' type: 'number' hidden: false definition: @aql order_items | sum(order_items.quantity * products.price);; } metric cumulative_revenue { label: 'Cumulative Revenue by Month' type: 'number' definition: @aql running_total(revenue, orders.created_at_month) ;; } } ``` ### High-level flow 1. **Create the normal aggregation metric:** In the `ecommerce` dataset, we create a simple `revenue` metric that calculate order value by summing over `order_items.quantity` multiplied by `products.price`. 2. **Prepare the running dimension:** we will create a `created_at_month` dimension in the `orders` model. This will be used in the final running metric. 3. **Create the cumulative metric:** We use the `running_total()` to run the `revenue` metric along the dimension. ### Implementation #### 1. Create the normal sum metric In the definition file of the `ecommerce` dataset, we can easily create a `revenue` metric that combines fields from two different models (`order_items` and `products`) without the need to pre-join them: ```aml Dataset ecommerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } } ``` #### 2. Prepare the running dimension When we first created the `orders` table model, Holistics automatically scanned the table and pre-create model fields representing the physical table columns. We already have `created_at` as the time dimension at the lowest grain (seconds), but we want a time dimension with a coarser grain (month). We can use the AQL function `date_trunc()` to truncate the timestamp values to month grain: ```aml Model orders { type: 'table' label: 'Orders' ... dimension created_at { label: 'Created At' type: 'datetime' hidden: false definition: @sql {{ #SOURCE.created_at }};; } dimension created_at_month { label: 'Created At Month' type: 'datetime' definition: @aql date_trunc(orders.created_at, 'month') ;; } } ``` #### 3. Create the cumulative metric Finally, we can use the `running_total()` function to create a Cumulative Revenue metric that will gradually add up revenue after each month: ```aml Dataset ecommerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } metric cumulative_revenue { label: 'Cumulative Revenue by Month' type: 'number' definition: @aql running_total(revenue, orders.created_at_month) ;; } } ``` ## Example 2: Cumulative Average Order Value over Months of each Category ### Setup Sometimes, simply looking at how a metric change over time is not enough - you may want to see how the metric differs between different groups. For example, you may ask: “How does the monthly cumulative average order value differs between categories?” In this example, we will add the `categories` model to the `ecommerce` dataset. ```aml // orders.model.aml Model orders { ... dimension id {...} dimension user_id {} dimension created_at {} // note that metrics defined in model are called measures measure total_orders { label: 'Total Orders' type: 'number' definition: @aql count(orders.id) ;; } } // order_items.model.aml Model order_items.model.aml { ... dimension order_id {...} dimension product_id {...} dimension quantity {...} } // products.model.aml Model products { ... dimension id {...} dimension price {...} dimension category_id {...} } // categories.model.aml Model categories { ... dimension id {...} dimension name {...} dimension parent_id {...} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [orders, order_items, products, categories] relationships: [ relationship(order_items.order_id > orders.id, true), relationship(order_items.product_id > products.id, true), relationship(products.category_id > categories.id, true) ] metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } metric average_order_value { label: 'AOV' type: 'number' definition: @aql safe_divide(revenue, orders.total_orders) ;; } metric cumulative_aov { label: 'Cumulative AOV' type: 'number' definition: @aql running_total(average_order_value, orders.created_at_month) ;; } } ``` ### High-level Flow 1. **Create the normal average metric:** In the `ecommerce` dataset, we create a simple `average_order_value` by dividing the previously created `revenue` metric with the `orders.count_orders` metric. 2. **Create the cumulative metric:** We use the `running_total()` to run the `average_order_value` metric along the `orders.created_at_month` dimension. In the final analysis, we will also include the `categories.name` dimension to see the effect. ### Implementation #### 1. Create the normal average metric In the `orders` model, we create a `total_orders` metric that count all the orders: ```aml Model orders { ... // note that metrics defined in model are called measures measure total_orders { label: 'Total Orders' type: 'number' definition: @aql count(orders.id) ;; } } ``` To create the `average_order_values` metric, we can actually reuse the `revenue` metrics we created earlier instead of writing the full calculations again: ```aml Dataset ecommerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } metric average_order_value { label: 'AOV' type: 'number' definition: @aql safe_divide(revenue, orders.total_orders) ;; } } ``` #### 2. Create the cumulative metric Similar to how we created the `cumulative_revenue` metric, we use the `running_total()` function to run the `average_order_value` metric along `orders.created_at_month` dimension. ```aml Dataset ecommerce { ... metric average_order_value { label: 'AOV' type: 'number' definition: @aql safe_divide(revenue, orders.total_orders) ;; } metric cumulative_aov { label: 'Cumulative AOV' type: 'number' definition: @aql running_total(average_order_value, orders.created_at_month) ;; } } ``` Since the `average_order_value` metric is only run against the `created_at_month` dimension, the `cumulative_aov` metric is partitioned by the `categories.name` dimension, which is what we wanted: Producing the cumulative average this way is similar to dividing the `cumulative_revenue` metric by a `cumulative_orders` metric: --- ## Customer Order Frequency ## Introduction This guide builds a **cumulative order-frequency distribution**: a chart that answers "what percent of our orders contain ≤ N items?" The X-axis is quantity-per-order, the Y-axis is order count, and a secondary line plots the running percentage. Along the way, it walks through four reusable AQL building blocks: `dimensionalize`, percent-of-total, nested aggregation, and `running_total`, all chained on top of each other. ## Video Tutorial ## Setup For this use case, we will work with `order_items` and `orders` tables from `e_commerce` datasets. `orders_items` has an N-1 relationship with orders ```aml // order_items.model.aml Model order_items { ... dimension id {...} dimension name {...} } // orders.model.aml Model orders { ... dimension id {...} dimension created_at {} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [order_items, orders] relationships: [ relationship(order_items.user_id > orders.id, true) ] dimension cohort_quantity_per_order { model: orders label: "Cohort Quantity per Order" type: "number" definition: @aql order_items | sum(order_items.quantity) | dimensionalize(orders.id) ;; } metric count_orders { label: "Count Orders" type: "number" definition: @aql count(orders.id);; } metric pot_orders { label: "% Orders" type: "number" definition: @aql (count_orders*1.0) / (count_orders | of_all(orders)) ;; } metric cumulative_percent_order_quantity { label: "Cumulative % Orders" type: "number" definition: @aql orders | group(cohort_quantity_per_order) | sum(pot_orders) | running_total(cohort_quantity_per_order) ;; } } ``` ## High-level flow 1. **Dimensionalize the metric**: We already have a `quantity` dimension in `order_items`, and we’ll extract the same dimension for `orders` without making any changes upstream. This dimension will serve as the X-axis later on. 2. **Create `Count Orders` metric**: This metric will be used for the Y-Axis bar 3. **Create percent of total metric**: This metric will be used to calculate cumulative percent. 4. **Create nested aggregation and cumulative metric** : Once we have `Percent of Total` metric, we can use the `running_total` function to compute `Cumulative %` by running it on `Quantity per Order` dimension. This metric will be used for the second Y-Axis line ## Implementation ### 1. Dimensionalize the metric Traditionally, in order to re-use an aggregation as a regular field within a model, the process requires pre-calculation in upstream modeling before bringing it to BI tool or either creating an additional Query Model at the BI layer. This constraint leads to a coupling between the BI and modeling layers. In AQL, you can leverage `dimensionalize` along with cross-model calculation capability directly at the BI layer to bypass that constraint. In the definition of `cohort_quantity_per_order` below, `dimensionalize` helps perform `sum(orders_item.quantity)` expression to the specific grain of `orders` , without being coerced to a scalar value like other metrics. Due to this reason, we’re allowed to materialize this expression as dimension ```aml Dataset e_commerce { (...) dimension cohort_quantity_per_order { model: orders label: "Cohort Quantity per Order" type: "number" definition: @aql order_items | sum(order_items.quantity) | dimensionalize(orders.id) ;; } } ``` ### 2. Create `Count Orders` metric This metric will be used for the Y-Axis bar to view the `Count Orders` of `Quantity per Order` ```aml Dataset e_commerce { (...) dimension cohort_quantity_per_order {...} metric count_orders { label: "Count Orders" type: "number" definition: @aql count(orders.id);; } } ``` ### 3. Create percent of total metric Even though we don’t use [Percent of Total](/as-code/aql/cookbook/aql-percent-of-total) directly for this use case, we still need this metric to define the `Cumulative % Orders` metric later on ```aml Dataset e_commerce { ... dimension cohort_quantity_per_order {...} metric count_orders {...} metric pot_orders { label: "% Orders" type: "number" definition: @aql (count_orders*1.0) / (count_orders | of_all(orders)) ;; } } ``` ### 4. Create cumulatively nested aggregation metric This metric will be used to draw a percentage line on our combination chart. We’ll utilize running_total to calculate sum of `% Orders` ```aml Dataset e_commerce { ... dimension cohort_quantity_per_order {...} metric count_orders {...} metric _pot_orders {...} metric cumulative_percent_order_quantity { label: "Cumulative % Orders" type: "number" definition: @aql orders | group(cohort_quantity_per_order) | sum(orders.pot_orders) | running_total(cohort_quantity_per_order) ;; } } ``` ### 5. Bring it together --- ## Moving Average Moving averages smooth out fluctuations in a time series so the underlying trend is easier to see. This page builds a 3-month moving average of monthly revenue two ways: with `trailing_period()` and with `window_avg()`. It explains when to pick which. Uses the [shared e-commerce schema](/as-code/aql/cookbook/metrics-by-example#shared-e-commerce-schema): `orders`, `order_items`, `products`. ## Which function to use? | | `trailing_period()` | `window_avg()` | |---|---|---| | **Operates on** | Calendar time | Table rows | | **Handles gaps in data** | Yes. uses the calendar, not the rows | No. Uses adjacent rows regardless of dates | | **Mixed time grains** (e.g. metric defined on day, viz on month) | Yes | No. viz grain must match | | **Affected by visual filters** | No. Computed before filters | Yes. Only sees visible rows | For example, with gaps in the data: | Month | Revenue | `trailing_period` (3M) | `window_avg` (3 rows) | |-------|---------|---------|----------| | Jan | 100 | 100 | 100 | | Mar | 100 | 50 | 100 | | Jun | 100 | 50 | 100 | `trailing_period` divides Mar's revenue by 3 because Feb and Apr are empty months. `window_avg` averages the three visible rows regardless of how far apart they are. Default to `trailing_period()` for time-based moving averages. Reach for `window_avg()` when you genuinely want row-based logic (e.g. last-3-orders rather than last-3-months). ## Setup Start with a plain revenue metric on the e-commerce dataset: ```aml Dataset e_commerce { metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } } ``` ## Option 1: `trailing_period()` `trailing_period()` re-aggregates `revenue` over a moving calendar window. Two things to decide: - **The time dimension**: the field that defines "which period a row belongs to." Use `orders.created_at` (no need to convert to month grain; the interval argument handles that). - **The window**: `interval(3 months)` for a 3-month trailing window. Because `trailing_period()` returns the *sum* over the window, divide by 3 to get the average: ```aml metric moving_avg_revenue_3m_tp { label: '3M Moving Avg. Revenue (trailing_period)' type: 'number' definition: @aql trailing_period(revenue, orders.created_at, interval(3 months)) / 3 ;; } ``` The first two rows look low because only 1 and 2 months of revenue exist within their windows. Filter them out in the visualization if that's confusing. ## Option 2: `window_avg()` `window_avg()` averages adjacent rows. You need to tell it: - **The frame**: `-2..0` means "from 2 rows back through the current row" (3 rows total). - **The order**: `orders.created_at | month()` so rows are ordered by month before the window is applied. ```aml metric moving_avg_revenue_3m_wa { label: '3M Moving Avg. Revenue (window_avg)' type: 'number' definition: @aql window_avg(revenue, -2..0, order: orders.created_at | month()) ;; } ``` The first two rows average 1 and 2 rows respectively. To NULL them out instead, gate on `window_count`: ```aml metric moving_avg_revenue_3m_wa { label: '3M Moving Avg. Revenue (window_avg)' type: 'number' definition: @aql case( when: window_count(revenue, -2..0, order: orders.created_at | month()) < 3, then: null, else: window_avg(revenue, -2..0, order: orders.created_at | month()) ) ;; } ``` ## Visualize Plot revenue and the moving average together. The smoothed line makes the trend much easier to read: ## See also - [`trailing_period`](/reference/aql/trailing_period): function reference - [`window_avg`](/reference/aql/window_avg): function reference - [Cumulative Metrics](/as-code/aql/cookbook/aql-cumulative-metrics): running totals and accumulation - [Period Comparison](/as-code/aql/cookbook/aql-period-comparison): YoY, QoQ, and CAGR --- ## Multi-Period KPI Summary ## Introduction Stakeholders often want a single summary view showing key **business metrics side-by-side across several time windows**, much like an executive scorecard or a KPI summary sheet in a spreadsheet. For example, you might want to see GMV, NMV, AOV, total orders, delivered orders, cancelled orders, and cancellation rate for the current week, month-to-date (MTD), MTD last year, year-to-date (YTD), and YTD last year, all in one compact table. This pattern works for any type of metric, whether it's a monetary sum like GMV, a count like Total Orders, a ratio like AOV, or a percentage like Cancelled Order Ratio. In this guide, we will walk you through how to build this kind of report in Holistics by combining a standalone query model, conditional metrics, and a [Pivot Table](/docs/charts/pivot-table). ## High-level flow Here is how the pieces fit together: 1. **Create a Time Type query model.** We build a standalone [query model](/docs/query-models) that generates one row per time period label (for e.g., `Week`, `MTD`, `MTD LY`, `YTD`, `YTD LY`). This model doesn't need any relationships to your fact tables. 2. **Define conditional metrics using `case()`.** For each metric (e.g., GMV), we write a `case()` expression that branches on the Time Type value. Each branch applies `where()` with a [natural time expression](/docs/datetimes/relative-dates) like `@(this week)` or `@(this year to today)`. For "last year" variants, we also apply `relative_period()` to shift the time window back by one year. 3. **Visualize in a Pivot Table.** We place the Time Type dimension in the **Columns** shelf and the metrics as **Values** (displayed as rows). This gives us the familiar spreadsheet-style layout. ## Setup For this guide, we use an `ecommerce` dataset with `orders`, `order_items`, `products`, `users`, and a `date_dim`. We also define several base metrics that we will later wrap with time-period logic. ```aml title="ecommerce.dataset.aml" Dataset ecommerce { models: [ orders, users, order_items, products, date_dim ] relationships: [ relationship(orders.user_id > users.id, true), relationship(order_items.order_id > orders.id, true), relationship(order_items.product_id > products.id, true), relationship(orders.created_date > date_dim.date_key, true) // No relationship for time_type: it's intentionally standalone ] // --- Base metrics (these are what we'll wrap with time-period logic) --- metric gmv { } metric nmv { } metric total_orders { } metric aov { } metric total_delivered_orders { } metric total_cancelled_orders { } metric cancelled_order_ratio { } } ``` With these base metrics in place, we are ready to create the time-period variants. ## Implementation ### Step 1: Create the Time Type query model The first step is to create a standalone query model **that generates the time period labels**. This model uses a simple SQL `VALUES` clause to produce one row per period. It doesn't need a relationship to your fact tables, as it serves as a virtual dimension table that provides column headers for the pivot. We prefix each label with a number (e.g., `1. Week`, `2. MTD`) to control the sort order of columns in the final pivot table. ```aml title="time_type.model.aml" Model time_type { type: 'query' label: 'Time Type' data_source_name: 'your_data_source' query: @sql SELECT time_type FROM (VALUES ('1. Week'), ('2. MTD'), ('3. MTD LY'), ('4. YTD'), ('5. YTD LY') ) AS t(time_type) ;; dimension time_type { label: 'Time Type' type: 'text' definition: @sql {{ #SOURCE.time_type }} ;; } } ``` :::info The `VALUES` syntax works on most modern databases (PostgreSQL, BigQuery, Snowflake, etc.). If your database doesn't support it, you can use `UNION ALL` instead: ```sql SELECT '1. Week' AS time_type UNION ALL SELECT '2. MTD' UNION ALL SELECT '3. MTD LY' UNION ALL SELECT '4. YTD' UNION ALL SELECT '5. YTD LY' ``` ::: ### Step 2: Add the Time Type model to the dataset Once the model is created, add `time_type` to the `models` list of your dataset. You don't need to define any relationship for it ```aml title="ecommerce.dataset.aml" Dataset ecommerce { ... models: [ orders, users, order_items, products, date_dim, // highlight-next-line time_type // standalone: no relationship needed ] relationships: [ // your existing relationships stay the same relationship(orders.user_id > users.id, true), relationship(order_items.order_id > orders.id, true), ... ] } ``` ### Step 3: Define conditional metrics Now we define metrics that compute different values depending on which time period is active. The `case()` function branches on `time_type.time_type`, and each branch uses `where()` with a [natural time expression](/docs/datetimes/relative-dates) to filter the date range. For "last year" variants, we pipe through `relative_period()` to shift the window back by one year. Here is what each branch does: | Branch | Time Expression | Meaning | |--------|----------------|---------| | `1. Week` | `@(this week)` | Current calendar week | | `2. MTD` | `@(this month to today)` | Start of current month through today | | `3. MTD LY` | `relative_period` + `@(this month to today)` | Same month-to-date window, shifted 1 year back | | `4. YTD` | `@(this year to today)` | Start of current year through today | | `5. YTD LY` | `relative_period` + `@(this year to today)` | Same year-to-date window, shifted 1 year back | Here are two examples (`GMV` and `Total Orders`), showing how the pattern works: ```aml title="ecommerce.dataset.aml" Dataset ecommerce { ... metric gmv_time_compare { label: 'GMV (time compare)' type: 'number' definition: @aql case( when: time_type.time_type == "1. Week" , then: gmv | where(date_dim.date_key match @(this week)) , when: time_type.time_type == "2. MTD" , then: gmv | where(date_dim.date_key match @(this month to today)) , when: time_type.time_type == "3. MTD LY" , then: gmv | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this month to today)) , when: time_type.time_type == "4. YTD" , then: gmv | where(date_dim.date_key match @(this year to today)) , when: time_type.time_type == "5. YTD LY" , then: gmv | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this year to today)) ) ;; format: "[$]#,###0" } metric total_orders_time_compare { label: 'Total Orders (time compare)' type: 'number' definition: @aql case( when: time_type.time_type == "1. Week" , then: total_orders | where(date_dim.date_key match @(this week)) , when: time_type.time_type == "2. MTD" , then: total_orders | where(date_dim.date_key match @(this month to today)) , when: time_type.time_type == "3. MTD LY" , then: total_orders | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this month to today)) , when: time_type.time_type == "4. YTD" , then: total_orders | where(date_dim.date_key match @(this year to today)) , when: time_type.time_type == "5. YTD LY" , then: total_orders | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this year to today)) ) ;; format: "#,###" } // ... same pattern for nmv, aov, total_delivered_orders, cancelled_order_ratio, etc. } ``` Notice how the two metrics are nearly identical, only the base metric name (`gmv` vs `total_orders`), the label, and the format differ. The `case()` structure and the `relative_period()` / `where()` logic are exactly the same. For "last year" branches, we apply `relative_period(date_dim.date_key, interval(-1 year))` first to shift the time window, then `where()` to filter the date range. As you add more metrics, this **copy-paste pattern quickly becomes hard to maintain**. #### Reduce repetition with AML Func Instead of duplicating this block for every metric, we can use an [AML Function](/reference/aml/func) with [string interpolation](/reference/aml/string-interpolation) to extract the pattern into a reusable function. Define the function in a separate file (or at the top of your dataset file): ```aml title="time_compare_func.aml" Func time_compare(metric_name: String, metric_label: String, format: String) { Metric { label: '${metric_label} (time compare)' type: 'number' definition: @aql case( when: time_type.time_type == "1. Week" , then: ${metric_name} | where(date_dim.date_key match @(this week)) , when: time_type.time_type == "2. MTD" , then: ${metric_name} | where(date_dim.date_key match @(this month to today)) , when: time_type.time_type == "3. MTD LY" , then: ${metric_name} | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this month to today)) , when: time_type.time_type == "4. YTD" , then: ${metric_name} | where(date_dim.date_key match @(this year to today)) , when: time_type.time_type == "5. YTD LY" , then: ${metric_name} | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this year to today)) ) ;; format: format } } ``` The function takes three parameters: - `metric_name`: the name of the base metric to wrap (e.g., `'gmv'`, `'total_orders'`) - `metric_label`: the display label (e.g., `'GMV'`, `'Total Orders'`) - `format`: the number format string Inside the function, `${metric_name}` and `${metric_label}` are injected into the metric definition via [string interpolation](/reference/aml/string-interpolation). Now each metric declaration becomes a single line: ```aml title="ecommerce.dataset.aml" Dataset ecommerce { ... // time comparison metrics metric gmv_time_compare: time_compare('gmv', 'GMV', '[$]#,###0') metric nmv_time_compare: time_compare('nmv', 'NMV', '[$]#,###0') metric aov_time_compare: time_compare('aov', 'AOV', '[$]#,###0') metric total_orders_time_compare: time_compare('total_orders', 'Total Orders', '#,###') metric total_delivered_orders_time_compare: time_compare('total_delivered_orders', 'Total Delivered Orders', '#,###') metric total_cancelled_orders_time_compare: time_compare('total_cancelled_orders', 'Total Cancelled Orders', '#,###') metric cancelled_order_ratio_time_compare: time_compare('cancelled_order_ratio', 'Cancelled Order Ratio', '#,###0.00%') } ``` Adding a new metric to the scorecard is now a one-liner, no more copy-pasting 15 lines of `case()` logic. ### Step 4: Build the pivot table With the model and metrics in place, create an exploration and set up the visualization: 1. Add `time_type.time_type` to the **Columns** shelf. 2. Add your time-compare metrics (e.g., `gmv_time_compare`, `nmv_time_compare`, `aov_time_compare`, `total_orders_time_compare`, `cancelled_order_ratio_time_compare`) as **Values**. 3. Set the chart type to **Pivot Table**. 4. In the Values section, switch to **Show as rows** so each metric becomes its own row. ## Customization ideas This pattern is flexible. Here are some ways you can adapt it: - **Different time periods.** Add or replace `case()` branches for windows like `QTD`, `QTD LY`, `Last Week`, or `Last Month`. Just add corresponding rows in the `time_type` query model and matching `when:` branches in each metric. - **More metrics.** Apply the same `case()` pattern to any base measure (revenue per active customer, refund rate, etc). Each new metric follows the identical structure. - **Custom sort order.** The number prefixes (`1.`, `2.`, etc.) control column ordering in the pivot. Adjust or remove them depending on how you want the columns arranged. --- ## Nested Aggregation A focused recipe for nesting aggregations across multiple models. For the underlying concept, see **[Learn AQL → Nested Aggregation](/as-code/aql/learn/nested-aggregation)**. ## The question > Which country has the user with the highest Average Order Value (AOV)? Three grains are in play: - **Order item** (`order_items`): the raw fact rows. - **User** (`users`): we aggregate AOV per user. - **Country** (`cities.country_name`): we take the max user AOV per country. ## Models and relationships ```aml Model order_items { dimension id {...} dimension user_id {...} dimension product_id {...} dimension quantity {...} } Model products { dimension id {...} dimension price {...} } Model users { dimension id {...} dimension city_id {...} } Model cities { dimension id {...} dimension country_name {...} } Dataset e_commerce { models: [order_items, products, users, cities] relationships: [ relationship(order_items.user_id > users.id, true), relationship(order_items.product_id > products.id, true), relationship(users.city_id > cities.id, true), ] } ``` ## Step 1: the inner metric (AOV) AOV is total order value divided by distinct order count: ```aml metric aov { label: 'AOV' type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price) / count_distinct(order_items.order_id) ;; } ``` ## Step 2: nest it at the user grain, then take the max ```aml metric max_user_aov { label: 'Max User AOV' type: 'number' definition: @aql order_items | group(users.id) | select(aov) | max() ;; } ``` Walking through: 1. `order_items`: start from order items, the finest grain. 2. `| group(users.id)`: bucket by user. 3. `| select(aov)`: compute AOV per user. 4. `| max()`: take the largest of those per-user AOVs. ## Step 3: use it with a coarser dimension In a report, drop `max_user_aov` alongside `cities.country_name`: Because nested aggregation respects surrounding context, AQL evaluates the inner per-user AOV *within each country* and returns the country-level max. Same metric, no rewriting needed for new dimensions. ## See also - [Learn AQL → Nested Aggregation](/as-code/aql/learn/nested-aggregation): the concept and the simpler "average monthly signups" example - [Learn AQL → Cross-Model Queries](/as-code/aql/learn/cross-model): how AQL resolves the order_items → users → cities chain --- ## Percent of Total :::info Alternative This is the AQL-centric approach to calculate Percent of Total. Holistics also provides a UI-friendly solution: [Percent of Total](/docs/percent-of-total) ::: ## Introduction The Percent of Total analysis is a commonly used reporting technique that helps understand the contribution of each row value to the overall population. In SQL, you have to divide your calculation into multiple CTEs (Common Table Expressions) and then cross-join those together to perform Percent of Total. Whereas with AQL, you can achieve this calculation in a more concise and reusable way by focusing on the business logic rather than the technical semantics. :::tip Recommended Prerequisites - [of_all](/reference/aql/of_all): Exclude your metric from being evaluated against certain dimensions/grains. ::: ## Video Tutorial ## High-level Flow Suppose we want to see how each product's sales contribute to global sales. To illustrate the calculation using AQL, we'll explore two different ways of performing it. First, we will break down the calculation into manageable steps or components. Then, we'll demonstrate how to write it as a single-line query. ## Setup To make it simple, we'll be working on the `order_items` modeling definition only with the following fields. :::tip Measures and Metrics You may see the use of `measure` in code snippets throughout this article. In Holistics, metrics defined inside data models are syntactically referred to as `measure`. We will use the word "metric" outside of code snippets to avoid confusions. [Read more about Metrics here](/as-code/aql/learn/defining-a-metric) ::: We'll show two equivalent ways to define `percent_of_total`. Pick whichever style suits the metric: ```aml Model order_items { ... dimension id {...} dimension product {...} dimension amount {...} measure total_sales { label: 'Total Sales' type: 'number' definition: @aql sum(order_items.amount) ;; } measure _total_sales_of_all { label: 'Total Sales of All' definition: @aql sum(order_items.amount) | of_all(order_items.id) ;; hidden: true } measure percent_of_total { label: "Percent of Total" type: 'number' definition: @aql (order_items.total_sales*1.0) / order_items._total_sales_of_all ;; } } ``` ```aml Model order_items { ... dimension id {...} dimension product {...} dimension amount {...} measure percent_of_total { label: "Percent of Total" type: 'number' definition: @aql (sum(order_items.amount) * 1.0) / (sum(order_items.amount) | of_all(order_items));; } } ``` ## Implementation **1. Define the aggregation** Calculate the total sales value for each product by defining the metric `total_sales` ```aml Model order_items { ... measure total_sales { label: 'Total Sales' type: 'number' definition: @aql sum(order_items.amount) ;; } } ``` export const step_1_sql = `SELECT products.name as "products->name" sum(order_items.amount) as "order_items->total_sales" FROM order_items LEFT JOIN products ON order_items.product_id = products.id GROUP BY 1 ` export const step_1 = { "fields": [ "Product", "Total Sales" ], "records": [ [ "Chocolate 2", "2187" ], [ "Samsung Tablet 2", "101313" ], [ "H&M T-shirt", "25194" ], [ "Face Treatment", "4743" ], [ "Adidas Shoes", "38901" ], [ "Xbox", "102797" ], [ "Jordan Shoes", "18760" ], [ "Bunk Bed", "13893" ], [ "AFC Biscuits", "2350" ], [ "Sony Headphone 1", "10665" ], [ "Sofa 1", "50334" ], [ "Body Scrub", "211359" ], [ "Flour 1", "800" ], [ "Converse Shoes", "40575" ], [ "Outdoor Sofa", "24999" ], [ "Bathroom Mirror", "3960" ], [ "Face Moisturizers", "4576" ], [ "Wii", "24090" ], [ "Cookies 2", "6049" ], [ "Face Cream", "17365" ], [ "Nokia C1", "87307" ], [ "High Cabinet", "8432" ], [ "Lacoste T-shirt", "21994" ], [ "Body Cleanser", "161143" ], [ "Reebox Shoes", "21231" ], [ "Ticwatch 1", "74019" ], [ "KitKat 1", "1037" ], ], "metadata": { "aql": "explore {\n dimensions {\n products.name\n }\n measures {\n order_items.sales\n }\n}", "executed_sql": "WITH \"aql__t3\" AS (\n SELECT\n (\"order_items\".\"quantity\" * \"products\".\"price\") AS \"value\",\n \"order_items\".\"product_id\" AS \"product_id\"\n FROM\n \"demo\".\"order_items\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n)\nSELECT\n \"products\".\"name\" AS \"products->name\",\n SUM(\"order_items\".\"value\") AS \"order_items->sales\"\nFROM\n \"aql__t3\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\nGROUP BY\n 1\n", "url": "https://play.amql.org/#code/uDritRdcsbtfekV2DrisS2OsH2FriisE2HtiVcRkZ2YtjYZgjsGxkDxYDtCxULrgtUVdfPTfl23tiZVjxKD2RtPxUGtZ2kxRFxbEuBxZF3ptMe3utkuBtgVsSrgntdRe2Wtk2o3LtVrnxXEtWifd2Xse20tcU3ktWxrG2B3M23tcxuDyRGxpFxrDxlExVEyfLxbFuFyjFtfyKbzvIyNRyFKxDD2C0tStg4mtUlTk5btZU0qGtfi2Hti2Otk2M2UxdHxcD0t7yULzlPyiK0wmzyIyKPyCK0tXzIF0rWxMH0p90pZygI0nmzsG0lwtlj0kR2PxVE0e9zTWyaH0YmzfF0XwtTZ2QtV0YK0mGxVD5D0X9zSUyZH0WmzeG0XarisErisHtmynD0WDsGtRdcrisSritT0gDtkrgsZlktPGsdtCDshnmrgsV2ysKGIrnxrEtU6DtR4ZtkjsHxpDxQNxJHyHHscxOG1nGtV3Vtdd3mtTVziGxqEtPjflxRDtPe2v1gExnEzYDxeE0BFtj2UtLz4G0IGsExNH21tekixQKtfi2sti6Wtk3BxSNxNItgi3PtlT4ixPHtl4X2bxLEtNytGti3r4Q1DDtjYZgy0KxWMsA0IFy5JxKD2RtPxUGzdFxbHuBxZFsErg2KtlVsBywHyKSzUI42tUyHG0HLxdIxcDyKg0rGyIVxMHyGh1VFyFO2PxVEx8j1FHyAIyOGxVD6Qx8Jx7EtN2FuFrisErisH2j2ntcjsHrisSrixOLz6LsGxTFsGtRdcxjDslxMErgxeLrguDyPE3ftgVsSrgntkRScVrnxRE4atS2oxSDsnxqKxYF2OtjTi4vz3ExeDxTG5ItRPjfliTVPe3pyLE0BExcFtfne3CxRHsYtYfcZj26tT4Y3BxeFyyFx0ItMrixlE2GsG2DzlL2NxqDypFtZ2m6wy6Drgtgi5NtlTk1ZD0FGzzKtAxaGrgshtUyiF0gJteld2htixUHtY243h0GDtWR6ztVxTG0eTxVEtWZe4KxUFsYtjhc3wuDrgjtDsntFCsbdGzJLuFFsTTxxEuFztQ0OHzrRsnxYEzp9zplzFJznZthlRe284GznQtBxYHzn9znizFJznZtm3htlVzkQtGxVEzhby2NtRy2D1VHtkVdjy3KsCrgtgifUlTk2U2JtZT3pzJN4HtRjli2Vtj3v5fzHQtEfk2XrgtDxbEzNs3UtdsAzRM0mFsBy7MuFrisErisHtd4c3ptjsHtTR2ltX2vtZ3nsGxSFsGtRdcrisSrislxMErgxdKziGzGH4dtS4azFFzxIsbxpJxXF28tjT2NtgzbGrnxTG2itRPjf526PtPe3nyKE2m31xcFtfne40xRHsYtYfcZj26tT4W3BxeFyxFx0ItMrixlE2GsG2DzjK2MxpDyoFtZ2ltejy5DrgtZUz4GzpKshtUyRF0OJte6z2ZtixUHtY2w3bzyDtW64tjVxTG0NTxVEtWZeZxUGsYtjh6uuDDrgjtDsntFCsbd4132uFFsTTxpEuFzVQ0HEzXQsmxUDzZO5ZuAtkzX9zXOy4FzZZtgRi2KtkP11StAxZFrg189zlkzHKzqNuFrisErisHtdf263dsHtTx7D3asGxOFsG5XtcrisSrislxMErgxZG0DGzmHtk5ItcVy6F0SIsbxlFxTFzjTtUR2ytPjfliTVP12ExYD2m3txcFtfne50xRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1Drg0sDzwFzhKshtU1tH0GHteld2ZtixUHtY2w3XzuDtWR6PtVxTG0JTxVEtWZey3DxUEsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ0DEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtTflekiuBtP2I3HzhQsbxcGrg2I2czp9zpgzLNzxNuFrisErisHtdxXD3gsHxmGtZ3gsGxRFsG5htcrisSrislxME0QHxcD0NGztI5Stc0DG0cOxoDxWFzvTtUR21tPj2mtiTVPe3lyJE2m3zxcFtfneVixRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2ktejy4D0AD06Dz4FzpM2UyUF0QKtVuAtkxSHtYZU2ezzDtWR6dtVxTG0OTxVEtWZeZxUGsYtjh6tuDDrgjtDsntFCsbdGy4FuFFsTTxrEuFzZQ0KEzZQsmxUDzZ9zZgy4FzZZ43te23te2NtkPzjU68xeHrgzt9ztkzPPz3NuFrisErisHtdf3C3isHtfi2HtijsGxOFsG2qtcrisSrislxMErgxZG0LGzpI5Qtcz8G0aIsnxlFxTFzoTtUR2ytPjfliTVzMFxYD2m3txcFtfn4jtixRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1DrgtZUzwGzhKshtU1yH0GHteld2ZtixUHtY2w3MzuDtWR6PtVxTG0JTxVEtWZeZxUGsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ5W6HtljzZQtDxWFzdOtkVuAtkzb9zbOy8HzfZ3RtV2htVUP2FzjQsbxaGrgsZzVIznHtUxWD3v3Dzr9zrOzLLzvZ5OtViP3vzsQtFxXDrgshtUzpOteld2etizn9znOzDIzkZ2K3Vtfle5JzlPscxYHzm9zmizFJzna4VtZm33uBtPRkkVdgjzvRxgHrgsZxgGz39z3jzVQ0CNuFrisErisHtdfxlDtjsHtgi2ItlTkjsGxQFsGtRdcrisSrislxMErgxbI0VGzxHtk5atcVzIF0kItAxnHxVFzwTtU4KtRPjfliTVPe3jyIE2m3xxcFtfne4sxRHsYtYf47tj26tT4S3BxeFyvFx0ItMrixlE2GsG2DzfI2KxnDymFtZ2jtejy3DrgtZUz0GzlKshtU18H0KHteld2ZtixUHtY2w3ZzwDtWR6XtVxTG0LTxVEtWZeZxUGsYtjh6ouDDrgjtDsntFCsbd4z32uFFsTTxpEuFzVQtT6ItVXfiuBtPzeSsbxbHrgzn9znkzLMzwZteR2KzpQsmxUDziO3MuAtkzg9zgOy4FzZZ2ItiTYRek0BEzgPslxbHrgshtUznOteld2itizp9zpOzLMzwZtg3WtT5SzqPtAxVEzk9zkiy8GzeZ3QtVRkVUP2FzjQsbxaGrgsZtkzoOtUxWD3u3Dzq9zqOzLLzvNuFrisErisHtdf273esHtl3htijsGxNFsGtRdcrisSrislxMErgxYF0FGznHtk5KtczkG0UItFxkExSFzhT0ZDtRPjfli6ztPe3d0sFtV3rxcFtfne26xRHsYtYfcZj26tT4M3BxeFysFx0ItMrixlE2GsG2DzZF2HxkDyjF14Dtejy0Drg62zuGzfKshtU1qH0EHteld2ZtixUHtY2w3WztDtWR6LtVxTG0ITxVEtWZeZxUGsYtjh6fuDDrgjtDsntFCsbd4w32uFFsTTxpEuFzVQ2FtXePlgP1NDtVzfQtDxcDrgtFgrgscxcDzpOxuEzn9znOzLNzxh2lzvYsZtkztS313Dzx9zxWzLDzvZ3BtijkPeR2QzvQsexaErgxaEzvO4tuA0EIzr9zrHzHLzrZ4ZzqXskxZDzp9zplzFKzoZtVdRZx0DzkOsdxVEzg9zggy6GzcZtSZikYPUR35zhQsaxaE2qxaDzmOxSLzm9zmHzHLzrZtX2I3GtiznQsfxWFzjO3JuAtkzj9zjOy8HzfZtT24uBtP3rzgQsbxXDrg2XzhOteld2ez1Izj9zjHzDIzkZtWlccPeR2PzmQsexZDrgxZEzoOtkVuAtkzm9zmOzFKzoNuFrisErisHtdf263d2H6dtTYRekjsGxRFsG2ttcrisSrislxMErgxcJ0HGzqI5Mtc0AG0WIslxoIxWFzsTtUR21tPjfl2ntVzVFxYD2m3zxcFtfne3CxRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2Utejy4DrgtZUz2GznKshtU15H0MHteld2ZtixUHtY2w3azxDtWR6btVxTG0MTxVEtWZeZxUGsYtjh6ruDDrgjtDsntFCsbd4032uFFsTTxpEuFzVQ0GEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtT22uBtP1zSsbxXDrg149zhkzDIzkNuFriuFsEritR2psb2y6W2DrisSri6ctgcfi5OzqF0EJtjz6ItgifUlTkj0zFyKH2F2LtRjl21xmJ3E4Qti3w3Wtd2ptj462ZxqH3y3xykHsctRk20tVksmyKDy5D2vtdfPVTfd2OtiT2R2mtT3S2k4XsetZ3AtA2qtYxjDsH2i2m6LsHyfMxTFsG3Gtc5Q" } } export const step_2 = { "fields": [ "Product", "Total Sales", "Total Sales of All" ], "records": [ [ "Chocolate 2", "2187", "4391797" ], [ "Samsung Tablet 2", "101313", "4391797" ], [ "H&M T-shirt", "25194", "4391797" ], [ "Face Treatment", "4743", "4391797" ], [ "Adidas Shoes", "38901", "4391797" ], [ "Xbox", "102797", "4391797" ], [ "Jordan Shoes", "18760", "4391797" ], [ "Bunk Bed", "13893", "4391797" ], [ "AFC Biscuits", "2350", "4391797" ], [ "Sony Headphone 1", "10665", "4391797" ], [ "Sofa 1", "50334", "4391797" ], [ "Body Scrub", "211359", "4391797" ], [ "Flour 1", "800", "4391797" ], [ "Converse Shoes", "40575", "4391797" ], [ "Outdoor Sofa", "24999", "4391797" ], [ "Bathroom Mirror", "3960", "4391797" ], [ "Face Moisturizers", "4576", "4391797" ], [ "Wii", "24090", "4391797" ], [ "Cookies 2", "6049", "4391797" ], [ "Face Cream", "17365", "4391797" ], [ "Nokia C1", "87307", "4391797" ], [ "High Cabinet", "8432", "4391797" ], [ "Lacoste T-shirt", "21994", "4391797" ], [ "Body Cleanser", "161143", "4391797" ], [ "Reebox Shoes", "21231", "4391797" ], [ "Ticwatch 1", "74019", "4391797" ], [ "KitKat 1", "1037", "4391797" ], [ "Umbrella", "29855", "4391797" ], [ "Guess Jeans", "13881", "4391797" ], [ "10 Deep T-shirt", "9398", "4391797" ], [ "iPad 1", "130244", "4391797" ] ], "metadata": { "aql": "explore {\n dimensions {\n products.name\n }\n measures {\n order_items.total_sales,\n order_items._total_sales_of_all\n }\n}", "executed_sql": "WITH \"aql__t3\" AS (\n SELECT\n (\"order_items\".\"quantity\" * \"products\".\"price\") AS \"amount\",\n \"order_items\".\"product_id\" AS \"product_id\"\n FROM\n \"demo\".\"order_items\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n), \"aql__t1\" AS (\n SELECT\n \"products\".\"name\" AS \"products->name\",\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n GROUP BY\n 1\n), \"aql__t4\" AS (\n SELECT\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n)\nSELECT\n \"aql__t1\".\"products->name\" AS \"products->name\",\n \"aql__t1\".\"sum_order_items->amount\" AS \"order_items->total_sales\",\n \"aql__t4\".\"sum_order_items->amount\" AS \"order_items->_total_sales_of_all\"\nFROM\n \"aql__t1\"\n CROSS JOIN \"aql__t4\"\n", "url": "https://play.amql.org/#code/uDritRdcsbtfekV2DrisS2OsH2FriisE2HtiVcRkZ2YtjYZgjsGxkDxYDtCxULrgtUVdfPTfl23tiZVjxKD2RtPxUGtZ2kxRFxbEuBxZF3ptMe3utkuBtgVsSrgntdRe2Wtk2o3LtVrnxXEtWifd2Xse20tcU3ktWxrG2B3M23tcxuDyRGxpFxrDxlExVEyfLxbFuFyjFtfyKbzvIyNRyFKxDD2C0tStg4mtUlTk5btZU0qGtfi2Hti2Otk2M2UxdHxcD0t7yULzlPyiK0wmzyIyKPyCK0tXzIF0rWxMH0p90pZygI0nmzsG0lwtlj0kR2PxVE0e9zTWyaH0YmzfF0XwtTZ2QtV0YK0mGxVD5D0X9zSUyZH0WmzeG0XarisErisHtmynD0WDsGtRdcrisSritT0gDtkrgsZlktPGsdtCDshnmrgsV2ysKGIrnxrEtU6DtR4ZtkjsHxpDxQNxJHyHHscxOG1nGtV3Vtdd3mtTVziGxqEtPjflxRDtPe2v1gExnEzYDxeE0BFtj2UtLz4G0IGsExNH21tekixQKtfi2sti6Wtk3BxSNxNItgi3PtlT4ixPHtl4X2bxLEtNytGti3r4Q1DDtjYZgy0KxWMsA0IFy5JxKD2RtPxUGzdFxbHuBxZFsErg2KtlVsBywHyKSzUI42tUyHG0HLxdIxcDyKg0rGyIVxMHyGh1VFyFO2PxVEx8j1FHyAIyOGxVD6Qx8Jx7EtN2FuFrisErisH2j2ntcjsHrisSrixOLz6LsGxTFsGtRdcxjDslxMErgxeLrguDyPE3ftgVsSrgntkRScVrnxRE4atS2oxSDsnxqKxYF2OtjTi4vz3ExeDxTG5ItRPjfliTVPe3pyLE0BExcFtfne3CxRHsYtYfcZj26tT4Y3BxeFyyFx0ItMrixlE2GsG2DzlL2NxqDypFtZ2m6wy6Drgtgi5NtlTk1ZD0FGzzKtAxaGrgshtUyiF0gJteld2htixUHtY243h0GDtWR6ztVxTG0eTxVEtWZe4KxUFsYtjhc3wuDrgjtDsntFCsbdGzJLuFFsTTxxEuFztQ0OHzrRsnxYEzp9zplzFJznZthlRe284GznQtBxYHzn9znizFJznZtRdfl2jzlQsZxWFzjby4NtRy4D1XHtkVdjy5KsCrgtgifUlTk2U2JtZTVrgzLN4JtRjli2Vtkfk5stPj2E5nzOQtExbErgtDxbEzTs3atdsAzXM0uGsBzFWtPzGLtPfWP2I3EzNa5ytWrgsZ2i1n3rguEziZ2azCGxdMz4NuFrisErisH2t2dtcjsHtTR2ftX2ptZ4WsGxSFyJDtcrisSrislxMErgxdK0RGzrH5MtS5JzqF0gIsbxpJxXF28tjT2Ntg0DGrnxTG2itR6m4ntiTVPe3nyKE2m31xcFtfne4uxRHsYtYfcZj26tT4W3BxeFyxFx0ItMrixlE2GsG2DzjK2MxpDyoFtZ2ltejy5DrgtZUz4GzpKshtUyRF0OJteld2ZtixUHtY2w3bzyDtWR6ftVxTG0NTxVEtWZeZxUGsYtjh6uuDDrgjtDsntFCsbd4132uFFsTTxpEuFzVQ0HEzXQsmxUDzZO5ZuAtkzX9zXOy4FzZZtgRi2KtkP11StAxZFrg189zlkzHKzqNuFrisErisHtdf263dsHtTx7D3asGxOFsG5XtcrisSrislxMErgxZG0DGzmHtk5ItcVy6F0SIsbxlFxTFzjTtUR2ytPjfliTVP12ExYD2m3txcFtfne50xRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1Drg0sDzwFzhKshtU1tH0GHteld2ZtixUHtY2w3XzuDtWR6PtVxTG0JTxVEtWZey3DxUEsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ0DEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtTflekiuBtP2I3HzhQsbxcGrg2I2czp9zpgzLNzxNuFrisErisHtdxXD3gsHxmGtZ3gsGxRFsG5htcrisSrislxME0QHxcD0NGztI5Stc0DG0cOxoDxWFzvTtUR21tPj2mtiTVPe3lyJE2m3zxcFtfneVixRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2ktejy4D0AD06Dz4FzpM2UyUF0QKtVuAtkxSHtYZU2ezzDtWR6dtVxTG0OTxVEtWZeZxUGsYtjh6tuDDrgjtDsntFCsbdGy4FuFFsTTxrEuFzZQ0KEzZQsmxUDzZ9zZgy4FzZZ43te23te2NtkPzjU68xeHrgzt9ztkzPPz3NuFrisErisHtdf3C3isHtfi2HtijsGxOFsG2qtcrisSrislxMErgxZG0LGzpI5Qtcz8G0aIsnxlFxTFzoTtUR2ytPjfliTVzMFxYD2m3txcFtfn4jtixRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1DrgtZUzwGzhKshtU1yH0GHteld2ZtixUHtY2w3MzuDtWR6PtVxTG0JTxVEtWZeZxUGsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ5W6HtljzZQtDxWFzdOtkVuAtkzb9zbOy8HzfZ3RtV2htVUP2FzjQsbxaGrgsZzVIznHtUxWD3v3Dzr9zrOzLLzvZ5OtViP3vzsQtFxXDrgshtUzpOteld2etizn9znOzDIzkZ2K3Vtfle5JzlPscxYHzm9zmizFJzna4VtZm33uBtPRkkVdgjzvRxgHrgsZxgGz39z3jzVQ0CNuFrisErisHtdfxlDtjsHtgi2ItlTkjsGxQFsGtRdcrisSrislxMErgxbI0VGzxHtk5atcVzIF0kItAxnHxVFzwTtU4KtRPjfliTVPe3jyIE2m3xxcFtfne4sxRHsYtYf47tj26tT4S3BxeFyvFx0ItMrixlE2GsG2DzfI2KxnDymFtZ2jtejy3DrgtZUz0GzlKshtU18H0KHteld2ZtixUHtY2w3ZzwDtWR6XtVxTG0LTxVEtWZeZxUGsYtjh6ouDDrgjtDsntFCsbd4z32uFFsTTxpEuFzVQtT6ItVXfiuBtPzeSsbxbHrgzn9znkzLMzwZteR2KzpQsmxUDziO3MuAtkzg9zgOy4FzZZ2ItiTYRek0BEzgPslxbHrgshtUznOteld2itizp9zpOzLMzwZtg3WtT5SzqPtAxVEzk9zkiy8GzeZ3QtVRkVUP2FzjQsbxaGrgsZtkzoOtUxWD3u3Dzq9zqOzLLzvNuFrisErisHtdf273esHtl3htijsGxNFsGtRdcrisSrislxMErgxYF0FGznHtk5KtczkG0UItFxkExSFzhT0ZDtRPjfli6ztPe3d0sFtV3rxcFtfne26xRHsYtYfcZj26tT4M3BxeFysFx0ItMrixlE2GsG2DzZF2HxkDyjF14Dtejy0Drg62zuGzfKshtU1qH0EHteld2ZtixUHtY2w3WztDtWR6LtVxTG0ITxVEtWZeZxUGsYtjh6fuDDrgjtDsntFCsbd4w32uFFsTTxpEuFzVQ2FtXePlgP1NDtVzfQtDxcDrgtFgrgscxcDzpOxuEzn9znOzLNzxh2lzvYsZtkztS313Dzx9zxWzLDzvZ3BtijkPeR2QzvQsexaErgxaEzvO4tuA0EIzr9zrHzHLzrZ4ZzqXskxZDzp9zplzFKzoZtVdRZx0DzkOsdxVEzg9zggy6GzcZtSZikYPUR35zhQsaxaE2qxaDzmOxSLzm9zmHzHLzrZtX2I3GtiznQsfxWFzjO3JuAtkzj9zjOy8HzfZtT24uBtP3rzgQsbxXDrg2XzhOteld2ez1Izj9zjHzDIzkZtWlccPeR2PzmQsexZDrgxZEzoOtkVuAtkzm9zmOzFKzoNuFrisErisHtdf263d2H6dtTYRekjsGxRFsG2ttcrisSrislxMErgxcJ0HGzqI5Mtc0AG0WIslxoIxWFzsTtUR21tPjfl2ntVzVFxYD2m3zxcFtfne3CxRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2Utejy4DrgtZUz2GznKshtU15H0MHteld2ZtixUHtY2w3azxDtWR6btVxTG0MTxVEtWZeZxUGsYtjh6ruDDrgjtDsntFCsbd4032uFFsTTxpEuFzVQ0GEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtT22uBtP1zSsbxXDrg149zhkzDIzkNuFriuFsEritR2psb2y6W2DrisSrisHHrg6ftgcfi5RztDxOD0KLtjxTJ2Btl4PtijsGtX2Z33tisExWJ23xPD2XtjkRk2hxWHysDxIF3ItRjl3ayIMtTfl34sAx1H4isBxyNznF2CzULzRQyGEtgifUlTk3CteR2b0hHyvOzeJ6K5ttd2ptkf4ktcPj2E2f0CDxeQtPxfLtPfWP2ItcyZH6463zTHsc5o3jtVksmy2DrisSri3AtdfPV5Vtd2OtiT2R2mtT4B2k5isetZ3HtA2qtYxjDsH2i2mtcjsHytMxTFsG3GtcriuF" } } **2. Define the aggregation with coarser level-of-detail** export const step_2_sql = `WITH aql__1 AS ( SELECT products.name AS "products->name" SUM(order_items.amount) AS "sum_order_items->amount" FROM order_items LEFT JOIN products ON order_items.product_id = products.id GROUP BY 1 ),\n aql__2 AS ( SELECT SUM(order_items.amount) AS "sum_order_items->amount" )\n SELECT aql__1."products->name" as "products->name", aql__1."sum_order_items->amount" as "total_sales", aql__2."sum_order_items->amount" as "_total_sales_of_all" FROM aql__1 CROSS JOIN aql__2 ` Total sales of all order items is also needed to be used as denominator for the percent of total. This metric can be definied by using [`of_all()`](/reference/aql/of_all) metric function. ```aml Model order_items { ... measure total_sales {...} measure _total_sales_of_all { label: 'Total Sales of All' type: 'number' definition: @aql order_items.total_sales | of_all(order_items) ;; } } ``` export const step_3_sql = `WITH \naql__1 AS ( SELECT SUM(amount) AS total_sales FROM order_items ), \naql__2 AS ( SELECT product, SUM(amount) AS value FROM order_items ) \nSELECT aql__2.product, aql__2.value, (aql__2.value / aql__1.total) AS percent_per_total FROM aql__2 CROSS JOIN aql__1 ` **3. Calculate the percent of total** Finally, the Percent of Total is determined by dividing the `total_sales` by `_total_sales_of_all`. Since `_total_sales_of_all` remains constant, the percent of total will adjust according to `total_sales` metric and its corresponding dimension ```aml Model order_items { ... measure total_sales {...} measure _total_sales_of_all {...} measure percent_of_total { label: "Percent of Total" type: 'number' definition: @aql (order_items.total_sales*1.0) / order_items._total_sales_of_all ;; } } ``` _To maintain the decimal format for the percent of total metric, multiplying by `1.0` explicitly casts the left side to decimal._ export const step_3 = { "fields": [ "Product", "Total Sales", "Percent of Total" ], "records": [ [ "Chocolate 2", "2187", "0.0498%" ], [ "Samsung Tablet 2", "101313", "2.307%" ], [ "H&M T-shirt", "25194", "0.5737%" ], [ "Face Treatment", "4743", "0.1080%" ], [ "Adidas Shoes", "38901", "0.8858%" ], [ "Xbox", "102797", "2.3407%" ], [ "Jordan Shoes", "18760", "0.4272%" ], [ "Bunk Bed", "13893", "0.3163%" ], [ "AFC Biscuits", "2350", "0.0535%" ], [ "Sony Headphone 1", "10665", "0.2428%" ], [ "Sofa 1", "50334", "1.1461%" ], [ "Body Scrub", "211359", "4.8126%" ], [ "Flour 1", "800", "0.0182%" ], [ "Converse Shoes", "40575", "0.9239%" ], [ "Outdoor Sofa", "24999", "0.5692%" ], [ "Bathroom Mirror", "3960", "0.0902%" ], [ "Face Moisturizers", "4576", "0.1042%" ], [ "Wii", "24090", "0.5485%" ], [ "Cookies 2", "6049", "0.1377%" ], [ "Face Cream", "17365", "0.3954%" ], [ "Nokia C1", "87307", "1.9879%" ], [ "High Cabinet", "8432", "0.1919%" ], [ "Lacoste T-shirt", "21994", "0.5008%" ], [ "Body Cleanser", "161143", "3.6692%" ], [ "Reebox Shoes", "21231", "0.4834%" ], [ "Ticwatch 1", "74019", "1.6854%" ], [ "KitKat 1", "1037", "0.2361%" ], [ "Umbrella", "29855", "0.6798%" ], [ "Guess Jeans", "13881", "0.3161%" ], [ "10 Deep T-shirt", "9398", "0.2140%" ], [ "Gucci T-shirt", "7826", "0.1782%" ], [ "Nike Shoes", "62529", "1.4238%" ], [ "Chips 2. Dried Fruit", "5435", "0.1238%" ], [ "Plant Pot", "21953", "0.5000%" ], [ "Sony Smart TV", "70961", "1.6158%" ], [ "Wrangler Jeans", "8562", "0.1950%" ], [ "Baking Soda 1", "4536", "0.1033%" ], [ "iPhone X", "72053", "1.6406%" ], [ "TV Stand", "53058", "1.2081%" ], [ "Dining Chair 1", "31056", "0.7071%" ], [ "Xbox 360", "90142", "2.0525%" ], [ "Playstation 4", "39596", "0.9016%" ], [ "Single Bed", "2340", "0.0533%" ], [ "Oil 2", "26525", "0.6040%" ], [ "Apple Watch 1", "197375", "4.4942%" ], [ "Samsung Smart TV", "72128", "1.6423%" ], [ "iPhone 7", "77086", "1.7552%" ], [ "Kindle Fire 2", "69054", "1.5723%" ], [ "Kindle Fire 1", "203271", "4.6284%" ], [ "BlackBerry Key 1", "146508", "3.3359%" ], [ "Samsung Bluetooth Earphone", "84791", "1.9307%" ], [ "Essence Lotions", "18550", "0.4224%" ], [ "Flour 2", "12157", "0.2768%" ], [ "Oil 1", "17379", "0.3957%" ], [ "Face Mask", "11484", "0.2615%" ], [ "LG Smart TV", "67865", "1.5453%" ], [ "Samsung Galaxy 2", "127246", "2.8974%" ], [ "Bedroom Mirror", "23857", "0.5432%" ], [ "Fireplace", "73942", "1.6836%" ], [ "iPad 2", "108816", "2.4777%" ], [ "Chips 1", "7673", "0.1747%" ], [ "Panasonic Smart TV", "129750", "2.9544%" ], [ "Outdoor Lighting", "11568", "0.2634%" ], [ "Bathroom Slippers", "7764", "0.1768%" ], [ "Samsung Tablet 1", "50690", "1.1542%" ], [ "Shower curtains", "8347", "0.1901%" ], [ "Towels", "4363", "0.0993%" ], [ "Playstation 3", "32682", "0.7442%" ], [ "Sony Headphone 2", "35732", "0.8136%" ], [ "Samsung Galaxy 1", "45974", "1.0468%" ], [ "Smart Lighting", "14635", "0.3332%" ] ], "metadata": { "aql": "explore {\n dimensions {\n products.name\n }\n measures {\n order_items.total_sales,\n order_items.percent_of_total\n }\n}", "executed_sql": "WITH \"aql__t3\" AS (\n SELECT\n (\"order_items\".\"quantity\" * \"products\".\"price\") AS \"amount\",\n \"order_items\".\"product_id\" AS \"product_id\"\n FROM\n \"demo\".\"order_items\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n), \"aql__t1\" AS (\n SELECT\n \"products\".\"name\" AS \"products->name\",\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n LEFT JOIN \"demo\".\"products\" \"products\" ON \"order_items\".\"product_id\" = \"products\".\"id\"\n GROUP BY\n 1\n), \"aql__t4\" AS (\n SELECT\n SUM(\"order_items\".\"amount\") AS \"sum_order_items->amount\"\n FROM\n \"aql__t3\" \"order_items\"\n)\nSELECT\n \"aql__t1\".\"products->name\" AS \"products->name\",\n \"aql__t1\".\"sum_order_items->amount\" AS \"order_items->total_sales\",\n ((\"aql__t1\".\"sum_order_items->amount\" * 1.0) / \"aql__t4\".\"sum_order_items->amount\") AS \"order_items->percent_of_total\"\nFROM\n \"aql__t1\"\n CROSS JOIN \"aql__t4\"\n", "url": "https://play.amql.org/#code/uDritRdcsbtfekV2DrisS2OsH2FriisE2HtiVcRkZ2YtjYZgjsGxkDxYDtCxULrgtUVdfPTfl23tiZVjxKD2RtPxUGtZ2kxRFxbEuBxZF3ptMe3utkuBtgVsSrgntdRe2Wtk2o3LtVrnxXEtWifd2Xse20tcU3ktWxrG2B3M23tcxuDyRGxpFxrDxlExVEyfLxbFuFyjFtfyKbzvIyNRyFKxDD2C0tStg4mtUlTk5btZU0qGtfi2Hti2Otk2M2UxdHxcD0t7yULzlPyiK0wmzyIyKPyCK0tXzIF0rWxMH0p90pZygI0nmzsG0lwtlj0kR2PxVE0e9zTWyaH0YmzfF0XwtTZ2QtV0YK0mGxVD5D0X9zSUyZH0WmzeG0XarisErisHtmynD0WDsGtRdcrisSritT0gDtkrgsZlktPGsdtCDshnmrgsV2ysKGIrnxrEtU6DtR4ZtkjsHxpDxQNxJHyHHscxOG1nGtV3Vtdd3mtTVziGxqEtPjflxRDtPe2v1gExnEzYDxeE0BFtj2UtLz4G0IGsExNH21tekixQKtfi2sti6Wtk3BxSNxNItgi3PtlT4ixPHtl4X2bxLEtNytGti3r4Q1DDtjYZgy0KxWMsA0IFy5JxKD2RtPxUGzdFxbHuBxZFsErg2KtlVsBywHyKSzUI42tUyHG0HLxdIxcDyKg0rGyIVxMHyGh1VFyFO2PxVEx8j1FHyAIyOGxVD6Qx8Jx7EtN2FuFrisErisH2j2ntcjsHrisSrixOLz6LsGxTFsGtRdcxjDslxMErgxeLrguDyPE3ftgVsSrgntkRScVrnxRE4atS2oxSDsnxqKxYF2OtjTi4vz3ExeDxTG5ItRPjfliTVPe3pyLE0BExcFtfne3CxRHsYtYfcZj26tT4Y3BxeFyyFx0ItMrixlE2GsG2DzlL2NxqDypFtZ2m6wy6Drgtgi5NtlTk1ZD0FGzzKtAxaGrgshtUyiF0gJteld2htixUHtY243h0GDtWR6ztVxTG0eTxVEtWZe4KxUFsYtjhc3wuDrgjtDsntFCsbdGzJLuFFsTTxxEuFztQ0OHzrRsnxYEzp9zplzFJznZthlRe284GznQtBxYHzn9znizFJznZtRdfl2jzlQsZxWFzjby4NtRy4D1XHtkVdjy5KsCrgtgifUlTk2U2JtZTVrgzLN4JtRjli2Vtkfk5stPj2E5nzOQtExbErgtDxbEzTs3atdsAzXM0uGsBzFWtPzGLtPfWP2I3EzNa5ytWrgsZ2i1n401MuEyPDyxEzxMzqX3ptiTV5LzlEyFFznPtMritAxhGzkEzzF2Sznr14NzoLsCJGI4XsH0KN16Uz5LuFrisErisHtdf2wtcjsHtTR2ytX28tZ2psGxSFsGtRdcrisSrislxMErgxdK0VGzvH3ftS3czuF0kHrnsbxpJxXF28tjT2Ntg0HGrnxTG2itR4Xtf606OtPe3nyKE2m31xcFtfne5ExRHsYtYfcZj26tT4W3BxeFyxFx0ItMrixlE2GsG2DzjK2MxpDyoFtZ2ltejy5DrgtZUz4GzpKshtUyRF0OJteld2ZtixUHtY2w3bzyDtW62tjVxTG0NTxVEtWZeZxUGsYtjh6uuDDrgjtDsntFCsbd4132uFFsTTxpEuFzVQ0HEzXQsmxUDzZO5ZuAtkzX9zXOy4FzZZtgRi2KtkP11StAxZFrg189zlkzHKzqNuFrisErisHtdf263dsHtTx7D3asGxOFsG5XtcrisSrislxMErgxZG0DGzmHtk5ItcVy6F0SIsbxlFxTFzjTtUR2ytPjfliTVP12ExYD2m3txcFtfne50xRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1Drg0sDzwFzhKshtU1tH0GHteld2ZtixUHtY2w3XzuDtWR6PtVxTG0JTxVEtWZey3DxUEsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ0DEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtTflekiuBtP2I3HzhQsbxcGrg2I2czp9zpgzLNzxNuFrisErisHtdxXD3gsHxmGtZ3gsGxRFsG5htcrisSrislxME0QHxcD0NGztI5Stc0DG0cOxoDxWFzvTtUR21tPj2mtiTVPe3lyJE2m3zxcFtfneVixRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2ktejy4D0AD06Dz4FzpM2UyUF0QKtVuAtkxSHtYZU2ezzDtWR6dtVxTG0OTxVEtWZeZxUGsYtjh6tuDDrgjtDsntFCsbdGy4FuFFsTTxrEuFzZQ0KEzZQsmxUDzZ9zZgy4FzZZ43te23te2NtkPzjU68xeHrgzt9ztkzPPz3NuFrisErisHtdf3C3isHtfi2HtijsGxOFsG2qtcrisSrislxMErgxZG0LGzpI5Qtcz8G0aIsnxlFxTFzoTtUR2ytPjfliTVzMFxYD2m3txcFtfn4jtixRHsYtYfcZj26tT4O3BxeFytFx0ItMrixlE2GsG2DzbG2IxlDykFtZ2htejy1DrgtZUzwGzhKshtU1yH0GHteld2ZtixUHtY2w3MzuDtWR6PtVxTG0JTxVEtWZeZxUGsYtjh6iuDDrgjtDsntFCsbd4x32uFFsTTxpEuFzVQ5W6HtljzZQtDxWFzdOtkVuAtkzb9zbOy8HzfZ3RtV2htVUP2FzjQsbxaGrgsZzVIznHtUxWD3v3Dzr9zrOzLLzvZ5OtViP3vzsQtFxXDrgshtUzpOteld2etizn9znOzDIzkZ2K3Vtfle5JzlPscxYHzm9zmizFJzna4VtZm33uBtPRkkVdgjzvRxgHrgsZxgGz39z3jzVQ0CNuFrisErisHtdfxlDtjsHtgi2ItlTkjsGxQFsGtRdcrisSrislxMErgxbI0VGzxHtk5atcVzIF0kItAxnHxVFzwTtU4KtRPjfliTVPe3jyIE2m3xxcFtfne4sxRHsYtYf47tj26tT4S3BxeFyvFx0ItMrixlE2GsG2DzfI2KxnDymFtZ2jtejy3DrgtZUz0GzlKshtU18H0KHteld2ZtixUHtY2w3ZzwDtWR6XtVxTG0LTxVEtWZeZxUGsYtjh6ouDDrgjtDsntFCsbd4z32uFFsTTxpEuFzVQtT6ItVXfiuBtPzeSsbxbHrgzn9znkzLMzwZteR2KzpQsmxUDziO3MuAtkzg9zgOy4FzZZ2ItiTYRek0BEzgPslxbHrgshtUznOteld2itizp9zpOzLMzwZtg3WtT5SzqPtAxVEzk9zkiy8GzeZ3QtVRkVUP2FzjQsbxaGrgsZtkzoOtUxWD3u3Dzq9zqOzLLzvNuFrisErisHtdf273esHtl3htijsGxNFsGtRdcrisSrislxMErgxYF0FGznHtk5KtczkG0UItFxkExSFzhT0ZDtRPjfli6ztPe3d0sFtV3rxcFtfne26xRHsYtYfcZj26tT4M3BxeFysFx0ItMrixlE2GsG2DzZF2HxkDyjF14Dtejy0Drg62zuGzfKshtU1qH0EHteld2ZtixUHtY2w3WztDtWR6LtVxTG0ITxVEtWZeZxUGsYtjh6fuDDrgjtDsntFCsbd4w32uFFsTTxpEuFzVQ2FtXePlgP1NDtVzfQtDxcDrgtFgrgscxcDzpOxuEzn9znOzLNzxh2lzvYsZtkztS313Dzx9zxWzLDzvZ3BtijkPeR2QzvQsexaErgxaEzvO4tuA0EIzr9zrHzHLzrZ4ZzqXskxZDzp9zplzFKzoZtVdRZx0DzkOsdxVEzg9zggy6GzcZtSZikYPUR35zhQsaxaE2qxaDzmOxSLzm9zmHzHLzrZtX2I3GtiznQsfxWFzjO3JuAtkzj9zjOy8HzfZtT24uBtP3rzgQsbxXDrg2XzhOteld2ez1Izj9zjHzDIzkZtWlccPeR2PzmQsexZDrgxZEzoOtkVuAtkzm9zmOzFKzoNuFrisErisHtdf263d2H6dtTYRekjsGxRFsG2ttcrisSrislxMErgxcJ0HGzqI5Mtc0AG0WIslxoIxWFzsTtUR21tPjfl2ntVzVFxYD2m3zxcFtfne3CxRHsYtYfcZj26tT4U3BxeFywFx0ItMrixlE2GsG2DzhJ2LxoDynFtZ2Utejy4DrgtZUz2GznKshtU15H0MHteld2ZtixUHtY2w3azxDtWR6btVxTG0MTxVEtWZeZxUGsYtjh6ruDDrgjtDsntFCsbd4032uFFsTTxpEuFzVQ0GEzXQsmxUDzZOtkVuAtkzX9zXOy4FzZZtT22uBtP1zSsbxXDrg149zhkzDIzkNuFriuFsEritR2psb2y6W2DrisSrisHHrg6ftgcfi5RztDxOD0KLtjxTJ2Btl4PtijsGtX2Z33tisExWJ23xPD2XtjkRk2hxWHysDxIF3ItRjl3ayIMtTfl34sAx1H4isBxyNznF2CzULzRQyGEtgifUlTk3CteR2b0hHyvOzeJ6K5ttd2ptkf4ktcPj2E2f0CDxeQtg2KtT1WDtPfWPxpFzJEyYF6362zSHsc5n3itVksmy1DrisSri28tdfPV5Utd2Ox7DxmDtT4A2k3GsetZ3ltA2qtYxjDsH2i2mtcjsHysMxTFsG3GtcriuF" } } **4. Percent of total in one line** The entire steps described above can also be straightforwardly condensed into one single-line expression. ```aml Model order_items { ... measure percent_of_total { label: "Percent of Total" type: 'number' definition: @aql (sum(order_items.amount) * 1.0) / (sum(order_items.amount) | of_all(order_items));; } } ``` --- ## Period Comparison :::info Alternative We also support [native GUI-based time period comparison](/docs/reporting/period-comparison.md). ::: ## Introduction Period Comparison is an analytic technique to compare metrics from different time periods, such as comparing the revenue between this year and last year. In this guide, we will walk you through detailed steps on how to calculate period comparison metrics in different approaches for different use cases ## Setup ```aml # e_commerce.dataset.aml Dataset e_commerce { models: [orders, countries] relationships: [ relationship(orders.country_id > countries.id, true), ] } # orders.model.aml ... # countries.model.aml ... ``` ## Method 1: Relative period You may consider writing measure that relatively changes based on the present period or the conditioned period. This is useful when you want to compare a specific KPI from one period to another one relatively. In AQL, we can employ [relative_period()](/reference/aql/relative_period) to perform that result ```aml # orders.model.aml ... measure count_orders { label: "Count Orders" type: "number" definition: @aql count(orders.id) ;; } measure count_orders_previous_year { label: "Count Orders Previous Year" type: "number" // highlight-next-line definition: @aql orders.count_orders | relative_period(orders.created_at, interval(-1 year)) ;; } measure percent_change_previous_year { label: "Percent Change Previous Year" type: "number" definition: @aql safe_divide( (orders.count_orders - orders.count_orders_previous_year)*1.0, orders.count_orders_previous_year );; } ``` *Example 1* *Example 2* ## Method 2: Fixed period vs fixed period :::tip Note that the filter condition applied at the exploration layer is combined with the measure condition using `AND` logic. Hence the measure condition may yield an inaccurate result if it doesn’t cover the entire exploration condition’s time range. E.g. `count_orders_2023` will return no data if there is an exploration condition `orders.created_at matches @(2024)` applied If you would like to use filter condition together with a defined period, check out the [*method 3*](#method-3) and [*method 4*](#method-4) below. It will provide a clearer understanding of how the filter condition and defined period interact ::: In case you prefer to maintain a fixed period within the measure definition. You can apply [where()](/reference/aql/where) along with a time operator to filter the specific period from which you want to derive the measure ```aml # orders.model.aml ... measure count_orders_2023 { label: "Count Orders in 2023" type: "number" // highlight-next-line definition: @aql orders.count_orders | where(orders.created_at matches @2023) ;; } measure count_orders_this_year { label: "Count Orders this Year" type: "number" // highlight-next-line definition: @aql orders.count_orders | where(orders.created_at matches @(this year)) ;; } measure percent_change_2023_this_year { label: "Percent Change 2022 and This Year" type: "number" definition: @aql safe_divide( (orders.count_orders_this_year - orders.count_orders_2023)*1.0, orders.count_orders_2023 );; } ``` *Example 1* *Example 2* ## Method 3: Fixed period and fixed granularity {#method-3} If you need to compare a specific period to a series of consecutive periods, you can employ the [of_all()](/reference/aql/of_all) function to fulfill this use case. ```aml # orders.model.aml ... measure count_orders { label: "Count Orders" type: "number" definition: @aql count(orders.id) ;; } measure count_orders_fixed_holiday_season_2022 { label: "Count Orders Holiday Season 2022" type: "number" definition: @aql orders.count_orders | where(orders.created_at matches @2022-12) | of_all(orders) ;; } measure percent_difference_fixed_holiday_season_2022 { label: "Percent Diffence to Fixed Holiday 2022" type: "number" definition: @aql safe_divide( orders.count_orders*1.0, orders.count_orders_fixed_holiday_season_2022 ) ;; } ``` _Example_ ## Method 4: Fixed period and dynamic granularity {#method-4} Similar to Method 2, this approach enables explicit filtering on the desired period. However, it also provides the flexibility to separate its context from the filter conditions which is applied at the exploration layer. We’ll employ [exact_period()](/reference/aql/exact_period) to perform this use case. In this example, we’ll compare a metric at an arbitrary period to Holiday Season period ```aml # orders.model.aml ... measure count_orders { label: "Count Orders" type: "number" definition: @aql count(orders.id) ;; } measure count_orders_holiday_2022 { label: "Count Orders Holiday Season 2022" type: "number" // highlight-next-line definition: @aql orders.count_orders | exact_period(orders.created_at, @2022-12-01 - 2023-12-31);; } measure percent_change_to_holiday_2022 { label: "Percent Difference to Holiday Season 2022" type: "number" definition: @aql safe_divide(orders.count_orders*1.0, orders.count_orders_holiday_season_2022) ;; } ``` *Example 1* *Example 2* *Example 3* The `exact_period()` function ensures that the condition applied to the `created_at()` field at the exploration level does not affect this measure. However, if `created_at()` is used as a dimension and the exploration is set to a smaller granularity, the comparison will be between two periods at that specific level of detail. For example, if `created_at()` is set to a daily granularity and the condition is applied as `last month`, the exploration will provide a day-by-day comparison between the previous month and the holiday season of 2022. ## Method 5: Compound annual growth rate (CAGR) For multi-year growth, a single year-over-year number can be misleading. CAGR smooths growth across N years into one figure, useful for "average annual growth across the period" headlines. Build it from two `relative_period()` metrics (current revenue and revenue N years ago). Then apply the CAGR formula: ```aml metric revenue_2_years_ago { label: 'Revenue 2 Years Ago' type: 'number' definition: @aql revenue | relative_period(orders.created_at, interval(-2 years)) ;; } metric revenue_2y_cagr { label: '2-Year CAGR' type: 'number' definition: @aql (pow(revenue / revenue_2_years_ago, 1.0 / 2) - 1) * 100 ;; } ``` The formula is `(end / start)^(1/n) - 1`. For an N-year CAGR, replace the `2`s with N. Multiply by 100 to express as a percent. This composes with any dimension. Drop `revenue_2y_cagr` alongside `merchants.name` to see each merchant's 2-year growth rate, or `categories.name` for category-level CAGR. --- ## Ranking ## Introduction The requirement to rank items in a report is a common business requirement. For example, you need to know the top-performing products in a certain aspect so you can double-down on them, or knowing the worst performing items so you can investigate for further problems. In this guide, we will walk you through some examples of how to use the `rank()` function in Holistics to answer the following questions: - What are the products with the highest sales in the whole platform? - What are the products rankings within each category? - Compare products’ ranks within the category and across all categories Overall, ranking falls into two categories: Dynamic Ranking and Static Ranking. - **Dynamic Ranking:** Affected by filters and dimensions, and is a metric in Holistics. - **Static Ranking:** Not affected by filters and dimensions. A Static Ranking field is a dimension. The first two questions can be answered by a ranking metric (dynamic ranking), while the third question need a ranking dimension (static ranking) ## Example 1: Ranks all products by total sales ### Setup In this example, the `e_commerce` dataset will contain the following models: `order_items` , `products` . ```aml // order_items.model.aml Model order_items.model.aml { ... dimension order_id {...} dimension product_id {...} dimension quantity {...} } // products.model.aml Model products { ... dimension id {...} dimension price {...} dimension category_id {...} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [order_items, products] relationships: [ relationship(order_items.product_id > products.id, true) ] metric revenue { label: 'Revenue' type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price);; } metric ranking { label: 'Ranking' type: 'number' definition: @aql rank(order: total_sales | desc());; } } ``` ### High-level flow 1. **Create a Revenue metric:** In the `e_commerce` dataset, we create a simple `revenue` metric that calculate order value by summing over `order_items.quantity` multiplied by `products.price`. 2. **Create a Ranking metric:** Use the `rank()` function to take `revenue` as in put and generate ranking numbers in descending order (larger revenue value comes first) 3. **Visualize:** Include the Ranking metric with Product Name in the visualization to answer your questions ### Implementation #### 1. Create a Revenue metric In the definition file of the `e_commerce` dataset, we can easily create a **Revenue** metric that combines fields from two different models (`order_items` and `products`). This will be the basis for our ranking: ```aml Dataset e_commerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql coalesce( order_items | sum(order_items.quantity * products.price), 0 ) ;; } } ``` In cases where a dimension (like products, or users) have no order records, the corresponding revenue will be 0 instead of NULL. This way we do not need to worry about different ways that different databases handle NULL when ranking. #### 2. Create a Ranking metric We continue to create the **Ranking** metric that use the **Revenue** metric as the ordering field. The syntax `revenue | desc()` is necessary if we want the revenue values are ranked in descending order. ```aml Dataset e_commerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } metric ranking { label: 'Ranking' type: 'number' definition: @aql rank(order: revenue | desc());; } } ``` #### 3. Visualize You can combine the Ranking metric with any dimensions to calculate it ranking basing on revenue. For example, here we visualize the ranking of Products: The ranking metric can also be used to filter. For example, we can choose to display the top 3 products by revenue: ## Example 2: Ranking within a category Sometimes knowing what sells the most across the whole platform is not insightful enough - we want to know the top products within each category. In this example, we will explore how to answer this question. ### Setup The setup will be quite similar to Example 1. The only difference is that we will add the `categories` model to the dataset: ```aml // order_items.model.aml Model order_items.model.aml { ... dimension order_id {...} dimension product_id {...} dimension quantity {...} } // products.model.aml Model products { ... dimension id {...} dimension price {...} dimension category_id {...} } // merchants.model.aml Model merchants { ... dimension id {...} dimension name {...} } // categories.model.aml Model categories { ... dimension id {...} dimension name {...} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [order_items, products, merchants] relationships: [ relationship(order_items.product_id > products.id, true) relationship(products.merchant_id > merchants.id, true) relationship(products.category_id > categories.id, true) ] metric revenue { label: 'Revenue' type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price);; } metric ranking_by_cate { label: 'Ranking by Cate' type: 'number' definition: @aql rank(order: revenue | desc(), partition: categories.name);; } } ``` ### High-level flow 1. **Create a Ranking by Category metric:** In this new ranking metric, we will include **Category Name** as the partition field 2. **Visualization:** For this ranking metric with partition to work, we will need to include the partition field (**Category Name**) field in the visualization. ### Implementation #### 1. Create a Ranking by Category metric When defining the ranking metric, we reuse the Revenue metric that we have defined. We will pass the `categories.name` field into the `partition` argument of the `rank()` function to specify that the ranking should be calculated within a category: ```aml Dataset e_commerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql order_items | sum(order_items.quantity * products.price) ;; } metric ranking_by_cate { label: 'Ranking by Category' type: 'number' definition: @aql rank(order: revenue | desc(), partition: categories.name);; } } ``` #### 2. Visualization When specifying the `categories.name` field as the partition field, we must include it in the visualization for the ranking to work correctly. Below is the result, where we only display products with rank from 1 to 3 within a category. ## Example 3: Static Product Ranking The two ranking examples we have done so far fall under the category of **“Dynamic Ranking”.** The ranking is dynamic in the sense that the rank values are affected by new filters and dimensions that we include in the visualization. For example, without any filter, the product **Chips 1** has rank 5, but when applying the filter **Category Name is “Snack”**, the ranking is re-calculated, and Chips 1 has rank 1. What if we want to display only Products in the Snacks category, but still retain their overall rankings? In this example, we will explore how to produce a **Static Ranking** that is unaffected by filters. ### Setup The only difference with the other examples is how we create the ranking field. In previous examples, the ranking fields are **metrics,** but in static ranking, the field will be a **dimension**. ```aml // order_items.model.aml Model order_items.model.aml { ... dimension order_id {...} dimension product_id {...} dimension quantity {...} } // products.model.aml Model products { ... dimension id {...} dimension price {...} dimension category_id {...} } // merchants.model.aml Model merchants { ... dimension id {...} dimension name {...} } // categories.model.aml Model categories { ... dimension id {...} dimension name {...} } // e_commerce.dataset.aml Dataset e_commerce { ... models: [order_items, products, merchants] relationships: [ relationship(order_items.product_id > products.id, true) relationship(products.merchant_id > merchants.id, true) relationship(products.category_id > categories.id, true) ] metric revenue { label: 'Revenue' type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price);; } dimension product_ranking_dim { label: 'Product Ranking (Dimension)' type: 'number' model: products definition: @aql rank(order: revenue | dimensionalize(products.id) | desc());; } } ``` ### High-level Flow 1. **Create a Ranking dimension:** Instead of creating a ranking as a metric, now we will define it as a dimension. 2. **Visualization:** For this ranking metric with partition to work, we will need to include the partition field (**Category Name**) field in the visualization. ### Implementation #### 1. Create a Ranking Dimension We will reuse the `revenue` metric in the dataset as the basis for the ranking. Then, we define the `product_ranking_dim` dimension in the dataset file, but specify that this dimension belongs to the `products` model. ```aml Dataset e_commerce { ... metric revenue { label: 'Revenue' type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price);; } dimension product_ranking_dim { label: 'Product Ranking (Dimension)' type: 'number' model: products definition: @aql rank(order: revenue | dimensionalize(products.id) | desc());; } } ``` For more details about this process of **dimensionalize a metric**, please refer to our [dimensionalize](/reference/aql/dimensionalize) doc. #### 2. Visualization To compare the result created by the ranking metric and dimension, we will use the following fields: Product ID, Product Name, Ranking, Product Ranking (Dimension). Without any filtering, the two return the same ranking values: Now if we add **Category Name is “Audio Gadget”** filter, we can see that the **Ranking metric**’s values are re-calculated, while the **Product Ranking dimension**’s values are maintained. There are gaps between the values because products of other categories have been removed from the visualization. ## Example 4: Performance tiers with `ntile()` Sometimes you don't want exact ranks. You want to bucket items into tiers (top 25%, bottom quartile, etc.). [`ntile()`](/reference/aql/ntile) splits an ordered set into N equal-sized buckets. The setup is the same as Example 1. We define a dimension on `products` that assigns each product to a quartile based on revenue: ```aml Dataset e_commerce { ... metric revenue { ... } dimension product_performance_tier { model: products label: 'Product Performance Tier' type: 'text' definition: @aql case( when: ntile(4, order: revenue | desc()) = 1, then: 'Top 25%', when: ntile(4, order: revenue | desc()) = 2, then: '25-50%', when: ntile(4, order: revenue | desc()) = 3, then: '50-75%', else: 'Bottom 25%' ) | dimensionalize(products.id) ;; } } ``` `ntile(4, order: revenue | desc())` ranks products by revenue and divides them into 4 buckets. Bucket 1 contains the top 25%. We wrap it in `dimensionalize(products.id)` so the tier is fixed to each product and reusable as a regular dimension. Use it to analyze the distribution of products across tiers: ```aml explore { dimensions { products.product_performance_tier } measures { product_count: count(products.id), total_revenue: revenue, avg_revenue_per_product: revenue / count(products.id) } } ``` Swap `4` for `10` to get deciles, or use `ntile(100, ...)` for percentiles. --- ## Semi-Additive Calculation ## Introduction Any calculations can be either Additive, Semi-additive, or Non-additive. - **Additive Metrics**: are metrics that can be aggregated (or summed) across all the Dimensions that are use with it. For example, the Total Order Items of a Year is the SUM of Total Order Items of each individual Months in that Year. - **Non-additive Metrics**: are metrics that cannot be aggregated (or summed) across any of the dimensions. For example, the Distinct Count of Products over a year is not the Sum of the Distinct Count Products of Each Month in that Year. - **Semi-additive Metrics**: are metrics can be aggregated across some dimensions, but not all dimensions. A typical example that can be referred in the Example below ## Example: Bank Balance For our example, think of a model that tracks bank balances. If you add up everyone's balance, that's the total amount for all users. But when considering time, it's not as straightforward. Say we're looking at a quarter: the balance isn't just adding up each month's amount. Instead, we go by the last recorded balance for that quarter. So, adding up balances depends on what we focus on: time or other factors. This is why we call it 'semi-additive': it can be both additive and non-additive, depending on the situation. As you can see, a simple SUM to aggregate balances returns the wrong result both at the quarter and at the year levels: The total at the quarter level cannot be computed by summing the individual months. Instead, the formula must only consider the last value. ## Possible approaches One of the solution is to search for the Last Date of the Balance Date and compare it with the Date used in the Dimension ```aml metric sum_bank_balance { label: 'Sum Bank Balance' type: 'number' definition: @aql sum(balances.bank_balance) | where(dim_dates.date == max(balances.record_date)) ;; } ``` You will get the below result, the Total Balance of Quarter is the Total Balance of the last Month in that Quarter, instead of summing all the Months in that Quarter However, you might notice that the Total Balance of all Customers looks wrong. It doesn’t take the sum balance of all Customers. To investigate this, you might want to use the actual balance date of each customer. The last balance dates for Hehr Elliott, Molleur Florance, and Francis Kub are January 26th, January 20th, and January 28th. When AQL looks at the latest date for the final two customers, it shows January 26th and January 20th. For Francis Kub, the latest date is January 28th. If no specific customer is selected, the latest date becomes the last date for all customers, which is January 28th. But there's no data for Hehr Elliott and Molleur Florance on this day. So, based on what your company needs, you might want the last balance day for each customer or the last balance day for all of them. ### Calculate the Last Balance Day for All Customers If you want to calculate the Last day for the All Customers, you may want to use the `of_all()` function in your calculation. ```aml metric sum_bank_balance_any_customers { label: 'Sum Bank Balance Any Customers' type: 'number' definition: @aql sum(balances.bank_balance) | where(dim_dates.date == ( max(balances.record_date) | of_all(balances.user_name) )) ;; } ``` You will get the result like below ### Calculate Last Balance Day for each Customer. The other Solution is to calculate the Last Balance Day for each individual customer, and then sum the number together. You can leverage the function `unique()` to calculate the **Last Balance day** by each Customer ```aml metric sum_bank_balance_each_customers { label: 'Sum Bank Balance Each Customers' type: 'number' definition: @aql unique(balances.user_name) | select( user_name: balances.user_name, sum_balance: (sum(balances.bank_balance) | where(dim_dates.date == max(balances.record_date))) ) | sum(sum_balance) ;; } ``` You will get the result like below --- ## Level of Detail Patterns For the concept, vocabulary, and decision table, see **[Learn AQL → Level of Detail](/as-code/aql/learn/level-of-detail)**. This page walks through four real-world patterns end to end. The simplest LoD case (percent of total with `of_all()`) has its own dedicated page: **[Percent of Total](/as-code/aql/cookbook/aql-percent-of-total)**. The patterns below cover the trickier shapes. ## Nested aggregation vs `dimensionalize()` These two come up most often, and on the surface they can produce similar results. They behave differently under filters: - Use **`dimensionalize()`** when you want the aggregation usable as a *group-able dimension* (bins, histograms, cohort buckets). - Use **nested aggregation** for everything else. It's the more intuitive default. A concrete difference: with a filter like `orders.created_at matches 'last month'`, "Max of (AOV measure)" computes AOV from only last month's orders, while "Max of (AOV dimension via `dimensionalize`)" computes AOV across all-time orders for users who placed an order last month. ## Use case 1: higher LoD. nested aggregation **Question:** "What is the maximum AOV (average order value) of a customer in each country?" Two-step calculation: 1. AOV per customer (high Calc LoD: needs `users.id`). 2. Max of those AOVs grouped by country. The customer-level AOV measure: ```aml Model users { measure aov { definition: @aql sum(order_items, order_items.quantity * products.price) * 1.0 / count_distinct(orders.id) ;; } } ``` The dataset-level metric that nests it: ```aml Dataset ecommerce { metric max_user_aov { definition: @aql users | group(users.id) | select(user_aov: users.aov) | max() ;; } } ``` When the report groups by country, AQL first computes AOV per user, then takes the max within each country group. ## Use case 2: fixed LoD. `dimensionalize()` **Question:** "Show countries colored by the highest customer AOV available there." Here AOV behaves like a customer-level *property*, not a measure that recomputes per report. Define it as a dimension fixed to `users.id`: ```aml Model users { dimension aov_dim { type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price) * 1.0 / count_distinct(orders.id) | dimensionalize(users.id) ;; } } ``` And in the dataset: ```aml Dataset ecommerce { metric max_user_aov { definition: @aql max(users.aov_dim) ;; } } ``` Same shape as use case 1, but the AOV is locked to the user grain. See the [comparison note above](#nested-aggregation-vs-dimensionalize) for when this matters. ## Use case 3: customer segmentation flag. `dimensionalize()` for filtering **Question:** "Identify VIP customers (lifetime revenue > $10k and 5+ orders), then break down orders by VIP vs. non-VIP." This is `dimensionalize()` applied to a *boolean expression*. The aggregation collapses to a per-customer flag that other reports can filter and group by. ```aml dimension is_vip_customer { model: users label: 'Is VIP Customer' type: 'truefalse' definition: @aql (sum_revenue > 10000 and count(orders.id) >= 5) | dimensionalize(users.id) ;; } ``` Now any report can filter on `users.is_vip_customer is true` or group by it. The flag is fixed to the user grain regardless of what the surrounding report does. That's the point of `dimensionalize()`. Same shape as use case 2, but the output is a category, not a number. ## Use case 4: per-level percentages. `is_at_level()` **Question:** In a pivot with `Continent → Country → City`, compute percent-of-parent at every level. ```aml case( when: is_at_level(cities.name), then: sum(sales.amount) / (sum(sales.amount) | of_all(cities.name)), when: is_at_level(countries.name), then: sum(sales.amount) / (sum(sales.amount) | of_all(countries.name)), when: is_at_level(countries.continent), then: sum(sales.amount) / (sum(sales.amount) | of_all(countries.continent)), else: 1 ) ``` [`is_at_level()`](/reference/aql/miscellaneous-functions#is_at_level) detects which dimension is active and picks the right denominator. The `else: 1` covers the Grand Total row. ## See also - [`of_all()`](/reference/aql/of_all): function reference - [`dimensionalize()`](/reference/aql/dimensionalize): function reference - [Nested aggregation guide](/as-code/aql/cookbook/aql-nested-aggregation) - [Percent of total guide](/as-code/aql/cookbook/aql-percent-of-total) --- ## Metrics by Example The Cookbook landing page. Three ways to find what you need: - **[By question](#by-question)**: "I want to do X" → the page that shows you how. - **[By metric shape](#by-metric-shape)**: what shape is the metric, and which function handles it. - **[Snippet gallery](#snippet-gallery)**: copy-paste AML for the most common patterns. ## Shared e-commerce schema Most examples on this page and across the Cookbook use this schema: **Models:** `users`, `orders`, `order_items`, `products`, `categories`, `merchants`, `cities`, `countries`. **Key relationships:** - `order_items` → `orders` (many:1) via `order_id` - `order_items` → `products` (many:1) via `product_id` - `orders` → `users` (many:1) via `user_id` - `users` → `cities` → `countries` (geographic chain) - `products` → `categories` (many:1) - `products` → `merchants` (many:1) ## By question When you know the *question* but not the *AQL feature*: | I want to… | Go to | |---|---| | Slice a metric by a dimension and add filters | [Learn → Filtering](/as-code/aql/learn/filtering), [Grouping](/as-code/aql/learn/grouping) | | Define a reusable metric or AQL dimension | [Learn → Defining a Metric](/as-code/aql/learn/defining-a-metric) | | Build a metric that spans multiple models | [Learn → Cross-Model Queries](/as-code/aql/learn/cross-model) | | Compute a ratio (e.g. completion rate) | [Learn → Defining a Metric](/as-code/aql/learn/defining-a-metric#metrics-compose), [Cookbook → Percent of Total](/as-code/aql/cookbook/aql-percent-of-total) | | Segment customers with a boolean flag (VIP, churned, etc.) | [Cookbook → LoD Patterns](/as-code/aql/cookbook/level-of-detail#use-case-3-customer-segmentation-flag--dimensionalize-for-filtering) | | Bucket records into tiers (deciles, quartiles) | [Cookbook → Ranking](/as-code/aql/cookbook/aql-rank#example-4-performance-tiers-with-ntile) | | Compute percent of total | [Learn → Level of Detail](/as-code/aql/learn/level-of-detail), [Cookbook → Percent of Total](/as-code/aql/cookbook/aql-percent-of-total) | | Aggregate already-aggregated data (avg of monthly totals, max of per-user metric) | [Learn → Nested Aggregation](/as-code/aql/learn/nested-aggregation), [Cookbook → Nested Aggregation](/as-code/aql/cookbook/aql-nested-aggregation) | | Rank items, with or without partitions | [Cookbook → Ranking](/as-code/aql/cookbook/aql-rank) | | Year-over-year, quarter-over-quarter comparison | [Learn → Time Comparisons](/as-code/aql/learn/time-comparisons), [Cookbook → Period Comparison](/as-code/aql/cookbook/aql-period-comparison) | | Smooth a time series with a moving average | [Cookbook → Moving Average](/as-code/aql/cookbook/aql-moving-average) | | Compound annual growth rate (CAGR) | [Cookbook → CAGR](/as-code/aql/cookbook/aql-period-comparison#method-5-compound-annual-growth-rate-cagr) | | Analyze customer order frequency / cohort distributions | [Cookbook → Customer Order Frequency](/as-code/aql/cookbook/aql-customer-order-frequency) | ## By metric shape Most metric questions resolve once you answer two questions: 1. **One aggregation, or several?** A single `sum`/`count`/`avg` is simple aggregation. Anything that aggregates an already-aggregated value (avg of monthly totals, percent of total, semi-additive sums) is multi-aggregation. 2. **What's the relationship between aggregations or filters?** Conditions, time periods, subgroups, accumulation, or nesting each map to a different AQL function. ### Single-aggregation metrics | Shape | Example | Where to go | |---|---|---| | Multiple tables/models | `order_items \| sum(order_items.quantity * products.price)` | [Cross-Model Queries](/as-code/aql/learn/cross-model) | | Filtered by condition | `avg(order_items.quantity) \| where(order_items.created_at matches @(last 3 months))` | [where()](/reference/aql/where) + [logical operators](/reference/aql/operator#logical-operator) | | Running / accumulated | `orders.total \| running_total(orders.created_at \| year())` | [Cumulative Metrics](/as-code/aql/cookbook/aql-cumulative-metrics), [window_sum](/reference/aql/window_sum) | | Different time period | `orders.count \| relative_period(orders.created_at, interval(-1 year))` | [Period Comparison](/as-code/aql/cookbook/aql-period-comparison) | ### Multi-aggregation metrics | Shape | Example | Where to go | |---|---|---| | Scalar combination of metrics | `sum(products.price) / count(products.id)` | Just write `metric_a / metric_b` | | Aggregations linked by a condition | `sum(balances.bank_balance) \| where(dim_dates.date == max(balances.record_date))` | [Semi-Additive Calculation](/as-code/aql/cookbook/aql-semi-additive-calculation) | | Calculated across different subgroups | `(order_items.total_sales*1.0) / (order_items.total_sales \| of_all(order_items))` | [Level of Detail Patterns](/as-code/aql/cookbook/level-of-detail), [Percent of Total](/as-code/aql/cookbook/aql-percent-of-total) | | Nested aggregations | `users \| group(month(users.sign_up_at)) \| select(count(users.id)) \| avg()` | [Nested Aggregation](/as-code/aql/cookbook/aql-nested-aggregation) | ## Snippet gallery Copy-paste-able definitions for the most common patterns. ### Simple aggregation Simple aggregation works by taking a table and an expression to evaluate over each row of that table. ```js title="Total Price of all Products after Discount" // `products` source table expression can be omitted products | sum(products.price * products.discount) ``` ```js title="Average Ordered Quantity of Order Items" avg(order_items.quantity) ``` ### Conditional aggregation ```js title="Average Ordered Quantity of Order Items over the Last 3 Months" avg(order_items.quantity) | where(order_items.created_at matches @(last 3 months)) ``` ### Aggregation with scalar functions ```js title="Average Price of Product" sum(products.price) / count(products.id) ``` ```js title="Total Actual Product Price" products | sum(products.price * products.discount) ``` ### Cross-model aggregation ```js title="Gross Merchandise Value (GMV)" order_items | select(order_items.quantity * products.price) | sum() // or more succinctly order_items | sum(order_items.quantity * products.price) ``` ```js title="GMV of refunded orders" order_items | sum(order_items.quantity * products.price) | where(orders.status != 'refunded') ``` See [Cross-Model Queries](/as-code/aql/learn/cross-model) for the full walkthrough. ### Cumulative metrics ```js title="Cumulative Sum of GMV" // orders.value = order_items | sum(order_items.quantity * products.price) sum(orders.value) | running_total() ``` ```js title="Running Percentage of Asia GMV over global GMV" (sum(orders.value) | running_total() | where(continents.name == 'Asia')) / (sum(orders.value) | running_total()) ``` See [Cumulative Metrics](/as-code/aql/cookbook/aql-cumulative-metrics) for the full walkthrough. ### Window functions ```js title="Running Sum of Count over Order Status" window_sum(count(orders.id), order: orders.status) ``` ### Multiple aggregation levels ```js title="Average GMV per Customer" orders | group(users.id) | select(sum(orders.value)) | avg() ``` See [Nested Aggregation](/as-code/aql/cookbook/aql-nested-aggregation). ### Custom relationships ```js title="Sum of revenue using custom relationships" sum(order_items.revenue) | with_relationships( order_items.order_id > orders.id, order_items.country_id > countries.id, ) ``` See [`with_relationships`](/reference/aql/with_relationships). ### Semi-additive metrics ```js title="Sum Bank Balance Any Customers" sum(balances.bank_balance) | where(dim_dates.date == ( max(balances.record_date) | of_all(balances.user_name) )) ``` See [Semi-Additive Calculation](/as-code/aql/cookbook/aql-semi-additive-calculation). --- In Holistics, data exploration is typically done through the UI, which generates AQL queries in the [`explore { }`](/reference/aql/explore-expression) format. The examples here show AML metric definitions. In practice, you define metrics once in AML and the UI generates the explore syntax for you. --- ## Enabling AQL for Legacy Users :::info This guide is only for users who signed up **before April 19, 2024**. Users who signed up on or after this date have AQL enabled by default and can skip this guide. ::: ## Enabling AQL For legacy users, enabling AQL for your dataset involves a simple process of adding version flagging at the dataset level. You just need to incorporate a version flag in your dataset definition as: `__engine__: 'aql'`. This will activate AQL within your specified dataset while other datasets continue to operate using the prior engine. ```aml Dataset aql_dataset { __engine__: 'aql' //turn this Dataset to using AQL Engine data_source_name: 'your_datasource_name' models: [ ] relationships: [ ] } ``` :::warning Important Note We suggest creating a duplicate of the dataset you wish to apply AQL to for backward compatibility. You can then add version flags to this cloned dataset. Once you've tested AQL on the clone, you are free to enable AQL for your main dataset. If you find that the new AQL engine impacts your existing reports, you can conveniently remove the version flags from the concerned dataset. ::: --- ## Learn AQL from SQL background This guide helps SQL practitioners understand AQL's metric-centric paradigm and migrate their analytics workflows to leverage Holistics' semantic layer capabilities. ## Target Audience - Data analysts comfortable with SQL looking to adopt semantic modeling - Analytics engineers migrating from SQL-based pipelines - Teams evaluating AQL for scalable analytics infrastructure ## What is AQL? AQL (Analytics Query Language) is Holistics' semantic modeling language that sits between your data warehouse and business users. Instead of writing SQL queries repeatedly, you define metrics, dimensions, and relationships once, creating a reusable semantic layer. **Traditional SQL workflow:** ``` Data Warehouse → SQL Queries → Reports ``` **AQL workflow:** ``` Data Warehouse → Semantic Layer (AQL) → Self-Service Analytics ``` ## Fundamental Shift in Thinking When transitioning from SQL to AQL, the most significant change is how you approach building analytics. While SQL encourages thinking in terms of data transformations, AQL promotes thinking in terms of business metrics. **SQL starts with raw tables and transforms them step by step:** 1. Transform the raw orders data to calculate daily sales 2. Transform those daily totals to get monthly aggregates 3. Transform the monthly data to calculate what users asked for - growth rates Each query builds a pipeline where output feeds into the next transformation. **AQL starts with what users need and works backwards:** 1. What metrics do users want? -> Month-over-month growth rate 2. What components can be used to build it? -> Current month sales and previous month sales 3. What existing metrics can we reuse? -> sales metric 4. What data is needed? → The orders table with amount field This approach creates a library of reusable metrics that combine flexibly for different analyses, rather than single-purpose SQL queries that solve one specific problem. ### Practical Example: Period-over-Period Growth **SQL's Sequential Build-up:** ```sql -- Layer 1: Daily aggregation WITH daily_sales AS ( SELECT DATE(order_date) as date, SUM(amount) as sales FROM orders GROUP BY DATE(order_date) ), -- Layer 2: Roll up to monthly monthly_sales AS ( SELECT DATE_TRUNC('month', date) as month, SUM(sales) as total_sales FROM daily_sales GROUP BY DATE_TRUNC('month', date) ), -- Layer 3: Compute growth metrics monthly_growth AS ( SELECT month, total_sales, LAG(total_sales) OVER (ORDER BY month) as prev_month_sales, (total_sales - LAG(total_sales) OVER (ORDER BY month)) / LAG(total_sales) OVER (ORDER BY month) * 100 as growth_rate FROM monthly_sales ) SELECT * FROM monthly_growth; ``` **AQL's Goal-First Definition:** ```aml Dataset ecommerce { // Business need: growth rate metric monthly_sales_growth { definition: @aql safe_divide((sales - sales_last_month) * 100, sales_last_month) ;; } metric sales { definition: @aql sum(orders.amount) ;; } metric sales_last_month { definition: @aql sales | relative_period(orders.created_at, interval(-1 month)) ;; } } ``` Notice how AQL starts with the desired outcome and works backward to identify dependencies, while SQL builds forward through transformation layers. ## From SQL to AQL Components ### SELECT -> explore The equivalent of a SELECT statement in Holistics is an [explore expression](/reference/aql/explore-expression). Unlike SQL, you don't write explore blocks directly - Holistics generates them through the UI when users build reports. Instead of writing queries, you focus on building reusable components: - **Models** - Business entities (users, orders, products) - **Relationships** - How models connect (orders -> users) - **Metrics** - Reusable aggregations (revenue, user count) - **Dimensions** - Attributes and calculated fields These components combine dynamically based on user selections in the UI. **SQL Query:** ```sql SELECT DATE_TRUNC('month', created_at) as month, status, COUNT(*) as order_count, SUM(amount) as revenue FROM orders WHERE created_at >= '2024-01-01' GROUP BY 1, 2 ``` **AQL Components (you define):** ```aml Model orders { dimension created_at { type: 'datetime' } dimension status { type: 'text' } dimension amount { type: 'number' } } Dataset ecommerce { metric order_count { definition: @aql count(orders.id) ;; } metric revenue { definition: @aql sum(orders.amount) ;; } } ``` **Explore (generated by UI):** ```aml explore { dimensions { orders.created_at | month(), orders.status } measures { order_count, revenue } filters { orders.created_at >= @2024 } } ``` ### FROM -> source table When aggregating data in SQL, you must choose a FROM table. This table determines the granularity of your data (assuming you don't cause fan-out with joins): ```sql SELECT SUM(amount) FROM orders -- Aggregates at order level ``` In AQL, all aggregation functions take the first parameter as the source table: ```aml // Explicit: aggregate amount from orders table sum(orders, orders.amount) ``` **Most of the time, you don't need to specify the source table** because AQL can auto-infer it from the expression inside the aggregate: ```aml sum(orders.amount) // Auto-inferred: clearly from orders table ``` This works because when you only reference fields from a single model, AQL knows to use that model as the source table. **Only specify the source table when:** 1. **Cross-model calculations** - When combining fields from multiple models: ```aml // Must specify order_items as base table for revenue calculation sum(order_items, order_items.quantity * products.price) ``` 2. **Complex aggregations** - When nesting aggregations: ```aml // Average number of items per order orders | group(orders.id) | select(sum(order_items.quantity)) | avg() ``` :::tip Knowledge Checkpoint The pipe operator (`|`) chains operations on expressions. In this example, `orders | group(...)` takes `orders` and applies a group on `orders.id` to it. Learn more about [pipe operators in AQL](/reference/aql/operator#pipe). ::: When choosing the source table, always choose the one that makes logical sense for your aggregation. For example, `count(order_items, orders.id)` would count order items instead of orders. ### JOIN ON -> Relationships SQL requires you to join tables explicitly in every query where you need data from multiple tables. In Holistics, you don't define joins - you define the relationships between tables once in the dataset. For example: - one user has many orders - one order has many order_items - many order_items have one product From these relationship definitions, AQL will choose the appropriate join in the generated SQL. ```sql -- SQL: Explicit joins in every query SELECT o.id, u.name, SUM(oi.quantity * p.price) as revenue FROM orders o JOIN users u ON o.user_id = u.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id GROUP BY o.id, u.name ``` ```aml -- AQL: Define relationships once Dataset ecommerce { relationships [ // many orders to one user relationship(orders.user_id > users.id, true), // many items to one order relationship(order_items.order_id > orders.id, true), // many items to one product relationship(order_items.product_id > products.id, true), ] } ``` ### Dimensions vs Columns In SQL, you work with physical columns and create calculated fields in your SELECT statements. In AQL, dimensions represent both physical columns and reusable calculated fields that become part of your semantic model. #### Table column -> dimension In Holistics, you can define calculated dimensions alongside physical columns in any model. These dimensions act like real columns and can reference other columns in their calculations. ```aml Model users { // Physical columns from database dimension id { type: 'number' } dimension created_at { type: 'datetime' } dimension first_name { type: 'text' } dimension last_name { type: 'text' } // Calculated dimensions dimension full_name { label: 'Full Name' type: 'text' definition: @aql concat(users.first_name, ' ', users.last_name) ;; } dimension account_age_days { label: 'Account Age (Days)' type: 'number' definition: @aql date_diff('day', users.created_at, @now) ;; } dimension user_segment { label: 'User Segment' type: 'text' definition: @aql case( when: users.account_age_days < 30, then: 'New User', when: users.account_age_days < 90, then: 'Active User', else: 'Established User' ) ;; } } ``` You can also use window functions in dimensions. **Example: Customer order history analysis** ```aml Model orders { dimension previous_order_date { label: 'Previous Order Date' type: 'datetime' definition: @aql previous(orders.created_at | day(), order: orders.created_at | day()) ;; } } ``` Window functions operate on the complete underlying table. The mental model for dimensions looks like this: ```sql WITH holistics_model AS ( SELECT *, -- all underlying real columns dimension_expression AS dimension_name, window_function_expression AS window_dimension_name FROM underlying_table ) -- Use holistics_model as if it's the underlying table SELECT * FROM holistics_model WHERE ... ``` #### Cross-model dimension Dimensions can directly access fields from related models when there's a many-to-one or one-to-one relationship. If many order items have one product, then it's guaranteed that one order item row only corresponds to one product. This means rows in `order_items` can access columns in `products` as if they are in the same table. ```aml Model order_items { dimension quantity { type: 'number' } dimension line_item_revenue { label: 'Line Item Revenue' type: 'number' definition: @aql order_items.quantity * products.price ;; } dimension product_category { label: 'Product Category' type: 'text' definition: @aql categories.name ;; } } ``` This works because: - `order_items` -> `products` (many:1) - `products` -> `categories` (many:1) So each order_item can "see" its product's price and category name directly. #### Dimensionalized Metrics One of AQL's most powerful features is turning metrics into dimensions using `dimensionalize()`. This enables analyses that would require complex subqueries in SQL: **SQL - Customer lifetime value as a dimension:** ```sql WITH customer_ltv AS ( SELECT user_id, SUM(amount) as lifetime_value FROM orders GROUP BY user_id ) SELECT CASE WHEN c.lifetime_value >= 10000 THEN 'VIP' WHEN c.lifetime_value >= 5000 THEN 'High Value' WHEN c.lifetime_value >= 1000 THEN 'Regular' ELSE 'New' END as customer_tier, COUNT(DISTINCT o.user_id) as customer_count, SUM(o.amount) as revenue FROM orders o JOIN customer_ltv c ON o.user_id = c.user_id WHERE o.created_at >= '2024-01-01' GROUP BY 1 ``` **AQL - Same logic as reusable dimensions:** ```aml // Define lifetime value as a dimension dimension customer_lifetime_value { model: users type: 'number' definition: @aql sum(orders.amount) | dimensionalize(users.id) ;; } // Create customer tier based on LTV dimension customer_tier { model: users type: 'text' definition: @aql case( when: customer_lifetime_value >= 10000, then: 'VIP', when: customer_lifetime_value >= 5000, then: 'High Value', when: customer_lifetime_value >= 1000, then: 'Regular', else: 'New' ) ;; } // Now use as normal dimension in explore explore { dimensions { users.customer_tier } measures { customer_count: count(users.id), revenue: sum(orders.amount) } filters { orders.created_at >= @2024 } } ``` The dimensionalized metric calculates each user's lifetime value once and makes it available as a dimension for grouping, filtering, or further calculations. ### Metric In AQL, metrics are building blocks that encapsulate business logic - from simple sums to complex calculations involving multiple tables, filters, and time intelligence. #### Basic Aggregation In SQL, aggregations are always tied to a specific query context. Aggregations in SQL always operate in a specific context (of a GROUP BY or a WHERE). In AQL, **a metric is a first-class citizen** - a reusable custom aggregation function. A basic metric is built like this: ```aml metric sales { definition: @aql sum(orders.amount) ;; } ``` Notice that **you don't specify a specific GROUP BY** or anything. This is one pitfall of people coming from SQL - they think that the grouping should be part of the metric. But in AQL, you should only introduce grouping if absolutely necessary. **The grouping should be chosen dynamically** as part of the explore for end-users, not in the metric definition itself. #### Multi-model metrics When creating metrics that aggregate across multiple models, you must specify the base table for aggregation. This is especially important for cross-model calculations. Since metrics in AQL are standalone, they must bring their own FROM table. When working with fields from a single model, AQL can infer the source table. But when combining fields from multiple models, AQL can't infer which table should be the base for aggregation - you must specify the correct FROM table explicitly. The first parameter tells AQL which table to use as the "FROM" for aggregation: ```aml // Basic revenue metric aggregating at order_items level metric revenue { label: 'Revenue' type: 'number' definition: @aql sum(order_items, order_items.quantity * products.price) ;; } ``` #### Composed metrics Metrics can be built from other metrics, creating a composable library of business logic: ```aml // Base metrics metric total_revenue { definition: @aql sum(order_items, order_items.quantity * products.price) ;; } metric total_orders { definition: @aql count(orders.id) ;; } // Composed metric metric revenue_per_order { label: 'Revenue per Order' type: 'number' definition: @aql safe_divide(total_revenue, total_orders) ;; } ``` #### SUM(CASE WHEN) -> Metrics with where() SQL uses CASE WHEN for conditional aggregation: ```sql SELECT SUM(amount) as total_revenue, SUM(CASE WHEN status = 'delivered' THEN amount END) as delivered_revenue, SUM(CASE WHEN created_at >= '2024-01-01' THEN amount END) as ytd_revenue FROM orders ``` AQL uses the `where()` function to filter any metric: ```aml // Base metric metric revenue { definition: @aql sum(orders.amount) ;; } // Filtered variations metric delivered_revenue { definition: @aql revenue | where(orders.status == 'delivered') ;; } metric ytd_revenue { definition: @aql revenue | where(orders.created_at >= @2024) ;; } // Or apply filters dynamically in explore explore { measures { total: revenue, delivered: revenue | where(orders.status == 'delivered'), cancelled: revenue | where(orders.status == 'cancelled') } } ``` **Important difference:** Unlike SQL, AQL returns `null` (not 0) when no data matches the filter: ```aml // Returns null if no electronics products exist electronics_revenue: revenue | where(categories.name == 'Electronics') ``` #### GROUP BY -> Level of Detail Metrics in AQL can override the default grouping behavior using `of_all()` to create Level of Detail calculations: ```aml // Calculate percent of total - ignoring product dimension metric product_percent_of_total { label: 'Product % of Total Revenue' type: 'number' definition: @aql safe_divide(revenue * 100.0, revenue | of_all(products)) ;; } // Calculate percent within category metric percent_of_category { label: '% of Category Revenue' type: 'number' definition: @aql safe_divide(revenue * 100.0, revenue | of_all(products.name)) ;; } ``` The `of_all()` function tells AQL to calculate the metric without grouping by the specified dimensions, enabling calculations like "percent of total" that would require subqueries in SQL. #### Nested (Multi-Level) Aggregation AQL supports nested aggregations through the `group()` and `select()` pattern. This is equivalent to SQL's subqueries or CTEs but more composable: ```aml // Average monthly customer acquisition metric avg_monthly_new_customers { label: 'Avg Monthly New Customers' type: 'number' definition: @aql users | group(users.created_at | month()) | select(monthly_count: count(users.id)) | avg(monthly_count) ;; } // Max daily revenue metric max_daily_revenue { label: 'Max Daily Revenue' type: 'number' definition: @aql order_items | group(order_items.created_at | day()) | select( daily_revenue: sum(order_items, order_items.quantity * products.price) ) | max(daily_revenue) ;; } ``` This pattern: 1. Groups data by specific dimensions 2. Calculates metrics for each group 3. Applies a second aggregation on the results It's more flexible than SQL because the same metric works in any context - by year, by country, etc. ### WITH … (CTEs) AQL doesn't require CTEs because its composable nature handles complex logic through: 1. **Reusable dimensions and metrics** - Define once, use everywhere 2. **Pipe operators** - Chain operations naturally 3. **Table functions** - `group()`, `select()`, `filter()` replace CTE patterns **SQL with CTEs:** ```sql WITH monthly_sales AS ( SELECT DATE_TRUNC('month', order_date) as month, SUM(amount) as sales FROM orders GROUP BY 1 ), avg_monthly AS ( SELECT AVG(sales) as avg_sales FROM monthly_sales ) SELECT month, sales, sales - (SELECT avg_sales FROM avg_monthly) as variance FROM monthly_sales; ``` **AQL equivalent:** ```aml // Define the metrics metric sales { definition: @aql sum(orders.amount) ;; } metric avg_monthly_sales { definition: @aql orders | group(orders.created_at | month()) | select(sales) | avg(sales) ;; } metric sales_variance { definition: @aql sales - avg_monthly_sales ;; } // Use in exploration explore { dimensions { orders.created_at | month() } measures { sales, sales_variance } } ``` The AQL approach creates reusable components instead of query-specific CTEs. --- ## Cross-model queries In SQL, every query that touches more than one table starts with a `JOIN`. AQL flips that around: relationships are declared once in your dataset, then any query just references the columns it needs. :::tip Try it interactively Practice in the AQL Playground: [Relationships](https://play.amql.org/learn/relationships). ::: ## The basic move You're querying `order_items`. You want each item's product name (which lives on `products`). Just ask: ```aml order_items | select(order_items.id, products.name) ``` No join. AQL sees that `order_items` is related to `products`, generates the right join under the hood, and gives you the result. ## Why this works A dataset isn't just a list of tables. It's tables plus the **relationships** between them. Each model represents a business entity; each relationship records how those entities connect: - An `order_items` row belongs to one `orders` row. - An `orders` row belongs to one `customers` row. - A `products` row belongs to one `merchants` row. Because the relationships are known up front, AQL can resolve `products.name` from inside an `order_items` query the same way you'd say it in plain English. ## Multi-hop is fine too You can reach further as long as each hop is many-to-one: ```aml order_items | select(order_items.id, merchants.name) ``` Here AQL walks `order_items → products → merchants`. One item belongs to one product, which belongs to one merchant. So the chain is unambiguous. ## The one rule: many-to-one only You can reference the "one" side of a relationship freely. The "many" side is different. There isn't a single row to point at. From an `orders` row, `order_items` is a *collection*, not a value: ```aml // This doesn't work: order_items.value is many rows orders | select(orders.id, orders.discount * order_items.value) ``` To use the many side, you have to **aggregate it first**: ```aml orders | select(orders.id, orders.discount * sum(order_items.value)) ``` `sum()` collapses the collection back to a single number. ## When relationships are ambiguous If two models are connected by more than one path, AQL won't guess. Either deactivate the paths you don't want in the dataset, or override per-metric with [`with_relationships()`](/reference/aql/with_relationships): ```aml sum(order_items.revenue) | with_relationships(order_items.product_id > products.id) ``` ## Next → [Defining a metric](/as-code/aql/learn/defining-a-metric): name an aggregation so you can reuse it instead of writing it out every time. --- ## Defining a metric So far we've been writing aggregations inline. That's the **explore** style: write an AQL expression directly into a report and get an answer. The moment you want to reuse the same aggregation elsewhere, you give it a name and stash it somewhere. That's a **metric**. The two are a pair. An explore expression is a one-shot AQL query. A metric is the same expression named and saved, ready to be referenced from any report. Same language, two ways to use it. :::tip Try it interactively Practice in the AQL Playground: [Reading & Writing Metrics](https://play.amql.org/learn/reading-writing-metrics). ::: ## The simplest possible metric ```aml Model orders { measure total_orders { label: 'Total Orders' type: 'number' definition: @aql count(orders.id) ;; } } ``` Now anywhere you used to write `count(orders.id)`, you can just write `orders.total_orders`. Reports show the same number. Change the formula here once, every report updates. This is the *"same metric, three reports"* behavior previewed in [What AQL Is For](/as-code/aql/learn/what-aql-is-for). You've now seen the syntax that makes it work. [Metric Context](/as-code/aql/learn/metric-context) (next page) explains how the adaptation actually happens. ## Metrics can include their own filter You can bake a `where()` into the definition. The filter rides with the metric: ```aml measure female_user_count { definition: @aql count(users.id) | where(users.gender == 'Female') ;; } ``` Anywhere `female_user_count` shows up, it only counts women. No caller has to remember the filter. ## Metrics compose Once you have a few base metrics, you can build new ones from them: ```aml metric orders_per_user { definition: @aql total_orders / total_users ;; } ``` This is the heart of the language. You build a small vocabulary of trusted metrics, then derive everything else from them. ## Dimensions can be defined in AQL too The same naming pattern works for dimensions: a "virtual column" derived from an AQL expression. Useful when a piece of row-level logic gets reused across reports. ```aml dimension is_electronics { model: products label: 'Is Electronics' type: 'truefalse' definition: @aql categories.name ilike '%Electronics%' ;; } ``` Now any report can filter or group by `products.is_electronics` instead of repeating the `ilike` expression. Same idea as a metric, just at row grain instead of aggregate grain. ## Two homes for a metric Holistics uses two words deliberately: a **measure** is defined on a model, a **metric** is defined on a dataset. They're the same kind of thing (a named, reusable AQL expression) at different scopes. - **In a model** as a `measure {}` when the metric is naturally tied to that one table. - **In a dataset** as a `metric {}` when the metric spans multiple models (e.g. revenue = sum of `order_items.quantity * products.price`). For when to use which, see [Where to Define AQL](/as-code/aql/where-to-define-aql). ## Next → [Metric context](/as-code/aql/learn/metric-context): why the same metric returns different numbers in different reports, and how to control that. --- ## Filtering AQL has two ways to apply a filter. They sound the same, but they target different things: - **[`filter()`](/reference/aql/filter)** filters a **table**. Drop rows that don't match. - **[`where()`](/reference/aql/where)** filters a **metric**. Restrict what the aggregation sees. You'll use both. The difference matters once you start defining reusable metrics. :::tip Try it interactively Practice in the AQL Playground: [Filters](https://play.amql.org/learn/filters) · [Function: filter](https://play.amql.org/learn/function-filter) · [where() vs filter() vs explore filters](https://play.amql.org/learn/where-filter-filters). ::: ## filter(): narrow down a table `filter()` is the SQL `WHERE` clause. Give it a table, give it a condition, get back a smaller table. ```aml orders | filter(orders.status = 'delivered') | select(orders.id, orders.total_value) ``` The condition can be any boolean expression valid on a row. ## where(): narrow down a metric `where()` attaches a filter to an aggregation. It doesn't return a table; it returns a metric that only counts the matching rows. ```aml // Count of delivered orders count(orders.id) | where(orders.status = 'delivered') ``` This is what you want inside a metric definition: ```aml measure delivered_orders { definition: @aql count(orders.id) | where(orders.status = 'delivered') ;; } ``` The filter rides along with the metric everywhere it's used. ## When to use which | You want to… | Reach for | |---|---| | Drop rows before aggregating | `filter()` | | Define a metric that only counts certain rows | `where()` | | Filter on a derived aggregate (e.g. groups with > 100 users) | `filter()` | | Filter on a plain dimension or another measure | `where()` works, and is more reusable | A subtle case: `where()` only accepts certain shapes (dimension-vs-value, dimension-in-list, or dimension-vs-measure). `filter()` accepts any boolean expression but only works on tables. For the full breakdown, see [where vs filter](/reference/aql/where-vs-filter). ## Example: both in the same query Find countries with at least 100,000 users who placed an order: ```aml orders | group(orders.country) | select(orders.country, users_count: count_distinct(orders.user_id)) | filter(users_count >= 100000) ``` `users_count` is a column that only exists *after* the `select()`, so `where()` can't see it. `filter()` is the right tool. ## Next → [Grouping and aggregation](/as-code/aql/learn/grouping): collapse many rows into one number per group. --- ## Grouping and aggregation Grouping is how you go from "every row" to "one row per category". It works the same way as SQL's `GROUP BY`, but in AQL it's its own pipe-able step. :::tip Try it interactively Practice in the AQL Playground: [Aggregate Functions](https://play.amql.org/learn/aggregate-functions) · [Dimensions & Measures](https://play.amql.org/learn/dimensions-measures) · [Function: select & group](https://play.amql.org/learn/function-select-group). ::: ## group() on its own [`group()`](/reference/aql/group) takes a table and returns one row per distinct value of the dimension you pass: ```aml order_items | group(order_items.product_id) ``` By itself that's just a list of unique product IDs. Useful, but the real point is to combine it with aggregations. ## Adding an aggregation Pipe the grouped table into an aggregator like [`count`](/reference/aql/aggregator-functions), [`sum`](/reference/aql/aggregator-functions#sum), or [`avg`](/reference/aql/aggregator-functions). You get one number per group: ```aml order_items | group(order_items.product_id) | select(order_items.product_id, count(order_items.id)) ``` That's "number of items sold, per product". ## Grouping across models Because AQL knows your relationships, you can group by a column on a related model without joining: ```aml order_items | group(products.category_name) | select(products.category_name, sum(order_items.revenue)) ``` ## A subtle thing about group() Inside a `group()`, when you reference rows from other models, you get **all rows related to the current group**, not just one. That's what makes the aggregation work: `count(order_items.id)` inside a group sees every order item for that product, not just one. This is the same trick that powers nested aggregation later. For now, just know: after `group()`, references to related rows fan out into collections that aggregators can collapse. ## The select() must match Whatever you `select()` after a `group()` has to either be the grouping dimension or an aggregation. This fails: ```aml order_items | group(order_items.product_id) | select(order_items.product_id, countries.name, count(order_items.id)) // ^^^^^^^^^^^^^^ not grouped, not aggregated ``` Same rule as SQL's "column must appear in GROUP BY or be aggregated". ## Next → [Cross-model queries](/as-code/aql/learn/cross-model): how AQL reaches into related models without you writing joins. --- ## Level of detail By default, a metric is calculated at whatever grain the surrounding report is using. Group by country → it aggregates per country. Group by month → it aggregates per month. **Level of detail (LoD)** is when you want a metric to aggregate at a *different* grain than the surrounding report uses. Three terms worth pinning down: - **Data LoD**: the grain of the rows you have. `order_items` is high (one row per item); `monthly_sales` is low. - **Visualization LoD**: the dimensions in the report. - **Calculation LoD**: the dimensions the metric actually uses. Usually equal to Visualization LoD; LoD functions are how you make them differ. Three classic cases where Calc LoD ≠ Viz LoD: 1. **Percent of total**: denominator is "across everything", regardless of the current grouping (Calc LoD *lower* than Viz LoD). 2. **Max of per-customer metric by country**: AOV per customer first, then max within country (Calc LoD *higher* than Viz LoD). 3. **Fixed-grain dimension**: a customer's lifetime revenue, used as a filter or category (Calc LoD *fixed*, independent of Viz LoD). AQL has functions for each. They're all "context modifiers" in the sense of [metric context](/as-code/aql/learn/metric-context). :::tip Try it interactively Practice in the AQL Playground: [Level of Detail](https://play.amql.org/learn/level-of-detail) · [Function: of_all](https://play.amql.org/learn/function-of_all) · [Function: dimensionalize](https://play.amql.org/learn/function-dimensionalize). ::: ## of_all(): drop dimensions out of the calculation `of_all()` tells a metric to ignore any dimension from that model. Percent of total: ```aml metric country_share { definition: @aql count(orders.id) * 1.0 / (count(orders.id) | of_all(countries)) ;; } ``` The numerator follows the report's grouping (per country). The denominator ignores `countries` and stays the global total. ## exclude(): same idea, different angle Where `of_all(countries)` says "ignore *only* countries", [`exclude()`](/reference/aql/of_all) says "ignore everything *except* this model's grain". Useful when the report has many dimensions and you want one to win. ## dimensionalize(): make an aggregation usable as a dimension `dimensionalize()` evaluates an aggregation at a fixed grain, then exposes the result as a dimension. This is how you build things like "VIP customer" flags: ```aml dimension is_vip_customer { model: users type: 'truefalse' definition: @aql (sum_revenue > 10000 and count(orders.id) >= 5) | dimensionalize(users.id) ;; } ``` Now `users.is_vip_customer` works like any other dimension (filter on it, group by it). ## Choosing between them Pick by how Calculation LoD relates to Visualization LoD: | Calc LoD vs Viz LoD | Reach for | |---|---| | **Lower** (denominator across everything) | `of_all()` | | **Higher** (aggregate of an aggregate) | [Nested aggregation](/as-code/aql/learn/nested-aggregation) with `group()` + `select()` | | **Fixed** (a reusable dimension at a set grain) | `dimensionalize()` | | **Single grain regardless of report** | `exclude()` | | **Switches by which dimension is active in a pivot** | [`is_at_level()`](/reference/aql/miscellaneous-functions#is_at_level): see [Cookbook use case 4](/as-code/aql/cookbook/level-of-detail#use-case-4-per-level-percentages--is_at_level) | For worked end-to-end examples, see [Level of Detail Patterns](/as-code/aql/cookbook/level-of-detail) in the cookbook. ## Next → [Nested aggregation](/as-code/aql/learn/nested-aggregation): when you need to aggregate the results of an aggregation. --- ## Metric context A metric on its own is just an aggregation formula. The actual number depends on the **context** it runs in: which dimensions group the data, what filters apply, what time window is in play. This is why one metric can power many reports, and why it's worth understanding what's in that context and how to bend it. :::tip Try it interactively Practice in the AQL Playground: [Metric vs Explore (1)](https://play.amql.org/learn/metric-vs-explore) · [Metric vs Explore (2)](https://play.amql.org/learn/metric-vs-explore-2). ::: ## The same metric, three contexts Take a basic revenue metric: ```aml metric revenue { definition: @aql sum(order_items.quantity * products.price) ;; } ``` Drop it into three different reports and you get three different answers: ```aml // Report 1: grouped by country → revenue per country explore { dimensions { countries.name } measures { revenue } } // Report 2: grouped by month → revenue per month explore { dimensions { orders.created_at | month() } measures { revenue } } // Report 3: no grouping, with a filter → grand total for 2024 explore { measures { revenue } filters { orders.created_at matches @2024 } } ``` Same definition. The context (dimensions and filters) gives it a different shape each time. ## What's in the context There are four levers you can pull: | Lever | What it does | Modified with | |---|---|---| | **Condition** | What rows the metric sees | [`where()`](/reference/aql/where) | | **Relationships** | Which join paths the metric uses | [`with_relationships()`](/reference/aql/with_relationships) | | **Level of detail** | What grain the metric aggregates at | [`of_all()`](/reference/aql/of_all), [`exclude()`](/reference/aql/of_all), [`dimensionalize()`](/reference/aql/dimensionalize) | | **Window** | Time-shifted or running calculations | [`relative_period()`](/reference/aql/relative_period), [`running_total()`](/reference/aql/running_total) | Each of these is a function you pipe a metric through to override one piece of its context. ## Quick demos **Override the condition**: count only male users, no matter what the report filters on: ```aml count(users.id) | where(users.gender == 'Male') ``` **Override the relationship**: when two paths exist between models, pick one: ```aml sum(order_items.revenue) | with_relationships( order_items.order_id > orders.id, order_items.country_id > countries.id ) ``` **Override the level of detail**: total order value across *all* dimensions, ignoring what the report is grouping by: ```aml sum(products.price * order_items.quantity) | exclude(order_items) ``` **Add a window**: running total of orders by month: ```aml count(orders.id) | running_total(run: orders.created_at | month()) ``` ## Why this matters Most "advanced" AQL is about *modifying context*. Period-over-period comparisons, percent-of-total, cohort retention: they're all "evaluate this metric in a slightly different context than the surrounding report is using". Once you've internalized that, the rest of the language clicks. ## Next → [Level of detail](/as-code/aql/learn/level-of-detail): controlling what grain a metric is aggregated at. --- ## Nested aggregation(Learn) Some questions are aggregations of aggregations: - "Average **monthly** new customers": first count per month, then average those counts. - "Best month for revenue": first sum per month, then take the max. - "Median customer's lifetime value": first sum per customer, then take the median. In SQL you'd use a subquery or CTE. In AQL, you do it inline with `group() + select()` wrapped in another aggregator. :::tip Try it interactively Practice in the AQL Playground: [Nested Aggregations (1)](https://play.amql.org/learn/nested-aggregations) · [Nested Aggregations (2)](https://play.amql.org/learn/nested-aggregations-2) · [Nested Aggregations (3)](https://play.amql.org/learn/nested-aggregations-3). ::: ## The pattern ```aml ( | group() | select() ) ``` Read it bottom-up: group by something, calculate per group, then aggregate the groups. ## Example: average monthly new customers First a regular base metric: ```aml metric new_customers { definition: @aql count(users.id) ;; } ``` Now nest it: ```aml metric avg_monthly_acquisition { definition: @aql users | group(users.created_at | month()) | select(new_customers) | average() ;; } ``` Walking through: 1. `users`: start from the users table. 2. `| group(users.created_at | month())`: bucket by month. 3. `| select(new_customers)`: count customers in each month. 4. `| average()`: average those monthly counts. Notice the metric has no year-specific logic. Drop it into a report grouped by year and you get the average monthly acquisition *for each year*. Drop it into a report grouped by country and you get average monthly acquisition *per country*. Same metric, adapts to context ([the usual AQL story](/as-code/aql/learn/metric-context)). ## When you need this vs LoD | Question | Tool | |---|---| | Percent of total | [`of_all()`](/as-code/aql/learn/level-of-detail) | | Aggregation of aggregations | Nested (this page) | | Use an aggregation as a dimension | [`dimensionalize()`](/as-code/aql/learn/level-of-detail) | A rough rule: if the outer step needs to *see all the inner results as separate numbers* (to average, max, median, etc.), it's nested. If the outer step just needs the *total ignoring some dimension*, it's LoD. ## More examples - Cookbook: [Max user AOV by country](/as-code/aql/cookbook/aql-nested-aggregation): a cross-model nesting walkthrough (order items → user grain → country max). ## Next → [Time comparisons](/as-code/aql/learn/time-comparisons): the last big context modifier. --- ## The pipe operator The pipe operator `|` takes whatever's on the left and passes it as the first argument to whatever's on the right. It's the syntax that makes AQL feel composable instead of nested. ```aml // These two are equivalent: users | avg(users.age) avg(users, users.age) ``` :::tip Try it interactively Practice in the AQL Playground: [Pipe (1)](https://play.amql.org/learn/pipe) · [Pipe (2)](https://play.amql.org/learn/pipe-2). ::: ## Why it matters AQL is composable by design: every function takes input, produces output, and that output can feed straight into the next function. You don't strictly need pipe for that. `sum(filter(orders, orders.country = 'Singapore'), orders.total_value)` works fine. But it reads inside-out, and three or four steps deep it stops being readable. Pipe is what makes that composability ergonomic. SQL forces one shape (`SELECT ... FROM ... WHERE ... GROUP BY ...`); AQL lets you break a query into small steps and chain them, so the code reads in the same order you think about it: start with a table, narrow it down, aggregate. The pipe is the signal that AQL is built to be read left-to-right, step by step. ## Example Say you want **total order value for Singapore**. You need two steps: - [`filter()`](/reference/aql/filter): keep only Singapore orders. - [`sum()`](/reference/aql/aggregator-functions#sum): add up the totals. With pipes: ```aml orders | filter(orders.country = 'Singapore') | sum(orders.total_value) ``` Read it top to bottom: *take orders, keep only Singapore, sum the totals*. Each pipe feeds its left side into the next function's first argument. :::note Mental model Each step that takes a table walks it **one row at a time**. `filter(orders.country = 'Singapore')` checks the condition on each row; `sum(orders.total_value)` adds the value column row by row. Whenever you write an expression like `orders.country` or `orders.quantity * orders.price`, it's evaluated against whichever row the step is currently looking at (the **current row**). This per-row evaluation is the foundation that filter, group, and aggregate all build on. ::: ## Tables in, scalars out Every AQL expression produces either a **table** (rows you can keep filtering or grouping) or a **scalar** (a single value, the end of a chain). The pipe respects that: - `orders` → a table. You can keep piping. - `orders | filter(...)` → still a table. Keep piping. - `orders | sum(orders.total_value)` → a scalar. The chain ends. There's nothing left to filter. If a step expects a table and you hand it a scalar (or vice versa), the chain breaks. Once you've internalized "what shape is this step's output?", reading AQL gets a lot easier. ## When pipes shine - Long chains (3+ steps): readable top-to-bottom instead of nested. - Reusable fragments: `users | filter(users.is_active)` can be a building block. - Debugging: comment out a single line to see the intermediate result. For the full signature and edge cases, see the [pipe reference](/reference/aql/operator#pipe). ## Next → [Filtering](/as-code/aql/learn/filtering): `where()` vs `filter()`, and when each one applies. --- ## Tables and rows AQL is a data query language. Like SQL or Excel, it operates on **tables**: collections of rows, where each row has columns. Before we touch any function, it helps to get precise about what AQL actually does as it reads your data. :::tip Try it interactively Practice in the AQL Playground: [Metric Expressions](https://play.amql.org/learn/metric-expressions). ::: ## A tiny example Imagine an `order_items` table: | id | order_id | product | quantity | price | |----|----------|---------|----------|-------| | 1 | 1001 | Mug | 2 | 10 | | 2 | 1001 | Pen | 5 | 2 | | 3 | 1002 | Book | 1 | 25 | Three rows, five columns. AQL operates on tables like this one. ## The current row Unlike Excel, you can't point at a single cell. The smallest thing AQL works on is a **row**. When AQL processes a table, it walks through the rows one at a time. The row it's currently looking at is called the **current row**. Inside the current row, you refer to columns by name. So `order_items.quantity` means "the quantity column of whatever row I'm on right now". As AQL walks the table above, that expression evaluates to `2`, then `5`, then `1`. You can combine columns too. `order_items.quantity * order_items.price` is a **row-level expression**: it runs once per row, giving `20`, then `10`, then `25`. ## Iteration That walk-through-the-rows process is called **iteration**. Most AQL functions iterate. A few you'll meet next: - [`select`](/reference/aql/select): walks rows, produces a new table with just the columns you ask for. - [`filter`](/reference/aql/filter): walks rows, keeps only the ones matching a condition. - [`sum`](/reference/aql/aggregator-functions#sum): walks rows, adds up a column into one number. For example, the total revenue across the table: ```aml order_items | sum(order_items.quantity * order_items.price) // walks all 3 rows, computes 20 + 10 + 25 = 55 ``` Don't worry about the `|` yet (that's the next page). The point for now: any expression you hand to one of these functions is evaluated **once per row**. ## Next → [The pipe operator](/as-code/aql/learn/pipe): how AQL chains operations together. --- ## Time comparisons "Revenue this year vs last year" is one of the most common things a dashboard needs. In SQL it's an annoying self-join. In AQL it's a context modifier: take your existing metric, shift its time context, compare. :::tip Try it interactively Practice in the AQL Playground: [Function: date_trunc](https://play.amql.org/learn/function-date_trunc) · [Date/Time Expressions](https://play.amql.org/learn/datetime-expressions). ::: ## relative_period(): shift the metric back in time `relative_period(