PBI services Odbc.DataSource dynamic dsn name

user_1444097 0
Viewed 123

In PBI desktop file no errors, erro appear only in PBI service on refreshing

ERROR:

Query contains unsupported function. Function name:Odbc.DataSource

Parameter1

Mydsn ' as parameter

used as text - not dynamic

= Odbc.DataSource("dsn=Mydsn", [HierarchicalNavigation=true])  ' no error

as used us text Parameter - not dynamic

= Odbc.DataSource("dsn=" & Parameter1, [HierarchicalNavigation=true])  ' no error,

Odbc_dsn ' Query

= settings[Column2]{0} ' from csv

from csv query

= Odbc.DataSource("dsn=" & Odbc_dsn, [HierarchicalNavigation=true])  ' Query contains unsupported function. Function name: Odbc.DataSource

directly from csv table

= Odbc.DataSource("dsn=" & settings[Column2]{0}, [HierarchicalNavigation=true])  ' Query contains unsupported function. Function name: Odbc.DataSource
  • No one of Privacy settings is not change anything, tryied all available ways. (change to none, private, organizational, public, disabling privacy settings and etc)

How to use odbs source DSN name from csv file?

m dax powerbi powerquery odbc
1 Answers

(Answer to be expanded with additional info provided - see comments on original question)

While I have never imported a DSN name through a CSV, your saying that it works on your local machine makes me accept that this is at least possible so we'll instead focus on issues with the gateway.

My first impression here as to why this might not be working is simply permissions and visibility.

Having worked with a number of PowerBI Service setups, the issue with an unrecognized ODBC DSN usually falls into the following issues:

  1. Is the DSN setup as a system DSN?
  2. Is the gateway setup as a LocalService Account vs PowerBI Gateway Host Account?
  3. Does which user the gateway is setup under actually have permissions to the directory that the data source (or custom connector) that the connection depends on?

So:

  1. Fairly straight forward: all gateway accessible ODBC sources need to be setup on the gateway host as system DSNs, not user DSNs. See your ODBC Data Source Administrator here: enter image description here

  2. Confirm the On-Premise Gateway "Logon" User on the gateway's host machine? Generally I recommend going to Windows Services and making sure to use the "Local System account" (to inherit permissions) but just consider this during the next step of checking local permissions. enter image description here

  3. This applies to anything which is "self-hosted" on the local machine that is the gateway host: Whichever account is hosting the powerbi gateway service must also be given explicit permissions to the local resources needed. For example, if you add a custom connector to the documents directory on the gateway host under your user account - make sure the PowerBI default user has access to that directory and file. I.E. File properties -> Security -> User permission etc. enter image description here

In my experience, 9/10 times one of these things isn't setup right.

Additional note - every time you upgrade or re-install a powerbi gateway host, you will have to change the service login account and double check all permissions. I don't know why but it overwrites that setting by default disabling all refresh until restored.

Edit:

After further thinking, I believe you will eventually run into the roadblock regardless - PowerBI Service's Gateway Data Source mappings are 1-1. After upload you will get this screen in the dataset settings:

Dataset Gateway Mapping

Which requires that the data source has been defined in the PowerBI service's settings:

Gateway Data Source Definition

I don't believe that it is currently possible to make that definition a variably composed string per user's request.

user_30733400
Related