Pros & Cons of Using the OLE DB Provider for SSAS 10.0 when Reporting in SSRS

October 23, 2011 Melissa Coates

Overview: This entry discusses some considerations for using the OLE DB Provider for SSAS 10.0 connection. The pros & cons mentioned are relevant to SQL Server Reporting Services, with SQL Server Analysis Services as a data source. SQL Server 2008 R2 is used for all screen shots.

Introduction

Typically when using SQL Server Reporting Services (SSRS) to retrieve data from an Analysis Services (SSAS) database, the first choice is to use the default data processing extension:

However, there are times it’s useful to use the OLE DB Provider instead:

Versions of the OLE DB Provider:

Version 10.0: SQL Server 2008
Version 9.0: SQL Server 2005
Version 8.0: SQL Server 2000

Pros & Cons of the OLE DB Provider for SSAS 10.0

So, why would you want to use OLE DB when reporting within SQL Server Reporting Services? To get some additional flexibility not afforded by standard SSAS connectivity.

Pros:

Returns parent/child results more flattened, in separate fields.
Intrinsic Member Properties (aka Dimension Properties) can be returned in the dataset as “real” fields.
Can use a cellset in the query.
Allows you to place measures on rows rather than just columns.
Better performance in some situations.

Cons:

Does not accept query parameters. (The workaround for this is to combine expressions with strings to build the query, which results in far more complex datasets which are more difficult to maintain.)
No automatic mapping of Extended Field Properties (aka Cell Properties). (The workaround for this is to adjust the connection string.)
Default field names are much longer.
No query designer available. (The workaround for this is to use the query expression pane.)
No support for server aggregates.

The following sections explore these Pros & Cons in greater detail. Let’s take a look at the Pros first.

Pro 1: Returning Parent/Child Results More Flattened

Following is a very simple query, using the parent/child hierarchy in the Employees dimension:

The above screen shot is using the standard SSAS connection. Using the MDX generated by the GUI (shown on the right above), I will create a second dataset using an OLE DB connection instead. In the next screen shot below, see how Level 2 is a different field than Level 3, which is different from Level 4, etc?

The MDX is actually the same in both datasets shown above; it’s the difference in the connection that drives the output.

The “flattening” of the parent/child levels can provide additional flexibility in report layout depending on what you need to do with your report. You definitely need to be careful with this though – summing the measure accurately is trickier after it’s been flattened out. It may be better to leave this as is, and use SSRS properties to report parent/child levels.

Pro 2: Intrinsic Member Properties Can Be Returned in Dataset as “Real” Fields

The same query used in Pro 1 above illustrates this topic nicely. In the following screen shot we have the MDX generated by SSRS, which asks for Dimension Properties (i.e., intrinsic member properties):

With the standard connection, member properties are not available in the drag-and-drop MDX Designer interface in SSRS, but they are accessible via expressions. Instead of the typical “Value” in an expression, the syntax would be:

Fields!FieldName(“PropertyName”) or
Fields!FieldName.PropertyName

However, if we use an OLE DB connection, we get the member properties of MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, and LEVEL_NUMBER are returned as “real” fields:

This isn’t a major advantage of the OLE DB connection type, but is kind of nice. It can be a bit more clear if an SSRS novice is trying to understand a report which has already been developed.

Pro 3: Using a Cellset in the Query

This one is a bit misleading – we do still need a rowset for Reporting Services. However, we can get away with a bit more in the MDX source query.

With the standard connection, a multidimensional cellset type of query (shown below) is not permitted.

If you attempt to use a cellset (like shown in the above query) with the standard connection, you will get this error:

The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.

However, you can execute the same query with the SSAS OLE DB connection type. The results are still flattened into a rowset for SSRS to process - see how the Measure & the Product Line are combined into one field returned by the dataset:

The flexibility of using a cellset is a particularly nice reason to use the OLE DB connection.

Pro 4: Place Measures on Rows

Very similar to Pro 3 mentioned above, the OLE DB connection permits you to place measures on the rows, whereas the standard connection requires measures to be placed only in columns.

With the standard connection, a query with measures on the rows (shown below) is not permitted.

If you attempt to place measures on the rows with the standard connection, you will get this error:

However, you can execute the query with the SSAS OLE DB connection type. The results are still flattened into a rowset for SSRS to process - see how the “Measures Level” is combined by the dataset:

The flexibility to place measures on either columns or rows is another nice reason to use the OLE DB connection.

Pro 5: Better Performance

I haven’t done any performance testing, so I will defer to Chris Webb’s discussion, as it relates to populating a drop-down list for a parameter.

Con 1: OLE DB Connection Does Not Accept Query Parameters

This is by far the biggest negative to using the OLE DB connectivity. The workaround to using parameters is to build the query with a combination of expressions & strings. For example:

Note in the above query the equals sign ( = ) is used at the beginning, so the entire thing is evaluated as an expression. The only thing not within double quotes are the expressions which SSRS evaluates to bring back the parameter value.

The vbcrlf’s are used just for readability – you could avoid a lot of the plus signs ( + ) and double quotes if more text was on each line. However, since the SSRS expression box doesn’t wrap terribly well, I tend to make my rows pretty narrow. Usage of the vbcrlf is discussed in this blog entry. A side benefit of formatting your MDX for readability with the vbcrlf’s is the MDX comes out quite readable within SQL Server Profiler.

Con 2: No Automatic Mapping of Extended Field Properties

Cell Properties, also known as Extended Field Properties, don’t come through the OLE DB connection type automatically. However, there is a workaround for this: you can modify the data source connection string.

Let’s start with the following query. Note how several Cell Properties are requested, such as BACK_COLOR, FORE_COLOR, FORMAT_STRING, etc.

Using the OLE DB connection with its default settings, these Cell Properties are not returned in the following dataset.

The standard connection doesn’t return the Extended Properties as “real” fields, and they aren’t available in the drag-and-drop MDX Designer interface in SSRS, but they are accessible via expressions. Instead of the typical “Value” in an expression, the syntax would be:

Fields!FieldName(“PropertyName”) or
Fields!FieldName.PropertyName

However, if we modify the connection string within our OLE DB connection, we can get them to return as “real” fields.

Provider=MSOLAP.4;Data Source=Localhost;Initial Catalog="Adventure Works DW 2008R2";Extended Properties="ReturnCellProperties=true"

After modifying the connection string, we get them returned as “real” fields:

Con 3: Default Field Names are Much Longer

This is not a big issue, but the field names generated by SSRS are significantly longer when using the OLE DB connection type. You could edit them if you prefer, but I don’t usually bother.

Con 4: No Query Designer Available

This is a big one in terms of usability:

No Query Designer is available
Cannot execute this query & preview results within the SSAS dataset

You need to use the expression pane for the query, as shown below. The Query Designer window (even if you click MDX mode instead of GUI mode) is not available.

Remember…if you click the “fx” button & paste in an mdx query (from SSMS or another tool), be sure to remove the equals sign ( = ) in there by default. (Or, alternatively, wrap the entire thing in double quotes if the = sign remains in front.) Normally I don’t use the = sign unless parameters are involved, which was discussed up in the Con 1 section above.

After you paste a query into the expression pane, if you click on the Fields pane (on the left), you’ll see the fields get added. If you manually edit the query & add or change fields, the Fields pane will not detect those changes automatically.

Con 5: No Support for Server Aggregates

This refers to the ability to use an expression such as =Aggregate(Fields!FieldName.Value) within the OLE DB connection type. The results will be blank because, according to Robert Bruckner, the OLE DB provider does not implement a RS data extension interface (IDataReaderExtension). You can use a Sum() or Count(), etc.

Summary

Personally, I use the standard SSAS connectivity unless I have a reason to switch to the OLE DB connection. The standard connectivity is newer, and is recommended by Microsoft. However, if you need some flexibility, using the OLE DB connectivity a good option to consider.

Finding More Information

MSDN: Data Sources Supported by Reporting Services (SSRS)

Chris Webb’s Blog: Tuning SSRS-Generated MDX Parameter Queries

Teo Lachev’s Blog: How to Get Extended Properties with SSAS OLE DB Provider

Greg Galloway’s Blog: Retrieving Cell Properties <—Really good stuff here!

MSDN: Analysis Services OLE DB Provider (Analysis Services – Multidimensional Data)

Analysis Services and PowerPivot Team Blog: Impact of the SQL Server OLE DB Deprecation on Analysis Services

MSDN: Intrinsic Member Properties

MSDN: Using Extended Field Properties for an Analysis Services Dataset