Forum Discussion

cjarvis's avatar
cjarvis
Frequent Visitor
2 years ago

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

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.
-----------------------

14 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion

    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.

  • cjarvis's avatar
    cjarvis
    Frequent Visitor

    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's avatar
      aj1973
      Icon for Community Champion rankCommunity Champion

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

      • cjarvis's avatar
        cjarvis
        Frequent Visitor

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