Support databases with >10000 objects

[pbiideas1]

The current version of the data source browser in PBI desktop only seems to support a limited numer of objects. Only the first 10.000 objects are shown in the list - and it becomes sloooow. If the desired object is not under the first 10.000, you are lost. The search won't work. Your only option is to write a query in the SQL field. But because there is no option to Name your data source, you can only do this once - another query on the same source will overwrite the data source. I experienced that with an Oracle DB, so perhaps the issue is source specific.

[rodrigomelo]

Do you have any solutions? In my organization we use a TOTVS's ERP, their database has no normalization, we use only one schema with 16,000 tables.

[mastro_michael_]

Yes, this limitation is aggravating for sure for inexperienced data architects/data developers. Most new developers just want to pull everything into Power BI. But this is not the way we want to do things. Most reporting efforts require some degree of ETL/ELT. IE: Changing column names, padding columns, selecting only the columns we need, using a WHERE clause to limit data, possibly aggregating data, etc Which involves the use of other schemas, views, stored procedures, databases, etc to shape the data we are after. So, what am I saying? Let's say you're using Dynamics and you need two tables that start with V (which is outside the 10,000 table list). What we want to do is think about the data we are after and shape it using a bit of SQL. Let’s say you want columns from VENDTABLE and the VENDINVOICEJOUR. You can write your query and put that into a view. Create the view in (let’s say) the .rpt schema (that I have put in several databases specifically for this purpose). And then you can access that view from Power BI. Each schema (dbo, rpt, information_schema) has a 10000 object limit. There are only about 10 objects on that server in the .rpt schema. So, do not think in terms of grabbing all the data first in Power BI. This works for smaller databases, but not for large ERP/database systems. Think in terms of limiting/building what you want through the use of SQL. Then put that in a different schema. Then querying those structures to build your reports. This is not a hokey solution. This is a good design principal.

[Paul_Swan1]

As an alternative to increasing the limit could the search function re-query the database and just pull the tables that match rather than the (dare I say it) lazy method of filtering the entries in the list.

[DBroer]

Minimal Valuable Product: Overrule selection button OR choice input field with 10.000 as default.

[rarboleda]

Good day,

I agree with this idea. We have a situation in which a database has more than 10000 objects, and we cannot look for specific once that the limit has been surpassed.