Teradata LDAP source; Cloud Refresh not working

[Anonymous]

I have Power BI reports connected to Teradata via ODBC because they use LDAP. These reports connect via the GUI/M code. The data refreshes fine in Desktop, but when Publishing to PBI Service the refresh time's out after 2 hrs per PBI's timeout setting.

When I convert the connection to a SQL custom statement, with some M code used to remove columns, etc., publish that and then refresh it, the refresh works fine.

Has anyone else had issues with M code statement not refreshing in cloud but Sql will? Anyone with this issue using ODBC to Teradata LDAP?

Any explanation of why this would be? Is it a Gateway configuration issue, a Power BI known issue, or possibly something with our network/firewall/something else? Just trying to understand why I need sql statements for this to work.

Thanks!

[v-yuta-msft]

HI MatthieuJC,

Which error are you encountering with when you refresh your report? You say "with M code not refresh but will SQL can", could you please clarify more details about "M code"?

Regards,

Jimmy Tao

[Anonymous]

Thanks for your response, @v-yuta-msft.

The error is usually a timeout error at the two-hour mark.

What I meant when I said it will refresh with custom sql code but not with M is that if the connection is setup using M/Power BI GUI, the refresh will always timeout whereas if I connect and put in a custom sql statement like Select * From SomeDatabase.SomeTable, it will refresh, even if I do some M afterwards like remove columns.

Ie- 

let
Source = Odbc.Query("dsn=Prod Teradata", "select #(lf)*#(lf)from SOMEDATABASE.SOMEVIEW"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Department ID] = 2 or [Department ID] = 4 or [Department ID] = 14))
in
#"Filtered Rows"

refreshes in cloud but 

let
Source = Odbc.DataSource("dsn=Prod Teradata", [HierarchicalNavigation=true]),
SOMEDATABASE_Schema = Source{[Name="SOMEDATABASE",Kind="Schema"]}[Data],
SOMEVIEW_View = SOMEDATABASE_Schema{[Name="SOMEVIEW",Kind="View"]}[Data]
in
SOMEVIEW_View

does not.

[v-yuta-msft]

Hi MatthieuJC,

Odbc.Datasource is different from Odbc.Query. It returns a table of SQL tables and views from the ODBC data source specified by the connection string, but doesn't return and load the table into power bi. 

In addtion, M code can only be used in power bi to shape and transform data. It can't be used to load external data source.

Regards,

Jimmy Tao 

[Anonymous]

@v-yuta-msft I appreciate the response.

I'm not sure I fully understand how this would impact refreshing in the cloud vs refreshing in Desktop. I did not code these myself: they were autogenerated M statements from the GUI. On one, I selected Get Data; ODBC; chose Server (PROD TERADATA); Selected the Database and View; on the other, I selected Get Data; ODBC; chose Server (PROD TERADATA); added a custom sql statement (Select * From Database.TheView).

Both refresh in Desktop fine, but when published to the cloud, only the query with the custom sql statement will refresh- the other will hit the 2 hr timeout limit and fail.

I have not experienced this with other datasources so i'm wondering if this is an issue with ODBC, with Teradata, with LDAP, with company specific security, with the gateway, or some other reason.

[v-yuta-msft]

Hi MatthieuJC,

When the report\dashboard in PBI service need to be refreshed, PBI service will send a encrypted message which contains query statement to gateway, then the gateway search the data source through AD and send the query to data source. The first query is a SQL query statement which can be recognize by power bi service. But the second M code is not a SQL query statement which can't be recoginzed by PBI service , encrypted and sent to gateway. 

This link about how PBI service interact with gateway for your reference:

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-indepth.

Regards,

Jimmy Tao