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.