Data gateway support for paginated reports

[Anonymous]

hello,

I'm just curious if paginated reports support connections through data gateway?

As currently I'm trying to PoC such solution but without success.

[v-danhe-msft]

Hi @Anonymous ,

Based on my research, the gateway could support for paginated reports, you could refer to below link:

https://docs.microsoft.com/en-us/power-bi/paginated-reports-save-to-power-bi-service#create-a-gateway

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft, thanks for the replay unfortunately I can't find this to work.

Currently I have solution wit multiple "standard" reports (.pbix) and with them everything is fine it can reach datasources (in Azure) behind data gateway and now I'm trying to introduce paginated reports and here comes problem as even when I try to connect to data source I get an error: 

Failed to update data source credentials: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)Hide details

Activity ID:	9f11e9be-0178-4be0-a72c-4be05e010e83
Request ID:	a787815d-cd7c-bae7-483c-cac5e8872883
Status code:	400
Time:	Mon Mar 18 2019 10:01:52 GMT+0100 (Central European Standard Time)
Version:	13.0.8730.162
Cluster URI:	https://wabi-west-europe-redirect.analysis.windows.net

db is in azure (sql managed instance)

report configuration is:

connection type: 

Microsoft Azure SQL Database

connection string:

Data Source=<server url> Catalog=<db name>

credentials are also correct connection works fine with Report Builder but not after deploying to Power BI Service.

Can you give me some hint what I'm doing wrong maybe I should update connection string somehow?

Best regards,

Michał

[jcheng]

michalg,

I got the same requirement and problem as you have. Do you find any solution yet?

James

[Anonymous]

hi @jcheng, unfortunately in my scenerio it's not working by I'm using Qzure SQL Managed Instance as db source, I found it working in another solutions what you have to do is to change SQL instead of Azure SQL as connetion type in your report datasource configuration.  

[jcheng]

I am able to find a solution for this pagenated report in connecting to SQL MI database. You will need to create CNAME in your local DNS for SQL MI server which is FQDN by default. The CNAME could be just the first part of FQDN. Then, the Power BI report builder will use the CNAME instead of FQDN as SQL data source.  The Report builder will try this CNAME as local SQL server instead of Cloud/Azure SQL server. After publishing pagenated report to Power BI App Workspace, you need to specify the rigt Gateway for this connection. Then, the pagenated report should run fine.

James

[Anonymous]

I have checked with Microsoft on this gateway support for paginated reports.  At this moment there is no gateway support and there is no roadmap for the same in 2020.  Also it is already called out in the microsoft documentation  https://docs.microsoft.com/en-us/power-bi/paginated-reports-data-sources

The other option on hand is to use a power bi dataset sourced from Azure or other data sources refreshing via gateway.  Also there is a limitation there, you cannot connect to the power bi dataset having live connection.  You can do it on the dataset having direct query or import or composite models.

Hope this helps!

[Anonymous]

Hi @Anonymous ,

I was just trying out @jcheng suggestion to make a CNAME of the Managed Instance URL and now paginated reports are actually working from Power BI using an on-premise Data Gateway connecting to a private-only SQL Managed Instance.

If this is all that it takes, shouldn't the documentation have a note about this?

Since the solution is only the introduction of a CNAME DNS record, I guees its a supported scenario?

And big thanks to @jcheng for your suggestion 🙂

[Anonymous]

Iam glad that @jcheng suggestion worked for you @Anonymous. Are you sourcing the data from SQL server? If yes then paginated report will support using the gateway as mentioned in the document shared, but in my case iam using the Azure SQL server which is not supported yet.

[Anonymous]

I'm sourcing from the special version of SQL Azure: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-quickstart-guide which is deployed in an Azure Virtual Network and where public endpoints are turned off so its only available inside the virtual network. (which is why we need the data gateway).

According to the documentation for paginated reports data sources which you also refer to, SQL Azure Managed Instance is only supported on public endpoint.

So I'm wondering why the product team says gateway is not supported for paginated reports using SQL Azure Managed Instance when this can be solved by a simple CNAME record? Or are they aware of other issues?

Thanks.

[chrisfin]

The feeling was asking customers to add a CNAME entry was unlikely to be easy for many larger organizations and didn't line up with any guidance Power BI reports have in this scenario.  We're adding proper support for this without requiring the CNAME workaround and are targetting the end of March to have it in production.

[Anonymous]

Hi @chrisfin ,

Thanks for the explanation and really great news that you soon will support this out-of-the-box 🙂

I wish though that you early on had shared the CNAME workaround with your support engineers since we have spent many hours ourselves and with MS support to get this working and we ended up with a rather complex solution using Reporting Services which is a technical debt now.

Nevertheless, looking forward to the support. Will it be announced in the regular release notes here on powerbi.com?

[rpatkar]

Hi Michal,

I don't think we have tested this scenario of using SQL Managed Instance through Gateway. Why are you trying to use GW here rather than connecting to it directly. Asking since GW is primarily a means to enable on-prem data sources to be used in cloud scenarios.

Also, if you have created a Power BI Dataset using this data source (you likely have this as part of uploading the PBIX report you created), you can have your Paginated Report connect to this Power BI Dataset through the XMLA endpoint. https://powerbi.microsoft.com/en-us/blog/power-bi-open-platform-connectivity-with-xmla-endpoints-pub.... In Report Builder, use "Microsoft SQL Server Analysis Services" as the data source. Thanks.

[Anonymous]

hi @rpatkar I need Data GW as our MI endpoint isn't exposed outside a dedicated vnet and in that case we can't trat it as public accessible source.