Search code examples
excelssas

Excel cannot retrieve data from Analysis Cube

I have an Analysis Services Cube delpoyed and processed on our server. If I log into SSMS, connect and browse the cube, it all looks fine, but when I attempt to do the same, as the same user, in Excel, I am presented with the following message (I can see the cube and get right up to the point where I select a cell before encountering the issue):

enter image description here

I have made roles for the user, which allows read access to the data source, the cube and all the relevant dimensions. The role membership uses an AD group, in which my user resides.

I have another cube on the same database, accessed by another role and this cube is accessible through Excel without any issues. I cannot think what the problem is that means Excel cannot access both cubes.

I have spent some time Googling for solutions and that has led me to delete, rebuild, and redeploy the cube and rebuild the security roles, all to no avail. Any suggestions for further things I could try would be much appreciated.

EDIT: Following the suggestion from @Subbu, I ran the profiler while connecting and attempting to retrieve data from the cube, I saw the following:

profiler snippet

The properties are these:

<PropertyList>
    <Catalog>Production Cubes</Catalog>
    <Content>SchemaData</Content>
    <DiscoverAmbiguity>false</DiscoverAmbiguity>
    <Format>Native</Format>
    <AxisFormat>TupleFormat</AxisFormat>
    <BeginRange>-1</BeginRange>
    <EndRange>-1</EndRange>
    <ShowHiddenCubes>false</ShowHiddenCubes>
    <VisualMode>0</VisualMode>
    <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>
    <SecuredCellValue>0</SecuredCellValue>
    <ImpactAnalysis>false</ImpactAnalysis>
    <SQLQueryMode>Calculated</SQLQueryMode>
    <ReturnCellProperties>false</ReturnCellProperties>
    <CommitTimeout>0</CommitTimeout>
    <ForceCommitTimeout>0</ForceCommitTimeout>
    <ExecutionMode>Execute</ExecutionMode>
    <RealTimeOlap>false</RealTimeOlap>
    <MdxMissingMemberMode>Default</MdxMissingMemberMode>
    <DisablePrefetchFacts>false</DisablePrefetchFacts>
    <UpdateIsolationLevel>2</UpdateIsolationLevel>
    <DbpropMsmdOptimizeResponse>9</DbpropMsmdOptimizeResponse>
    <ResponseEncoding>Default</ResponseEncoding>
    <DirectQueryMode>Default</DirectQueryMode>
    <DbpropMsmdActivityID>9DE53651-350A-4393-9CBB-079F51C15F00</DbpropMsmdActivityID>
    <DbpropMsmdRequestID>2322D98E-3620-470C-996F-DA3ACC0D75D1</DbpropMsmdRequestID>
    <DbpropMsmdCurrentActivityID>9DE53651-350A-4393-9CBB-079F51C15F00</DbpropMsmdCurrentActivityID>
    <Certificate />
    <AuthenticationScheme />
    <ExtAuthInfo />
    <LocaleIdentifier>2057</LocaleIdentifier>
</PropertyList>
Solution

  • I found out what the problem was in the end through trial and error. Apparently Excel cannot read from any but the first cube in a database - I was trying to connect to a second cube.

    I have therefore separated the two cubes into two databases and Excel now browses both of them correctly. I have not found an acknowledged bug relating to this, but so far as I can tell it is reproducible so I may try to report it, although I do not know the way.