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.

[fbcideas_migusr]

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.

[k_soehner]

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.

[mse4]

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.

[martin34]

I have + 10,000 items within a single schema. What to do then ?

[brian_howard]

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.

[kclubok]

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.

[pawel_potasinsk]

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!

[rafael_mf]

KEN CLUBOK, ERES EL MEJOR!!!!

[sodaniel117]

Ken Clubok, eres el put@ amo!! gracias!

[paul_swan]

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.