Home > SSRS > SSRS – AS Vs OLE DB Connection

SSRS – AS Vs OLE DB Connection

I like errors as those are the main source for me to learn new things. I always feel that I am going to learn a new thing whenever I get an error while developing.
While converting MDX – Label query which presented in the previous post  into a report, I got an error as
 
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.
Parameter name: mdx (MDXQueryGenerator)

 
Process of solving the error as
 
Step 1: Error message
Used a standard SQL server Analysis services connection as source and the error has report as (This is the best practice of connection string while accessing the cubes from SSRS)

 
Step 2: OLEDB Connection
No idea on how to solve the issue so as usually started searching on the web and came with a minimal understanding of the limitation of Standard Analysis services connection.
Implemented the query using OLEDB connection as

As the diagram is self-explanatory of the implementation as
Embedded connection
Type: OLE DB
Connection string
Data source: OLE DB (OLE DB)
OLE DB Provider: Microsoft OLE DB Provider for Analysis Services 10.0
 
Step 3:
Used the OLE DB Data source for the dataset as

The query parsed successfully and the result dataset as

 
Points to consider:
 
1. OLE DB is not a recommended connection string until otherwise has a strong reason to use
2. The objects will become a flat records so the objects properties will come into report designer and need to be cautions

Advertisements
Categories: SSRS Tags:
  1. Stephanie
    August 30, 2012 at 2:11 am

    Great post! It has helped me in a huge way getting over the 8K character limit for OpenQuery — which is what I was using to get measures on rows. However, the problem I’m running in to with this method is that I can’t seem to use parameters.

    For instance:
    SELECT
    { [Measures].[Measure1], [Measures].[Measure2]} ON COLUMNS,
    { [Time].[Month Name].&[2012]&[1]&[1], [Time].[Month Name].&[2012]&[1]&[2]} ON ROWS
    FROM
    (SELECT (StrToSet(@MyParam, CONSTRAINED)) ON COLUMNS FROM [MyCube])

    This prompts me for the parameter value. If I type in [MyDimension].[MyAttribute].&[Bob] and click the OK button I get an error that states the “the parameter is incorrect”.

    If I substitute the StrToSet with [MyDimension].[MyAttribute].&[Bob] it works just fine.

    Do you know if there is some special way to do params with this provider?

    Thanks!!!
    Steph

    • August 30, 2012 at 8:43 pm

      Hi Steph,

      We have work around for this parameterisation
      1. Make the source query as an expression as

      ="SELECT { [Measures].[Internet Total Sales] } ON 0 ," +
      " [Geography].[Country Region Name].&[Australia] ON 1" +
      " FROM [Internet Operation]" +
      " where [Date].[Calendar Year].&["+Parameters!Year.Value+"]"

      2. Pass parameter value from report parameter to source query as

      Source Query Construction

      Please let me know for any help

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: