Using gateway for Azure SQL DB and Cloudera Hive

[Anonymous]

Hi,

We have 2 data sources, one in Cloudera Hive and the other on Azure SQL DB.

Data size is around 200 MB on azure SQL DB and 2GB  in Cloudera Hive. The data size is not going to increase beyond another 25%.

So we intend to go with import mode for Azure SQL DB and Direct Query for cloudera.

Will it cause a problem in schedulingvif we try to mix Direct Query and Import mode?

I have had problems earlier working with oline source and imported data as part of the same data set.

Also, should I install data gateway on Azure VM for Azure SQL DB or will it auto refresh?

[v-yuezhe-msft]

Hi @Anonymous,

Do you want to combine the two data sources in a single Power BI Desktop file? If that is the case, it is not supported to mix DirectQuery and import mode. When using DirectQuery mode, all data imported to Power BI Desktop must come from same data source and same database.

In your scenario, you have to use import mode if you want to combine the above data sources. And you would need to install gateway to refresh your dataset. However, if your dataset only contain Azure SQL database, gateway is not required when Power BI refreshing the dataset.

Thanks,
Lydia Zhang

[Anonymous]

Thanks Lydia @v-yuezhe-msft for the response.

Yes, I wanted to combine two data sources into the same PBIX.

if I correctly understand the second half of your reply - you meant that the on-premise gateway installation is not required if import mode is used for Azure SQL DB. However, we still need to enable windows service on Azure SQL DB to refresh the data right?

For cloudera or other non-azure cloud based DB (used in import mode) we need to use on-premise gateway.

Please correct me if Im wrong.

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

Do you mean the "allow windows azure services to access this SQL database" option? If so , this option should be enabled.

In addition, if your dataset contains only Azure SQL database, gateway is not required. If you combine Azure SQL database and cloudera/other on-premises data sources in a single dataset, you need to use on-premises gateway or personal gateway.

Thanks,
Lydia Zhang