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?
    Friday, February 10, 2012 3:32 PM


All replies

  • 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

    Friday, February 10, 2012 6:26 PM
  • 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.
    Monday, February 13, 2012 1:26 PM
  • 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

    Monday, February 13, 2012 1:51 PM
  • 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)?
    Monday, February 13, 2012 2:25 PM
  • 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

    Monday, February 13, 2012 3:00 PM
  • Just an update to everyone... this issue has not been resolved and I do not know the solution.
    Friday, February 17, 2012 7:45 PM