Automating Your Power BI Refreshes: Synapse Edition

In our previous Power BI automation blog, we utilized the Power Platform in combination with Azure SQL DB to store refresh history of our datasets leveraging the Power BI REST API when on a Power BI shared capacity. A solution to manage Power BI dataset refreshes is particularly useful when data source refresh times are variable and end users need to know when dataset refreshes are successful. However, Power Automate and Azure SQL create two failure points. This solution also requires additional licensing cost in Power Automate to leverage premium data connectors to Azure SQL. So, how can we consolidate the number of tools we use and at the same time leverage the security features and access controls that Azure provides?

Untitled design(65)

Azure Synapse Analytics Has Entered the Chat

Azure Synapse Analytics provides a unified framework for analytics, data integration, storage, and security all in one user interface. In this section of the blog, we will document how we can leverage pipelines in Synapse, Logic Apps for emailing our user base that refreshes have started and completed, and Azure Key Vault to securely store all our Power BI App Client ID and secrets.

Key Tools

  • Azure Active Directory
    • Power BI App Registration with a Client ID/Secret
    • Security Group to Add App Registration Created
  • Azure Synapse Workspace
    • To build our pipelines and create integration datasets to populate results to our data lake storage account
  • Azure Logic Apps
    • To send emails to our business users that refreshes have started and completed
  • Azure Key Vault
    • Securely store client ID and secrets
    • Ensure your Synapse Workspace has a linked service and connection to Key Vault
  • O365 Service Account
    • Dedicated Service Account to perform automation of emails in Logic Apps
  • Power BI Tenant Settings
    • Enable Service Principals to use Power BI REST API

Figure 1: Tenant Settings + Security Group Option

Note: App Registration must be an Admin to all the Power BI Workspaces that we will be getting refresh data from.

  • Other
    • Knowledge of API testing and sending GET/POST requests

Logic Apps Workflow UI

We will create a base template for Logic Apps. This template will be called by Synapse via the HTTP Web request and parameters will be sent via Synapse to fill the user emails, subject line, and message.

Picture1_censored(1)


Figure 2: Logic Apps Template

 

Azure Synapse: Integration Datasets + Data Lake Architecture

Before we review the pipelines set up, we need to configure the following integration datasets and linked services.

  • Linked Services Needed
    • Key Vault
    • REST API
  • Integration Datasets
    • ds_PowerBIResponse: To leverage the Power BI REST API
    • ds_Parameters: To access our csv file of our parameters that we will be looping over to refresh
    • ds_RefreshHistory: To provide a sink point to take our results and store in our data lake

Figure 3: Integration Datasets

Our Data Lake folder set up will have the following items.

  • Our Parameter Table with our Power BI Datasets and Workspace Information (GUIDs)
  • A Refresh History Table where we will drop our storage information
    • Will have a nested folder structure based on time of data refresh

Figure 4: Synapse Storage Setup

Azure Synapse: Master Pipeline Overview

This pipeline below is the master pipeline that will kick start our Power BI Refreshes and checking refresh status. In order, it does the following:

  1. Grabs our secret information from Azure Key Vault
  2. Gets an AAD Token by calling the Power BI REST API
  3. Gets our dataset GUID parameters from our flat file that houses the datasets we will need to refresh
    1. From here, we will start the refresh process for all the datasets and trigger the Logic Apps email to let us know the refreshes have started
    2. Datasets will be refreshed in parallel via the for loop. The check status pipeline will be called within this loop to ensure proper timings before sending refresh completion

Figure 5: Main Master Pipeline

Figure 6: Loop Step to Call Power BI Refresh and Call Pipeline to Check Status

Figure 7: Email Trigger that Refreshes Have Started

Azure Synapse: Check Status Pipeline Overview

Below is the check status pipeline. At a high level, this pipeline is called multiple times per dataset that is needed to be refresh and will run until all datasets either complete or fail. Some things to note are the following:

  • The Reset Token step provides a failsafe when a refresh takes more than a certain amount of time to complete. The Power BI REST API token by default, is only available for 1 hour and this step allows us to get a new token once we are close to that threshold
  • The Until Loop will stop only when the dataset refresh status something that is not in progress. Once it is not in progress, we will log the results as a parquet file in our data lake storage account

 

Figure 8: Check Status Pipeline Main Level

Figure 9: Check Status Pipeline Until Loop

 

Figure 10: Refresh History Logging in ALDS Gen 2

Once all the dataset refreshes are done, we will get an email that tells us the refresh is completed from our master pipeline.

Figure 11: Complete Email from Logic Apps

Conclusion and Extensions

Using Azure Synapse Analytics and Logic Apps, we were able to consolidate and reduce the number of services required with Azure Synapse’s unified framework. Data storage, and pipelining was done entirely in the Synapse environment, and we leveraged Logic Apps to orchestrate sending emails to our end users. Azure storage provides an inexpensive alternative to leveraging a standalone database by storing all our refresh history in our default Azure Data Lake Gen 2 Storage provided by Synapse.

Some extensions are also possible and are outlined below

  • Pulling history information from other artifacts like dataflows
  • Pulling and logging other information from the PowerBI REST API
  • Adding this pipeline to a broader ETL pipeline in Synapse. Once ETL is done this pipeline can be used to refresh datasets downstream
  • Creating auditing reports to monitor PowerBI refresh performance

Connect with us!

Please reach out to us today to find out more about how BlueGranite’s talented team can help you bring clarity and efficiency to your Modern Analytics endeavors today! 

BlueGranite offers a variety of resources and free training events to help you learn how you can leverage Modern Data Analytics.

Please visit our website and YouTube Channel to learn more, or contact us directly to see how we can help you explore your about modern data analytics options and accelerate your business value.

About the Authors

NithinSankar_CircleNithin Sankar
Nithin is a Solution Consultant at 3Cloud with experience working with stakeholders in the healthcare, hospitality, and tourism industries to design and develop robust data-driven solutions across different service lines and business units. Nithin holds a B.S. in Industrial & Systems Engineering from the University of Florida and is currently pursuing a M.S. in Analytics from the Georgia Institute of Technology. Check him out on LinkedIn!

CallenSmerkerCallen Smerker
Callen is a Solution Architect at 3Cloud who, since 2014, has had a variety of experience in database and business intelligence solutions. This includes leading large, complex projects as both Data Architect and Project Management Professional. He enjoys designing solutions within the Microsoft BI stack (SQL Server, SSIS, SSAS, and PowerBI) in combination with Azure DevOps CI/CD pipelines to deliver enterprise grade analytics solutions. Callen has experience working in a variety of industries including healthcare, life science, pharmaceutical, and logistics. Check him out on LinkedIn!

Uncategorized

Automating Your Power BI Refreshes: Synapse Edition

In our previous Power BI automation blog, we utilized the Power Platform in combination with Azure SQL to store refresh history of our datasets leveraging the Power BI REST API when on a Power BI shared capacity. A solution to manage Power BI dataset refreshes is particularly useful when data source refresh times are variable and end users need to know when dataset refreshes are successful. However, Power Automate and Azure SQL create two failure points. This solution also requires additional licensing cost in Power Automate to leverage premium data connectors to Azure SQL. So, how can we consolidate the number of tools we use and at the same time leverage the security features and access controls that Azure provides?
Uncategorized

Retail Analytics: Incorporating a Date Dimension with Comparative Dates

A company performing financial performance analytics year over year on consumer data may run into the challenge of gaining trust regarding the metrics from internal stakeholders. A common pain-point can be contributed to holidays (and their associated sale campaigns) not lining up each year. A reoccurring criticism we have seen at BlueGranite is summed up with the following real-life scenario: “I don’t trust these numbers because Easter was on a different week last year and our stores sell a lot of Easter chocolates.” In this post, we\'ll walk through how to build a comparative system to aid in this instance.
Uncategorized

Automating Your Power BI Refresh History

With Power BI Premium and the Capacity Metrics App, you can track metadata such as refresh statistics of your reports and data sources, as well as additional detail regarding CPU usage, memory usage, and average wait times for a dataset to refresh. Refresh data can be invaluable for users monitoring the health of their workspace and troubleshooting issues as well as IT leaders ensuring proper SLA standards from ETL to the Power BI Report. It also provides transparency and trust to business users to know that the data they are viewing is up to date.
Uncategorized

Consulting From The Client's Perspective

When clients and consultants tackle MBI and MDP projects together, there can often be an assumed \"one right way\" to get the job done. This shared myopic focus for the presumed solution comes both from the consultant and the client side. Oftentimes, consultants may feel pressure to conform to industry-based solution templates, while clients may have preconceived notions about the problem they’ve articulated as well as about the type of solution it requires. But these concerns can truly be benefited by asking for outside counsel and being subjected to external scrutiny.
Uncategorized

How Power BI Metrics and Scorecards Can Transform Productivity Within Business Objectives

Metric and goal setting is paramount when formulating business operations. That\'s why Microsoft Metrics (previously known as ‘Goals’) make it possible to keep every member of a team striving toward a singular, unified key objective and ensures a higher probability of positive outcomes. In fact, in this study regarding goal setting and optimism, it is shown that the goal setting process assists individual workers to improve their performance and enhances optimism for business success. So, how can productivity metrics and business objectives be met while also integrating the latest and greatest data analysis technology available? In this blog, we will be performing a deep dive into the world of productivity within Power BI’s Metrics and Scorecards.
Uncategorized

A Step-by-Step Guide to Creating Canvas Apps

Creating a custom app is a breeze thanks to Microsoft’s PowerApps. Users of any skill level can design sleek applications to service numerous business needs as designing an app now requires little to no coding experience. The simple drag and drop interface in Canvas apps boosts efficiency by speeding up development time, while empowering user allow users ultimate control over the layout and navigation experience of the app. Canvas apps can connect to data in hundreds of cloud-based or on-premises sources. Further, your potential audience can access your apps via mobile devices, web browsers, or embedded in external sources or model-driven apps. The flexible nature of canvas apps expands app designers’ horizons and unlocks new potentials for streamlining business processes.
Uncategorized

Create End-To-End Self-Service Analytics With Power BI Datamarts

Since its inception, Power BI has been a leading tool in self-service analytics. This week at Build, Microsoft released an exciting new feature available to preview – Power BI Datamarts! Power BI Datamarts enable self-service users to build and distribute an analytical solution from end-to-end. Data ingestion and preparation, data exploration and analysis, data modeling, and report design can all be done in a low- or no-code experience in Power BI Service!
Uncategorized

Create a Custom Theme with an Image Background

While there are settings that you can customize through the menu system in Power BI for a custom theme, one of the settings you cannot edit is to add a default background image to your pages. Here are 5 easy steps to create a theme that incorporates a background image for each page.
Uncategorized

Using Partitioning to Optimize Performance During Data Ingestion

Data ingestion is the process of transferring data from its source system to a data store, often a data lake. Efficient data ingestion can be a daunting task depending on the complexity of your data sets. Yet, optimizing performance is a crucial part of the data ingestion process. 
Uncategorized

Modern Supply Chain Spend Analytics Showcase

In modern supply chains, the one constant is change. The increasing rates of change have led to new methodologies built around planning for and optimizing agility. In addition, the uncertainty of the past 2 years has highlighted the need for lightning-fast data and analytics for making decisions across organizational systems. To manage this extreme uncertainty while optimizing both service levels and inventory, companies are looking for their supply chains to be more flexible, responsive, and resilient. Supplier diversity, demand forecasting, and end-to-end supply chain visibility have become vital for the survival of modern manufacturers.
Uncategorized

The Building Blocks of Power Apps

Gone are the days when creating a sophisticated app required extensive coding knowledge! Microsoft’s Power Apps empower citizen-developers of any skill level to build custom apps.
Uncategorized

Call Center Solutions: Agent Status Timelines

We recently worked to visualize call center agents’ statuses on a Gantt chart in Power BI for a client, but this turned out to be trickier than anticipated! When internet searches failed us, my colleague came up with the perfect solution, and all it takes is two lines of M code in Power Query!