Properties of Dimensions

For each dimension, there are several useful settings that you can define to support the business user. In most cases, the business user can change these settings in the front-end tool, but it is helpful to set the defaults in the analytical query.

For each dimension, using the annotation @AnalyticsDetails.query: you can define several settings.

In the first setting we can determine the axis on which each dimension is displayed. The values can be ROWS, COLUMNS or FREE. If you don't include this setting for a dimension, it is set to the value FREE.

The value FREE means the dimension will not be displayed by default in the query result and the business user must add it to the rows or column manually if they would like to include it.

Within the annotation @AnalyticsDetails.query:, the setting sortDirection: is used to define the default sort sequence. This can be either ascending or descending. The allowed values for the setting are #ASC or #DESC. If you don't include this setting, the sorting direction (from top to bottom or from left to right) of the dimension values is ascending (a,b,c or 1,2,3).

And finally, within the annotation @AnalyticsDetails.query:, the setting totals: is used to define whether the total of the dimension values should be displayed by default. The possible values are #SHOW and #HIDE. If you don't include this setting, then totals are hidden.

When reporting measures, a business user usually only wants to see the dimension values where a measure is assigned. In other words, where a record exists in the cube view. This is also known as a booked value.

For example, I want to see the order quantities for each product that I sold. This means that the dimension values where there is no measure, (in our example, a product that has never been sold), would not be displayed in the analytical query result. You might assume that this is always the required behavior, but this is not the case.

There are times when a business user would like to see not only the dimension values where a measure is assigned, but also the dimension values where no measure is assigned. This is where there is no record in the cube view for a dimension value.

In our example, it would mean the business user is able to observe the products that sold and also those that did not sell. In this case, each dimension value without an assigned measure would be given a null measure value in the analytical query result. A run-time filter could even be applied to remove rows where the assigned measure is not null, so that the analytical query results then shows only the unbooked dimensions. We would then have a list of products that are not selling.

Let's look at the code to work with booked and unbooked values:

As you can see it is a simple setting within the annotation @AnalyticsDetails.query. The setting is resultValuesSource.

The setting values are #DIMENSION to display booked and unbooked values, and #CUBE to display only booked values.

Here are the results below of using the settings: