I'm playing around with a custom connector based off the SqlOdbc sample. It will be connecting to MSSQL data.
For tidiness within Power BI Desktop, when the user elects to use this connector to get data, I would like:
- Only a subset of the databases on the server to be presented, probably based on a naming scheme, e.g. only list databases named like 'MyBusinessUnit1', 'MyBusinessUnit2' and so forth.
- One the user has selected a database, only certain views to be presented, again probably based on name. such as 'PBISales', 'PBICustomers' and so forth.
What I have done so far is on the MSSQL side. I have created a schema, only added the required views to the schema, then added a login tied to that schema, and made the connector use that login with username\password authentication.
Which is fine, except when in Power BI Desktop Get Data, all server databases are listed, but any that the SQL login doesn't have access to show an ODBC error under them.
I'm guessing that I could add an SQLTables function handler in the .pq file to somehow filter the list of tables returned but have no clue what the syntax there would be. Can anyone suggest anything?
