This article is the first in a three-part series
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.
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.
7 thoughts on “Designing Reports with Custom MDX Queries – part 1”
Many thanks for sharing this neat webpage.
Good evening Mr Turley,,,,Can you suggest me a good bokk that wirte about SSRS with OLAP Cubes ….I did not find any…Thanks a lot for your kind reply
Hi I am very new in MDX. I need to get the TotalSale-Monthly Sale.
I have Sales data from which I can easily get Sales per month, but I need to find out totalSale-PeriodSale Data.
I have below data
Period Sale Amount
Total Sale 36688
Need to find out below data In third column.
Period Sale Amount Total Sale-Period Sale
200101 100 36588
200102 2500 34188
200103 3200 33488
200104 200 36488
200105 300 36388
200106 1500 35188
200107 6500 30188
200108 1500 35188
200109 8500 28188
200110 120 36568
200111 8652 28036
200112 755 35933
200201 655 36033
200202 241 36447
200203 455 36233
200204 655 36033
200205 855 35833
Total Sale 36688
Could you please help me.
Thanks in advance.
This is certainly a very informative post. I am new to MDX after using SSRS with T-SQL. The examples you have provided regarding passing dynamic values to fields and measures will undoubtedly save a lot of work in making the reports provided as dynamic as possible. Negating the need for the design and creation of multiple reports each with a different slant.
I am, however, having a problem creating a dynamic management view. I have entered the following into the expression builder:
Select Measures.[Take_on_Balance] from $System.MDSCHEMA_MEASURES WHERE CUBE_NAME = ‘NAME_OF_CUBE’
ORDER BY Measures.[Take_on_Balance];
N.B. NAME_OF_CUBE is supplemented with my organisations cube name
However, the dataset does not populate and I receive the following error:
“Could not create a list of fields for the query. Verify that you can connect to the datasource and that your query syntax is correct.
Parser: The dot expression is not allowed in the context at line 1, column 8.”
Any help would be greatly appreciated?
I don’t think you can create a calculated member from an DMV query. Calculated members work with MDX and not the specialized query syntax used for getting results from an DMV.