locked
OLAP formulas won't update RRS feed

  • Question

  • I have a workbook that joins an MDX query and a SQL query in PowerPivot. I constructed two pviot tables based on the PowerPivot data, used "OLAP Tools" > "Convert to Formulas" to convert the two tables to fomulas, and then combined the resulting cells into a single table, adjusting cube formula references as needed by naming column/ row/ value header cells and replacing the anchored cell references in the table cube formuals with named references.This worked fine.

    I then created a third powerpivot table to use for auditing. When I was trying to move fields from rows to columns in the third power pivot table, the pivot table locked up. I was able to switch to other workbooks, and even other worksheets within the same workbook. However, the pivot table I was building never refreshed the data, and the timer cusor showed whenever it was over the body of the stalled pivot table. I saved the workbook and tried to close Excel, but excel would not close. I eventually had to use Task Manager to kill Excel.

    When I reopened the workbook, the cells with OLAP formulas all showed "#N/A". I clicked in the cells and hit "Enter", which changed them to "#GETTING_DATA", but that never resolved to a value.

    I built a new pivot table based on the existing PowerPivot tables, and it worked fine as a pivot table, but converting to formulas made all the cells show "#GETTING_DATA". I closed and re-opened the workbook, and one worksheet with OLAP formulas populated properly, while another, based on the same data, still showed "#N/A".

    All this freaky behavior adds up to an application that is not ready for prime time in my book. Any ideas on how to restore this workbook? I'd really prefer not to lose the hours of work.

    Thanks.


    George W. (different George W.)
    Monday, October 10, 2011 11:56 PM

Answers

  • Resolved this after much trial and error by adding a calcualted column to one of the PowerPivot tables. That apparently forced the application to rebuild the cube and resolved the issue.
    George W. (different George W.)
    • Marked as answer by G Dubya Wednesday, October 12, 2011 1:54 PM
    Wednesday, October 12, 2011 1:54 PM