Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
This might seem like a really rookie question and hopefully it has a simple answer, I'm relatively new to powerBI so apologies if there is an easy answer.
I've been doing some anyalytics with a client over the last couple of years, they've been pulling reports out of their ERP system and sending them to me me as excel spreadsheets. I've then loaded these into powerBI and then fed my work back through powerBI online.
This has been fine dealing with 20 or less data sets.
Recently though the analytics operation has scaled up and I've been tasked with analysing historic data going back to 2014. The client has given this to me in a load of files, each with multiple sheets. I've loaded these into PowerBI and I have more than 70 data sources.
My system has 16Gb of RAM and an Intel i7 Processer, but whenever I try and edit the queries, applying the changes takes half an hour or more to complete.
My question is, how do I make this quicker?
The potencial options I see are:
- Get data directly from their ERP system so I can get it as one data set. (Not sure if this is possible as they don't like giving third parties access to their ERP system.
- Upgrade my system adding more RAM (Cost isn't a problem but if I get another client with 100 data sets plus, do I just keep updrading?
- Use SQL to somehow combine the datasets using cloud processing (I've no experience with this at all)
If anyone has any opinions or suggestions it would be great to hear them,
Thankyou in advance,
Josh
Data Processer at Barcanet
@JoshSimpson,
When you have multiple transform or calculation steps in Power BI and work with large dataset, it will take long time to apply query changes.
In your scenario, I would recommend you import all data from Excel to SQL Server database, then use DirectQuery mode to connect to SQL database in Power BI Desktop.
Regards,
Lydia
Hello,
I thought so,
Would there be any improvement in performance running DirectQuery from an SQL database or is that just best practise?
I've bumped up my setup to 32Gb of RAM.
Hopefully that helps.
Thankyou for your response.
Josh
@JoshSimpson,
Please take a look at the following article about improving performance in Power BI Desktop.
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.