Analysis Services

You are currently browsing the archive for the Analysis Services category.

Introduction

This article is aimed at report developers who are used to develop 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.

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 modeling 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!

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:

Shared Data Source connecting to Adventure Works OLAP Database

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.

Connection Properties specifying the Adventure Works DW 2008 OLAP 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?!

How to open the MDX Query Designer

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.

MDX Query Designer: dragging a measure into the query

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

MDX Query Designer: dragging a hierarchy into the query

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).

Query Designer toolbar

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:

Warning message when switching back to design mode.

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]
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:

Fields available in OLAP 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.

Table with three groupings defined

Rendering the report in preview gives us something like this:

Report without any numeric formatting applied

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\AdventureWorks 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.

Cube in Design with Reseller Sales Amount selected

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.

Properties of the Reseller Sales Amount measure showing Currency as format string

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)

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

Report Preview showing no numbers after retrieving the FormattedValue property

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

Now hit the Refresh button: Refresh button in Report Preview

This time we’ve got some numbers:

Report Preview showing formatted numbers, and errors!

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:

[rsAggregateOfNonNumericData] The Value expression for the textrun ‘Reseller_Order_Quantity1.Paragraphs[0].TextRuns[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

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")

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:

Format is working for the quantity amounts but not for Currency!

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"))

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:

Both Currency and regular numeric cells are showing formatted values!

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.

Group Properties showing how to activate drilldown

 

Let’s have another look at the report Preview:

Fully working drilldown report

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:

Visibility toggle is broken!

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!

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!

Valentino.

References

BOL 2008: The Basic MDX Query

BOL 2008: Using Cell Properties (MDX)

MDX: Retrieving Cell Properties by Greg Galloway

Share

Tags: , , , , , ,

For this example I’ll be using the Adventure Works cube running on SQL Server Analysis Services 2008 and Reporting Services 2008.

In case you don’t have the AdventureWorks databases and cubes yet, they’re available at CodePlex.

Scenario

The sales department has asked for a report that displays the number of product items sold during a selected period.  As the company is active in two different markets, both the internet and reseller numbers should be shown.  The figures need to be grouped by product category, with drilldown to product level through subcategory.

Besides the period filter, it should be possible to filter on product category to limit the number of items shown.

Also, the background of the numeric cells should get a color depending on the value in the cell.  Colors range from red for low sales figures to green for high sale volumes.  The ranges are variable and should thus be configurable using 3 threshold parameters.  Following table shows the ranges as the department has requested them:

Value X Background Color
X < LowThreshold Red
LowThreshold <= MiddleThreshold Orange
MiddleThreshold <= X < HighThreshold Yellow
HighThreshold < X Green

 

Selecting The Data

At first sight this seems like a fairly simple report.  So you start building your MDX query using the Query Designer:

MDX Query Designer

Two filters have been specified: one of them is a date range and the other is based on product category.

Visualizing The Data

Then you drag a tablix onto the report body and play around with it until you get to the following:

Tablix in design mode

This is what it looks like when rendered, all seems to work fine:

Rendered report

To get the textbox background coloured based on the thresholds, you’ve produced an expression.  This expression is specified in Textbox Properties > Fill > Fill Color and looks like the following:

=Switch
(
    Fields!Internet_Order_Quantity.Value < Parameters!LowThreshold.Value, "#ff0e0e",
    Fields!Internet_Order_Quantity.Value >= Parameters!LowThreshold.Value
        and Fields!Internet_Order_Quantity.Value < Parameters!MiddleThreshold.Value, "#ff922d",
    Fields!Internet_Order_Quantity.Value >= Parameters!MiddleThreshold.Value
        and Fields!Internet_Order_Quantity.Value < Parameters!HighThreshold.Value, "#fff70f",
    Fields!Internet_Order_Quantity.Value >= Parameters!HighThreshold.Value, "#5cff21"
)

It’s a simple Switch statement using the threshold parameters.

A Missing Field Issue

So you deploy your report to the server for the users to test.  All is quiet, until someone starts complaining that the colouring doesn’t always work, for instance when filtering on Components.  Of course, you don’t always believe what the user says and try it out for yourself:

Rendered report with missing field issue

Indeed, the background is no longer coloured for the internet sales.  On top of that, the BIDS shows a couple of warnings in its output window:

[rsMissingFieldInDataSet] The dataset ‘ProductSales’ contains a definition for the Field ‘Internet_Sales_Amount’. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The dataset ‘ProductSales’ contains a definition for the Field ‘Internet_Sales_Amount’. The data extension returned an error during reading the field. There is no data for the field at position 4.

Hang on, but I am selecting the field in my dataset, how can it be missing?  Except, this is MDX and OLAP, not SQL and OLTP.  By default, the MDX Query Designer uses NON EMPTY in the SELECT statement.  This means that the rows where there are no values for the selected measures will not be contained in the result set.  It also means that the complete measure will be omitted in the case that there are no values for it in any of the rows, which is the reason for our problem.

You could choose to not use NON EMPTY in the query.  To achieve this using the designer, right-click in the results pane and click the Include Empty Cells item.

MDX Query Designer result pane popup menu

Keep in mind that this will result in more rows in your result set because you’re now selecting all the empty measure cells as well.  Depending on your report requirements this may not be the desired effect.  On the other hand, it could be exactly what you want.  If our sales department had asked that the report should always show all products, even when there are no sales for the period, then we’d need to query the cube in this way.

For the sake of the example (and to save some trees in case the sales department is going to print the report :-) ) we will not choose this option.

Attempt to fix #1

As the field does not always exist, you decide that it’s a good idea to test for its existence.  A field in a resultset has an IsMissing property which serves that purpose.  So you adapt your expression to the following:

=IIF(Fields!Internet_Order_Quantity.IsMissing, Nothing,
    Switch
    (
        Fields!Internet_Order_Quantity.Value < Parameters!LowThreshold.Value, "#ff0e0e",
        Fields!Internet_Order_Quantity.Value >= Parameters!LowThreshold.Value
            and Fields!Internet_Order_Quantity.Value < Parameters!MiddleThreshold.Value, "#ff922d",
        Fields!Internet_Order_Quantity.Value >= Parameters!MiddleThreshold.Value
            and Fields!Internet_Order_Quantity.Value < Parameters!HighThreshold.Value, "#fff70f",
        Fields!Internet_Order_Quantity.Value >= Parameters!HighThreshold.Value, "#5cff21"
    )
)

However, when filtering on Components the same problem still occurs.  How can this be?  Expressions in SSRS are built using Visual Basic where expressions are evaluated completely.  In our case both the True and the False part of the IIF function are evaluated even when it will always be true.

On to another attempt to get this working.

(Attempt to) fix #2

The previous fix attempt has shown that it’s not possible to use an expression for the field validity test.  At least, not in the way we’ve tried until now.  Let’s try using custom code.

Custom code can be added to a report through the Code page in the Report Properties dialog box (accessible through the menu Report > Report Properties… or by right-clicking the report’s yellow background).

Report Properties > Code dialog box

Let’s start with a small extra requirement.  When a measure is not present in a row, such as the Internet Order Quantity for the products in the Components category, the report should display a zero instead of blank space.  To get this done we again need to test on whether or not the field exists in the result set.

The following Visual Basic function accepts a Field object and returns the value of the field when the field exists or zero when the field does not exist.

'returns the field's value or zero if the field does not exist
Public Function GetValue(field as Field) as Long
  If (field.IsMissing) Then
    Return 0
  ElseIf (IsNothing(field.Value)) Then
    Return 0
  Else
    Return field.Value
  End If
End Function

This function can now be used in an expression anywhere in the report.  Here’s what the expression looks like for the Value of the textbox that shows the Internet Order Quantity:

=Code.GetValue(Fields!Internet_Order_Quantity)

The same expression is used for the textboxes that display the sum values:

=Sum(Code.GetValue(Fields!Internet_Order_Quantity))

Attention: the function calls above are passing the actual Field object, not the Value property of the field, so not Fields!Internet_Order_Quantity.Value.

So, on to getting our coloring working as required.  For this we need a function that returns the right color for the given amount.  Something like this:

Public Const ColorLow As String = "#ff0e0e"      'red
Public Const ColorLowMid As String = "#ff922d"   'orange
Public Const ColorMidHigh As String = "#fff70f"  'yellow
Public Const ColorHigh As String = "#5cff21"     'green

Public Function GetColor(field as Field, low as Integer, mid as Integer, high as Integer) as String
  If (field.IsMissing) Then
    Return ColorLow
  ElseIf (IsNothing(field.Value)) Then
    Return ColorLow
  Else
    Select Case field.Value
      Case Is < low
        Return ColorLow
      Case Is < mid
        Return ColorLowMid
      Case Is < high
        Return ColorMidHigh
      Case Is >= high
        Return ColorHigh
    End Select
  End If
End Function

This function accepts a field plus the three threshold values.  Depending on the value of the field and the thresholds, the expected color string is returned.  The red color is returned as well when the field does not exist.

As a good coding practice I’ve created constants for the color strings.  This method allows you to define constants that are available in the whole report – could be interesting if the same colors are used in different parts of a report for instance.

This is the expression used for the BackgroundColor property of the TextBox:

=Code.GetColor(Fields!Internet_Order_Quantity,
    Parameters!LowThreshold.Value,
    Parameters!MiddleThreshold.Value,
    Parameters!HighThreshold.Value)

Again the actual Field object gets passed as first parameter, not just the value.

If we now run the report with a filter on Components, the warnings will still appear in the Output window, but the report will function as expected as the following screenshot shows.  (No Photoshop was used in the making of this screenshot.)  Instead of empty cells the report shows zeroes and the background is coloured even when there are no sales.

Rendered report - fully functional

Extra info can be found in the MSDN page about Using Dataset Field Collection References in Expressions.

Share

Tags: , , , , , , ,

Have you ever had the situation that you wrote an MDX query to get some data out of a cube in Management Studio and executed it and waited and waited and the query never seems to end so you decide to cancel it so you click the red square button and then you wait and wait and wait some more and….  Well, it’s a bug.

And the worst part of it, in my opinion, is that, even when you close your client connection to Analysis Services, the query keeps running on the server consuming CPU and memory and will finally take it down.

The good part is that Microsoft has fixed it in Cumulative Update 2 for Service Pack 2 for SQL Server 2005.  And now of course SP3 can be used as well to fix this situation.

The bug is described here: http://support.microsoft.com/kb/935832

Share

Tags: , , , , , , , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved