SSAS (2008R2) & Excel Filter Issue When Refreshing Data

Question

We have an instance of 2008 R2 which is running approx 6 cubes. The situation I am finding is as follows.

A user connects to the cube via MS Excel 2010 using the normal Data Sources method. Lets say they create a very simple pivot table...Accounts on the rows & Months on the columns with a measure giving us the total value for each Account. At the time of the user creating the report there were 11 months of data in available in the cube (Jan > Nov) so the pivot table reflects this in Excel. The user has applies a few filters (say 5) on the row to select some Accounts ad hoc. The user then saves this file to his desktop and leaves it until Decembers data has become available in the cube. December is upon us and we refresh the cube so that it now contains Decembers data as well. The user reopens the Excel file and right clicks on the pivot and refreshes the data with the expectation of seeing a full years data for the five account selected but this is not the case. What in actual fact happens is that all the data which is there before is lost and the pivot table is completely blank. Can anyone help with why this is happening and a potential solution?

I have an idea in my mind that Excel is storing the accounts being filtered using some unique identifier and when the cube is reprocessed for the new month new unique IDs are being assigned to the accounts and therefore a match is not happening. There must be a work around to this as otherwise every report which is generated is effectively a throw away effort. Cheers!

Why do the accounts not have stable keys? i would assume the best solution would be to change your ETL process so that a key for an account stays stable over time. — FrankPl
You can download OLAP PivotTable extensions and use it to view the MDX that Excel has written to populate your pivot table. If you have an issues with your account keys changing, you should be able to see if that attribute is being used in the query. — mmarie

Nickolay Nickolay · Accepted Answer · 2014-05-02T01:17:42

In my case this indeed was caused by the poor cube design: the generated MDX referenced an ID that changed every time the cube was rebuilt:

WHERE [Log Author].[PM PRIMARYKEY].&[29872574],

I agree the best way to fix this is to change the ETL.

PS The way I viewed the generated MDX (thanks to Marco Russo) was by selecting the pivot and then running this macro (open VBA, paste it, hit F5):

Sub DisplayMDX()
    Dim mdxQuery As String
    Dim pvt As PivotTable
    Dim ws As Worksheet

    Set pvt = ActiveCell.PivotTable
    mdxQuery = pvt.MDX

    ' Add a new worksheet.
    Set ws = Worksheets.Add
    ws.Range("A1") = mdxQuery
End Sub

SSAS (2008R2) & Excel Filter Issue When Refreshing Data

1 Answers