MSAS: Introduction and Working with Measures

The quantitative and numerical columns from a fact table of a cube are the measures of the cube. When the cube is processed the data in the measures get aggregated across the dimensions of the cube. These measures are of primary interest to the end user and are the central values that get analyzed in a cube.

Introduction to Measures

The quantitative and numerical columns from a fact table of a cube are the measures of the cube. When the cube is processed the data in the measures get aggregated across the dimensions of the cube. These measures are of primary interest to the end user and are the central values that get analyzed in a cube.

Every cell in a cube contains a value that is relatable to a measure that has been defined in the Fact table and while processing the cube. Therefore, all queries on a cube will return a measure of the data queried. The value may be retrieved from the cube’s aggregations, its source data, a copy of it on the server or client cache or a combination of these sources depending on the storage settings of the cube.

Measures are aggregated by Microsoft SQL Server 2000 Analysis Services and help in quick retrieval of data on queries. The aggregate functions that are used are Sum, Min, Max, Count and Distinct Count. Common measures used are Sales, cost, expenditure and production count

Analysis Services supports measures based on both additive and non additive columns. Additive columns can be summed. For instance a monetary column is additive. Additive columns are regarded as most suitable measures. However, non additive columns are sometimes used as measures. For instance a numeric identifier like Account Number could be used as a measure. These measures are suitable where the user needs to make a Distinct Count or count as an aggregate function.

Look at the example in the following picture,. It is a simple illustration of how measures are used. Sales_Amount is the measure in the Sales Fact table. The dimension of each of the other tables contain a common element with the fact table:--Product_ID, Customer_ID, Retail_Store_ID. Each cell in the returned dataset would contain a sales value aggregated from the Sales_Amount.

A measure can be derived from multiple columns combined in an expression. The profit measure, for instance, is the difference between two numeric columns--that is:--Sales and Cost.

Calculated members are sometimes used as measures. Calculated members are values created from the formulas. These values are not stored but merely invoked when the cube is browsed.

In the object hierarchy measures are immediately subordinate to the cube. Measures of a cube are created when the cube is created. Measures have to be selected when a regular or a virtual cube is built with the Cube Wizard. Or Cube Editor. After the regular cube is built, the measures are to be maintained in the Cube Editor. A Virtual cube is maintained in the Virtual Cube Editor.

Measures are derived from columns of the fact table and since a regular cube can have only one fact table in its schema, all of the cubes measures have to be contained in it.

Some cubes contain a special type of dimension called the measure dimension. This dimension contains a member for each measure. When end users browse this cube the members in the Measure dimension can be sliced to display values for single measures, or they can place the measure dimension on an axis for viewing values for all the cubes measures.

The measure dimension is distinct from other dimensions in that it is created automatically when the cube is created. It cannot be displayed or edited in the Dimension Editor and can be edited and viewed only in the Cube Editor or the Virtual Cube Editor. It always contains only one level. Custom rules for security can be created for the Measure dimension or the access to the dimension can be restricted by implementing cell security.

While programming with Decision Support Objects(DSO), measures are associated with clsCubeMeasure, clsPartitionMeasure, clsAggregation Measures.

End users will see measures in a tabular form or in a graphical form depending on the kind of client application they are using for browsing the cubes. In both the above presentations of measures, they remain the focal point while the dimensions provide the labels for the rows and columns

In a tabular presentation measures are displayed in rows and columns. The cubes dimensions determine the column and row headings, but measures are the data in the rows and columns except when the user multiplies measures in a cube. In such instances the measures also provide multiple headings to separate the measures.

In graphical presentations, measures display in a variety of ways including lines, shapes, colors, shades and shadows.