Support databases with >10000 objects
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.
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.
Rodrigo Dornelas Melo commented
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.
Paul Swan commented
This needs to be fixed, what is the point in having the selection screen if us ERP report writers have to hand code SQL just to get the table we are after.
If you have a NAV database with anything more than 2 companies it will easily exceed 10,000 tables.
At least you could look at getting the search function working by re-querying the database for all tables containing the search term instead of just the downloaded list of entries.
A simple fix for a really annoying problem.
Rafa Morales commented
KEN CLUBOK, ERES EL MEJOR!!!!
Daniel Pernigotti commented
Ken Clubok, eres el put@ amo!! gracias!
Pawel Potasinski commented
I don't believe it. Microsoft, really? You really think there are no databases with tens of thousands of tables in a single schema? Well known old-fashioned Dynamics apps, IFS-like Oracle-based ERPs, intensively developed SAP BW databases almost ALWAYS have these numbers of objects! It is a shame to force people to use dirty workarounds like duplicating queries, writing SQLs or creating dedicated schemas with views just to pass a limitation of poorly designed BI tool. Shame!
Ken Clubok commented
I have found that even on a database with over 10,000 objects, you can access the tables beyond the first 10,000. First, use the search to select one of the tables in the first 10,000. PBI will create a query against that table that you can see and manipulate in the Query Editor. From the Query Editor, right-click that query, and then select "Duplicate". Then right-click on the new copy you just made, and select "Advanced Editor". Replace the old table name with any table name you like, and click "Done". Right-click on that query again, and select "Properties" in order to change the name to the name of your table, and you're done!
It's a lot less convenient than using the dialog, but at least you're not completely dead in the water.
10,000+ items within a single schema for an ERP solution such as Oracle is the norm and not the exception. The filter only works at the schema level and not the entities within the schema.
I have + 10,000 items within a single schema. What to do then ?
Morten Seifert commented
Microsoft Dynamics NAV 2017 does create a table for each company in the database, thus you may have many more han 10,000 tables and no way to reach them by the explorer.
I get complaints from users about this. Those users want to query SAP BW tables on the database directly (don't ask). In this case, they've got around 60.000 objects in a single schema and the hierarchical navigation doesn't help. the major complaint is about the fact, that Power BI doesn't show a message that a limit is reached - for them Power BI simply can't find the desired tables - a bad start for new users. I thought about a workaround that involves a user with permissions on only a subset of tables - not good to explain that to the SAP Basis guys... I agree that noone ever will scroll though 60k tables, but I'd expect the search to work on the whole set of tables and not only on the first 10000.
Thanks for your feedback - note that, as a mitigation for this issue, you can enable Hierarchical Navigation from the connector dialog (i.e. From Oracle), which will group the navigator tables by schema. Generally, you won't have 10,000+ items within a single schema.