on 01 Sep 2016 22:17:00
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.
- Comments (12)
RE: Support databases with >10000 objects
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.
RE: Support databases with >10000 objects
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.
RE: Support databases with >10000 objects
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.
RE: Support databases with >10000 objects
KEN CLUBOK, ERES EL MEJOR!!!!
RE: Support databases with >10000 objects
Ken Clubok, eres el put@ amo!! gracias!
RE: Support databases with >10000 objects
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!
RE: Support databases with >10000 objects
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.
RE: Support databases with >10000 objects
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.
RE: Support databases with >10000 objects
I have + 10,000 items within a single schema. What to do then ?
RE: Support databases with >10000 objects
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.