Reporting on an SSAS Cube with SSRS 2008

If you're like me, you're not exactly what someone would call a "Jedi Master" of MDX. So if someone asked you to report on an Analysis Services Cube, you might feel a little intimidated. Well have no fear. Reporting from a cube is easier than you may think. In this article, I'm going to cover the basics of building a report on a cube using SSRS 2008 and the MDX Query Designer.

NOTE: If you're interested in learning to write your own MDX query for reporting purposes, Devin Knight has a great article he just published here that you should definitely check out.

You could be wondering why someone would want to report on a cube with SSRS 2008. After all, building a static report kind of eliminates the cool factor of being able to drag and drop different measures and dimensions enabling adhoc reporting. So why report on a cube?

• The data is pre-aggregated! Reporting on the cube allows you to easily display aggregations across multiple groups in your reports.
• Results of multi-dimensional queries are returned very quickly. This means that if you are trying to show many different measures with their aggregations across several different groups with a time parameter, your query will probably run much quicker in MDX executed against the cube as opposed to using some heinous T-SQL query.
• Lastly, a cube is designed to answer analytical questions about your data, which is what many end users are looking for.

Let's get started on building our report on a cube. In this demonstration, I'm using the Adventure Works cube. The first thing we need to do is create a report project.

1. Open BIDS, go to File, click New, and select Project. Then click Report Server Project and click OK.

2. Next, we need to create a Shared Data Source to the Adventure Works cube. To do this, right-click Shared Data Source and click Add New Data Source. Give your Data Source a Name and in the drop down list under type, select Microsoft SQL Server Analysis Services. Click Edit, type in your Server name and select the correct database. Click OK twice.

3. Now that we have created the Shared Data Source, its time to start building our report. In the Solution Explorer, right-click the Reports folder, (and because I don't like to use the wizard) select Add, then click New Item. Select Report, name your report in the Name text box, and click Add.

4. Then in the Report Data window, create a new data source. Name your data source something pertinent, like AWCube, and click the shared data source reference and select the Shared Data Source you just created.

5. Then right-click your newly created data source and select Add Dataset. Then click Query Designer. This is where you will build your MDX query. The Query Designer will allow you to graphically build your MDX query without requiring any knowledge of MDX.

6. To build the query, you can expand the folders containing your dimensions and measure and drag them into the design area. As you drag the members and measure into the design area, the Query Designer will attempt to execute the query to give you a preview of what the flattened data will look like. For this example, I dragged in Reseller, Department, Title, and Employee for the dimensions. The measures I used were Reseller Order Quantity and Reseller Sales Amount.

7. I also wanted to add a date range parameter. At the top of the Query Designer in the Dimension column, select Date as the dimension. In the Hierarchy column, select a date hierarchy. For the Operator, select Range (Inclusive). And for the filter expression, select " : ". Then in the Parameters column, check both check boxes. I also wanted to added a Parameter so the end users can filter the reports by Reseller. So I selected the Reseller as the dimension and hierarchy. For the Operator, I specified it as Equal and I left the Filter column blank. Lastly, I checked the check box in the Parameters column. This is what it should look like:

8. You can also filter your MDX query. For my query, I only wanted to see Resellers with a Reseller Type of Specialty Bike Shop. Under dimension I selected Reseller, under Hierarchy I selected Business Type, for the Operator I selected Equal and under Filter Expression I selected {Specialty Bike Shop}. But this time do not check the check box under Parameters since this is not a parameter, just a filter.

9. If you wanted to create a Calculated Members, right click in the Calculated Members and select New Calculated Members. This will open the Calculated Member Builder. You can drag in different measures and dimensions to build your Calculated Member. Here is an example of a simple calculated member that adds Internet Standard Product Cost to Internet Tax Amount to get Total Cost:

10. To view the MDX generated by the Query Designer, click the icon at the top of the Query Designer. Just a note: If you make any changes to the MDX while viewing it, you cannot go back to the MDX Query Designer.  Once you are done building your query, click OK. You can now use the fields specified within your report. After dragging the fields into my tablix and adding a few groups, here is the finished results of my report:

Here are few other links to some articles I've written involving MDX and reporting:

How to Use the Date Picker Control with MDX Based Reports

MDX Parameter with Multiple Members Consolidated Into One Value

And if you have questions about building a basic report, you should definitely check out these articles covering building a report from the ground up.

Please feel free to leave any comments or ask any questions. Thanks, guys!