Aggregating a Selected Group of Members
Sometimes you need to aggregate a group of dimension members for one query. For example, suppose you want to return Unit Sales for the quarters of 1997 for each product family. The solution is easy. But what if you want to run the same query for only the customers in California and Oregon, leaving out Washington? This is a common problem with a simple solution. All you have to do is create a calculated member that aggregates California and Oregon, and select that calculated member in the WHERE clause, as Listing 7 shows.
The Aggregate function aggregates the set of members passed to it and uses the Aggregation method defined for the member's dimension. In this case, the Customers dimension is aggregated with a Sum function that we defined in the OLAP Manager when we built the cube, so the new dimension member [CA-OR] is the sum of [CA] and [OR].
This tip is useful, but be careful. Performance can suffer if you use aggregation heavily in the WHERE clause. If you have a common alternative aggregation, you might be better off creating a second hierarchy for your dimension.
—Brian Moran and Russ Whitney
Mastering OLAP, "MDX Tip of the Month" October 1999, InstantDoc ID 6143
Determining the Percentage of a Product's Contribution
A common business problem is determining percentage of contribution to a group. For example, you might want to know what percentage of the total revenue of a product line a particular product contributed, or what percentage of growth of sales in a country each region in that country contributed.
Here's one way to solve this problem: For each revenue or dimension combination you want to analyze, create a new calculated member. For instance, if you want to analyze Store Sales as a percent of a group in the Product dimension, create a member, as Listing 6 shows. Figure 6 shows the result set from this query.
—Brian Moran and Russ Whitney
Mastering OLAP, "MDX Tip of the Month" June 1999, InstantDoc ID 5417
Avoid Crippled Client Software
Can you imagine using a front-end tool for a relational database management system (RDBMS) that doesn't let you specify an SQL statement? Of course not. Yet somehow that's what developers are faced with in the OLAP space. Remarkably, many shrink-wrap query and reporting tools that work with Analysis Services are crippled in a fundamental sense—they don't let developers supply an MDX SELECT statement. The problem is this: None of the commercial clients, even the most robust, come close to exposing the full power of MDX. Maybe simple cube browsing is all your users require. Nonetheless, to avoid painting yourself into a corner, choose a front-end tool that lets the developer specify custom MDX SELECT statements.
There's a catch to this advice, however. The client tools that don't expose MDX tend not to be tightly bound to Analysis Services—they provide connectivity to other data sources. However, I don't think it's asking too much for these query- and reporting-tool vendors to expose an MDX SELECT query string as a pass-through.
—Tom Chester
"Top 9 Analysis Services Tips" January 2003, InstantDoc ID 27322
Setting OLAP Cube Aggregation Options
After you create an OLAP cube and choose the storage technique that's optimal for your situation, the OLAP server designs the aggregations and processes the cube. If you choose the Relational OLAP (ROLAP) storage technique, the OLAP server will create the summary tables in the source database after it processes the cube. Otherwise, aggregations are stored in OLAP server native format. You can choose the degree of aggregation by considering the level of query optimization you want versus the amount of disk space required. Figure 7 shows the Storage Design Wizard. For example, I chose 80 percent performance, which produced 124 aggregations and required 22.5MB of storage space for Multidimensional OLAP (MOLAP) storage. The aggregations roll up, so if you choose low performance in favor of conserving disk space, the OLAP server query engine will satisfy queries by summing existing aggregations.
—Bob Pfeiff
"OLAP: Resistance Is Futile!" April 1999, InstantDoc ID 5103
—Tom Chester
"Top 9 Analysis Services Tips" January 2003, InstantDoc ID 27322
Use Views as the Data Source
Always use views as the data source for dimension tables and fact tables. In addition to providing a valuable abstraction layer between table and cube, views let you leverage your staff's expertise with relational database management systems (RDBMSs). When you use a view as a fact table, you can manage incremental updates by altering the WHERE clause within the view instead of assigning the WHERE clause to an OLAP partition. When you use a view to source a dimension, you can define logic inside the view that otherwise would have to be defined in Analysis Services (e.g., formulated member names, formulated member properties).
—Tom Chester
"Top 9 Analysis Services Tips" January 2003, InstantDoc ID 27322
Enter Count Estimates
When you first build a dimension, Analysis Services stores the member count for each level as a property of the level. This count is never updated unless you explicitly update it (manually or by using the Tools, Count Dimension Members command). In addition, it's typical for cubes to initially be built against a subset of the data warehouse. In this case, the cube will likely go into production with the count properties understated by an order of magnitude. Here's the gotcha: The Storage Design Wizard uses these counts in its algorithm when you're designing aggregations. When the counts are wrong, the Storage Design Wizard is less effective at creating an optimal set of aggregations. The solution is simple—when you build the dimension, manually enter estimated counts for each level.