SQL Server Magazine

Microsoft's entry into the OLAP market has significantly lowered the cost of implementing analysis solutions. OLAP products are less expensive and more widely available. And because the products are more accessible, companies are deploying analysis solutions instead of or alongside traditional reporting solutions.

Many experts in the OLAP marketplace believe that analysis solutions will continue to displace reporting solutions—not that reporting solutions will ever go away, but many customers need more information than the traditional reports provide. I think of a report as a still photograph that captures a snapshot of your business in time. OLAP analysis is more like a movie. But it's a special movie—one that lets you guide the camera. For some business decision makers, a couple of good still photographs from different angles are enough to help them understand a particular business problem. But for a decision maker who is responsible for solving the problem or communicating the details of the problem to others, there's no substitute for being able to explore the problem with guided moving pictures.

OLAP is most useful for online, interactive exploration of data. Depending on the application, you can use OLAP preanalysis or postanalysis exploration. You use preanalysis to discover particular trends, relationships, and exceptions in your data. Postanalysis is for exploring a particular trend, relationship, or exception after its discovery. Named sets are useful for creating OLAP reports. Named sets help you to identify a natural grouping of business entities (dimension members) with a name; you can use the name repeatedly to create many reports or as a shortcut during interactive analysis.

Discover OLAP Reports
If OLAP is about interactive exploration and reporting is a snapshot of data, then what is an OLAP report? Although it sounds like an oxymoron, an OLAP report is a view of data that you use as a starting point for analysis—typically, postanalysis. Imagine that a business analyst discovers that profit margins for a particular product line have disintegrated during the past three quarters. The analyst might communicate this problem by distributing an OLAP report that shows a trend line (line chart) of revenue and cost of goods for that product line during the past three quarters. Decision makers who receive this report can then use it to further analyze the problem. They can drill down into the cost of goods to see the product's constituent parts; or, they can compare volume and product pricing against their competitors' volume and pricing to learn more about the problem and identify potential solutions.

The key to making good OLAP reports is creating report entities and values that match the business terminology. For example, in a particular report, the entities might need to be sales regions and the values might need to be year-to-year percent changes in sales. You'll probably need to use MDX formulas to derive some of these entities and values from the entities in the OLAP cube. That task doesn't sound too hard; using formulas to derive values is commonplace in reporting. But a good OLAP report has an additional requirement: The entities and values need to be browsable, meaning that when you drill down, drill up, pivot, or otherwise navigate through the data, the report entities still calculate meaningful values.

For a simple example of browsability, look at the following formula for year-to-year change in sales:

[Time].[99Q1 Year to Year] = ( 99Q1 -- 98Q1 ) / 98Q1

This formula for a new time dimension member gives you the percent change for first-quarter (Q1) sales from 1998 to 1999. You could create equivalent formulas for each of the other three quarters and use all four formulas to create a static OLAP report. Now, compare that technique to the following formula:

[Measures].[Year to Year Sales Change] = 
( (Time.CurrentMember, Sales) - 
( ParallelPeriod( Year, 1 ), Sales ) ) / ( ParallelPeriod( Year, 1 ), Sales )

This formula defines a measure as year-to-year percent change in sales for whatever time dimension member you've selected. If you use this formula to create an OLAP report that contains all four quarters of 1999, decision makers can drill down into any of the quarters and see the year-to-year percent change for the months in that quarter. Thus, because the second formula works at any level in the time dimension, it's a browsable formula. The first formula isn't browsable because when you drill down in the time dimension, the formula no longer applies. You would have to create a separate formula for each member in the time dimension. (I explored the concept of browsability in detail in "The Zen of MDX," April 2000.)

Defining Sets
Calculated members are crucial in modeling business entities and values, but another, lesser-known feature—named sets—rivals the power of calculated members. Named sets are available with SQL Server 7.0 OLAP Services, but they become more powerful in SQL Server 2000 Analysis Services. In OLAP Services, you can define a set in one of two ways, both of which control the set's lifetime. One technique defines the set for the life of an MDX query; the second defines it for the life of a client session. Analysis Services adds a third technique that defines the set on the server so that the set is available to all clients. Sets that you define this way persist beyond sessions until you delete them.

You need to know a couple of things about sets. First, the scope of a set is a cube rather than a dimension, so a set can include members from more than one dimension. Second, technically speaking, a set consists of tuples. A tuple can be one dimension member or several dimension members, with no more than one member from each dimension. In most situations, a set consists of several members from one dimension; each member represents a tuple.

Here's an example of a set that exists for the lifetime of an MDX query:

WITH SET [Best Beers] AS '{[Portsmouth Imported Beer], [Walrus Imported Beer]}'
SELECT [1997].Children ON COLUMNS,
  [Best Beers] ON ROWS
FROM Sales

When you use the WITH keyword to define a set in a query, as you do with this technique, you don't need to specify which cube the set belongs to because the FROM clause contains that information. However, the second technique of using the CREATE SET command to create a set requires a cube name:

CREATE SET [Sales].[Best Beers] AS '{[Portsmouth Imported Beer], [Walrus Imported Beer]}'

   Prev. page   [1] 2     next page