PowerPivot refreshes table during Edit


  • I am using PowerPivot and when I attempt to edit a query connecting to an Oracle data source (OracleClient Data Provider, 11g) it appears to validate/run the existing query and grays out the edit query box for 2-5 minutes.   Is there a way to disable the query from executing so I can modify the SQL and THEN pull back the correct data?
    venerdì 10 febbraio 2012 15:32


Tutte le risposte

  • When you select 'Table Properties' on a table in the PowerPivot window you can switch from 'Table Preview' to 'Query Editor'.  This setting will be saved, and next time you go to change it, only the query will be shown and it wont try to generate a sample of actual table rows.

    Javier Guillen

    venerdì 10 febbraio 2012 18:26
  • When I select 'Table Properties', the window is already set to 'Query Editor' and it is still executing the query in the window before I can modify the SQL.  The window is completely grayed out for minutes and when it comes back, it is still set to Query Editor which also cannot be changed.
    lunedì 13 febbraio 2012 13:26
  • When you open up the 'Table Properties' window, there is an indicator for 'Last Refreshed' on the lower right side of the window.   Is the time it shows the same time in which the window stopped being greyed out?

    Javier Guillen

    lunedì 13 febbraio 2012 13:51
  • The last refreshed date did not change when the window stopped being greyed out.  The query that I have in the window takes about 3-4 seconds when I run it using an Oracle client.  Is PowerPivot trying to validate the connection with Oracle and this is causing the window to not respond for a period of time?  Does it have something to do with the data provider that I chose (OracleClient Data Provider, 11g)?
    lunedì 13 febbraio 2012 14:25
  • You may have to re-open the window and verify the refresh date/time in order to ensure the data was not really refreshed.   I had the opportunity to work with the Oracle provider once as a PowerPivot source, but did not notice this behavior.   However, if data is not being refreshed it can definitely be related to validation.  Are you using the most up-to-date provider?

    Javier Guillen

    lunedì 13 febbraio 2012 15:00
  • Just an update to everyone... this issue has not been resolved and I do not know the solution.
    venerdì 17 febbraio 2012 19:45