none
Is there a way to disable Power Query queries and disable the auto refresh of Pivot Tables? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Basically there is some contention between the Power Query (PQ) queries and the Pivot Tables whilst pointing to the same Power Pivot data model.

    All the Power Pivot tables have PQ's as their data source, and all PQ's  have CSV files as their data source.

    There is an auto data refresh process which is triggered from Pivot Table which needs to be disabled. Also for added insurance the Power Query queries need to be disabled when we don’t want to explicitly refresh the Pivot Table / PowerPivot model from the input sheets.


    More Technical Detail
    The evidence of this is as follows; -
    1) Refreshing  the Power Query query DimYear seemed to either freeze or take in access of 5 minutes where DimYear returns a single record!
    2) I deleted all Pivot Tables,  and made sure all the Power Pivot table relationships were correct.
    3) Refreshing  the Power Query query DimYear completed instantly as expected.
    4) I attempted to rebuild again the Pivot Table from scratch. The Pivot Table became too slow. Dragging another calculation into the Pivot Table took over 45 seconds once; - all the attributes were included and the first 10 calculations were included. There was only one record within each of the input sheets, so only one record within the Pivot Table output. So this is certainly not a record volume issue.
    5) After the attempt at rebuilding from scratch the Pivot Table I noticed that the DimYear Power Query query took a full minute to execute.

    Next Steps
    1) Create a new copy of the solution and delete all the Power Query queries.
    2) See how the Pivot Table performs after the removal of the Power Query queries.

    Thanks in advance,

    Kieran.

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Thursday, May 14, 2015 5:25 PM
    |

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    The following utility helped by disabling the refresh within Pivot Table; - https://olappivottableextend.codeplex.com/wikipage?title=Disable%20Auto%20Refresh&referringTitle=Home

    However this utility abled me to view the MDX layer  which Pivot Table generates when pointing to a PowerPivot data source.

    This MDX layer seems to be very inefficient especially when there are many (aprox.) two dozen dimensional attributes within the Pivot Table since for each dimensional attribute an MDX DrilldownMember is used.

    Working through the following blog to attempt to work around the above; -

    http://www.purplefrogsystems.com/blog/2015/05/excel-creates-inefficient-mdx/

    So in summary disabling the auto refresh within large Pivot Tables can dramatically speed up the execution of Power Query query execution where the target of the Power Query queries is a PowerPivot model, and the data source of the  Pivot Table is the same PowerPivot model.

    I am not aware of any feature to disable an individual Power Query query. This sort of thing an SSIS developer would take for granted.

    Hopefully the ability to enable, disable the update of individual Power Query queries will be included in a future release of Power BI / Excel.

    Kind Regards,

    Kieran.

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/




    Friday, May 15, 2015 12:34 PM
    |