Mastering OLAP: Member Properties

Build OLAP cubes that include detailed database attributes

This month we explore a little-known but important feature of OLAP Services—Member Properties. We describe what Member Properties are, and why they are so important. Then we look at the features they enable.

In the April and May columns, we told you that dimension members are elements that businesses use, such as time periods, customers, geographic regions, or products. OLAP techniques help you answer business questions by letting you evaluate core business measures, such as sales and inventory, in the context of dimensions. For example, you might want to find the growth rate of the number of boxes of Cheerios sold in Store 10 during the past 12 quarters. Most tools that work with OLAP Services present dimension members in a hierarchial list that let you select the hierarchy level you're interested in evaluating. For example, the Time dimension used in the FoodMart Sales cube has levels of Year, Quarter, and Month. Members in the Time dimension include the year 1998 and the month January, which is also a descendant of the 1998 member.

Names of dimension members in a hierarchy are helpful, but you often need more information about a member than its name. If you're looking at the Name level of a Product dimension, you might want to perform an analysis based on the product's SKU number, color, size, package style, and other variables. Modeling this information in a pure relational database environment is a task that many people are familiar with: You simply add a Color column to your Product table and issue queries with Color as part of the WHERE or GROUP BY clauses. Handling this information in OLAP Services is just as simple, but the approach for implementing the OLAP solution is slightly different from the relational database solution. And different usually means harder when you're first learning something.

You handle such queries with the OLAP Services Member Properties feature. Member Properties let the data-mart cube designer build OLAP cubes that include attributes for each dimension member. These attributes, or Member Properties, are loaded into the cube and are available to the client application through OLE DB for OLAP or ActiveX Data Object (ADO) and ActiveX Data Object, Multidimensional (ADO MD).

Unfortunately, many first-generation tools that support OLAP Services don't fully support the Member Properties feature. The OLE DB for OLAP specification describes Member Properties in detail, but Microsoft didn't plan to implement them in the first release of OLAP Services. Microsoft changed this decision late in the SQL Server 7.0 development cycle, and many client-application vendors didn't have time to react with their product support. (For example, write-back, or the ability to change cube data for what-if analysis, is an OLAP Services feature that Microsoft added late in the development cycle, so most client tools do not fully support write-back. Several reviews of OLAP Services incorrectly state that the product does not support write-back what-if analysis. Microsoft added write-back after shipping Beta 3, and many people still don't realize this feature exists.)

Of course, future releases of popular OLAP client applications will surely support Member Properties. If you are developing an OLAP application, or if you're a DBA who builds OLAP cubes, consider incorporating Member Properties.

In OLAP Services, a Member Property's name and type information is the same for all dimension members at the same hierarchy level. However, the values can be different for each member. For example, the sample FoodMart Sales cube has a dimension named Store, which has the following hierarchical levels: Store Country, Store State, Store City, and Store Name. All dimension members within one of these levels would have similar properties, but those properties may not apply to the other levels. The Store Name level might have a property named Square Footage, the Store City level could have a property named Elevation, and the Store State level might have a property for population. The Sales cube that ships with the sample FoodMart database includes three Member Properties for the Store Name level of the Store dimension. Screen 1 shows the three Member Properties.

What types of client-side features become available if your OLAP tool fully supports Member Properties? One simple feature is to show the Member Properties whenever the member name appears. This feature can be in the form of fly-over tool tips or additional columns or rows in a grid of results. Another feature is dimension member aliasing. OLAP applications that support aliasing can switch between displaying member names and displaying member aliases, which are stored in Member Properties. Product Part Numbers is an example of an alias for a dimension with product names. Another common alias is language. Executive information system (EIS) application users in an international company might want to view business information in French, Spanish, German, or English. If information is stored in different languages in different Member Properties, users can freely switch between them.

Dimension subsetting is another powerful technique based on Member Properties. Many dimensions, such as Customer, are large, and it is useful to subset them for viewing purposes even before you create a query. You might want to subset information by customer income level, educational background, or marital status before you begin to drill down into a data set, stratifying the population.

Even if your OLAP client application doesn't support Member Properties, you can still filter based on their values with the virtual dimensions feature of OLAP Services. Virtual dimensions expose Member Properties as another dimension where the members of the dimension are the individual values of the Member Property. After you've defined a Member Property in the OLAP Manager, you can use that property as the basis for a virtual dimension. For example, the Store Size in the SQFT dimension in the FoodMart sample is a virtual dimension based on the Store Sqft Member Property in the Store Name level of the Store dimension. Using OLAP Manager, you can tell the difference between a real dimension and a virtual dimension by looking at the icon in the cube editor. Screen 1 shows the three virtual dimensions based on Member Properties of the Store Name member. Virtual dimension icons have a small calculator as part of the image. Virtual dimensions include all the unique values of the underlying Member Property as dimension members, and these members aggregate to an ALL member. Thus, virtual dimensions have only two hierarchical levels. In the client application, the virtual dimensions show up as just another dimension and don't significantly increase the size of the cube. Unfortunately, in the current release of OLAP Services, virtual dimensions are very slow compared to real dimensions. Still, virtual dimensions are worth using because they enable you to filter OLAP queries on Member Properties when the client application might not directly support that capability.

Links to qualitative information are another feature that Member Properties enable. A Member Property containing a URL or a file path name can let you access a significant amount of information related to a dimension member. You can even integrate external programs and systems into your OLAP viewer through the use of URLs embedded in Member Properties. Possibilities are limited only by your imagination.