Dataset needs gateway

[sergazyyev]

One dataset (A) is connected to another (B). When I want to refresh the first one (A) I cannot do it because second dataset (B) acts as a datasource and needs a gateway. How to avoid this issue because I do not want second dataset (B) to be refreshed but just have a connection to the first one (A)?

[sergazyyev]

Hi! Thank you for your response!

Let me explain a full picture step by step.

I have a dataset B that contains some historical data and does not need to be refreshed. So, in each query in Power Query Editor I turned off report refresh. Then I published this dataset B to the Service. 

Then I have a dataset A that contains fresh data and needs to be refreshed. Also, it must have historical data. So, while I am in dataset A, I connected to the dataset B through "Get data -> Power BI datasets" and chose dataset B. 

To put dataset A on scheduled refresh, I go to the gateway connection settings. This is when things get unclear and dataset B which acts as a data source requires adding to gateway

Why does it happen? 

[ibarrau · Accepted solution]

Ok, now it's making sense. It's a composite model. PowerBi Dataset B + other sources. Are those other sources connected with a gateway? There is an option when using a gateway to allow the dataset use cloud and onpremise sources without requiring a gateway. That one can make conflict if it's not checked.

I hope that helps,

[sergazyyev]

Hi!

Thank you for your response!

After checking an option of allowing user's cloud data sources to refresh through the gateway cluster, it became optional to add gateway for the dataset B.

At least this allowed me to apply the settings. However, when I wanted to refresh dataset A it led to another problem.

I am investigating the issue but if you have a solution, it would me much appreciated

[ibarrau]

Hi. I'm glad it worked. The new error specifies the issues very clear. "Refresh is not supported for datasets witha calculated table or calculated column that depends on a table which references Analysis Services using Direct Query".

You might have a column that is not allowed using direct query on the tabular model. Please check the model and remove the calculated column (dax). You might be able to try a Measure if it's reaaaaaally necessary.

I hope that helps,

[sergazyyev]

Hi!

Thank you for your responses and time! I appreciate the way you structurely and patiently helped me!

The problem has been solved after I removed calculated columns but I really needed them (however, it's whole another story)

[sergazyyev]

Thank you for your answer!

To add more information, read the following:

When I connect to dataset B through get data from Power BI Dataset it automatically connects to AnalysisServices. 

Here is my lineage:

I want to refresh dataset A but it requires a gateway for dataset B. How to avoid this requirment of adding the gateway?

[ibarrau]

Hi. If you get data from dataset B when creating A as Live Connection like I have said before, then there is nothing to refresh. It should always be the same source from A as B. No gateway, no refresh, just live connection.

If you need a gateway it might be for refreshing B from the SQL Servers if they are on premise.

I hope that helps,

[ibarrau]

Hi. If you want to connect a PowerBI Desktop to a PowerBi Dataset published on Service. Just get data from Power Bi Dataset and don't use AnalysisServices conector with the address powerbi://api.powerbi.... Unless you know what you are doing and you have your reasons, you shouldn't do that.

Connecting a Web instance of Anaysis Services shouldn't need a gateway. Are you sure that's the only source? try checking Data Sources Credentials on the same screen. It might just need to edit credentials in order to work. PowerBi won't recognize the credentials automatically because it's an Analysis Service instance and not a powerbi dataset.

I hope that helps,