cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
augustindelaf
Skilled Sharer
Skilled Sharer

SharePoint Files connector -Scalability in the Number of Excel Files imported ???

Hello Everyone,

 

The method SharePoint.files (Sharepoint Folder) works fine. 

For my client, the purpose is to connect to Excel (xlsx) files that are stored in this folder.

 

15 people will upload up to 1 Excel file, each day.

Right now, the system works very well, and the refresh on demand is always 2 minutes, while 15 minutes for scheduled refresh. We currently have ~100 files.

 

Is this system scalable ?  Will Power BI be able to import these Excel files when there will be 1,000 ... or 10,000 files ?

Many thanks in avance

14 REPLIES 14
MFelix
Super User
Super User

Hi @augustindelaf ,

 

Instead of making the link trough a sharepoint folder directly to the PBI I would make a dataflow (just copy and paste the query from desktop) and would connect PBI to the dataflow, that will give you better results in term of performance.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix 

 

I tried that today  by curiosity.

 

Out of my 40 requests, half have "Merge", or "Reference" steps, and then, they are defined as Computed entities.

Dataflow says "This dataflow contains computed entities, which require Premium to refresh. To enable refresh, upgrade this workspace to Premium capacity."

c.PNG

 

Too bad ! very disappointed.

Hi @ augustindelaf,

What are you using this tables for? To connect to ohter tables and using them on your model to what?

In calculated tabels in dataflows if you din't need them directly on your model you can disable the upload and the dataflow is calculated normally. So merging this with other tables is possible.

Again this depends on how youbare using thr tables.

I have some models that had this error and was abble to work around it by doing that.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ?

 

These tables are used to process my data with simple operations like in any project.

Yes, I connect my tables between them.

 

In my model, the table VRF Samples is a final table, which means it is used (very much, it is the main table) for visualization. Then it is a problem, right ?

 

For other tables, yes they are not necessary and I can disable them.

Hi @augustindelaf,

I hace to check my dataflows since I beliece there is a way of getting this to work, not really sure but believe that is not loading the calculated table but making a new one that refers to that one, so you would have a duplication of the table but since one is not loaded you only get one on your pbi.

Let me get back to you.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix yes, you can go back to me but for me the answer is clear as water :

 

you have to pay (Premium) if you want to use Dataflow for any operation that is a little bit complex.

 

Best regards,

Augustin

MYPE

Hi @augustindelaf ,

 

I understand your comment however this is not totally accurate, has I refer you can have calculated tables in dataflow I have made the workaround but currently don't have access to that specific report.

 

Try one thing please:

Disable the calculated table on the dataflow and then make a new one referencing the calculated one so final result would be a table with the information of the first.

 

check if this causes the error of calculated asking for premium.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix ,

 

Thank you for ur answer.

" make a new one referencing the calculated one" >>> I tried, and it is not possible. Each table refencing another is considered as a computed Entity on Dataflow.

Anyway, with GA of Incremental Refresh in Power BI since yesterday, even for Pro users, I feel like SharePoint and Power BI can work together for very long.

https://powerbi.microsoft.com/en-us/blog/incremental-refresh-is-generally-available/

 

BR

Augustin

Hi @augustindelaf ,

 

In fact the Incremental refresh is great since you won't have in every single refresh the total data refreshed.

 

Sorry for no being abble to assist you onj the work around of the calculated entities but it was made in my previous job and don't recall what I did by heart.

 

If you need anything else please tell me, and don't forget to mark the correct answer.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



thanks @MFelix 

 

Do you think it could allow me to import 10 000 excel files ?

Hi @augustindelaf ,

 

This depends on the size of the files and format, also on your license. Personnaly not really sure if having this based on 10.000 excel files is the best option if you have errors on the data source (any of the 10.000 files) it will be difficult to check.

 

Can I ask why is this based on excel files?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Because ~20 people in the world with limited internet connnexion should input data in these files that they know and that have been existing for some time.

Thanks for the feedback. I also had this worry about error management.

 

We will implement something like MS Access or SQL Server soon.

 

hnguy71
Solution Supplier
Solution Supplier

@augustindelaf 
Not a problem as long as you stay under the 1 GIG limitation.

 

EDIT: Do note also that for each file there is, it would take longer depending on your transformation steps

 

To check the size of my pbi dataset, i just need to go to Properties window of the pbix, and check the file size, right ?

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!