Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Refresh of Data Model on Win 11 PC Takes an Hour
Hello all. Merry Christmas, Happy Holidays to all.
I am having significant issues when I refresh the data models on a newly built PC. Long post as I am trying to provide as much info as possible.
I have ported my PortfolioSlicer from a Win 10 PC to a new Win 11 PC. I am running Excel 2016 with the latest updates installed. The workbook and PSConfig.txt have been copied as-is from the Win 10 PC to the Win 11 PC.
Generally speaking the performance of my Win 11 PC is faster than my Win 10 PC. Executing UpdatePSData.bat runs approximately 2x to 3x quicker. The overall performance of Excel seems quicker as well. Except when it comes to performing a data refresh.
On my Win 10 PC the refresh takes 2 to 4 minutes (not timed…just an educated guess). On Win 11, it takes an hour to refresh, give or take 15 minutes…an order of magnitude longer. Keep in mind I am using the exact same spreadsheet on both PC's so I don't think it is something specific to the Excel workbook.
Here is what I have tried and some data points:
• I have reviewed what is documented on the Portfolio Slicer Refresh Issues - Portfolio Slicer page.
• I have run Excel in safe mode (which disables add-ins) which yields the same result.
• I downloaded a fresh copy of the Excel 2016 template and copied (as values) my data to the new spreadsheet. The new workbook performs the same way.
• Saving the Win 11 version of the workbook is quite quick, less than 2 seconds which is much quicker than my Win 10 PC
• Excel Versions on Win 10 and Win 11 are the same - Microsoft® Excel® 2016 MSO (Version 2111 Build 16.0.14701.20240) 32-bit
• A selective table refresh, such as Trans, is just as painfully slow (close to an hour to refresh).
• I do track cash, but again, this spreadsheet works fine on my Win 10 PC.
• Changing a slicer value on Win 11 (ex. From Original currency to CAD as an example), takes approximately 2 minutes to refresh compared to doing the same on my Win 10 PC which takes about 20 seconds
Does anything else come to mind that I can investigate further in my problem determination?
One question: Does the refresh data use Powershell at all? I don't think so but I know this is an environment different between my 2 PCs. Win 10 has version Powershell 7.2 installed while Win 11 has Powershell 5.1. I did install Powershell 7.2 but it runs side by side with v5.1. As I stated above, the UpdatePSData.bat which I know uses powershell runs very quickly compared to my Win 10 PC.
Full Disclosure: I am running an insider edition of Win 11 on an ARM64 VM running on a MacBook Pro (Silicon chip…not an intel chip). Win 11 runs on Parallels VM. My Win10 PC is a VM that runs on Mac as well. It is possible that Win 11 is the culprit here, but the few things I do run in Windows run as quick as I would expect. It is just the refresh of the data model that seems to take a very long time.
Thanks all....Mark
I am having significant issues when I refresh the data models on a newly built PC. Long post as I am trying to provide as much info as possible.
I have ported my PortfolioSlicer from a Win 10 PC to a new Win 11 PC. I am running Excel 2016 with the latest updates installed. The workbook and PSConfig.txt have been copied as-is from the Win 10 PC to the Win 11 PC.
Generally speaking the performance of my Win 11 PC is faster than my Win 10 PC. Executing UpdatePSData.bat runs approximately 2x to 3x quicker. The overall performance of Excel seems quicker as well. Except when it comes to performing a data refresh.
On my Win 10 PC the refresh takes 2 to 4 minutes (not timed…just an educated guess). On Win 11, it takes an hour to refresh, give or take 15 minutes…an order of magnitude longer. Keep in mind I am using the exact same spreadsheet on both PC's so I don't think it is something specific to the Excel workbook.
Here is what I have tried and some data points:
• I have reviewed what is documented on the Portfolio Slicer Refresh Issues - Portfolio Slicer page.
• I have run Excel in safe mode (which disables add-ins) which yields the same result.
• I downloaded a fresh copy of the Excel 2016 template and copied (as values) my data to the new spreadsheet. The new workbook performs the same way.
• Saving the Win 11 version of the workbook is quite quick, less than 2 seconds which is much quicker than my Win 10 PC
• Excel Versions on Win 10 and Win 11 are the same - Microsoft® Excel® 2016 MSO (Version 2111 Build 16.0.14701.20240) 32-bit
• A selective table refresh, such as Trans, is just as painfully slow (close to an hour to refresh).
• I do track cash, but again, this spreadsheet works fine on my Win 10 PC.
• Changing a slicer value on Win 11 (ex. From Original currency to CAD as an example), takes approximately 2 minutes to refresh compared to doing the same on my Win 10 PC which takes about 20 seconds
Does anything else come to mind that I can investigate further in my problem determination?
One question: Does the refresh data use Powershell at all? I don't think so but I know this is an environment different between my 2 PCs. Win 10 has version Powershell 7.2 installed while Win 11 has Powershell 5.1. I did install Powershell 7.2 but it runs side by side with v5.1. As I stated above, the UpdatePSData.bat which I know uses powershell runs very quickly compared to my Win 10 PC.
Full Disclosure: I am running an insider edition of Win 11 on an ARM64 VM running on a MacBook Pro (Silicon chip…not an intel chip). Win 11 runs on Parallels VM. My Win10 PC is a VM that runs on Mac as well. It is possible that Win 11 is the culprit here, but the few things I do run in Windows run as quick as I would expect. It is just the refresh of the data model that seems to take a very long time.
Thanks all....Mark
Comments
PowerShell is used just when you execute .bat file (that will execute PowerShell scripts to get data from web sources). When you do refresh in Excel, PowerShell is not used at all. During refresh first stage is to read data from external files in PSData folder and then the second step is to recalculate all Pivot tables in each Excel worksheet.
When poking around I saw that I was able to turn on a trace for Power Pivot. I did that, performed a “Refresh All” and did a “tail -f” on the trace file so I could watch the trace file updates as they happened. What I observed was updates occurred fast and furious for 2 to 5 seconds and then nothing for 5 to 20 seconds. The trace file gave no indication (at least to me) what was actually going on when the refresh stalls.
I will also see if downloading the 64 bit version of Excel is an option…I didn’t see it when I built this new MacBook/PC. Win 11 for ARM seems to update every 1 to 2 weeks so I hope an update is coming imminently.
Finally, as I mentioned in my original post, I had successfully run Portfolio Slicer in a VM (both Win 7 and Win 10) using both Parallels and VirtualBox on an Intel Mac without any issues.
I will continue to update as I find out more. Thanks again.
Now I need to figure out how to download the 64 bit version of Office 2016 and see if I get the same results.
I will post an update in a couple of days. Mark
What remains to be seen is once Win 11 on ARM is GA (i.e. released for everyone), does the 32 bit version of Office still has an extreme data refresh time.
Thanks all. Mark
I don't want to stretch this thread out much longer, but before you close it off, could you describe the hardware side of your MacBook/PC system? eg: How much RAM, and the VM allocation type stuff. "Someday" I'm going to try setting PS up on possibly a Linux based system. (Please don't tell the hard-core Windows folks that). Also I might come across some Mac users that might be interested in what you are doing.
BuddyB
I'm not using an early release or mac.
I usually use Power BI so this isn't important to me.
- VM software is Parallels 17 for Mac Standard Edition (which is important since it limits the CPU's that can be assigned)
- Host operating system is MacOS Monterrey (12.1) on the Mac M1 Silicon chip (not Intel)
- Guest operating system is Win 11 for ARM Pro Insider Preview Edition Build 22523 (this will get updated until Win 11 for ARM is released)
- Windows configured with 4 CPUs and 8 GB of RAM (the maximum allowed for the version of Parallels I am licensed for).
- Windows runs quick enough although I only use it for 3 applications: tax software, Portfolio Slicer and Visual Studio to maintain a piece of software I wrote a few years ago. Visual Studio would be the heaviest application and it runs just fine on this configuration (although the debugger won't run an exe on the ARM based chip !$%*@&;!...yet the exe runs just fine).
Think that is it.Host hardware was a 10 year old Mac Mini running OSX Catalina (Intel quad core CPU)
VM Software is VirtualBox (free open source software from Oracle) version 5.7
Windows 10 configured with 2 CPU’s and 8 GB memory
Microsoft Office 2016 (32 Bit)
Prior to the above I had Win 7 running on the exact same setup as above (in fact both VM’s are installed and can be spun up whenever I need them).
Portfolio slicer ran on both of these setups just fine.
Good luck to anyone else running Portfolio Slicer within a VM.
Mark