Posts Tagged ‘Connections’

SSRS – AS Vs OLE DB Connection

January 3, 2012 2 comments

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

Categories: SSRS Tags: