Don't use ROUND() in a Reporting Services Model against a UDM

I recently had an interchange with someone on the Olap newsgroup who was having trouble with a Report Builder model based on their Analysis Services UDM.

Just as a bit of background, Marco Russo has an excellent post here http://sqljunkies.com/WebLog/sqlbi/archive/2005/11/15/17397.aspx on how to build a report model based on an Analysis Services UDM. This is not exactly what I would call an intuative operation, but I am assuming that the function was put in SSMS rather than BIDS as there is no real “designing” that can be done with a model based on a UDM.

This person was lamenting the fact that their users could not use the Round() function because it relied on the Excel library and their IT people would not let Excel be installed on a production server (which is not an unreasonable thing in my opinion). I pointed out to them that if they used the built-in VBA.Round() instead of Excel.Round() they would not need Excel on the server. Unfortunately they did not have any control on how the round function was being applied as the query in question was generated from a Report Model.

I did a quick check by running SQL Profiler against Analysis Services whil building and executing a report using a Report Model against a cube. I did not really have to check the trace, the performance alone proved that Excel was being invoked. When you make an MDX query using Excel.Round() (or any Excel function for that matter), the server has to fire up an instance of Excel and then communicate across processes. This is a very expensive think to do and some of the simple queries I was using for testing blew out from less than 2 seconds to more than 30! When you use the built-in VBA library the calls run in-process. (there is still some marshalling that goes on as the unmanaged code in Analysis Services communicates with the managed MDXVBA library, but this is nothing compared to the overhead of cross-process calls).

So, at this point it appears that Reporting Services is using the Round function from Excel and not the one from the VBA library and there is not too much that we can do about it. The person I was discussing this issue with has posted a suggestion with Microsoft that you can see here: http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=cd039fd1-c0d7-4b02-818e-e7d690a5b5de (vote on it if you think it deserves attention).

Until Microsoft “fixes“ reporting services, I can only see two possible workarounds:

  1. Do any rounding that needs to be done in calculate measures and hide the raw measures. The advantage to doing any rounding in the cube is that, no matter what client application is used to query the cube, the same figures are always reported.
  2. Use formatting instead of rounding. This can lead to some questions from end users when total rows don't appear to add up to the sum of the detail rows. Unlike the first option, you can implement formatting either in the cube or in the report.

Print | posted on Sunday, May 14, 2006 9:55 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)