none
Multiple member mdx query returns error (permission to access the specified member) RRS feed

  • Question

  • I want to mention that I'm new to SSAS and MDX.

    In the past several days I've been dwelling with an excel generated query that errors out. 

    The problem is that a query is generated by excel when trying to read data from an online cube data source preventing other reads fot that cube. The query is executed against an AZURE cube and I manage to profile it and get the following query:

    with set __XLUniqueNames as {[Stores].[Chain].[Chain].&[SuperBrugsen], [Stores].[Chain].[Chain].&[Salling], [Stores].[Chain].[Chain].&[SuperBrugsen] } 
    set __XLDrilledUp as 
    Generate(__XLUniqueNames, 
    { IIF([Stores].[Chain].currentmember.LEVEL_NUMBER <= 2147483647, 
    [Stores].[Chain].currentmember, 
    Ancestor([Stores].[Chain].currentmember, 
    [Stores].[Chain].currentmember.LEVEL_NUMBER - 2147483647)) } ) 
    member [Measures].__XLPath as 
    Generate( 
    Ascendants([Stores].[Chain].currentmember), 
    [Stores].[Chain].currentmember.unique_name, 
    "__XLPSEP") 
    select { [Measures].__XLPath } on 0, 
    __XLDrilledUp on 1 
    from [SomeCube] 
    cell properties value


    Each time query contains more then one member (an existing member from that dimension) it errors out with this message: 
    > "Either you do not have permission to access the specified member or the specified member does not exist.".

    What I have tryed:


    * First, I tried to identify a pattern of member combinations that errors out, with no luck. It seems that for some certain members I get the error and for some, It doesn't. For single member, duplicate members and combination of members that don't exist in the cube it doesn't error.

    * Second, I did try the query on a different cube (on-premise SSAS) and I didn't get the error.

    * Third, by modifying the connection string I tried to make Excel ignore the missing members in the hope it will work using the "MDXMissingMemberMode" flag set to Ignore. I didn't work.

    * Forth, I tried to dissect the query to see which clause was giving the error. With my limited knowledge of MDX I suspect that "currentmember" with its "LEVEL_NUMBER" property is at fault. My guess is that it fails to get the current member for the next member in the set.

    * Fifth, the last thing and the longest, by accident I discovered that in SSMS you can execute a query in an mdx session (Right-click on cube -> New query) or you can  open the cube in browse mode (Right-click on cube -> Browse) which results in a UI similar to the mdx query like. 
    No here comes the surprise, in this browse "mode" my query executes successfully each time. Intrigued by this I started to profile the request and see what was different. The difference was some additional xml structure like a list with properties. Seeing this I figured I could manipulate my connection string from excel to send some of the properties in an attempt to make it work, but in the end, I didn't work.

    Additonal proprities that worked:

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
    <Catalog>SomeCatalog</Catalog>
    <ShowHiddenCubes>true</ShowHiddenCubes>
    <SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName>
    <Timeout>3600</Timeout>
    <LocaleIdentifier>1033</LocaleIdentifier>
    <ClientProcessID>24400</ClientProcessID>
    <DataSourceInfo/>
    <Format>Tabular</Format>
    <Content>Schema</Content>
    <DbpropMsmdFlattened2>true</DbpropMsmdFlattened2>
    <ReturnCellProperties>true</ReturnCellProperties>
    <DbpropMsmdActivityID>2309dfa2-3607-41b2-9446-8ece2f5ababa</DbpropMsmdActivityID>
    <DbpropMsmdCurrentActivityID>2309dfa2-3607-41b2-9446-8ece2f5ababa</DbpropMsmdCurrentActivityID>
    <DbpropMsmdRequestID>d3dbd079-5ca7-496c-ab55-afea71889238</DbpropMsmdRequestID>
    </PropertyList>


    Additional properites that didn't work:

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
    <Catalog>SomeCatalog</Catalog>
    <SspropInitAppName>Microsoft SQL Server Management Studio - Query</SspropInitAppName>
    <LocaleIdentifier>1033</LocaleIdentifier>
    <ClientProcessID>24400</ClientProcessID>
    <DataSourceInfo/>
    <Format>Native</Format>
    <AxisFormat>TupleFormat</AxisFormat>
    <Content>SchemaData</Content>
    <Timeout>0</Timeout>
    <DbpropMsmdActivityID>e5e75ad6-8fca-4f25-abba-047f86198602</DbpropMsmdActivityID>
    <DbpropMsmdCurrentActivityID>e5e75ad6-8fca-4f25-abba-047f86198602</DbpropMsmdCurrentActivityID>
    <DbpropMsmdRequestID>8901787f-15a7-48a0-86eb-18ff0b92bdc4</DbpropMsmdRequestID>
    </PropertyList>

    Excel additional properties:

    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <Catalog>SomeCatalog</Catalog>
    <Timeout>0</Timeout>
    <Format>Native</Format>
    <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>
    <SafetyOptions>2</SafetyOptions>
    <Dialect>MDX</Dialect>
    <MdxMissingMemberMode>Error</MdxMissingMemberMode>
    <DbpropMsmdOptimizeResponse>9</DbpropMsmdOptimizeResponse>
    <DbpropMsmdActivityID>9D69640F-553A-4970-BD4E-7234F1CD928C</DbpropMsmdActivityID>
    <DbpropMsmdRequestID>B5E10FF0-EF2F-409E-83BF-CD2DBA20C2BE</DbpropMsmdRequestID>
    <LocaleIdentifier>1030</LocaleIdentifier>
    <DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>
    </PropertyList>


    Result of a single member working mxd query:
    SuperBrugsen [Stores].[Chain].[Chain].&[SuperBrugsen]__XLPSEP[Stores].[Chain].[All]





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

    This all the info that I could gather for my problem. My next step is to get to Microsoft for help by I don't want to do that just yet due to the costs. 

    Can someone of you guys please help me out? any ideas or suggestion are most welcomed because I ran out of ideas.
    • Edited by ValerSi Monday, August 12, 2019 7:33 AM Removed code formatting.
    Monday, August 12, 2019 7:32 AM

All replies

  • Hello ValerSi and thank you for your inquiry.  I see nobody has responded yet, but I wanted to let you know, that you have been heard.  I am authorized to provide you with a 1 time free support ticket, but I wanted to allow the community a chance to help first.  If in 24 hours nobody has contributed, I will provide you instructions for the one time free support ticket.

    Linking to  StackOverflow: https://stackoverflow.com/questions/57456526/multiple-member-mdx-query-returns-error-permission-to-access-the-specified-memb
    Tuesday, August 13, 2019 1:57 AM
    Moderator
  • Hello ValerSi,
    This will require some deeper analysis. Can you please file a support request https://aka.ms/azsupt? If you do not have access to a support plan, please reach out to AZCommunity@microsoft.com with a link to this MSDN thread as well as your subscription ID and we can help get the support ticket opened for this issue.
    Wednesday, August 14, 2019 6:42 PM
    Moderator
  • ValerSi, I haven't found an email mentioning this thread.  Did you send one and I missed it, or do you not need help any more?
    Saturday, August 17, 2019 1:24 AM
    Moderator