Your First OLAP Report

1. Introduction

This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes.

It demonstrates how to build a report using SQL Server Reporting Services 2008 with data coming from an OLAP cube running on SQL Server Analysis Services 2008.

The OLAP database used in the article is called "Adventure Works DW 2008", available for download at CodePlex.

If you're fairly new to Reporting Services (aka SSRS) and you find that this article is going a bit too fast, I'd like to point you to my other article which explains how to build a report that's retrieving data using regular stored procedures.

2. OLAP <> OLTP

When people are talking about databases, what they are usually referring to are "regular" relational OLTP databases.  OLTP stands for Online Transaction Processing.  As the name implies, these types of databases are built to handle many simultaneous transactions (consisting of actions such as inserts, updates, deletes) in real-time.  I'm sure you're familiar with these types of database so I won't go further into them.

OLAP (Online Analytical Processing) on the other hand is a totally different story.  OLAP cubes are built to answer multi-dimensional analytical queries as fast as possible.  For that purpose, what you can find in such a database are measures (these are the numbers) stored in cubes, and dimensions which allow filtering the measures.  This filtering is often referred to as slicing and dicing.  Furthermore, OLAP cubes contain pre-aggregated data, again to be able to answer queries as fast as possible.

Let's make this clear with an example.  Imagine the following request:

"Give me the sum of all sales of product X for period Y in country Z."

Three dimensions can be recognized in that request: "product X" is found in the Product dimension, "period Y" in the Date dimension and "country Z" in the Geography dimension.  (I've used the actual dimension names as they are called in the Adventure Works OLAP database.)

Each dimension consists of attributes and attribute hierarchies and it's those attributes that you're actually referring to when building an MDX query.  MDX stands for Multidimensional Expressions and that is the language used to query an OLAP database, just like you use SQL to query a relational database.

Looking at our example, what we need is for the Product attribute in the Product dimension to be equal to X.  An attribute in a dimension can also be written as [Dimension].[Attribute], thus we also want [Date].[Date] to be equal to Y and [Geography].[Country] equal to Z.

As for the measure part, that's what "the sum of all sales" is referring to.  When looking at the measures available in the Adventure Works cube, one of the measures that would fulfill the request is the Reseller Sales Amount in the Reseller Sales measure group.  The Analysis Services engine searches the cube and retrieves the aggregated number for [Measures].[Reseller Sales Amount] available at the intersection of [Product].[Product] X, [Date].[Date] Y and [Geography].[Country] Z.

OLAP cubes are usually, although not necessarily, build on top of a data warehouse.  In SQL Server, a data warehouse is still a relational database, unlike an OLAP cube, but the table structure is different from an OLTP database.  A data warehouse contains tables that represent dimensions and other tables that contain the facts.  The facts are the numbers, so the measures that were mentioned earlier.  This is called a dimensional model.  Dimensional modelling was invented by Ralph Kimball, one of the pioneers in data warehousing.  For completeness I'd like to mention that another data warehousing approach was described by Bill Inmon.  I'll leave it up to you to do some research on both approaches and decide for yourself which one you prefer, possibly even a mix of both.

As far as the "Adventure Works DW 2008" OLAP database is concerned, it's built on top of the AdventureWorksDW2008 dimensional database.

Okay, I believe this theoretical explanation was sufficient for now, let's start with the report!

4. Your First Report

Business Requirements

You've gotten the assignment to create a report that shows the reseller sales numbers by region.  The highest level to be shown is Country, with drilldown through State/Province to City.

Creating The Shared Data Source

Just like when building reports on OLTP databases, we're not going anywhere without a Data Source.  I'm going to create a Shared Data Source called OLAP_AdventureWorks.rds:

 
The Type that we need is Microsoft SQL Server Analysis Services, which is the SQL Server service that's running the OLAP databases.  Furthermore I've selected the "Adventure Works DW 2008" database.

 
There's no need to type the database name yourself.  After you've provided sufficient credentials in the Credentials page, you can just select it from the dropdown in the Connection Properties screen.  This screen is opened by clicking that Edit button on the Shared Data Source Properties window.

Your First OLAP Dataset

I've created a new report called FirstOLAPReport.rdl.  In that report I've specified that I'll be using the Shared Data Source created earlier.  This source is known as srcAdventureWorksOLAP in my report.

Next step is to create the dataset.  I'm calling it dsResellerSalesByRegion.  As this is our first OLAP report, we're not going to write the MDX ourselves but we will use the Query Designer which is opened by clicking the button that has the words Query Designer printed on them, how difficult can that be?!

 
The BIDS knows that it should open the MDX Query Designer because our data source is connecting to an Analysis Services server.  All we need to do now is to drag the measures and dimension attributes that we require into the area marked with "Drag levels or measures here to add to the query.".

Let's start by dragging our measures into that area.  We need two measures, both located in the Reseller Sales measure group.  They are called Reseller Order Quantity and Reseller Sales Amount.  Following screenshot shows the situation after the first measure has been added.  The second measure was being dragged into it as well.  When dragging items into the area, a vertical blue line appears to indicate where the item can be added.

 
Next I'm going to drag the Geography hierarchy, located in the Geography dimension, into the design area.

 
Now we've got all the data we need for our report.

As you have noticed, the Query Designer automatically executes the query each time it gets modified when you're dragging an item into the design area.  If you don't want this behaviour, it can be switched off by clicking the Auto Execute button in the toolbar (indicated by a red 1 in the screenshot below).

 
Another interesting button is the Design Mode button (indicated by a green 2).  This one allows you to toggle between the graphical designer and the text editor.  By clicking it you can see the actual MDX query that the designer has prepared for you.

As you can see, the query is nicely formatted using capitals for the keywords and so on.  Well, no, actually it's the worst editor around!  No syntax coloring, no multi-line formatting, nothing.  So if you are going to take a close look at the query, I recommend you to use the Management Studio.  Connect to your Analysis Services server, locate your database and right-click it in the Object Explorer.  Then choose New Query > MDX and paste the query into that new window.  You'll still need to manually break it down into different lines but at least you get syntax coloring.  Furthermore, if you're going to make manual modifications to it, you've got some command completion and error indicators as well.

Please take into account that once you've made manual changes to your query, you cannot switch back to the graphical designer.  Well, you can, but you will lose all manual modifications.  Don't worry about doing it accidentally though, a nice pop-up will warn you:

 
Something else that you'll also notice is that the results displayed in the Query Designer and those displayed in the Management Studio are not exactly the same.  That's because both environments interpret the results differently.  Remember, you're not retrieving two-dimensional row/column data like with a SQL query.  You're retrieving multi-dimensional data!

If you take a closer look at the query that we've produced above, it's similar to this:

SELECT something ON COLUMNS, 
                          something_else ON ROWS
                      FROM [Adventure Works]
                      

Select all Open in new window

That query is selecting data on two axes: COLUMNS and ROWS.  But in fact, MDX supports up to 128 axes.  However, the client tools that we are using here are not able to visualize that kind of cellset (as the result set of an MDX query is also called).
 
Okay, enough about our dataset.  We've got the data, let's put it on the report!
 
Displaying The Result Set

As a reference, these are the fields available in our dataset:

 
Without going into too much detail - there's no difference compared to reporting off a relational database - I've set up a table with three grouping levels on the rows.  I've also added some makeup like background colors and font modifications.

As shown in following screenshot, the highest-level group is Country, followed by State_Province and City to conclude, just as specified in the requirements mentioned at the start of this chapter.

 
Rendering the report in preview gives us something like this:

 
What is still missing at this point is decent formatting for those numbers!  And here's where we can take advantage of the fact that we're retrieving data from an OLAP cube.  A cube developer has the possibility to define the format for the measures in the cube itself.  Doing that ensures that the same formatting is applied no matter what OLAP client tool is used.  Any client that supports this way of formatting will show the numbers using the same format.

As you've seen in that last screenshot, there's no formatting applied at all.  Does this mean that there was no format defined in the cube?  Let's find out!

A Little Walk Into The Analysis Project

We are going to open up the Analysis Services project that contains the cube definition.  If you don't have any experience with SSAS, don't worry!  We will just have a look at a couple of properties and that's it, plus I'll explain each step as needed.  In case you've forgotten where the sources are located, this is the default location: C:\Program Files\Microsoft SQL Server\100\Tools\Samples\A dventureWo rks Analysis Services Project\.  I'm opening the project located under the \enterprise subfolder by double-clicking the Adventure Works.sln file.

Once the project is loaded into the BIDS, locate and open the Adventure Works.cube in the Solution Explorer.  You can find it in the Cubes folder of the Adventure Works DW project.

By default it will open the cube Design showing the first page called Cube Structure.  At the top-left, we've got the Measures pane.  The measures are shown in measure groups.  Open the group called Reseller Sales.  Now locate the measure called Reseller Sales Amount and select it.

 
Now that we've selected one of the measures that we are retrieving in our report, have a look at the Properties window.  In case it's not open yet you can right-click the measure and select Properties.  The property that we're interested in is called FormatString.

 
The cube developer has specified that this measure should be shown as being a Currency.

Now that you're in the cube, have a look at the properties for our other measure, the Reseller Order Quantity.  This one is being formatted as #,#.

The FormattedValue Field Property

So why are we not seeing those formats in our report?  Because by default they are not applied in an SSRS report!  When dragging fields from the Report Data window onto the design area, what the BIDS is retrieving is the Value property of the field.  However, there's also a property called FormattedValue.

(You may want to make a copy of your report before applying the following changes.)

Now, change the six table cells that are showing the numbers (so including the ones showing the totals) to retrieve the FormattedValue property instead of the Value property.  The expression for the totals of the Reseller Sales Amount looks like this:

=Sum(Fields!Reseller_Sales_Amount.FormattedValue)
                      

Select all Open in new window

Once you've done that, have a look at the Preview:

 
That doesn't look right, does it?  We've lost our numbers!

Now hit the Refresh button:  
This time we've got some numbers:

 
But we've also got some errors for free!  Looking at the Output window we get some extra details on the reason for the error.  Here's one of them:

[rsAggregateOfNonNumericDa ta] The Value expression for the textrun ‘Reseller_Order_Quantity1. Paragraphs [0].TextRu ns[0]' uses a numeric aggregate function on data that is not numeric.  Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

In short, what it says is that our data is not numeric.  And this poses an issue when it tries to apply the SUM() aggregate function.  Right, as our data now contains formatting, it became a string instead of a number, and strings can't be added together using SUM().

So that's not a good way to apply the formatting, not in this case anyway.  Luckily there's another method to do that.

But first, undo those last changes and replace the FormattedValue with the Value property.

(Or switch back to the original report if you took a copy earlier.)

The Cell Properties

What exactly is our MDX query doing?  I'm taking a closer look at it by taking it from the Dataset Properties window and pasting it into a MDX query window in the Management Studio:

SELECT 
                      NON EMPTY { [Measures].[Reseller Sales Amount], [Measures].[Reseller Order Quantity] }
                      ON COLUMNS, 
                      NON EMPTY { ([Geography].[Geography].[Postal Code].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
                      

Select all Open in new window

Besides retrieving the requested measures and dimension attributes, it's retrieving several Cell Properties, including FORMATTED_VALUE and FORMAT_STRING.  I believe that the first one rings a bell by now.  What we're going to do is to retrieve the second one and apply it as Format property for our numeric table cells.

In the report's Design, select one of the table cells containing a number.  In the Properties window, one of the properties is called Format.  Click to select it, then in the dropdown choose Expression….  For each of the six numeric cells, create an expression similar to the following:

=Fields!Reseller_Order_Quantity("FORMAT_STRING")
                      

Select all Open in new window

The example above tells the BIDS to retrieve the FORMAT_STRING cell property from the Reseller_Order_Quantity field.

Tip: you don't need to open up the Expression builder for each of the six cells.  You can just copy/paste the string from the Format field.  Just ensure that you're retrieving the format from the same field as the one that the cell is displaying.

Now let's have a look at the Preview again:

 
Hmm,  the quantities are fine now, but the currencies are not!  So, let's try out yet another method for those cells.

For the three cells containing a currency measure, remove the Format property - it's not working anyway!

Next, change the expression that's retrieving the Value property to something similar as this one:

=Format(Sum(Fields!Reseller_Sales_Amount.Value),
                          Fields!Reseller_Sales_Amount("FORMAT_STRING"))
                      	
                      

Select all Open in new window

This expression applies the value of the FORMAT_STRING property using the Format() function.  In this particular case it's the expression used to produce the Reseller Sales Amount total.

Having modified all three currency cells, here's another Preview look:

 
That certainly looks better doesn't it?!

Okay, to conclude, let's activate drilldown by setting the subgroup levels to a collapsed state by default.

I will not go into full detail on this.  To start, make sure that the cells that are going to contain the +/- toggle have gotten a decent name, such as txtCountry for the cell that shows the Country name.  Then edit the properties of the subgroups by setting Visibility to Hide.  Also, activate the Display can be toggled by this report item checkbox and select the textbox showing the label one level higher.  Shown below is how to configure the group on State_Province.

 
Let's have another look at the report Preview:

 
By default all nodes were collapsed.  I've expanded a couple of them just to show that it's all working.

The InitialToggleState Property

Okay, I will not let you go just yet.  To really conclude I'll let you in on a little feature related to the drilldown.  Open up the group properties for the State_Province group and set the initial visibility to Show (leave the "Display can be toggled by this report item" checked!).  Then checkout Preview:

 
Wow, that's weird, the country level is expanded and yet there's a plus icon in front of the country's name.  Clicking it will collapse the states and change the icon to minus.  If that isn't mixed up then I don't know what is!

Well, the solution to this problem is simple.  Select the textbox showing the country name and locate the InitialToggleState property.  By default this is set to False, which means collapsed or in other words, False shows the plus icon.  Change it to True and now your initial state icon will be a minus!

4. Conclusion

With this article I believe to have shown you how to get started with reporting off an OLAP cube while throwing in a couple of tips in the process.

Have a look at another article that I wrote earlier, it explains an issue which you may run into when taking OLAP reporting a step further: SSRS and MDX: Detecting Missing Fields.

Happy Reporting!

PS: it's small, it's blue, it's an image button, and it's quietly calling out the letters Y, E and S... ;-)

Valentino.

Originally appeared at my own website: http://blog.hoegaerden.be/2010/01/24/your-first-olap-report 

References
BOL 2008: The Basic MDX Query
BOL 2008: Using Cell Properties (MDX)
MDX: Retrieving Cell Properties by Greg Galloway
SQL Server Analysis Services Tutorial