Can't refresh dataset with merged table from different data sources (incompatible credentials?)

[cjarvis]

I have a need to merge tables from two data warehouses. I load a table from Teradata into one (supported credential types: Database, Windows), and another table from Snowflake (supported credential types: Snowflake, Microsoft Account). Everything is fine if I use data from each table in separate visuals. 

If I append one table to the other, everything is fine in the desktop application. However, the dataset cannot be refreshed in the Power BI service. I keep getting the error information copied below. "Merged Query" is the name of the merged table. The key error message is "The data source kind doesn't support the credential type. Data source kind: Snowflake. Credential type: Windows." I clearly agree - Snowflake doesn't support Windows as the credential type. I didn't even select that - I used my Microsoft Account (i.e. Azure AD) credentials. Why Power BI thinks that this credential type is associated with this data source is bewildering.

The other bit I'm failing to understand is why credentials are even involved in the merged table. The data can be loaded from Teradata and the data can be loaded from Snowflake. Then the data gets combined. Why does the same credential type need to be supported across the underlying sources?

Is there some way to decouple the data tables in PwoerQuery Editor so that their underlying credentials aren't associated with the linked table?

Note: Combining the data from both sources into the same warehouse is planned, but won't happen for another 18 months.

Full PoweryQuery code for the merged table:

-----------------------

let
Source = #"Snowflake query",
#"Appended Query" = Table.Combine({Source, #"Teradata query"})
in
#"Appended Query"

-----------------------

Error information:

-----------------------
{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The data source kind doesn't support the credential type. Data source kind: Snowflake. Credential type: Windows."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}}],"exceptionCulprit":1}}} Table: Merged query.
-----------------------

[cjarvis]

Hi @aj1973,  

Thank you for the suggestion - unfortunately, it did not work. Power BI seems to retain the full data lineage for the merged table. The Power BI Service returned the same error message about authentication types.

By the way, I assume that you mean the tables should be set to not load like this in Power Query Editor:

I should also note that once the tables were set to not load, data from the tables could not longer be used in the report:

[aj1973]

Well you should now use the merged query table and its fields to build the report, visuals and KPIs

[cjarvis]

No, I still can't use the merged query to build visuals in the report as the online service still returns this error message:

[aj1973]

You need to edit credentials for each source in the service through dataset settings, did you do it?

[cjarvis]

... In what way am I meant to edit the data source settings?

The data loads work perfectly in the Power BI Desktop application. I have credentials for each data source that can access and retrieve the data.

These are the details for the Snowflake data source:

And these are the details for the Teradata source:

[aj1973]

Ok, I didn't mean the way you connect in the desktop, even though it looks like you should not connect to snowflake using Windows. Please use Snowflake connection and then you need check ou this here in the service

Also, Terradata DB looks like is Local, therefore you need to install an On-Prem Geatway

[cjarvis]

On the gateway front - this is not my first rodeo. We have on-prem gateways set up - both data sources have connections available on the same gateway cluster.

The screenshot below shows that the credentials for the two data sources are set up and are valid. 

The "Some credentials..." warning is not applicable because I have proven that the gateway connection works for both data sources in other Power BI reports. In fact, I can load queries from both sources in the one file if the tables are not combined. i.e. These data sources both work independently and in the same file. The issue here is that an error appears when the data tables are combined.

For example, here is information from a different Power BI file that queries both Teradata and Snowflake.

Two queries are in this file - note that the two queries are not combined in this case.

The dataset can be refreshed without error in the Power BI Service.

The report is linked to the same gateway connections as the one that has the error.

Here is a screenshot of the credentials section of the dataset settings page for this other file. Note the same warning about credentials but this dataset refreshes without issue.

[aj1973]

Double checking this

Snowflake doesn't need a Gateway connection, why did you map it in there? couldn't it be that the problem?

[cjarvis]

For security, my company has a private / direct connection to Snowflake. We cannot use a cloud connection.

I have tried to set up a cloud connection like this:

Settings the credentials fails with this error:

I really need to point out that, without the table merge, this report retrieves data successfully from Snowflake. The problem is not connecting to Snowflake. The problem is the Power BI Service getting hung up on an irrelevant concern about Teradata and Snowflake having different sets of supported credential types. The service can query data from both data sources, even in the same file. 

[aj1973]

Ok I see.

Before appending the 2 Queries did you make sure that Names, Format, Number of columns... are the same in both Tables? In Power query did you check if all Applied Steps in both tables and in the Appended one that the Query folding is not greyed out?

It seems more like there is an incompatibility format somewhere. 

Since you are testing go by appending one column at once and see how it works out.

Let me know please.

[cjarvis]

I've seen some conflicting information about query folding - I assume that you mean I should look for "view native query" being available on the context menu...

I've reduced the Snowflake query to selecting the top 10 of a single ID:

I've done the same with Teradata:

Both OBJECT_NUMBER columns are text. The merged table is also as simple as I can make it:

Neither of the Snowflake or Teradata "source" steps have "view native query" available, even when "enable folding" is set to TRUE on the Snowflake query. The merged query also doesn't have it available.

Even with this setup, the error still occurs in the Power BI Service.

[aj1973]

Ok good I love testing.

Just one last try please. Format both columns of the 2 tables to Numbers and try again. Of course the merged Columns has to be Format Number as well.

[cjarvis]

The first few characters of the column are letters, so I needed to remove those before changing the data type - here is the example from the Teradata query.

The column in the merged query became a number by default. The same error resulted.

[aj1973]

Hi @cjarvis 

Try merging the 2 tables(Queries) into a new query and then Disable the load for the 2 tables. Publish and see how it works.