Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JoshSimpson
Frequent Visitor

How do I scale powerBI? My clients are giving me over 70 data sets.

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

3 REPLIES 3
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors