• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Pivot takes ages to resfresh|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Power Pivot takes ages to resfresh|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotPower Pivot takes ages to resfresh
sp_PrintTopic sp_TopicIcon
Power Pivot takes ages to resfresh
Avatar
Mitryaeva Margarita

New Member
Members
Level 0
Forum Posts: 1
Member Since:
May 27, 2022
sp_UserOfflineSmall Offline
1
May 27, 2022 - 9:01 pm
sp_Permalink sp_Print sp_EditHistory

Hello!

I'm new to advanced add-ins like Power Pivot but I'm currently dealing with it at work - there is an Excel data model that was built by someone else that combines queries in Power Query linked to SQl source and results in one Power Pivot which is basically PnL of the company. Each month I have to update this model since SQL source tables are themselves updated (new month data is added or for some tables I need to upload new versions of them to SQL). Then these SQL tables are loaded to Power Query where they are transformed from raw data to the format needed and also act as references for other queries. Not that I each month create new queries - I just change the link in Navigation step to the new SQL tables I just created or in case of SQL tables that just got new month data I do nothing, queries that link to them just need to be updated. Then these queries are consolidated in one main query that references them all and also makes some transformations. This main query is then loaded to Excel data model where changes applicable for all data are made (I don't know much about this step since I never used Excel data model before). The result is Power Pivot which is simply loaded to Excel sheet. 

The problem is that each new month OR whenever I need to make any changes and update this pivot, Resfresh All function in Excel takes more than 40 minutes to perform. Since new data adds every month and we are only in May, by the end of the year it could be around 2 hours. This is a problem because when the model is updated each period, it's imperfect and needs different changes - not technical, but rather financial - which I can't make quicky and immediately see the result. Just for info, the initial data in SQL from all the tables that are used is approximately 2 mln rows now.

For better understanding I'm attaching a scheme of what the whole process looks like. I tried some tricks like turning off dowload of background data for PQ, added Table.Buffer to the main query, then went to connections and turned off 'Refresh this connection on Refresh all' and 'Enable background refresh' functions for all queries except the main one. I also created a query that has SQL database as its source and then linked all queries to this one instead of SQL tables. Those are just random chaotic actions, I read about them on different forums including this one but since I don't know how they work exactly and whether they are applicable in my situation, they didn't change anything. Maybe they are not effective because I don't see the whole picture and therefore missing someting or doing something wrong.

The ideal situation would be if the pivot updated quickly and at the same time I could still make changes at different stages of the process - in SQL, Power Query or Excel - sometimes it's easier to change data in SQL by one simple code line, and in case data from SQL is not updated this would be a problem. I want some kind of balance between flexibility in manipulating the model and time needed to update the model to reflect the results of these manipulations. Any help would be appreciated.

Scheme.pngImage Enlarger

P.S. I use Excel 2016 on PC. Microsoft SQL Server Management Studio as SQL program.

sp_PlupAttachments Attachments
  • sp_PlupImage Scheme.png (264 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 29, 2022 - 8:53 am
sp_Permalink sp_Print

Hi Mitryaeva,

I would have your SQL database administrator create a "view" of the tables you want to bring into Power Query, rather than having Power Query bring in all the tables and then manipulate them to give you the view you want. It's more efficient to do it this way. This post explains more on views vs tables in relation to Power BI, but remember that Power Query is the same in Excel and Power BI, so it's relevant here too.

This should remove the need for multiple queries which are likely being called multiple times due to the dependencies from one query to the next. 

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Roslyn Finlayson, Shanna Henseler
Guest(s) 3
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6210
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.