Template Variables in Metric Expressions

Templates in the Zing Model Layer

The Zing Modeling Layer already allows you to specify calculations, dimensions, join relationships, and ensure that queries you run adhere to these relationships.

Now, we’re excited to introduce templatized metrics, which are a flexible way to define complex calculations that may have different required inputs – providing the flexibility of dynamic value substitution, with trusted guardrails of defining metric calculations.

Suppose you want to define a brand’s share as a percentage of all sales but you want a user to be able to specify only individual brands. Since you query needs to ‘see’ the sales across all brands even though a user is only requesting one brand, you can’t simply apply a brand filter. This is because that filter would apply to the denominator as well. This is solvable with a view, or subquery but more flexibly, solved with templatized metrics:

"metrics": [
  {
    "description": "Brand Share",
    "expr": "SUM(CASE WHEN \"CarBrands\".\"Brand\" = {{brand}} THEN \"CarBrands\".\"SalesAmt\" ELSE 0 END) / SUM(\"CarBrands\".\"SalesAmt\")",
    "groupBy": {
      "allowed": [],
      "required": [],
      "forbidden": []
    }
  }
]

Now a user can ask “Show me Toyota brand share” and get Toyota (or any other brands) substituted in to the {{brand}}.

This is particularly powerful for ensuring high accuracy natural language AI responses, since all the queries and results are bound to the Zing Data Model (if you’ve specified one). This avoids the common issue of hallucinations (an artifact of LLMs in some instances) when generating arbitrary SQL for a data query.A templatized metric can have multiple templated values – for instance if you wanted a user to be able to specify various time periods (day, month, year) or definitions (rolling 12 months vs current value), you can make those templatized inputs in a metric.

Configuring Templates

Templatized metrics work as part of the Zing Data Layer so when you define a metric, you can optionally use templates within it.

When you include a variable like {{variable_name}} in your metric expression, Zing will intelligently match it against relevant parts of users’ natural language questions. Here is a sample metric definition that uses multiple templates.

{
  "description": "Revenue growth % by period",
  "expr": "((SUM(case when categories.categoryname = '{{category}}' then orderlines.quantity * products.price else 0 end) - LAG(SUM(case when categories.categoryname = '{{category}}' then orderlines.quantity * products.price else 0 end), 1) OVER (ORDER BY date_trunc({{period}}, orders.orderdate))) / LAG(SUM(case when categories.categoryname = '{{category}}' then orderlines.quantity * products.price else 0 end), 1) OVER (ORDER BY date_trunc({{period}}, orders.orderdate))) * 100",
  "groupBy": {
    "allowed": ["orders.orderdate"],
    "required": ["orders.orderdate"],
    "forbidden": []
  },
  "vars": {
    "period": {
      "allowedValues": ["month", "quarter", "year"]
    }
  }
}

In this example:

• The metric calculates period-over-period revenue growth percentage
• {{period}} variable lets users analyze growth by month, quarter, or year
• {{category}} variable allows filtering for specific product categories
• The optional vars object lets you restrict variables to specific values for governance

When users ask natural language questions, Zing automatically matches their intent to your metric definitions. For example, the same metric definition above can answer questions like:

• “What’s the YoY revenue growth for Electronics?”
• “Show me Books revenue growth month over month”

Getting Started

1. Log in to the Zing Data Console, then click on ‘data sources’ and select the data source you’d like to create (or update) a modeling layer for

2. Click the ‘semantic layer’ tab

3. When creating a semantic model, add

4. Upload a semantic model .json file in the format outlined above

5.  Any questions that a user asks with natural language, will adhere to the modeling layer you have uploaded and templatized will be substituted in the the templatized values where appropriate.

    

   Detailed documentation about template variables in metrics is here.The modeling layer functionality is available on the Pro Plan and Enterprise Plan