Home » MS OfficeRSS

How to make up MDX with parameters of SSIS OLEDB data source

Hi everyone,

I want to make up a MDX query by using some parameters, just like:

SELECT
{
[Date].[Calender].[Day].&[1]&[1]&[2009]
}ON COLUMNS,
{...}ON ROWS
FROM [CubeName]

I put this query in the 'sql command' field of OLEDB data source, and replaced the date parts with parameter token '?':

SELECT
{
[Date].[Calender].[Day].&[?]&[?]&[?]
}ON COLUMNS,
{...}ON ROWS
FROM [CubeName]

However, when I clicked 'parameter' button, the exception below showed:

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable. (Microsoft Visual Studio)
===================================
Error Code = 0x80040E51, External Code = 0x00000000:. (Microsoft OLE DB Provider for Analysis Services 2008.)

Thanks
 

3 Answers Found

 

Answer 1

Hi,

You can use a variable to store the MDX query and then pass parameters to it. Try these steps:

 Create three variables of type of your parameter.(V_Date,V_time,V_year)

Initialize these variables by using an Execute Sql task and use a result set to initialize these variables.

 Create a variable V_MDX of string datatype and then in the properties of the variable use expression and paste the MDX query inside it, something like

"
SELECT
{
[Date].[Calender].[Day].&["+ ' V_Date '+ "]&["+ ' V_Time '+ "]&["+ ' V_Year '+ "]
}ON COLUMNS,
{...}ON ROWS
FROM [CubeName]
"

Now use this variable as a source in the OLEDB source drop down.

Note: I haven't tested this myself but sure will work , the expression provided might have some syntax issues, check it out.
 

Answer 2

Hi,

I have a similar task where : I want to retrieve top 25 author names with their count in previous month. I have written a query and in where clause

SELECT

 

([Measures].[Author : Count]) ONCOLUMNS, TopCount(order([Author - Name].members, [Measures].[Author : Count],DESC),26) onrowsfrom [Cube] where ([Company].[Company].[CompName],[Date].[Year].[Year],[Date].[Month In Year].[Month In Year].&[6])

 

How to make it dynamic to replace the month value in time dimension.

Vaibhav 


 

Answer 3

Hi

Do you now if is possible to use "WITH MEMBER" in a MDX query using SSIS?

 

I ask you this, becausse when I Try to use the following MDX query :

WITH
MEMBER [Measures].[FechaInicial] as '([Periodo].[Año-Semana-Día].[Year].&[2010-01-01T00:00:00].&[2010-09-09T00:00:00].&[2010-09-15T00:00:00],[Measures].[Cant Servicios])'
MEMBER [Measures].[FechaFinal] as '([Periodo].[Año-Semana-Día].[Year].&[2010-01-01T00:00:00].&[2010-09-16T00:00:00].&[2010-09-22T00:00:00],[Measures].[Cant Servicios])'
SELECT
NON EMPTY
({   [Measures].[FechaInicial], [Measures].[FechaFinal]
})ON COLUMNS , ...... etc.etc...

this raise the following error, when I press button "Parse Query":

"The Measures hierarchy already appears in the Axis0 axis."

Cheers,

Alvaro

 

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter