Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
When I want to use data from a sql server database I can only see tables and views which exists in the dbo-schema.
tables and views which exist in another schema are not visible.
Will this be supported in future editions ?
Solved! Go to Solution.
Hi Franky,
interesting. 10k tables are definitely a lot, i do not know how PBI handles that.
Have you tried "crafting" your own connection string without using the "get data" api?
Take your example database and copy the "advanced options" of the connection. It should look a bit like this (please be aware that i used pseudo-code, so it does not fit 100%).
let
Source = Sql.Databases("YOURSERVERNAME.database.windows.net"),
YOURSERVERNAME= Source{[Name="YOURDATABASENAME"]}[Data],
Schema_TableName = YOURDATABASENAME{[Schema="scheme",Item="TableName"]}[Data]
in
Schema_TableName
Try to replace the elements in the string with the elements of the big Navision Database. 🙂
You could also try using the SQL statement in the "Get Data" box to directly specify the tables and views you want rather than selecting from the list
Are you sure your user credentials are set up correctly in sql server?
I can see all schemas in my database no problem at all
Yes, I use NT-authentication. just like I connect with SSMS. (I make the views with my account)
When I create the same view below dbo-schema I can see it, when I create the view in another schema (that I create) I do not see the view.
(I've just downloaded the last version of powerbi desktop.)
Not sure how to resolve that then sorry, as a workaround you could try using ODBC to connect.
I also do not have the problem. I can see all schemas.
Are you trying to access via PowerBI Desktop or the webservice? Try to install the enterprise gateway and then use the web-service. So you might be able to tell whether it is a local issue, web-service or database-side.
Hi,
thx for the feedback.
I'm using powerbi desktop (Version: 2.29.4217.341)
I made a small testdatabase with just a table in the dbo schema and a table and a view in the PBI schema.
All are visible. (dbo.Table1, PBI.Table1 and PBI.vw_Table2)
When I use the filter PBI, I only see PBI.Table1 and PBI.Table2, so this works correct.
But when I make a PBI schema and views in that schema in a Navision sql database (over 10 K tables) I cannot see them.
When I filter on PBI they also do not show, (So it's not a problem that there are to many tables to show)
So I would think the amount of tables/views are the problem. (both databases on same server, my laptop)
Hi Franky,
interesting. 10k tables are definitely a lot, i do not know how PBI handles that.
Have you tried "crafting" your own connection string without using the "get data" api?
Take your example database and copy the "advanced options" of the connection. It should look a bit like this (please be aware that i used pseudo-code, so it does not fit 100%).
let
Source = Sql.Databases("YOURSERVERNAME.database.windows.net"),
YOURSERVERNAME= Source{[Name="YOURDATABASENAME"]}[Data],
Schema_TableName = YOURDATABASENAME{[Schema="scheme",Item="TableName"]}[Data]
in
Schema_TableName
Try to replace the elements in the string with the elements of the big Navision Database. 🙂
Hello,
Thank you for your answer. What if I wanted more than one database with the same table's name?
let
Source = Sql.Databases("YOURSERVERNAME.database.windows.net"),
YOURSERVERNAME= Source{[Name="MORETHANONEDATABASE"]}[Data],
Schema_TableName = MORETHANONEDATABASE{[Schema="scheme",Item="SameTableName"]}[Data]
in
Schema_TableName
I've done the following to get what I am trying to describe above but I bet there's a way better way of doing it using something similar to what you suggested:
let
Source = Sql.Databases("YOURSERVERNAME"),
#"Filtered Rows1" = Table.SelectRows(Source, each ([Name] = "DATABASE1" or [Name] = "DATABASE2" or [Name] = "DATABASE3")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"Name", "Data"}, {"Data.Name", "Data.Data"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = "SameTableName")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"Data.Data"}),
#"Expanded Data.Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data.Data", {"COLUMN1", "COLUMN2"})
Can you think of a way of doing this?
Thanks a lot!
Laura
Hi Bjoern,
thx, this fixes my problem without having to move my views to the dbo schema. (in which exists 1000's of tables and views)
You could also try using the SQL statement in the "Get Data" box to directly specify the tables and views you want rather than selecting from the list
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |