Introducing the SQL Server 'MDX in Analysis Services' Series - Page 3

December 2, 2002

Fundamental MDX Syntax -- Building Simple Expressions

Having briefly explored the terrain that we can expect to find in a multidimensional cube, and having discussed the role of the MDX language in obtaining information from the cube, we are ready to begin using MDX to retrieve data from a data source.

We will start Analysis Services and navigate to the Warehouse cube, which is supplied as a sample when we install Analysis Services.

  1. Start Analysis Manager (Start -> Programs -> Microsoft SQL Server -> Analysis Services -> Analysis Manager), expanding the Analysis Servers folder by clicking the "+" sign to its left.

  1. Expand your server (typically named the same as your PC, but determined by the installation/setup), and then expand the FoodMart 2000 database.

As we learned earlier, MDX acts as a tool for extracting information from our OLAP cubes. In addition to the data query role, MDX can generate calculated single values. In its capacity as an expression language, MDX allows us to create complex expressions that can be used to serve a wide range of functions within a cube. Among these functions is the creation of calculated members, custom actions, and custom formulas (and variations upon each of these), as well as other functions.

We will begin our exploration of MDX with the creation of a simple calculated member.

Multidimensional Data and MDX

Earlier, we touched upon the meaning of the term "member" in our cube environment. In addition to the standard member, a calculated member (a member generated via MDX) can range in sophistication from the simplest specifications (a constant value) to a complex formula that is customized to meet a complicated business need. To familiarize ourselves with some of the characteristics of members, we will examine their properties and how they interact with dimensions in general. We will first create a calculated member using a constant value. Our exploration will progress to more advanced calculations later, but for now the point is to gain an understanding of

  • The nature of members and how they fit into the OLAP scheme of things;
  • The mechanisms used to create a calculated member;
  • The navigation of members, including the display of properties and other information; and
  • The interaction between dimension members and MDX expressions.

Page 4: Simple Calculated Members

See All Articles by Columnist William E. Pearson, III