Key Settings of an Analytical Query

Let's familiarize ourselves with the basic elements of an analytical query.

The following elements define the default settings for the analytical query. These settings can usually be changed by the business user at run-time in their front-end tool. The developer should choose the default settings with the business user to minimize the need for changes to be made in the front end tool.

• Cross-tab: A cross-tab is achieved when you place one or more dimensions in the columns and one or more dimensions in the rows, so that you create an intersection of the results. In the CDS view that defines the analytical query, you can assign each dimension to the rows or columns to generate a default cross-tab result that appears when the query is first executed.

• Filters: Restrict the results of the analytical query by one or more dimension values.
• Sub-totals: For each dimension, you can choose to generate a sub-total. Sub-totals are useful but there might be times when they create too much visual clutter, so you might want to avoid them. For example, there is no point in generating a sub-total for a dimension if it only contains one row, or the results are displayed as a chart.
• Displayed Value: For each dimension you can choose to display the key, text, or the key and the text. If you choose key and the text you can define the order in which they appear.
• Sorting: You can define the sorting logic and direction for dimensions.

The following elements define the settings for the analytical query that the business usercannot change at run-time.

• Exception Aggregation Behavior: You can define special methods of aggregation such as: first value in the month and last value in the month. It is possible to nest multiple aggregations to calculate complex values such as average number of products sold across all stores in each country.
• Currency & Quantity Handling: Most of the measures in the analytical query will either be a monetary value or a quantity. Monetary values must be assigned to a currency and quantities must be assigned to a unit of measurement. This is required so that the units are correctly aggregated and calculated. For example, if you divide a measure with the currency "USD" by another measure with "LITRE" then the resulting measure will have the unit "USD/LITRE". In your query you can determine how the units of measurement and currencies are assigned to measures.
• Formulas: Define calculations using a combination of measures and dimensions.
• Restricted Measures: Define measures filtered by one or more dimension values.

We will cover these elements and settings in their corresponding detailed, lessons later. For now let's look at a setting that is defined at the header level of the anaytical query.