Excel Pivot working with SSAS - by Sebastiaan Gerards

Status : 


Sign in
to vote
ID 786084 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 5/2/2013 12:37:09 AM
Access Restriction Public



I have an Excel Pivot report which retrieves data from a SSAS Cube, using the hierarchy (Fig 1)

I add all the fields as rows into the Pivot, but when I filter only one hour (using the filter by selection option as shown below) the Excel Pivot does not respond anymore. (Fig 2)

If I use the field hour (LET) as a slicer or as report filter there is no problem with it. Then I have the expected result in 2 seconds. 

Has anyone had this issue before? Is there a solution for it?

Looks like Excel Pivot is sending inefficient query to Analysis Server.
If I monitor the SSAS database with SQL Profiler I see result (fig 3):

A lot of discovers with in each discover the same query but only in the where statement a different day of the time dimension. 
After 10 minutes he sends the correct query to Analysis Server with the filter for day ’01-02-2013 16:00’. Once that comment is send, my Excel is also responding again and giving back only that record. 

Sign in to post a comment.
Posted by Microsoft on 6/11/2013 at 2:03 PM

In order for us to be able to do anything with this issue we need more information. Can you please share the cube / query that is being send ?

Kasper de Jonge
Analysis Services team