none
MDX Named Set and OPENQUERY

    Question

  • When I run the following MDX Query in SSMS towards an SSAS 2005 cube, I get
    correct results:
    with set SelectedStores as {Stores.Stores.members}
    select
    {[Measures].[Units]} on columns,
    nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows
    from [smCube]

    However if I run the same query from SQL Server (relational) using
    OPENQUERY, I get an error "OLE DB provider "MSOLAP" for linked server "olap"
    returned message "Query (5, 56) The dimension '[SelectedStores]' was not
    found in the cube when the string, [SelectedStores], was parsed."."
    select *
    from openquery(olap,'
    with set SelectedStores as {Stores.Stores.members}
    select
    {[Measures].[Units]} on columns,
    nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows
    from [smCube]
    ')

    What am I doing wrong?

    When I run the same query without the named set, I also get correct results:


    Prashant Mhaske
    Wednesday, December 21, 2011 7:21 AM

All replies

  • Hi,

    Try this one

     Declare

    @MDXExpression as Varchar(MAX)

    Set

    @MDXExpression ='with

    set SelectedStores as {Stores.Stores.members}

    select

    {[Measures].[Units]} on columns,

    nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows

    from [smCube]'; 

    Exec (' SELECT * Into #temp FROM OpenQuery(olap,''' + @MDXExpression+ ''')')

    Select * FROM #temp

      

     


    Sanjeewan
    Wednesday, December 21, 2011 7:58 AM
  • Hi Sanjeewan,

     

    I am trying to execute below code in Sql query window but i am getting few errors . Could you please validate below code ?

    Declare
    @MDXExpression as Varchar(MAX)

    Set
    @MDXExpression ='WITH
    MEMBER  [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Qualification].[Qualification Desc].CurrentMember.Parent) * 100, Format_String ="#,0.00" 
    SELECT
    NON EMPTY
    { [Measures].[Volume],[Measures].[VolumePercent]} ON COLUMNS , NON EMPTY  {   Order   (   Tail   (   [Date].[Year -  Month -  Date].[Date].MEMBERS   ,10000   )  
    ,[Date].[Year -  Month -  Date].CurrentMember.Member_Key   ,DESC   )   
     * [Qualification].[Qualification Desc].CHILDREN   }  
    ON ROWS  
    FROM [cube_PresortIntegrity]  
    WHERE ([Date].[Date].&[2010-01-01T00:00:00] : [Date].[Date].&[2012-01-01T00:00:00])';

    Exec (' SELECT * Into #temp FROM OpenQuery(MSOLAP, DATA SOURCE=localhost; Initial Catalog=My_AS;,' + @MDXExpression+ ')')

     select * from #temp

     

    Thanks !


    Prashant Mhaske
    Wednesday, December 21, 2011 10:19 AM
  • Will you please post the error ?

     


    Sanjeewan
    Wednesday, December 21, 2011 10:30 AM
  • Hi Sanjeewan,

     

    I am getting below errors,

     

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'DATA'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near '{'.
    Msg 208, Level 16, State 0, Line 18
    Invalid object name '#temp'.


    Prashant Mhaske
    Wednesday, December 21, 2011 10:45 AM
  • Try this  please check your dates and where portion

    Declare
    @MDXExpression as Varchar(MAX)

    Set
    @MDXExpression ='WITH
    MEMBER  [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Qualification].[Qualification Desc].CurrentMember.Parent) * 100, Format_String ="#,0.00" 
    SELECT
    NON EMPTY
    { [Measures].[Volume],[Measures].[VolumePercent]} ON COLUMNS , NON EMPTY  {   Order   (   Tail   (   [Date].[Year -  Month -  Date].[Date].MEMBERS   ,10000   )  
    ,[Date].[Year -  Month -  Date].CurrentMember.Member_Key   ,DESC   )   
     * [Qualification].[Qualification Desc].CHILDREN   }  
    ON ROWS  
    FROM [cube_PresortIntegrity]  
    WHERE ([Date].[Date].&[2010-01-01T00:00:00] : [Date].[Date].&[2012-01-01T00:00:00])';

    Exec (' SELECT * Into #temp FROM OpenQuery(MSOLAP, DATA SOURCE=localhost; Initial Catalog=My_AS;,' + @MDXExpression+ '''); select * from #temp ')


    Sanjeewan
    Wednesday, December 21, 2011 11:03 AM
  • Hi Sanjeewan,

     

    Still I am getting same error messages.

     

    Thanks !


    Prashant Mhaske
    Wednesday, December 21, 2011 11:34 AM
  • Hi

    Try this one , Check your linked Server is properly confiugred

    Declare

    @MDXExpression as Varchar(MAX)

    Set

    @MDXExpression ='WITH

    MEMBER [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Qualification].[Qualification Desc].CurrentMember.Parent) * 100, Format_String ="#,0.00"

    SELECT

    NON EMPTY

    {

    [Measures].[Volume],[Measures].[VolumePercent]

    } ON COLUMNS,

    NON EMPTY { Order ( Tail ( [Date].[Year - Month - Date].[Date].MEMBERS,10000)

    ,[Date].[Year - Month - Date].CurrentMember.Member_Key,DESC)

    * [Qualification].[Qualification Desc].CHILDREN

    } ON ROWS

    FROM [cube_PresortIntegrity]

    WHERE ([Date].[Date].&[2010-01-01T00:00:00] : [Date].[Date].&[2012-01-01T00:00:00])'

    ;

    Exec

    (' SELECT * Into ##temp FROM OpenQuery(MSOLAP( write Link server Name only ),''' + @MDXExpression+ ''');' )

    select

    * from ##temp

     

     

     
    Sanjeewan
    Wednesday, December 21, 2011 12:20 PM
  • Hi Sanjeewan,

     

    I am getting just two errors.

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '('.
    Msg 208, Level 16, State 1, Line 43
    Invalid object name '##temp'.

    Thanks !

     


    Prashant Mhaske
    Thursday, December 22, 2011 6:44 AM
  • Hey Prashant ,

     I also got this error when I run the query from the DB where linked server is not created . otherwise it was working to me !! anyway post your latest query will see


    Sanjeewan
    Thursday, December 22, 2011 6:47 AM
  • Hi Sanjeewan,

     

    Please find my code

     


    Declare @MDXExpression AS VARCHAR(MAX)

    Set @MDXExpression ='WITH

    MEMBER [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Tbl Qualification Master].[Qualification Desc].CurrentMember.Parent) * 100, Format_String ="#,0.00"

    SELECT

    NON EMPTY

    {

    [Measures].[Volume],[Measures].[VolumePercent]

    } ON COLUMNS,

    NON EMPTY
        {
            Order
                  (
                    Tail
                    (
                        [Date].[Year -  Month -  Date].[Date].MEMBERS
                        ,10000
                    )
                    ,[Date].[Year -  Month -  Date].CurrentMember.Member_Key
                    ,DESC
                   )
       
             * [Tbl Qualification Master].[Qualification Desc].CHILDREN
        }
    ON ROWS

    FROM [cube_PresortIntegrity]

    WHERE ([Date].[Date].&[2010-01-01T00:00:00] : [Date].[Date].&[2012-01-01T00:00:00])'

    ;

    Exec (' SELECT * Into ##temp FROM OpenQuery(MSOLAP(PIVOLUME),''' + @MDXExpression+ ''');' )

    select
    * from ##temp


    Prashant Mhaske
    Thursday, December 22, 2011 11:26 AM
  • Hey Prashant ,

     

    Try This one :-

    Check your link Server Name MSOLAP(PIVOLUME) is correct,

    Declare

    @MDXExpression AS VARCHAR(MAX)
     

    Set

    @MDXExpression ='WITH

    MEMBER [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Tbl Qualification Master].[Qualification Desc].CurrentMember.Parent) * 100, Format_String ="#,0.00"

    SELECT

    NON EMPTY

    {

    [Measures].[Volume],[Measures].[VolumePercent]

    } ON COLUMNS,

    NON EMPTY

    {

    Order

    (

    Tail

    (

    [Date].[Year - Month - Date].[Date].MEMBERS

    ,10000

    )

    ,[Date].[Year - Month - Date].CurrentMember.Member_Key

    ,DESC

    )

     

    * [Tbl Qualification Master].[Qualification Desc].CHILDREN

    }

    ON ROWS from

    (SELECT(([Date].[Date].&[2010-01-01T00:00:00]):([Date].[Date].&[2010-01-01T00:00:00]) )ON COLUMNS FROM

    [cube_PresortIntegrity])'

    ;
     

    Exec

    (' SELECT * Into ##temp FROM OpenQuery(PIVOLUME,''' + @MDXExpression+ ''');' )
     

    select

    * from ##temp

     

     

     

     

     

     


    Sanjeewan

    Thursday, December 22, 2011 11:46 AM
  • Hi Prashant,

    Please try copying + pasting the following statement in your relational SQL Server -

    select *
    from openquery(olap,
    '
    select {[Measures].[Units]} on columns,
    nonemptycrossjoin ({Stores.[Store Types].members}, {Stores.Stores.members}) on rows
    from [smCube]
    ')

    If worked, go ehead with following statement. If error, post the error message and version information of your analysis services. -

    select *
    from openquery(olap,
    '
    with set SelectedStores as {Stores.Stores.members}

    select {[Measures].[Units]} on columns,
    nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows

    from [smCube]
    ')

    please let me know the result.

    Regards,
    Jerry

    Friday, December 23, 2011 3:33 AM
    Moderator
  • Hi Jerry,

    I executed below code

     

    select *
    from openquery(PIVOLUME,
    '
    WITH
    MEMBER  [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Qualification].[Qualification Desc].CurrentMember.Parent),format_string="0.00%"

    SELECT
    NON EMPTY
        {
            [Measures].[Volume],[Measures].[VolumePercent]
        }
    ON COLUMNS,
    NON EMPTY
        {
             [Qualification].[Qualification Desc].CHILDREN         
        }
    ON ROWS
    FROM [MyCube]
    WHERE ([Date].[Date].&[2010-10-05T00:00:00] : [Date].[Date].&[2013-10-06T00:00:00])
    ')

     

    I am getting error like "OLE DB provider "MSOLAP" for linked server "PIVOLUME" returned message "Query (8, 23) The member '[VolumePercent]' was not found in the cube when the string, [Measures].[VolumePercent], was parsed.".

     

    Thanks !


    Prashant Mhaske
    Friday, December 23, 2011 5:20 AM
  • Hi Sanjeewan,

    Now I am getting different error "OLE DB provider "MSOLAP" for linked server "PIVOLUME" returned message "Query (7, 23) The member '[VolumePercent]' was not found in the cube when the string, [Measures].[VolumePercent], was parsed.".
    "

     

    Thanks !


    Prashant Mhaske
    Friday, December 23, 2011 5:30 AM
  • Prashant ,

    Run this query(MDX Part) on the MDX editor and check you are getting this error there ? if not then it may be problem that your linked server is not confiugred properly or its not pointing the enviornment where your cube is deployed.


    Sanjeewan
    Friday, December 23, 2011 6:56 AM
  • Hi Sanjeewan ,

    When I removed with name set , query was executed successfully .Code was the same which you have provided.

    I guess,  there is some problem with using name set in OpenQuery.

     

    Thanks !

     

     

     


    Prashant Mhaske
    Friday, December 23, 2011 7:03 AM
  • Hey,

    Which Set you are saying I am not seeing any named set in the query or you are saying about

    Set @MDXExpression   ='  MDX Query'

     

    Or post query

    ------------------------------------------------------------------------------

     

    Sanjeewan

     


    Friday, December 23, 2011 7:09 AM
  • Hi Sanjeewan,

    I am saying about nemed set used in mdx query . Sql Set is fine.

    Thanks !


    Prashant Mhaske
    Friday, December 23, 2011 7:41 AM
  • Prashant ,

    Set works in open query I checked it at my end, Chek your Set Statements carefully


    Sanjeewan
    Friday, December 23, 2011 9:48 AM
  • select *
    from openquery(olap,'
    with set SelectedStores as {Stores.Stores.members}
    select
    {[Measures].[Units]} on columns,
    nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows
    from [smCube]
    ')

    Hi Prashant

    Please Execute the below Code in SSMS. It will create 'TestTest1' Link server. I Thik you have the  Adventure Works Cube with you.

    Just let me know the what type of error you get while executing the below code. If this execute just replace with your code. 

    EXEC
    sp_addlinkedserver
    @server='TestTest1',
    @srvproduct='',
    @provider='MSOLAP',
    @datasrc='localhost',
    @catalog='Adventure Works DW 2008R2' 

    Declare @MDXExpression as Varchar(MAX)
    Select @MDXExpression = '
          SELECT
    NON EMPTY
     {
     [Measures].[Sales Amount]
     ,[Measures].[Standard Product Cost]
     ,[Measures].[Tax Amount]
     ,[Measures].[Total Product Cost]
     } ON 0,
    NON EMPTY
     {
     [Sales Channel].[Sales Channel].[Sales Channel]
     } ON 1
    FROM
     [Adventure Works]
          ';      
    Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(TestTest1,''' + @MDXExpression + ''')')

    SELECT
     CONVERT(varchar,t."[Sales Channel].[Sales Channel].[Sales Channel].[MEMBER_CAPTION]") AS [Date],
     (CONVERT(nvarchar,t."[Measures].[Sales Amount]")) AS [Economic Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Standard Product Cost]")) AS [Diversified Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Tax Amount]")) AS [Standalone Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Total Product Cost]")) AS [Diversification Benefit]
    from
     ##TestTemp t

    Suhas | MCP | MCTS


    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.
    Friday, December 23, 2011 11:00 AM
  • Hi Suhas,

    I have created linked server using your script. While executing query , I am getting error like "OLE DB provider "MSOLAP" for linked server "TestTest1" returned message "Either the user, Sys15\administrator, does not have access to the AdventureWorksDW database, or the database does not exist."."

     

    Could you please help me to resolve this issue ?

     

    Thanks


    Prashant Mhaske
    Friday, December 23, 2011 1:57 PM
  • Hi Jerry,

    I executed below code

     

    select *
    from openquery(PIVOLUME,
    '
    WITH
    MEMBER  [Measures].[VolumePercent] AS [Measures].[Volume] / ([Measures].[Volume],[Qualification].[Qualification Desc].CurrentMember.Parent),format_string="0.00%"

    SELECT
    NON EMPTY
        {
            [Measures].[Volume],[Measures].[VolumePercent]
        }
    ON COLUMNS,
    NON EMPTY
        {
             [Qualification].[Qualification Desc].CHILDREN         
        }
    ON ROWS
    FROM [MyCube]
    WHERE ([Date].[Date].&[2010-10-05T00:00:00] : [Date].[Date].&[2013-10-06T00:00:00])
    ')

     

    I am getting error like "OLE DB provider "MSOLAP" for linked server "PIVOLUME" returned message "Query (8, 23) The member '[VolumePercent]' was not found in the cube when the string, [Measures].[VolumePercent], was parsed.".

     

    Thanks !


    Prashant Mhaske


    Hi Prashant,

    try below sql set to see if it works -

    select *
    from openquery(PIVOLUME,
    '
    WITH
    MEMBER  [Measures].[VolumePercent] AS 100

    SELECT
    NON EMPTY
        {
            [Measures].[Volume],[Measures].[VolumePercent]
        }
    ON COLUMNS,
    NON EMPTY
        {
             [Qualification].[Qualification Desc].CHILDREN        
        }
    ON ROWS
    FROM [MyCube]
    WHERE ([Date].[Date].&[2010-10-05T00:00:00] : [Date].[Date].&[2013-10-06T00:00:00])
    ')

    If it still didn't work, can you check the current user permission and try to run the query with admininstrator permission to see whether it works for you?

    thanks,
    Jerry

    Monday, December 26, 2011 8:55 AM
    Moderator
  • Hi Prashant,

                    It seems there is a problem with linked server. Can you try with testing the connection of your linked server.

    Go to Object Explorer in  Sql Server Management Studio->Server Objects->Linked Servers-> Right Click on “olap” and select “Test Connection”. Make sure that you are getting the message as “connection to the linked server succeeded.”


    Aftab Ansari
    Monday, December 26, 2011 3:52 PM