Calculated Measures and Named Sets

Overview

Fields from fact tables get converted into measures in measure groups in a cube. When measure groups are created in a cube, one measure group is created per fact table. Often in production systems, developing calculated measures is a regular requirement. Multi-Dimensional Expressions (MDX) is the query language for a cube and is synonymous to what T-SQL is to SQL Server. Often queries that are frequently used are required to be in some ready format in a cube, so that the users do not need to develop them over and over again.

 One of the solutions for this is named sets, which can be perceived as a query already defined in the cube, similar to views in SQL Server. We will develop a calculated measure and a few named sets in this section.

Measures are the numeric values that users want to slice, dice, aggregate, and analyze; they are one of the fundamental reasons why you would want to build OLAP cubes using data warehousing infrastructure. By using SSAS, you can build OLAP cubes that will apply business rules and calculations to format and display measures in a customizable format. Much of your OLAP cube development time will be spent determining and defining which measures will be displayed and how they will be calculated.

Measures are values that usually map to numeric columns in a data warehouse fact table, but they can also be created on dimension and degenerate dimension attributes. These measures are the most important values of an OLAP cube that are analyzed and the primary interest to end users who browse the OLAP cube.

 An example of a measure that exists in the data warehouse is ActivityTotalTimeMeasure. ActivityTotalTimeMeasure is a measure from ActivityStatusDurationFact that represents the time that each activity is in a certain status. The detail level of a measure is made up of all the dimensions that are referenced. For example, the detail level of the ComputerHostsOperatingSystem relationship fact consists of the Computer and Operating System dimensions.

Aggregation functions are calculated on measures to enable further data analysis. The most common aggregation function is Sum. A common OLAP cube query, for example, sums up the total time for all activities that are In Progress. Other common aggregation functions include Min, Max, and Count.

After the raw data has been processed in an OLAP cube, users can perform more complex calculations and queries using multidimensional expressions (MDX) to define their own measure expressions or calculated members. MDX is the industry standard for querying and accessing data that is stored in OLAP systems. SQL Server was not designed to work with the data model that multidimensional databases support.

Named sets are simply MDX expressions defined with an alias that return a set of members. If you find yourself writing complex MDX expressions to return a set of members often or if you have a commonly used expression, consider creating a named set. This will prevent you from having to duplicate your work.