cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoshSimpson Frequent Visitor
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
Moderator v-yuezhe-msft
Moderator

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

@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.
JoshSimpson Frequent Visitor
Frequent Visitor

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

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

 

 

Moderator v-yuezhe-msft
Moderator

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

@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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 378 members 3,639 guests
Please welcome our newest community members: