SQL Queries

SQL queries can be used to provide data to Una components that expect the result of a multidimensional query. These queries must return data in a predefined format.

Result Sets

Multiple result sets (i.e., multiple SELECT statements in the same batch) are required to provide the necessary data. The first set(s) provide metadata information about axis dimensions. The last set provides the actual data.

Metadata

Each metadata result set returns metadata for an axis. The result set must have the following fields, where n is replaced with the axis number (starting at 0) and x is replaced with the dimension number inside the axis (starting at 0):

Field Pattern	Description
M{n}D{x}Dimension	Name of the dimension
M{n}D{x}Key	Member key
M{n}D{x}Name	Member name
M{n}D{x}Caption	Member caption

Example: Returning a Scenario and Period axis with Actual for periods Jan-Mar and Forecast for periods Apr-Dec:

SELECT 
  'Scenario' AS [M0D0Dimension], 
  s.[Key] AS [M0D0Key], 
  s.[Name] AS [M0D0Name], 
  s.[Caption] AS [M0D0Caption],
  'Period' AS [M0D1Dimension], 
  p.[Key] AS [M0D1Key], 
  p.[Name] AS [M0D1Name], 
  p.[Caption] AS [M0D1Caption]
FROM [dbo].[Dim_PC_Scenario] s
INNER JOIN [dbo].[Dim_PC_Period] p ON (
  (s.[Name] = 'Actual' AND p.[Key] BETWEEN 202401 AND 202403)
  OR (s.[Name] = 'Forecast' AND p.[Key] BETWEEN 202404 AND 202412)
)
ORDER BY p.[Path]

Using variable substitution for a tenant-independent query:

SELECT 
  '{Dimension.Scenario}' AS [M0D0Dimension], 
  s.[Key] AS [M0D0Key], 
  s.[Name] AS [M0D0Name], 
  s.[Caption] AS [M0D0Caption],
  '{Dimension.Period}' AS [M0D1Dimension], 
  p.[Key] AS [M0D1Key], 
  p.[Name] AS [M0D1Name], 
  p.[Caption] AS [M0D1Caption]
FROM {DimensionTable.Scenario} s
INNER JOIN {DimensionTable.Period} p ON (
  (s.[p_ScenarioType] = 0 AND p.[Key] BETWEEN 202401 AND 202403)
  OR (s.[p_ScenarioType] = 1 AND p.[Key] BETWEEN 202404 AND 202412)
)
ORDER BY p.[Path]

note

The order in which queries return data is the order in which metadata will be displayed.

Data

The last result set must return the query data. The result set must have the following fields:

Field Pattern	Description
A{n}D{x}Key	Dimension member key
V_{vvv}	Value for measure vvv

Example:

SELECT 
  f.[ScenarioId] AS [A0D0Key], 
  f.[PeriodId] AS [A0D1Key], 
  f.[KpiId] AS [A1D0Key], 
  f.[V_Existing] / f.[V_New] AS [V_Value]
FROM [dbo].[M_Main] f
INNER JOIN [dbo].[Dim_PC_Scenario] s ON (s.[Key] = f.[ScenarioId])
INNER JOIN [dbo].[Dim_PC_Period] p ON (p.[Key] = f.[PeriodId])
INNER JOIN [dbo].[Dim_PC_Kpi] k ON (k.[Key] = f.[KpiId])
WHERE (
  (s.[Name] = 'Actual' AND p.[Key] BETWEEN 202401 AND 202403)
  OR (s.[Name] = 'Forecast' AND p.[Key] BETWEEN 202404 AND 202412)
) 
AND k.[Name] IN ('GRR', 'NRR')
AND f.[S_MarketId] = 5
ORDER BY k.[Path], s.[Path], p.[Path]

Tenant-independent version:

SELECT 
  f.[ScenarioId] AS [A0D0Key], 
  f.[PeriodId] AS [A0D1Key], 
  f.[KpiId] AS [A1D0Key], 
  f.[V_Existing] / f.[V_New] AS [V_Value]
FROM {Model.MTable} f
INNER JOIN {DimensionTable.Scenario} s ON (s.[Key] = f.[ScenarioId])
INNER JOIN {DimensionTable.Period} p ON (p.[Key] = f.[PeriodId])
INNER JOIN {DimensionTable.Account} k ON (k.[Key] = f.[KpiId])
WHERE (
  (s.[p_ScenarioType] = 0 AND p.[Key] BETWEEN 202401 AND 202403)
  OR (s.[p_ScenarioType] = 1 AND p.[Key] BETWEEN 202404 AND 202412)
) 
AND k.[p_KpiType] IN (3, 4)
{DimensionFilters(f, 0, ScenarioId, KpiId, PeriodId)}
ORDER BY k.[Path], s.[Path], p.[Path]

important

Queries must sort data in the same order as metadata, in reverse axis order. For instance, if you have 2 axes, data must first be sorted by axis 1, then axis 0.

Imagine a table where axis 0 represents the columns and axis 1 represents the rows. Data cells must be ordered left to right, top to bottom. Cells can be skipped where there is no data, but a cell should not appear before another cell on a row above or on the same row to the left of the current cell.

Using Substitutions

Substitutions can be used in the text of the query by using curly braces: {variable} or {function(params)}. If a variable or function with the given name exists (note: names are case-sensitive), the entire construct including the curly braces will be replaced with the value of the variable or the result of the function.

caution

Substitution is done blindly, without any knowledge of the context. It can be inside a string, inside the text of the query, and can even span keywords.

See Variables for details on what variables and functions are available.