powerbi - PBI 服务 Odbc.DataSource 动态 dsn 名称

Question

在 PBI 桌面文件中没有错误，错误只出现在 PBI 服务刷新时

错误：

查询包含不受支持的功能。函数名称：Odbc.DataSource

参数1

Mydsn ' 作为参数

用作文本 - 不是动态的

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

正如我们使用的文本参数 - 不是动态的

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

 

odbc_dsn '查询

= settings[Column2]{0} ' 来自 csv

来自 csv 查询

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

直接从 csv 表

= Odbc.DataSource("dsn=" & settings[Column2]{0}, [HierarchicalNavigation=true])  ' Query contains unsupported function. Function name: Odbc.DataSource

• 没有一项隐私设置不会改变任何东西，尝试了所有可用的方式。（更改为无、私人、组织、公共、禁用隐私设置等）

如何使用 csv 文件中的 odbs 源 DSN 名称？

标签： powerbiodbcdaxpowerquerym

Answer 1

(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:

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.

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.

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:

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

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