cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Auto building pbix file

I have a report that uses a flat file that is pulled from a database using import.  The pbix file is then provisioned for use in an embedded version.  Is there anyway I can automate the building of the pbix file daily so that it contains the latest data? Or any other way to ensure that the data is updated once a day.

 

I can use direct query as the database is on a secure VPN.

 

Thanks in advance 

5 REPLIES 5
Highlighted
Community Champion
Community Champion

@DrBiker You don't need to build pbix file daily to get latest date. Once published to power bi service you need to setup schedule refresh of that report in service daily from on-premise data source using data gateway.

Highlighted

Does that work on embedded (not service?) 

 

I could not find a way to schedule update for embedded through the gateway, only for service.

 

Would be very happy if someone could let me know how to do this for a provisoned embedded solution.

 

 

Highlighted

@DrBiker

 

As you're using Power BI embeded, I suspose that you're already in an Azure subscription. One approach is using Azure SQL database to automate your practice.

  1. Create an Azure SQL Database and then connect to it in Power BI desktop in DirectQuery mode.
  2. Design your report and import the pbix to your workspace.
  3. Set the azure credential via REST.
    Patch GatewayDatasource
    Data source connectivity
  4. Set up an automation process to load the flat file into the Azure SQL database via an ETL tool like SSIS(if SQL Server, a database is mentioned in your post) or any other ways.

 Then the embeded reports would be always up to date.

Highlighted

@Eric_Zhang

 

So the above solution is to use an Azure SQL database?  Our data is already in one but we find that the connection is rather flakey such that we often get half the visuals on a page loaded while the rest have errors indicating that the server could not be found.  Also with the direct query it can take minutes to laod ten visuals.

 

For the reasons above we have been forced down the import route and was hoping that, if we cannot get the SQl server working consistently, then we could automatically rebuild the pbix file each day with the new data in it and provision it to be used by the embedded version.

 

Kind regards

 

 

Paul

Highlighted

@DrBiker

 

Have you confirmed the reason of the slowness with direct query? Can it be caused by the query perfomance in your Azure SQL database? Ideally there should be no problem workspace collection(where your pbix is imported)

 

By the way, according to one reply from this thread, new client side API would come soon. Let's see what can the API do with a PBIX file.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors