Forum Discussion

sckienle's avatar
sckienle
Icon for Advocate II rankAdvocate II
7 years ago

Weird ODBC Gateway Failure

I am having an odd error when publishing a report using and ODBC source and the Power BI service connecting it to the gateway.

 

The gateway ODBC source is set up properly.  If I have a Power BI report which uses that source alone, I can connect it to the gateway when published to the service.

 

If I reference another data source, but keep them separate in the M code, the gateway can be connected as well.  What I mean by "separate" is that the two sources are not used in the same table in the Power BI M code.

 

But, as soon as I use both datasources in the same M code for a table, whether by a join, a List.Contains, or other activity, when I publish I can no longer connect to the gateway.

 

Has anyone seen this behaviour before and is there a way to correct it?

 

Thanks

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi sckienle ,

     

    Did you use on prem or personal gateway ? Could you please share more details about your issue? like screenshot of the error message or something else.

     

     

    • sckienle's avatar
      sckienle
      Icon for Advocate II rankAdvocate II
      7 years ago

      This is using the on premises gateway.  I do not get an error message, just don't see the gateway:

       

       

      This is the code that will show the gateway:

       

      Let
        Source = Odbc.DataSource("dsn=FIS", [HierarchicalNavigation=true]),
        WMED_PROD_Database = Source{[Name="WMED_PROD",Kind="Database"]}[Data],
        int_Schema = WMED_PROD_Database{[Name="int",Kind="Schema"]}[Data],
        employee_vw_View = int_Schema{[Name="employee_vw",Kind="View"]}[Data]
      in
        employee_vw_View

       

      But if I add a reference to a buffered list from a different datasource, with the following code, the gateway is not available, as shown above:

       

      let
        Source = Odbc.DataSource("dsn=FIS", [HierarchicalNavigation=true]),
        WMED_PROD_Database = Source{[Name="WMED_PROD",Kind="Database"]}[Data],
        int_Schema = WMED_PROD_Database{[Name="int",Kind="Schema"]}[Data]
        employee_vw_View = int_Schema{[Name="employee_vw",Kind="View"]}[Data],
        #"Buffer Known IDs" = List.Buffer(#"Known UserIDs"),
        #"Filtered Rows" = Table.SelectRows(employee_vw_View, each List.Contains(#"Buffer Known IDs", [id_1]))
      in
        #"Filtered Rows"

       

      Some context:

      • I have the #"Known UserIDs" query in the working report, I just don't reference it in the query with the pull form the ODBC datasource
      • THE ODBC connection is pretty basic but does reference a PostgreSQL database hosted on AWS: ....us-west-1.rds.amazonaws.com
      • The #"Known UserIDs" query directly connects to an internally hosted PostgreSQL database

       

      I hope this helps.  Let me know if and how to get you more information if needed.  As I said, there is no error reported, just not being able to use the gateway.

       

      Steve

    • sckienle's avatar
      sckienle
      Icon for Advocate II rankAdvocate II
      7 years ago

      v-frfei-msft Any update on this?  I am starting to perform more and more DAX back flips to get the work done that should have been accomplished in the data layer rather than the analytical layer.

       

      Thanks,

           Steve