Excel Against a SSAS Cube Slow: Performance Improvement Tips

Posted on January 13, 2011 19:21

Applies to SSAS 2008 and Excel 2007.

Also see this blog entry: OLAP Reporting with Excel 2007: Use Member Properties!! at http://blog.davyknuysen.be/2009/08/03/olap-reporting-with-excel-2007-use-member-properties/

As you add columns from a SSAS cube to pivot table in Excel it will progressively get slower to the point that it is virtually unresponsive. In order to understand exactly why this occurs we need to look at how Excel builds its MDX. Let’s take a look at a simple pivot table against AdventureWorks.

So what is the underlying MDX that is executed to get this result?

SELECT NON EMPTY

CrossJoin(

Hierarchize({DrilldownLevel({[Product].[Product].[All Products]})})

, Hierarchize({DrilldownLevel({[Product].[Color].[All Products]})}))

, Hierarchize({DrilldownLevel({[Product].[Size].[All Products]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME

,[Product].[Product].[Product].[Class]

,[Product].[Product].[Product].[Color]

,[Product].[Product].[Product].[Days to Manufacture]

,[Product].[Product].[Product].[Dealer Price]

,[Product].[Product].[Product].[End Date]

,[Product].[Product].[Product].[Large Photo]

,[Product].[Product].[Product].[List Price]

,[Product].[Product].[Product].[Model Name]

,[Product].[Product].[Product].[Reorder Point]

,[Product].[Product].[Product].[Safety Stock Level]

,[Product].[Product].[Product].[Size]

,[Product].[Product].[Product].[Size Range]

,[Product].[Product].[Product].[Standard Cost]

,[Product].[Product].[Product].[Start Date]

,[Product].[Product].[Product].[Status]

,[Product].[Product].[Product].[Style]

,[Product].[Product].[Product].[Subcategory]

,[Product].[Product].[Product].[Weight] ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Order Count])

CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Notice that there are two CrossJoin function calls in the MDX which build a set of 3 Hierarchize function call for each of the selected dimension attributes. The CrossJoin function performs a cross product of two set and the sets are the results of Hierarchize function which organizes the members of the set in hierarchical order. As you can see the number of member of the set returned can grow exponentially as more and more columns are added to the pivot table. As you add a column to the excel spreadsheet Excel will add another CrossJoin and Hierarchize function the MDX. For example the adding the Size Range attribute leads to the following query:

SELECT NON EMPTY

CrossJoin(

Hierarchize({DrilldownLevel({[Product].[Product].[All Products]})})

, Hierarchize({DrilldownLevel({[Product].[Color].[All Products]})}))

, Hierarchize({DrilldownLevel({[Product].[Size].[All Products]})}))

, Hierarchize({DrilldownLevel({[Product].[Size Range].[All Products]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME

,[Product].[Product].[Product].[Class]

,[Product].[Product].[Product].[Color]

,[Product].[Product].[Product].[Days to Manufacture]

,[Product].[Product].[Product].[Dealer Price]

,[Product].[Product].[Product].[End Date]

,[Product].[Product].[Product].[Large Photo]

,[Product].[Product].[Product].[List Price]

,[Product].[Product].[Product].[Model Name]

,[Product].[Product].[Product].[Reorder Point]

,[Product].[Product].[Product].[Safety Stock Level]

,[Product].[Product].[Product].[Size],[Product].[Product].[Product].[Size Range]

,[Product].[Product].[Product].[Standard Cost]

,[Product].[Product].[Product].[Start Date]

,[Product].[Product].[Product].[Status]

,[Product].[Product].[Product].[Style]

,[Product].[Product].[Product].[Subcategory]

,[Product].[Product].[Product].[Weight] ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Order Count])

CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

So you can see all these cross products quickly get out of hand and performance suffers. Since we cannot control how Excel builds the MDX how can we help this.

There are two options that I am going to bring up here. First we could just add a unnatural hierarchy to the dimension which consist of Product, Color, Size and Size Range and this will materialize the cross join in the cube so that performance will be much better but this is really impractical since there is virtually unlimited number of combinations that would have to be handled. Another approach is to use another less well know feature of Excel and that is Show Properties in Report.

If you notice in our example [Product].[Product] is the primary key of the dimension so the measure is unaffected by the additional columns in pivot table. A product can only have 1 color, size and size range. We are at the grain of the dimension once we have put Product in the pivot table.

In order to have Excel not CrossJoin the additional attributes we do the following:

1. Remove the Color and Size attribute from the pivot table.

2. Select them to show in the report using the Show Properties in Report field menu item.

Now we have the same data in Excel and at virtual no extra cost we can add any number of additional attributes to the pivot table.

The MDX generated looks like this:

SELECT NON EMPTY

Hierarchize({DrilldownLevel({[Product].[Product].[All Products]})})

DIMENSION PROPERTIES PARENT_UNIQUE_NAME

,[Product].[Product].[Product].[Class]

,[Product].[Product].[Product].[Color]

,[Product].[Product].[Product].[Days to Manufacture]

,[Product].[Product].[Product].[Dealer Price]

,[Product].[Product].[Product].[End Date]

,[Product].[Product].[Product].[Large Photo]

,[Product].[Product].[Product].[List Price]

,[Product].[Product].[Product].[Model Name]

,[Product].[Product].[Product].[Reorder Point]

,[Product].[Product].[Product].[Safety Stock Level]

,[Product].[Product].[Product].[Size],[Product].[Product].[Product].[Size Range]

,[Product].[Product].[Product].[Standard Cost]

,[Product].[Product].[Product].[Start Date]

,[Product].[Product].[Product].[Status]

,[Product].[Product].[Product].[Style]

,[Product].[Product].[Product].[Subcategory]

,[Product].[Product].[Product].[Weight] ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Order Count])

CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Notice there is no longer any CrossJoin at all and we have virtual the same data in Excel.

If you want to control the order of the properties that appear in Excel you can use the Field Properties tool in the pivot table ribbon.

You can really see the difference in performance if you just put all the dimension attributes as columns in the pivot table and compare its performance with the performance when you Show all the Properties in the Report.

Conclusion

Using Show Properties in Report can provide much better performance but it is not always possible because the attributes you are looking for are not properties of a column you are showing but it is just one more way to get better performance out of Excel.

In general once you pull in the key attribute of the dimension you should use Show Properties in Report for all other attributes from that dimension.

Posted in: Analysis Services, SSAS, MDX, Blog

Actions: E-mail | Permalink | Comments (0)

| Kick it! | DZone it! | del.icio.us

SSRS: Unexplained Warning “This field is missing from the returned result set from the data source” And Checking a Field for Null Exception

Posted on September 17, 2010 16:13

I recently had a report that ran fine with no error and then we reprocessed the cube with a different data source and the report had many cells displaying #Error. After playing with it for a while I noticed that it was also getting a warning message “This field is missing from the returned result set form the data source. The columns displaying errors were the ones reference in the error message and had a value derived by an expression to replace nulls with 0, something like Iif(Field!MTD_Incident_Count.Value is nothing,0,Field.MTD_Incident_Count.Value).

It turns out the problem is caused by the way Analysis Services(SSAS) data provider and SSRS handle a column in a result set that has nulls in every row. What the SSAS data provider does is not send the column down to the client because the value in every row is null. What that means is you cannot even check the field!MTD_Incident_Count.Value in any way because it does not exist in the result set so the Value method on the fields object will give an exception if reference in anyway.

When the report runs you see the warning about fields missing in result set.

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

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

You only get this warning if the value of every row for a column is null. The way to fix this is to change the expression that is used to replace the null with 0.

First we must create a report function (Code tab in Report Properties dialog) that can look at the IsMissing field property before it looks at the Value property. An Immediate If (IIF) statement cannot be used because all parts of the statement are evaluated which causes an exception.

Here is an example of the function to add. We may need multiple versions if the data type it returns need to change.

'If a column in a AS resultset contains all nulls. It is dropped from the dataset so you

'cannot reference the value field in any way so IsNothing(Field!A.Value) will throw an

'exception. In order to get around this error you can define a function to in the report

'you can define a function to check if the field is missing before checking for nulls

Function NullAsZero(ByRef F As Field) As Double

If F.IsMissing Then

Return 0

Else

If F.Value Is Nothing Then

Return 0

Else

Return F.Value

End If

End Function

The Report Properties Dialog is used to add the function.

Next you need to replace the expression used on the report cell Iif(field!MTD_Incident_Count.Value is nothing,0,field.MTD_Incident_Count.Value) with something like this Code.NullAsZero(Fields!MTD_Incident_Count).

And finally notice the NullAsZero returns a double which provides the most flexible numeric result but it must be formatted to what you want. Use the TextBox Properties Dialog Number tab to get it to display correctly, with the number of decimal places as needed.

Text Box Properties

This problem was very aggravating and I hope this entry will help someone keep their sanity.

Posted in: Analysis Services, Reporting Services Development, SQL Server 2008, SSAS

Actions: E-mail | Permalink | Comments (0)

| Kick it! | DZone it! | del.icio.us

Excel Against a SSAS Cube Slow: Performance Improvement Tips

Conclusion

SSRS: Unexplained Warning “This field is missing from the returned result set from the data source” And Checking a Field for Null Exception

John Hall