loader image

Share:

Share on facebook
Share on twitter
Share on linkedin

Automate Update For Power BI

At one of our clients -that was previously using a solution that includes a limited set of the Acterys functionality- we saw that they had licensed a separate update solution that refreshes the underlying Power Pivot model in an Excel workbook that is used in a Power BI report. This is a requirement that can easily be implemented in a few minutes using a Windows PowerShell script in conjunction with the Windows Task Scheduler. In the following steps I describe the process:

Create The PowerShell Script

  1. Open Windows PowerShell
  2. Copy and paste the following code and replace “Yourpath\yourExcelworkbookname.xlsx” with the details of your workbook:
#Set the file path (can be a network location)
$filePath = "Yourpath\yourExcelworkbookname.xlsx"
#Create the Excel Object
$excelObj = New-Object -Com Excel.Application
#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10
#Make Excel visible. Set to $false if you want this done in the background
$excelObj.Visible = $true
$excelObj.DisplayAlerts = $false
#Open the workbook
$workBook = $excelObj.Workbooks.Open($filePath)
#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10
#Refresh all data in this workbook
$workBook.RefreshAll()
Start-Sleep -s 10
#Save any changes done by the refresh
$workBook.Save()
$workBook.Close()
#Uncomment this line if you want Excel to close on its own
$excelObj.Quit()
$excelObj = $null
write-host "Finished updating the spreadsheet" -foregroundcolor "green"
Start-Sleep -s 5

This script will open the respective workbook run the refresh for the data models, save and close the workbook. This means that if you have a Power BI report that is using this workbook, you can just refresh the Power BI report and will get the updated data.

Setup The Update Interval

This process can be scheduled so that it runs completely automatic in a specific interval.

  1. Open Windows Task Scheduler:

Setup The Update Interval

2. Create a new task:

Setup The Update Interval

To configure the interval go to the “Triggers” tab and:

3. Click on new:

Setup The Update Interval

Her you can now:

4. Setup the trigger and specify the update interval. For example, every day at 12:54:

Setup The Update Interval

The final step is to specify what action you want to run. To do this:

5. Go to the “Actions” tab and choose: “Start a program” and point to the location of your script:

Setup The Update Interval

That’s it. This can obviously be configured and refined with all the options that PowerShell gives you respectively be integrated into a standalone app which is available in Acterys for an even easier usability and with additional features. Please don’t hesitate to contact us for any further questions.

Recent Posts

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.

Power BI offers unparalleled features for analysis and visualization: in this post we explain the quickest way to connect Oracle Netsuite in Power BI Deskop and how you can add comprehensive planning and budgeting.After your account has been setup, click on the “Integration” menu and go to the “Oracle Netsuite” section:

The default visuals in Power BI are not perfectly suited for financial reporting. There are ways to realize more advanced financial reporting requirements using DAX as Managility describes in Income Statement using DAX These, though typically require intermediate knowledge of DAX that many financial professional don’t necessarily have.

The Acterys Modeller is cloud based service (with on-premise installation option) that allows enables business users to automatically create optimal analysis models (star schema data warehouse) from a variety to ERP. accounting and SaaS systems. The created models are write and planning enabled and can be edited and extended as needed by business users e.g. for adding new products/attributes, account mappings, new scenarios etc.

Tags