BI Aggregation, different source connections

[Anonymous]

Hi,

In Power BI Desktop I've set up a connection to HDInsight Spark and used this for the direct query table.  I have also connected to Hive and used this to import an aggregated table. I've then set up aggregations, mapping the import table (Hive) against the direct query table (Spark).  This works well in desktop with no issues.

If I publish this to the premium BI service then I get a failure, with the message "no available gateway".

In the BI service is there a requirement for the aggregation composite model to use the same data source  for the direct query and for the imported data set?

Thanks

[v-lili6-msft]

hi, @Anonymous 

Yes, you need to install a gateway and add the data source in it for power bi service to connect to data source and refresh.

Please refer to the documents:

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

Best Regards,

Lin

[Anonymous]

Hi Lin,

Sorry i should have posted the tests below in the original post. There is already a gateway set up so I think that message is a bit of a red herring we are getting. Prior to posting i testing this:

1. Published to service the model with a connection to the HDInsight Spark Cluster as direct query. This worked

2. Published a model with 2 imported tables from Hive ODBC. This worked 

3. Published a composite model with a spark table set as direct query and 2 hive imported tables (tests 1 & 2 above) but no aggregations set up between Spark and Hive.  All data sources worked independently

4. Published a composite model with a spark table set as direct query and 2 hive imported tables and aggregations set up.  This failed with the no gateway available message

5. Published a composite model with Spark table set as direct query and also a table imported from Spark.  Aggregations were were set up and works in the service.

As all of the 5 tests work in Power BI desktop and only test 4 fails in the service. I also noticed on test 4 that when in the service I have no ability to set up the credentials as it is greyed out, although they are set in the desktop 

The question  therefore is, do you know if there is a restriction on the BI service, that means when using aggregations the direct query source and  the imported aggregated sources have to be from the same source i.e. Spark & Spark or Hive & Hive but not a combination of Spark & Hive? 

Thanks