MDX query with parameter using OleDb?

I posted this question on the Analysis Services forum ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087288&SiteID=1 ) and Irina asked me to post it over here to get an answer.

I'm trying to run an MDX query against SSAS2005 with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:

//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

AdomdCommand cmd = new AdomdCommand(MDX, conn);

cmd.Parameters.Add("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

AdomdDataAdapter adp = new AdomdDataAdapter(cmd);

adp.Fill(ds);

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

The following code fails:

//using System.Data.OleDb;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

OleDbCommand cmd = new OleDbCommand(MDX, conn);

cmd.Parameters.AddWithValue("Param1", "abcde"); //changing it to @.Param1 doesn't help

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

adp.Fill(ds); //produces error: "The following system error occurred: The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

reported this issue to connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251601

I hope that's the right place to report OleDb bugs.

MDX query with parameter using OleDb?

I posted this question on the Analysis Services forum ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087288&SiteID=1 ) and Irina asked me to post it over here to get an answer.

I'm trying to run an MDX query against SSAS2005 with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:

//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

AdomdCommand cmd = new AdomdCommand(MDX, conn);

cmd.Parameters.Add("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

AdomdDataAdapter adp = new AdomdDataAdapter(cmd);

adp.Fill(ds);

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

The following code fails:

//using System.Data.OleDb;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

OleDbCommand cmd = new OleDbCommand(MDX, conn);

cmd.Parameters.AddWithValue("Param1", "abcde"); //changing it to @.Param1 doesn't help

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

adp.Fill(ds); //produces error: "The following system error occurred: The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

reported this issue to connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251601

I hope that's the right place to report OleDb bugs.

MDX query problem (NONEMPTY)

Could someone have a look at the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1042606&SiteID=1

It is TeamSystem related but it's all about reporting and MDX.

Thanks.

Refer to my response on the thread - basically, it looks like there are separate "Current Work Item" versus "Work Item History" fact tables and perspectives. The data you want seems to be historical, and hence related to "Work Item History", rather than to "Current Work Item" ..|||See my reply in the original post in the Team Foundation forum

MDX Queries in SSIS

Posted to SSIS forum but was told this forum might be of better help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=215075&SiteID=1

Essentially I'm using an OLE DB Source to fire off an MDX Query and I want to dump the results into a csv file.

I think I have this setup properly but when I run the package I get the following error.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x80004005 Description: "Internal error: An unexpected error occurred (file 'pcrsstore.cpp', line 325, function 'PCRSStore::UnLock').".

Can seem to find anything about this error, can anyone help?

Keith

Hi Keith.

First
Have you installed SP1 on you machine. If not, please try installing it and trying your experiment again.

Second.
Can you explain in a bit more details the way you've build your task in SSIS

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes I have installed SP1 on both the Ananlysis Server and my Client tools.

The tasks are pretty simple. I have 2 connections and 1 Data Flow Task.

Create OLEDB Connection, select
Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0
as OLE DB provider, enter connection information.

In data flow, create OLE DB Source component, choose this connection, in Data access mode field select SQL command. Enter your MDX query in SQL command text box.

At this point I can preview the results of the query after receiving the following warning.

Warning at {C498A441-B272-48F7-AA8E-1C1BC11DDCD2} [OLE DB Source [1]]: The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

I then see the results of the MDX query and the data is returned as expected.

I then have a Flat File Destination setup where I point the Data Flow Output to. It's a simple 2 column 1 row result set. I recieve no warnings or errors in the designer but when I run the package I get the error as explained above.

Just as a sanity check becuase this is my first time working in SSIS. I Changed the query to be a simple SQL query returning 2 rows and was able to run that just fine so I think I have the data flow setup properly.

In addition I have run a SQL Profile and I can see the query being sent to AS and the results being returned.

Appreciate the help.

|||

This looks like a bug with Analysis Services.

If you have some ideas try to work around it using SQL like syntax when sending query to Analysis services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.