You can execute this statement from a client session with OLAP Services, and the definition will persist until you terminate the session (exit your application) or until you use the DROP SET command. More advanced OLAP applications will remember these set definitions for you and recreate them each time you start the application. If you're going to use a set multiple times in a session, using CREATE SET is better than using the WITH clause because CREATE SET causes OLAP Services to cache the results of the set definition. In contrast, SQL Server must parse and evaluate the WITH clause each time the application passes the clause to OLAP Services.
The third technique you can use to create a set definition, available only in SQL Server 2000 Analysis Services, uses the Analysis Manager. From the cube editor (right-click a cube, and select Edit), you can drop down the Insert menu and select Named Set to bring up the Named Set Builder, which Figure 1 shows.
Having named sets on the server gives you power. You can create a collection of named sets that represent your business entities. You can then base OLAP reports for distribution on those definitions. Later, you can change the sets' definitions without rebuilding the cube. For example, if you have a named set called Last 4 Quarters, you can roll the definition forward each time you load a new quarter's data in the cube.
If you're developing a client application and want to take advantage of server-defined sets (named sets that you can define on the server through Analysis Manager), you need to know about the set schema rowset. ActiveX Data Object, Multi-Dimensional (ADO MD) exposes all the OLAP meta data through schema rowsets. For example, each list of cubes, dimensions, levels, and other information has a schema rowset. Each server-defined set has a schema rowset that includes columns for the set name, set description, expression defining the list of tuples, and the dimensions that the set references. The dimensions column, which Microsoft included in later beta releases of SQL Server 2000, is important for front-end applications that use sets. Front-end applications that formulate MDX queries must not include members from the same dimension on different axes (rows or columns) of the query. The dimensions information tells the front end which dimensions the set uses so that the application won't use the same dimensions again on another query axis.
In a few scenarios, sets consist of a simple list of members (as in the Best Beer example I used earlier). The most common of these scenarios is alternative member groupings. For example, if you have a customer dimension that rolls up geographically into cities, states, and countries, you might want to group your customers in an alternative way, such as into sales regions. If sales regions are a collection of states, you could rebuild the cube with a new level (regions) between the country and state levels. If sales regions didn't land evenly on state boundaries, you could create a second hierarchy for the customer dimension that rolls up as city, region, and country. Creating named sets for the sales regions is easier than either of these two solutions. However, using sets isn't as flexible as restructuring a dimension because writing queries that span sets is difficult. For example, writing a query that returns the top three regions is much easier if the regions are dimension members.
Sets with Expressions
The power of sets becomes apparent when you use MDX expressions to define sets. Following are a few examples of the kinds of information you can find by using sets in MDX expressions.
Most recent 3 months' sales with data in the cube. The first of these two sets, NonEmpty Months, returns all the month dimension members that have data. The second set, Recent 3 Months, returns the most recent 3 months from the first set.
CREATE SET [Sales].[NonEmpty Months] AS
'Filter( Time.Month.Members, Not
IsEmpty(Time.CurrentMember) )'
CREATE SET [Sales].[Recent 3 Months] AS
'{ [NonEmpty Months].item( Count([NonEmpty
Months]) - 3),
[NonEmpty Months].item( Count([NonEmpty Months])
- 2),
[NonEmpty Months].item( Count([NonEmpty Months])
- 1) }'
Top 10 customers based on unit sales. This simple set uses the TopCount function to return the top 10 customers.
CREATE SET [Top 10 Customers] AS
'TopCount( [Customers].[Name].Members, 10, [Unit Sales] )'
Three most profitable cities. This set demonstrates that you can base the TopCount on one measure, Profit, while displaying another, Unit Sales, in the resulting query.
WITH SET [Top 3 Cities] AS
'TopCount([Customers].[City].Members, 3, [Profit] )'
SELECT {[Unit Sales]} ON COLUMNS,
[Top 3 Cities] ON ROWS
FROM Sales
Three least profitable cities. This set, Bottom 3 Cities, is the inverse of the three most profitable cities. Here, you use BottomCount instead of TopCount. This formula also filters out the cities that have a NULL profit before it calculates the BottomCount.
WITH SET [Bottom 3 Cities] AS
'BottomCount( Filter([Customers].[City].Members, Not IsEmpty([Customers].CurrentMember)), 3, [Profit] )'
SELECT {[Unit Sales],\[Profit]} ON COLUMNS,
[Bottom 3 Cities] ON ROWS
FROM Sales
1998 variance report, with fourth quarter broken down by month. This formula demonstrates the use of tuples in a set. The set Recent Periods includes tuples with Time and Category together. I used the Union and Crossjoin functions to display actual and budget values for only the most recent 3 months. The previous 9 months display actual values only.
WITH SET [Recent Periods] AS 'Union( Crossjoin( {[1998].[Q1],\[1998].[Q2],\[1998].[Q3]},{[Current Year''s Actuals]} ),
Crossjoin( [1998].[Q4].Children, {[Current
Year''s Actuals],\[Current Year''s Budget]}) )'
SELECT [Recent Periods] ON COLUMNS,
[Account].Members ON ROWS
FROM [Budget]
Get Set—Go
You need to know one last thing about sets: If you nest definitions of sets and calculated members, you must drop (delete) them in the order opposite of the order in which you created them. For example, if you create set A based on calculated member B, you must drop A before you drop B. If you try to drop a set or member that has dependencies on it, OLAP Services sends you an error message.
Two key elements make good OLAP reports. First, use the right business terminology. Second, build the report entities in such a way that people can use them not just while viewing the report but also while exploring the data surrounding the report. Calculated members and sets are essential for achieving these objectives; they're the building blocks that raise the business terminology in OLAP reports to a level that business analysts understand. And they ease maintenance and enable reuse of OLAP reports. (For answers to the September MDX Puzzle, see "September MDX Puzzle Solution Revealed," page 71. For the October puzzle, see "October MDX Puzzle," page 71.)