Composite report publishing error: "unable to find a gateway"

[Anonymous]

Hi there! We are unable to use this cool preview feature. Please look at the below screenshots.

The weird thing is that when we publish the same .pbix without the DirectQuery tables we have no problem, it publishes ok (the tables come from the same data source: a database in an sql server; so i guess it is not a permissions issue.)

#1 - Publishing composite model error.#2 - In the server, display error for every visual.#3 - In the server, error message.#4 - Publishing the same .pbix, with the same data source, without DirectQuery tables.

#5 - Obviously the Composite Models preview feature is enabled.

Regards.

[Anonymous]

BTW we have updated the gateway to the latest version (3000.0.144.3 - october 2018). Still no luck.

[v-shex-msft]

Hi @Anonymous,

Based on my test, I found composite 'direct query' mode reports are available on power bi service side.

The notice message seem not correct, you need to add datasource on service side and manually turn on 'use gateway' and choose gateway who conquered correspond data source.

After these steps, your report works well on service side.

BTW, if you haven't finish configure your datasource and refresh settings, it will show error instead to notice you to finish your dataset configurations.

Regards,

Xiaoxin Sheng

[Anonymous]

I think I found the problem.

When I publish a composite report with just two tables (one query import, another direct query) from the same SQL server, it goes ok.

When I publish a composite report with just four tables (two queries import, another two direct query) from the same SQL server, it goes ok.

If I try to publish a composite model that has another query from a different SQL server I get the error in the OP.

@v-shex-msft can you or someone else please confirm this is the case? Meaning, you can´t publish files using the new Composite Models feature with multiple SQL Server sources. Also, is there an ETA on when this will be available?

Regards.

[v-shex-msft]

Hi @Anonymous,

>>If I try to publish a composite model that has another query from a different SQL server I get the error in the OP.

What type of sql server data sources, can you explain more about this?( On-premise, online or mixed on-premise and online data source)

In addition, is there any advance operations in query tables?(append, merge, reference, invoke custom function...)

Regards,

Xiaoxin Sheng

[Anonymous]

Can you @v-shex-msft or anyone confirm or deny the impossibility of publishing a composite model with several SQL Server sources? Is there an estimated time to fix this?

[v-shex-msft]

HI @Anonymous,

On premise version means normal sql server database you installed on device, online version means azure sql which is cloudy based and not require gateway.(sql install in azure vm means on-premise version)

I following your steps but I can't reproduce your issue, it works after I configure all data sources and add to gateway.

Add datasourcechoose gatewayenable refreshafter refresh

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Thanks for the help @v-shex-msft!

At last! I´ve found and resolved the issue. You were right, the gateway was not correctly configured. I built several composite models with a single data source (so test1.pbix had only Server1 as data source, test2.pbix had only Server2 as data source, etc.) and I published them. One gave me the error..., because that data source was not included in the gateway. 

[Anonymous]

Hi @v-shex-msft, 

there are no advanced operations, I´m just using the raw data from the queries to test this.

When you say "On-premise SQL server data source" do you mean the image below, right?

 

If that is the case, then all the queries are online. To clarify, this has been my workflow:

In PBI desktop, open a blank .pbix, go to "Edit queries", go to "New Source", go to "SQL server", load Table1 from Database1 from Server1 in import mode.

Then go to "New Source" again, go to "SQL server", load Table1 from Database1 from Server1 in DirectQuery mode.

Close & Apply and then publish the composite model. All ok, no error.

But, if I go back t "Edit queries", "New Source", "SQL server", and load Table2 from Database2 from Server2 in import mode, then I get the error in OP.

Regards.

[Anonymous]

Hello @v-shex-msft,

We get the error below when trying to activate the personal gateway. We only get this error with the composite dataset, every other is working ok with the same gateway.

  

Sorry for the language. It sais: there was an error with the gateway, make sure the gateway is working ok and try again. Which is odd because the gateway is working ok with every other dataset. 

Best regards, 

Alejandro.

[Anonymous]

Update: I made a new report to test if the error had something to do with this report in particular.

Thanks to that, I realized that when you open a composite model you get a dialog like this:

 

While developing the report that we are having trouble with, I clicked "Continue", but with this test report I just made I´ve clicked "Validate" and it has published to the service with no problem whatsoever:

Composite model published to the service. 

But, although I´ve managed to get this dialog with the original report (and I´ve clicked Validate), I keep getting the same error that I posted in the previous answer when trying to activate the gateway: "there was an error with the gateway...".

Should I redo the report from scratch?

[Anonymous]

Thank you for your answer. I have a busy day today but I´ll try that asap and come back with an answer.