Connect to Power BI Dataset using XMLA EndPoint Au...

rachcostello · ‎12-15-2021

Hi,

We have a requirement to have a backup solution that allows end users to view critical reports in the event of Azure Active Directoy AAD going down (like what happened last year for a number of hours).

Currently our planned backup solution will consist of using Power BI Report server. Currently all our reports, a combination of Desktop and paginated reports use live connections to a number of datasets published to the Power BI Service. Our backup solution aims to create data sources and datasets on the Power BI Report server from these datasets using XMLA endpoints and scheduling refreshes once a day so in the event that the Power BI Service goes down or AAD going down for a period of time we have this temporary backup of datasources and reports on Power BI Report server to use until the Power BI Service is backup and running. This may never happen but we were asked to have a solution in place if it does.

Our issue currently: We are using a embedded capacity NOT a premium capacity at the moment. We are trying to connect to the datasets on the Power BI Service from Power BI Report server using the XMLA endpoint. Details of how to do this were taken from the "Hybrid Use Case" section in the link below https://www.bluegranite.com/blog/tips-for-a-successful-power-bi-report-server-implementation . We have the XMLA endpoint enabled on the service but we are unable to connect to the dataset just yet. We are getting errors as we are not entirely sure which authentication method to use. Note we did try the belwo connection string with spaces also.

Firstly, do we require a Premium capacity for this to work properly? Secondly, if not what authentication method should we use?

Thanks.

lbendlin · ‎12-18-2021

You need Premium or PPU

Dataset connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft Docs

But - why make it so difficult? Why not connect to the same original data sources from Report Server?

rachcostello · ‎12-21-2021

Thanks Ibendin,

I believe our issue was down to not having conditional access configured in AAD. Hoping to test that today. Secondly, the thought process behind our approach is to utilise the Power BI datasets as Analysis Service models for data re-usability. We can have up to 30 reports using the one dataset on the Power BI Service, with all the measures and calculated columns already created. If we were to create a dataset tfor each report we would have to re-create all of the calculated columns and measures again. Secondly, our data source is OData. That is not a supported data source for pagainated reports. I am open to correction on this.

Thanks