Forum Discussion
Can't refresh dataset with merged table from different data sources (incompatible credentials?)
Community ChampionOk, 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
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
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.
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.
- 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?
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.
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.