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

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 for 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 than 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 tried:

• 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 to make it work, but in the end, I didn't work.

Additional proprieties 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 properties 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.

Answer 1

It seems that the problem solved itself. Most likely there was an update that solved this issue. Ref. to azure update logs page: https://azure.microsoft.com/en-us/updates/?product=analysis-services&status=nowavailable