sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
If you’re in the greater Seattle or Olympia area, mark your calendar and don’t miss SQL Saturday on April 9th. Greg Larsen and his user group have put together a great line-up of FREE, high value presentations from an army of industry experts.
SQLSaturday#68 is at South Puget Sound Community College, Building 35 – Natural Sciences, 2011 Mottman Road SW, Olympia, WA 98512.
On the Microsoft Developer Network (MSDN) forum for Reporting Services and other online discussion resources, I see many report developers asking how to build analytical reports using relational data sources. Some of the most fundamental capabilities often require complex and convoluted coding techniques. Sometimes these features require the report developer to populate temporary tables, inefficient and complicated subqueries and work-around techniques to derive calculated values and simple report features.
You probably know that OLAP cubes make this kind of reporting work much easier to manage and the data much easier to query and report on. To take that step – the transition from relational storage to SQL Server Analysis Services – also means taking a big step from the familiar T-SQL query language to the Multi-dimensional Expression query language. MDX is not a difficult language to use once you get the basic concepts down but it’s quite a bit different than SQL and it takes some re-learning to get to that point. For many, at first MDX seems strange and foreign until arriving at a moment of clarity when the mechanics of the language begin to make practical sense. Understanding concepts like “sets”, “tuples”, “members”, “hierarchies” and “levels” will help you appreciate the elegance of this simple language and the power of the OLAP query engine.
This article will show you many of the essential report and MDX query design techniques, beyond the fundamentals, related to OLAP reporting. I’m not going to teach you all about MDX in this short article so I expect that you already know some of the basics. However, I will show some examples that may help newcomers get started if you’re willing to venture out of your comfort zone. You’ll see how to use techniques in Reporting Services that will let you take advantage of the power of MDX and Analysis Services cubes. I assume that you’ve had some experience with, and understand the fundamentals of report design. I won’t demonstrate every step but I will provide enough direction to build a report if you already know the basics. You should know what to do when I provide directions like “create a new dataset with the provided query script” or “create a new parameter and use the dataset for the available values.” If you don’t have prior report design experience, there are several good books available on Reporting Services that teach essential report design. I encourage you to pick one up and learn report design basics before you read this article.
Just to make sure we’re on the same page; let’s briefly discuss the kinds of things that SSAS and MDX reports are typically used for. Analytical reporting is not about showing transactional details like you would on an invoice or ledger. It’s about grouping and aggregating data to analyze status, progress and trends so business leaders and analysts know what’s going on in their business at a relatively high level.
Consider the following common challenges and business scenarios:
When analyzing sales progress over periods of time, business users want to see totals consistently for each period in the time series – even those that don’t have any data. For example, if there were no sales on a particular day, they still need to have a placeholder for that day to show that there were no sales.
For a given period, business users need to see the aggregate sales value for that period alongside the aggregate value for a prior period for comparison. For example, a report should show the total sales for each day for the past 30 days; and for each of those days’ total sales, it should also show and compare the total sales for the same day of the previous year.
An OLAP database, consisting of cubes and dimensions, makes it much easier to efficiently resolve these challenges without the need for writing complex queries that scan and aggregate large volumes of transactions.
Figure 1
Using the Adventure Works Sample Data
To use these examples, you’ll need to have the Adventure Works Analysis Services database which you can find at codeplex.com; Microsoft’s community for sample projects and code sharing. Search for “Adventure Works SQL Server Sample Databases” and then download and install the correct version. I have tested these examples with the data for SQL Server 2008 and SQL Server 2008 R2. After installing the relational data warehouse database and the Analysis Services project, open the Enterprise Analysis services project in Business Intelligence Development Studio and then deploy and process the database on your server.
MDX Query Builder
When a dataset is created and SQL Server Analysis Services is selected as the data source, the query design experience changes. The Build Query button on the dataset dialog now opens the MDX query builder. The graphical query builder is a useful tool for designing basic queries by dragging-and-dropping objects into the grid and various landing zones. Beyond this, the graphical designer doesn’t provide the flexibility to make manual modifications to the query script. Using the graphical designer, you can easily build an entire query, complete with multiple hierarchies on the rows axis and multiple measures and complete parameter filtering logic. I often use the graphical query builder to prototype an MDX query and sometimes use the query as a starting point. For highly-parameterized queries, I usually start over and write the query from scratch as you will see in the following examples.
The query builder uses a layering pattern to build MDX queries using sub select statements for each filtering slicer or parameter. This technique is an effective way to handle different filtering logic but queries can easily become overly complex, difficult to decipher and maintain. We won’t be using the graphical query builder. Use the left-most toolbar button “Edit as text” to switch to generic text mode.
MDX Reporting Essentials
Reporting Services can be used quite effectively for reporting on SSAS cubes and in most cases reports may be designed in the same manner as relational queries. However, you should be aware of the following common considerations:
Reporting Services must flatten MDX results before the data can be consumed. As a rule, place all measures on the columns axis and the initial set of dimension members on the rows axis.
Include empty members
One essential characteristic of trend analysis is that all periods should be represented, even if no values exist. For example, all time periods should appear on a trend line chart even if no activity is reported for a day or other time period. Include all dimension members by using the NON EMPTY directive before dimension references on an axis (usually on the rows axis.)
Allow the cube to aggregate and roll-up measures
By default the report designer applies the SUM function to numeric fields referenced in a report group. Some measures defined in the cube may not additive. For example, a measure that performs an average, inventory count or other calculated value cannot be summed across levels of a dimension hierarchy. For these measures, use the AGGREGATE function in place of the SUM function for the report items used for group totals. Consult Books Online to understand the restrictions and properties used to control the behavior of the AGGREGATE function in Reporting Services.
Every year, Microsoft brings a couple thousand specialists from every corner of the globe together for a few days to Bellevue and the Microsoft campus in Redmond, Washington. The Microsoft MVP community represents every discipline and Microsoft product.
The MVPs are the external advisory committees for each Microsoft product group. They give us early prerelease versions of products and share protected information about product strategies and future development. Within each of our technical communities, we sit with the leadership, architects and developers of each Microsoft product team to validate their ideas and give feedback on designs and features. Every MVP is under strict non-disclosure agreements and we are reminded in every session that one violation can screw up the trust and privilege for the entire community. Believe it or not, the product teams take this feedback seriously and prioritize a lot of their work around our suggestions. In those cases where they go in different directions, they explain their thinking and strategy. MVPs don’t discuss, tweet or blog about these details so you’re not going to learn anything more about undisclosed features here.
There was a movie in the 60s called “The President’s Analyst”. James Coburn played Dr. Schaefer, a psychiatrist who becomes the US president’s personal analyst. He learns all the president’s top secret information and gets chased around by all kinds of bad guys who want to use his knowledge for their evil designs. Unlike Mr. Slugworth trying to get the recipe for Willy Wonka’s Ever-lasting Gob Stopper, I have not been offered untold fortunes to sell Microsoft secrets to the other guys but I do feel very privileged to be trusted with important information and some very cool tidbits of things to come.
Microsoft has publically revealed some of their high-level plans for next-version SQL Server, Self-service BI, Office, Azure cloud computing and SharePoint development so I can officially say that there is a lot of movement going on in these areas. Please stay tuned for specific announcements as soon as we get the green light to talk about details.
Since I’m not going to talk tech and details in this post, I’ll share a quick funny story. In the keynote session on Tuesday, after Microsoft CEO Steve Ballmer finished his highly-energizing address to the general audience, Tony Richards, General Manager of Community & Online Support introduced the Seattle Mariners’ Moose. The baseball team mascot did his usual crazy routine to get the crowd energized by running around the 2,000-seat hall, giving high-fives & throwing baseball team swag. North Americans were in the minority so most of the MVP crowd didn’t really get the baseball culture and just stood in front of their chairs with little enthusiasm. American and Canadian sports fans tried our best to set an example for everyone else by jumping, hand-clapping and singing but the crowd just didn’t take to it and the overall low energy was a bit of an anti-climax to an eventful meeting. I just have to imagine that the mascot went home to his wife and said something like “Honey, you’ll never believe the day I’ve had. Microsoft hired me to do to this geek convention and the crowd wasn’t into baseball or a dancing moose”. Poor guy. It just goes to show that you can’t force culture onto a diverse audience – but they certainly tried.
Unlike most technical conferences, the attending MVPs all work and participate while at the summit. Sure, we socialize but most of the time, everyone is networking with others and racing from each meeting and session to another to be engaged and involved. The summit was awesome and the MVP community is unlike any other organization I’ve seen. We’re volunteers and we give our time and energy to a community that we love.
Please seek MVP bloggers. If you need project consulting or training, look for MVPs. Look for MVP authors when you shop for tech books and watch for local events and user groups hosted and organized by MVPs. Our efforts are backed by Microsoft leadership, the product teams who develop the software you use, our employers, vendors, large organizations and community leaders. Please support the community that supports you.
I’ve just moved my blog from a site I hosted myself using the dasBlog ASP.NET application to WordPress.com. Please let me know if you experience any issues at all. Traffic to my domain at http://sqlserverbiblog.com is redirected to the new blog site at https://sqlserverbiblog.wordpress.com. WordPress is a much more robust blogging environment so please enjoy and let me know what you think. Since there is no easy way to migrate content between the two formats, I had all kinds of fun opening each individual post and then reposting to the new site! Use the Email Subscription option on the right-hand sidebar to receive notification of updates.
