Model Queries

Model queries retrieve data from a model.

Query Concepts

Axes

Each query contains one or more axes and zero or more filters. The most common format has two axes (first axis being columns and second axis being rows) and looks like an Excel pivot table. Each axis can contain one or more dimensions—for instance, you may want to retrieve Periods on columns and a combination of Stages and KPIs on rows.

		Jan	Feb	Mar	Apr	May	Jun	Jul
SAL	Volume	10	15	11	14	16	18	20
SAL	Amount	20,000	35,235	21,720	28,340	33,000	37,672	40,000
SQL	Volume	4	6	8	10	11	13	15
SQL	Amount	8,000	12,000	16,000	20,000	22,000	26,000	30,000

IncludeEmpty

For each axis, you can specify whether to include empty headers or not. In the table above, for instance, if the SAL/Other row has no values associated with it and IncludeEmpty is set to false, it will not be returned as part of the query results.

JoinType

If an axis has more than one dimension, the join type indicates how members from the different dimensions combine to generate the axis metadata.

Join Type	Description
Crossjoin (Symmetrical)	Uses all possible combinations of members from each dimension. For example, if the first dimension has members SAL and SQL and the second dimension has members Volume and Amount, the resulting headers will be: SAL/Volume, SAL/Amount, SQL/Volume, SQL/Amount. This is the default.
Direct (Asymmetrical)	Takes the first member from the first dimension with the first member from the second dimension, and so on. In the example above, the resulting headers would be SAL/Volume, SQL/Amount. In a direct join, all dimension queries should return the same number of members.

Filters

Filters (also known as slicers) are similar to an axis, but unlike axis dimensions, data is not broken down by the members in the filter dimension. Instead, each filter dimension query is supposed to return a single member by which the result will be filtered.

Unlike axis dimension queries, a filter dimension query can return the virtual root member, indicating no filtering should be performed on that dimension. Since all filter dimensions return a single value, the concepts of join type or including/excluding empty values do not apply.

Request Type

Each model contains various types of data, organized in different fact tables. The request type indicates which type of data to retrieve:

Request Type	Number	Data Type	Fact Table
Kpi	0	Diagnostic data (trending)	B_xxx
Metrics	2	Metrics/financial data	M_xxx
Stats	7	Statistics/sales drivers	S_xxx

note

Not all fact tables are linked to all dimensions in the model. For instance, M_xxx tables will not have a Stage dimension.

Time Perspective

Time perspective is a query parameter that describes how to perform aggregation on the time dimension:

Value	Name	Description
1	MTD (Month-To-Date)	Default; time aggregation is performed the same way as for other dimensions
2	QTD (Quarter-To-Date)	Numbers for a period are calculated by aggregating from the first period in the quarter to the requested period
3	YTD (Year-To-Date)	Numbers for a period are calculated by aggregating from the first period in the year to the requested period

The time balancing rules described in Key Concepts apply for time perspective aggregation as well. The time balancing rules described in Key Concepts apply for time perspective aggregation as well.

Query Object Structure

interface ModelQuery {
  Model?: string;                  // Model to query (default if null)
  RequestType: RequestType;        // Type of data to retrieve
  Axes?: QueryAxis[];             // One or more query axes
  Filters?: DimensionQuery[];     // Zero or more dimension query filters
  TimePerspective?: TimePerspective;  // MTD, QTD, or YTD
}

interface QueryAxis {
  JoinType?: string;              // Crossjoin or Direct
  IncludeEmpty?: boolean;         // Whether to include empty member combinations
  Dimensions: DimensionQuery[];   // List of dimension queries for the axis
}

Member	Description
Model	Model to run the query against; use the default model if null
RequestType	Type of data to retrieve (see above)
Axes	One or more axes for the query
Filters	Zero or more dimension query filters for the query
TimePerspective	Time perspective (see above); optional, default value is 1 (MTD)
JoinType	Can be "Crossjoin" or "Direct"; optional, default is Crossjoin
IncludeEmpty	Whether to include empty member combinations; optional, default is false
Dimensions	List of dimension queries for the axis