Publishing dashboard to Office 365

[Chrisp22o]

Hi,

I have created a dashboard that uses three different data sources - ODBC connection, SQL connection and an Excel file.

I would like to publish this to my company's Office 365 environment. With my dashboard using connections to our on premise sources, how will Power BI refresh the data? Do I need to allow ports through on our firewall to that Power BI can communicate with our databases in order to refresh? I am using the Pro licence.

If anyone can provide any information that would be appreciated.

Kind Regards,
Chris

[v-yuezhe-msft]

Hi @Chrisp22o,

Firstly, do you combine the three data sources in a single dataset? If that is the case, in order to refresh the data in Power BI Service after you publish reports from Power BI Desktop, you would need to install on-premises gateway following the instructions as other post  and add the three data sources(ODBC connection, SQL Server and an Excel file) under the gateway. You can add a data source by either selecting a gateway and click Add data source, or go to Gateway > Add data source.


Secondly, for SQL Server, you would need to add TCP port and UDP port 1434 into Windows Firewall inbound rules in order to add SQL Server data source under on-premises gateway, also please ensure that server and database name completely match between Power BI Desktop and the data source within the on-premises data gateway.

Thirdly, when adding ODBC data source under the gateway, please enter connection string like ”dsn=odbcsourcename”, for more details, please review this similar thread.

In addition, after you successfully add the three data sources, you can you select the ellipse (...) for your dataset and set schedule refresh for it as shown in the following screenshot.


For more details about data refresh in Power BI, please review this article: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/ .

Thanks,
Lydia Zhang

[Chrisp22o]

Thank you very much for your replies, I'll have a look installing the on-premises gateway and for the SQL connection allowing port 1434 into the inbound rules.

Thanks

Chris

[v-yuezhe-msft]

Hi @Chrisp22o，

Also add TCP port into inbound rules. Default instance uses 1433 as TCP port.

Thanks,
Lydia Zhang

[Anonymous]

I m trying to publish from Power BI Desktop and unable to do so.  Seems like firewall/ port issue.

Here, I m not trying to configure on-premises data refresh.

Is there a specific port that I need to open in order to publish Power BI Reports?

I m using Impala for Data Source and able to connect to the source from within the box.

[mifrasmm]

Check below may helpful

Ports

The gateway creates an outbound connection to Azure Service Bus. It communicates on outbound ports: TCP 443 (default), 5671, 5672, 9350 thru 9354. The gateway does not require inbound ports. 

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/#ports

any error message your getting while publish ? 

[Anonymous]

Thanks @mifrasmm for the response.

I m not using gateway (at least not yet). I m only trying to publish the report from Power BI Desktop.

[mifrasmm]

Any error message pops up?

[Anonymous]

Hi @mifrasmm

I also noticed that the error - 500 Internal Server error is coming up only in Direct Query (with Impala as data source).

In import mode, the same report is getting published.

Documentation states that data gateways are not currently supported for Impala !!!

Curious to know if it is still possible  to do it?

 Thanks again for your help.

[blopez11]

You may want to take a look at the following as it may have the answers to your questions

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/