Forum Discussion

Franky's avatar
Franky
Regular Visitor
10 years ago
Solved

sql server as a datasource, only dbo-tables/views visible

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 suppo...
  • Bjoern's avatar
    Bjoern
    10 years ago

    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. :)

  • itchyeyeballs's avatar
    itchyeyeballs
    10 years ago

    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 

Bjoern's avatar
Bjoern
Continued Contributor
10 years ago

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. :)

Franky's avatar
Franky
Regular Visitor
10 years ago

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)