Describing the Key Elements of an Analytical Query

All analytical query results include three elements: measures, dimensions and data cells.

Let's explore each of these elements.

• Measures - the numerical values that you are evaluating, such as sales revenue, profit, quantity. Although technically, you do not need any measures in an analytical query and you could include only dimensions, there are usually multiple measures defined in an analytical query. A analytical query without measures offers very little value to the business user. The position of each measure in the definition determines its position in the result. Each measure usually includes other settings to determine how it is handled by the analytical query. We will come the additional settings later. For now, we focus on the basic syntax that is used to define measures:

  Code Snippet

  Copy codeSwitch to dark mode

  1234
  {
  OccupiedSeats,
  NumberOfFlights
  }

• Dimensions - give meaning to the measures. For example, month, country, product. Although technically, you do not need dimensions in an analytical query, there are usually multiple dimensions defined to give meaning to the measures. A measure without a reference to at least one dimension value is usually meaningless. Each dimension usually includes additional settings to determine how it is handled by the analytical query. We will come to the additional settings later. For now, we focus on the basic syntax to define dimensions:

  Code Snippet

  Copy codeSwitch to dark mode

  1234
  {
  AirlineID,
  DepartureAirportID
  }

• Data Cells - are the intersection of dimensions and measures. You do not define data cells in an analytical query. Data cells are generated at run-time from the measures and dimensions that you defined.

In most front-end tools, an analytical query can be presented as a tabular display or a graphical display. The query definition provides the defaults that determine the initial layout. However, many front-end tools provide the business user with the possibility to change the layout.

When you define a query, you must decide the default position of each dimension in the tabular layout. You can choose to position each dimension in the rows or columns of the layout. You do the same for the measures except that the measures must stay together. This means that once you assign any measure to an axis, the other measures will be assigned to the same axis. You do not need to explicitly define the axis for each measure after the first one. Dimension can be individually assigned to rows or columns axis. Remember, in the query definition, you are choosing the default positions of each dimension and the measures in the query. The business user is able to override your choices when they execute the query.

• Columns

  Columns are on the vertical axis of the analytical query result. You can assign either dimensions or measures to the columns. If you assign a dimension to the columns, you will generate a column for each dimension value. For example, if you assign the dimension month to the columns axis, you can expect 12 columns in the query result. You can mix dimensions and measures in the columns. As you add more columns, the analytical query result grows horizontally.

• Rows

  Rows are on the horizontal axis of the analytical query result. You can assign either dimensions or measures to the rows. If you assign a dimension to the rows, a row is generated for each dimension value. For example, if you assign the dimension month to the rows axis, you can expect twelve rows in the analytical query result. You can mix dimensions and measures in the rows. As you generate more rows, the analytical query result grows vertically.

  

• Free

  Dimensions that have been assigned to either the rows or columns appear in the result when the analytical query is executed. Sometimes you might want to offer the business user additional dimensions in the analytical query for drill-down, without these dimensions appearing in the initial result. These dimensions are assigned as free. If you do not assign a dimension to either rows or columns in the query definition, it will be assigned to the free category. Once the analytical query result appears, the business user can select any free dimension and include it in the rows or columns. For example, if a analytical query result displays the monthly sales totals, the business user might want to add the additional dimension week to display a lower level of detail. Only dimensions can be assigned to free. Measures cannot be assigned to free.

It is good practice to assign dimensions to free instead of forcing them to appear in the initial result. Only assign the most important dimensions to the rows or columns.

There are three reasons for this:

1. You improve the performance of the analytical query when generating the initial result by including only the essential dimensions in the rows or columns.
2. You avoid frustrating the business users who has to remove the unwanted dimensions from the rows and columns, and then wait for the analytical query to re-execute before they can begin their analysis.
3. You avoid generating a large result set that is challenging for the user to navigate.

Here is an example of the code used to assign measures and dimensions to the axis:

123456789
{
  @AnalyticsDetails.query.axis: #ROWS
  AirlineID,
  @AnalyticsDetails.query.axis: #COLUMNS
  OccupiedSeats,
  @AnalyticsDetails.query.axis: #FREE
  DepartureAirportID
}

An analytical query can be displayed graphically (charts or maps). Many business user tools provide chart options with the possibility to adjust settings to fine-tune the way each chart is displayed. Each chart type will have its own settings. For example, setting for a heat map will not make sense for a stacked bar chart.

It is not possible to define all settings for every type of chart, in the analytical query definition. But there are some defaults to be aware of, that apply to most tools.

Most analytical tools that provide graphical capabilities, automatically assign the analytical query measures and dimension to the axis of the different types of chart. For example, the SAP multidimensional analysis tool (MDA), assigns all measures to the value axis, and all dimensions to the category axis. You can see this in the diagram before this.

Many of the settings defined in the analytical query are relevant to a graphical display, but some settings are not used. For example, the assignment of measures and dimensions to the rows and columns will be ignored by the graphical display.

Depending on the analytical tool, the totals may or may not be displayed by default. In most cases, it does not make sense to display the total in a chart.