Based on verbal feedback I got at the SQL Server Connections following my session, "Creating Reports with Reporting Services 2005 and Analysis Services 2005," one of the most popular topics I covered was the use of server aggregations in a report. My friend Reed Jacobson touched on this on the Hitachi Consulting BI Blog, but he didn't spell out the details there. Books Online doesn't provide many clues either, nor can I find anyone else writing about the topic - my apologies if I missed something somewhere. Consequently, I thought it would be helpful to explain what the Show Aggregates button does in the MDX query designer and how to get server aggregates from the cube.

Why Use Server Aggregates?

First - why would you care about getting cube aggregates? Can't you just use the Reporting Services aggregate functions (sum, min, max, count, etc.) to do the necessary calculations? Yes and no. The built-in aggregate functions in SSRS work just fine if your measures are all additive. But you run into a problem with semi-additive measures - like inventory counts or general ledger balances that add up nicely across products or accounts, respectively, but not across time. That is, you don't add the inventory you had on the last day of January to the count you had on the last day of February (and any days in between if you track inventory more frequently) to determine your total inventory for March 1.

You also have a problem with non-additive measures, like ratios, because you can't add up the ratios for each member of a dimension to determine the total for the dimension. For example, if you calculate a gross margin percent for each product sold as (Sales - Cost)/Sales, the gross margin percent calculation for all products is not the sum of each product's individual gross margin. You must calculate total sales, subtract total costs, and then divide the result by total sales to get the correct value.

In both examples, the correct answer is obtained only by using server aggregations. After all, one of the key reasons to use a cube is to get aggregated values computed correctly no matter what type of measure it is: additive, semi-additive, or non-additive.

How to Define the Report Layout to Use Server Aggregates

The Show Server Aggregates button on the Data tab - using the MDX Query designer - is tempting, but it doesn't appear to do anything. It's job is simply to show you what server aggregates are available to the report, but it doesn't tell SSRS to get them. Instead, all the work is done in the report layout which seems a bit counterintuitive to me. After all, the purpose of a dataset is to get data and the purpose of layout is to define where that data goes. In this case, the layout affects the MDX query in the dataset - an interesting way to go about it, but workable nonetheless.

The secret to accessing server aggregates is to use the SSRS Aggregate function in the report layout and to define a grouping in the data region. So, using the AdventureWorksDW sample cube that ships with SSRS, let's start with a simply query with the following attributes: Sales Channel, Date.Calendar Year, Sales Territory Group. Add the Gross Profit Margin measure. Now add a table to the report layout and drag Sales Territory Group from the Datasets window to the detail row in the first column and drag Gross Profit Margin to the detail row in the second column and also in the table footer row. By default, the Sum aggregate function is applied, which will yield an incorrect result. But that's okay for now. Let's look at the MDX at this point. Switch back to the Data tab and click the Design Mode button to see the following query:

SELECT NON EMPTY { [Measures].[Gross Profit Margin] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query returns values for each combination of Sales Channel, Calendar Year, and Sales Territory. In other words, the All member for each dimension is excluded from the query - no server aggregations are returned.

Now let's get some aggregate action going. Switch to the Layout tab, and insert two groups into the table. In the first group, set the expression as =Fields!Sale_Channel.Value and set the second group's expression as =Fields!Calendar_Year.Value. For this demonstration, I like to keep the layout simple, so keep only the group header or the group footer selected, but not both. Be sure to add the fields to the respective rows in column one to show the data in the report. Now drag Gross Profit Margin into the second column of each group row.

Does adding groups change the MDX query? Let's see. Switch back to the Data tab. Nope - nothing's changed. Switch back to the Layout tab for the final step. Change the Sum function to Aggregate in the group rows and the table footer row such that the expression now reads as follows: =Aggregate(Fields!Gross_Profit_Margin.Value). You might want to set formatting styles on the group rows to make them easier to identify and set the formatting on the gross profit margin column to p2 to make the values easier to read. When you're ready, preview the report. If the final row in the report shows 11.43%, then you know the correct value is being retrieved from the cube.

If you were to use SQL Server Profiler to capture the query, you'd see a different MDX query than we see in the dataset. Switch back to Data and you can see the query hasn't changed. Even if you click the Design Mode button to toggle back to the graphical query designer and then click it again to return to the generic query designer, you won't see a change here. It all happens under the covers - the grouping and the Aggregate function tell Reporting Services that the MDX needs to be altered to get the aggregates from the cube.

Does this mean you can only see what's happening if you use SQL Server Profiler? No. Now let's try the Show Aggregages button. You need to toggle to the graphical query designer if it's not visible on the Data tab. Then click the Show Aggregates button. The aggregates appear as shown here:


The null values really represent the All member for the attribute represented in that column. So the very first line should read Internet | All Periods | All Sales Territories | 0.4114... if the name of the All member were retrieved. If you now switch to the generic query designer (click the Design Mode button), you can see the MDX query has changed:

SELECT NON EMPTY { [Measures].[Gross Profit Margin] } ON COLUMNS, NON EMPTY {[Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * {[Date].[Calendar Year].[All Periods]} * {[Sales Territory].[Sales Territory Group].[All Sales Territories]}, [Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * {[Sales Territory].[Sales Territory Group].[All Sales Territories]}, ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query now includes the All members - All Periods, All Sales Territories - for the attributes that are used in the grouping of the table. You don't need to follow these steps to get the query right in the dataset before you deploy. Reporting Services knows what to do when it sees the grouping and the Aggregate function. However, as the report developer, you may want to see the query generated to retrieve server aggregates and make modifications to suit your needs more specifically.

Or not. Honestly, I don't know where the time goes. Back in October 2006, I expected to start settling down a bit and develop a better blogging routine. One or two time warps later, I find myself five months since the last post and knee-deep in a variety of time-consuming activities after which I expect to settle down. I have been ultra busy since October, and meanwhile friends and family just wink and nod when I say I'm going to slacken the pace a bit. One of these days.

After the conference season last November, I was looking forward to a nice quiet holiday season, but instead took on a book project. Subject: SQL Server 2005 Express Edition. More details forthcoming soon as it's due to be released in a couple more weeks. It's a beginner's book, so no one I know in the industry ought to be reading it. However, my daughter became a convert to database technology seemingly overnight after taking great pains to know as little as possible about computers as she could, so at least one reader has benefited from the effort! 

As for conferences this year, I'll be in Orlando next week for SQL Server Magazine Connections presenting the following topics:

SBI305: Creating Reports with Reporting Services 2005 and Analysis Services 2005

With the introduction of Reporting Services 2005, you now have a Query Designer to generate the MDX required to retrieve data from a cube as a great first step, but you can extend this query to satisfy more complex reporting requirements. In this session, you’ll learn several tips and tricks about working with Analysis Services 2005 as a source for your reports, including how to customize an MDX query, how to handle aggregate values, and how to implement cascading parameterized MDX queries.

SBI306: Integrating SQL Server 2005 Business Intelligence with Office SharePoint 2007
Office SharePoint Server 2007 enables consolidation of your business intelligence applications for easy access, analysis, and collaboration across the organization. Come to this session to learn how you can use Office SharePoint Server as a central location for administrators to organize and manage information assets and for information workers to locate, analyze, and personalize information available from Analysis Services, Reporting Services, and other sources.

SBI304: Putting Some Actions into Your Cube
Is your Analysis Services 2005 cube the last stop for users in their quest for information? It doesn’t have to be. In this session, you’ll learn how to add actions that take a cube beyond the basics by displaying transactional details with drillthrough and by linking to internal reports, applications, or external Web sites.

If you can't make it to Connections, I'll also be in Orlando again in June for Tech-Ed 2007 with a reprisal of BI integration in Office SharePoint 2007. I hope to see some familiar faces at either of these events.

I've been teaching several BI Voyage classes, which was mentioned in my last post at which time we were conducting the beta delivery. It has turned out to be quite a popular class with many rave reviews. I'm pleased to have shared that experience with my friend Peter Myers who has finally allowed himself to take a well-deserved break as he performed the bulk of the deliveries around the globe. I primarily landed in Redmond to deliver the classes, which doesn't have quite the same ring as, say Paris, or Milan, or Seoul - destinations on Peter's itinerary. But the students in each of my classes are all amazing people that I'm glad I had the opportunity to meet and hope to cross paths with again someday. My international travel took me to Warsaw, Poland where I had the extra special treat of having a home-cooked Polish meal prepared for me while sharing great conversation with a group of students during what felt like the Great Hurricane of '07 that swept through Europe in mid-January. Of course, it wasn't really a hurricane, but it was quite the storm.

A by-product of teaching the BI Voyage classes is the accumulation of a variety of future blog topics which I will start posting after I get back from Orlando next week. I've also been spending a lot of time working with SharePoint Server 2007 and PerformancePoint Server 2007 so will have some thoughts about these technologies to share. So with the first quarter of 2007 nearly over, thanks to that silly time warp, I have finally accumulated a variety of topics I feel are worthy enough to share and will get the blogs rolling soon. Stay tuned!

My silence since September results from the fact that I have been incredibly busy of late. In addition to some training and consulting engagements, I've been collaborating with Peter Myers (who will be blogging soon here) to develop a course for Microsoft targeted for application developers. The course is called BI Voyage and covers the spectrum of the Microsoft BI platform, including Integration Services, Analysis Services, Reporting Services, Office 2007 BI functionality in Visio and Excel, BI features in Microsoft Office SharePoint 2007, and PerformancePoint 2007. Peter and I, as well as other Solid Quality Mentors, will be delivering this course worldwide to Microsoft partners over the next several months. This is a great collection of technologies for business intelligence solutions - I will be sharing my experiences working with them in this blog soon.

I don't have anything technical to contribute today, but thought I'd take this opportunity to let you know about some upcoming events in November.

I will be at SQL Connections in Las Vegas, NV the week of November 6 with three presentations on the following topics:

·          Inside the Report Model: Using the Report Model Wizard, you can generate a reasonable report model that delivers ad-hoc reporting capabilities to your end users through Report Builder, but what additional tuning is required to get the most benefit? In this session, we review how report model objects are generated and how to fine-tune the model by changing properties for entities, attributes, and roles and by working with collections to take your report models to the next level.

·          Introducing MDX Scripts: A new concept in Analysis Services 2005 is the MDX script, which is used to calculate cell values for a cube. In this session, we review the fundamental concepts of the MDX script, explore some new functions and statements you can use to add calculations to a cube, and use the debugging capabilities in the development environment to see how cube values are modified by the MDX script.

·          Proactive Caching in Analysis Services: With SQL Server 2005 Analysis Services, you can choose to process a cube only when the data changes rather than process on a fixed schedule. In this session, we will take a look at the available options for configuring proactive caching and monitor the effect of changing data at the source on the initiation of cube processing and the response to user queries while the cube is processing. With an understanding of how proactive caching works, you will be able to minimize data latency and reduce the administrative overhead of keeping cubes up-to-date.

I will next be at PASS in Seattle, WA to deliver a preconference sesssion on November 13: Then I will be generally hanging out the rest of the week to see some friends do their presentations. My session is:

M4: Microsoft Business Intelligence: Tools and Applications – Building on the BI Platform
Microsoft has delivered great BI functionality for many years in its industry leading BI platform, SQL Server. Microsoft has been expanding its BI investments in recent years in the analytic client and application space through product investment and acquisition. Come to this Pre-Conference seminar to learn about Microsoft’s end-user tools and application offering and how you can benefit from these investments.  We’ll cover Business Scorecard Manager 2005, ProClarity and the upcoming BI investments in the 2007 Office system (primarily Excel 2007 and Office SharePoint Server 2007). Additionally, we will share plans around the next generation performance management application, Office PerformancePoint Server. This Pre-Conference seminar will be packed with information that will show you how you can start leveraging your investments in SQL Server 2005 in a new, value added way.

If you are at either event, please be sure to say hello.

Finally, this past year I co-authored Microsoft® SQL Server™ 2005 Administrator's Companion with Edward Whalen, Marcilina Garcia, Burzin Patel, and Victor Isakov. This book will finally be released on November 15. This book is a great resource for the SQL Server 2005 platform, including an introduction to the BI features. Only one chapter was devoted to SSIS, SSAS, and SSRS respectively and one chapter combined Notification Services and Services Broker, so these are not comprehensive resources for these technologies. But if you're a DBA that's not had much exposure yet to any of these technologies that are bundled with SQL Server 2005, I hope you'll find these chapters a great way to get familiar with the basics. Each of these chapters also contains references to other resources for learning more.

Q.     Is it possible to load ratios at each level of a dimension and avoid cube aggregations? In this case, the components of the ratios are unknown and cannot be decomposed, but the ratios at each level are known.

A.      Take a look at Richard Tkachuk’s article at http://sqlserveranalysisservices.com/OLAPPapers/Loading%20Aggregate%20Data%20in%20AS2005v2.htm. The first part of this article explains the process for parent-child hierarchies. Look for the section titled “Without Parent-Child Hierarchies” for an explanation of how to solve this problem for a user hierarchy. This article is focused entirely on the dimension design, however. Let’s consider how you combine this technique with fact table and cube design. I’ll extend Richard’s example by using a similar Geography dimension.

Here are the tables used to create the dimension:

CityKey	StateKey	CityName		StateKey	CountryKey	StateName
1	1	Seattle		1	1	WA
2	1	Tacoma		2	1	OR
3	2	Portland		3	1	CA
4	2	Salem		4	1	NV
5	3	Los Angeles		5	1	USA
6	3	San Francisco
7	4	Las Vegas
8	4	Reno
9	1	WA
10	2	OR
11	3	CA
12	4	NV
13	5	USA
14	5	All

 

CountryKey	Country
1	USA
2	Canada

After creating the dimension by using these tables, I opened the dimension designer to create a user hierarchy: City, State, Country (bottom to top). Then, on the Dimension Structure tab, I changed the HideMemberIf property for the city and state levels in the user hierarchy to ParentName to hide WA, OR, and CA from the City level and USA from the State level. The dimension now looks like this after deploying the project and processing the dimension:

The fact table uses only keys in the City table, like this:

After creating a cube from a measure group based on this fact table, I opened the Calculation tab of the cube designer, clicked the Script View button on the designer toolbar, and added the following code below the CALCULATE; command:

this = STRTOMEMBER("[Geography].[Geography].[City].[" + [Geography].[Geography].CurrentMember.Name + "]");

I deployed the project and browsed the cube as shown below. Note the values at the state, country, and All level do not aggregate the value on the city level, but use the values provided in the fact table.