Export updated data to be used in another bi tool.

[ioniPeis]

Hello,

a client has asked me for access to transformed data (raw data to which different business rules have been applied). This customer wants to use the data in his own BI tool.

It is important that the data is kept updated in each automatic refresh that is programmed in PBI service.

I was thinking that it would be possible to export the data from PBI to an Azure SQL DB with an R script, and then the customer connects their own BI tool with Azure. But I think it could have limitations in terms of updating the data.

Any better idea?
Is there any other functionality that allows me to do this?

[lbendlin]

"It is important that the data is kept updated in each automatic refresh that is programmed in PBI service."

This means you need to write polling software that checks for dataset refresh completion (successful completion actually)  and then you can issue an API call against the dataset via DAX.

Or - you can do all that much more convenient from the comfort of your SSMS console - including refreshing partitions on your own schedule and then pulling the refreshed data into the Azure db.

The real question here is WHY?  Can't you implement the business logic in MySQL and give your client access to the MySQL database?

[ioniPeis · Accepted solution]

Hi @lbendlin 

Thanks for your answer.

Your question makes a lot of sense, it happens that the DB is provided by a partner so we only have read access. Regarding your answer, I see it a bit complicated for me, I think that at this point it would be more convenient to create an intermediate DB where we can apply the transformations and then give the client access.

[v-shex-msft]

HI @ioniPeis,

Did you mind using the live mode to connect to these datasets?
If that is the case, you can create a pbix report and publish it to power bi service, setting the refresh scheduler to bring data from your database.

After these steps, you can ask your customer to create a report that connects to the power bi service dataset as the data source.

For the new report, it will live connected to the power bi service dataset and get the latest data(based on the raw report refresh scheduler) every time you interact what it's report contents.

Regards,

Xiaoxin Sheng