close

Creating A Power BI Serverless Application For Azure Synapse

Posted by Errin O'Connor on Jul, 14, 2022 03:07

Creating A Power BI Serverless Application For Azure Synapse

The Microsoft Azure cloud platform comprises more than 200 products and services. These services are designed specifically to help organizations willing to create innovative solutions that are efficient enough to solve modern business challenges and set the status-quo of the future. It brings seamlessness and flexibility to use tools and frameworks of your choice to build, run, and manage versatile applications across multiple clouds, on-premises, or at the edge.

Azure Synapse is a serverless, big data analytics platform that allows you to utilize either serverless or query data according to your terms provisioned resources—at scale. As a result, extensive data analytics and warehousing subjects come together in a unified experience ready to ingest, prepare, manage, and serve data for immediate Power BI serverless application and machine learning needs. 

The Azure Synapse platform integrates linked services, including Power BI, Azure Machine Learning (ML), and Azure Data Share. In addition, the new shared online site for creating and maintaining numerous Azure Synapse artifacts, Azure Synapse studio, allows users to create interactive Power BI reports and enterprise-grade semantic models.

Many new features have been made available in Synapse, but for the sake of this blog article, we will just be focusing on Synapse SQL. Users of the Azure Synapse Analyses workspace may do SQL-based analytics at scale thanks to Synapse SQL. We can observe from a technical perspective that there are some complexities to leveraging this, as well as the possibility of significantly altering the cost model in Azure for SQL workloads.

Data Exploration with Azure SQL

The ability to query data in a cloud service is a highly valued feature of modern data platforms. Azure Synapse SQL is the one-stop shop for analyzing data using SQL technology. Synapse SQL allows you to query data using the following factors: Provisioned data warehouse with SQL pools and serverless queries over the data lake.

Synapse SQL offers data engineers the ability to run serverless queries without having to provision any infrastructure to address the need for on-demand computing power. In addition, the Azure Synapse workspace offers several tools for data exploration and analysis. These include the ability to perform SQL queries via the on-demand SQL endpoint provided in the Azure Synapse workspace and tools such as SQL Server Management Studio (SSMS) and Azure Data Studio.

Azure Synapse provides the flexibility to either provision and elastically scale pool pools or leverage serverless capabilities for on-demand compute resources for Azure SQL Database. With Azure Synapse, organizations can dramatically simplify the management of their data environments and bring together teams of data professionals, including data engineers, data scientists, BI professionals, and IT administrators, thus increasing collaboration and productivity.

Data Layers

Why Should You Use Azure Synapse With Power BI?

Azure Synapse is a cloud-based solution that provides powerful data discovery capabilities for Power Bi serverless application users. It works with all Power BI versions and enables users to quickly and easily create new dashboards, reports, and visuals based on data from any source. It also offers automated insights generation and real-time analysis of large data sets.

A single trusted source of truth for Power BI

With the launch of Azure Synapse for Power BI, organizations can use a single source of truth for the data in their Power BI applications. Building on the successful legacy of Azure SQL Data Warehouse, organizations can deploy Power BI, and other applications may use Azure Synapse as a solitary, verified source of truth. The data being displayed has been verified for quality, consistency, and correctness by using the officially approved data warehouse objects stored in supplied SQL pools, which gives Power BI developers and users of Power BI applications confidence.

For example, Power BI administrators and other BI stakeholders may insist that only those datasets built exclusively against Azure Synapse will be eligible to be marked as a Power BI-certified dataset or published to a production Premium capacity. 

DirectQuery Performance Challenges & Workarounds In Power BI

The most challenging aspect of using DirectQuery connectivity for Power Bi serverless applications is that most data sources supporting DirectQuery connectivity for Power BI have historically struggled to deliver both the high user concurrency and the low query response times required for enterprise Power BI solutions.

Power BI reports are designed for interactive data exploration user experiences, implying a high volume of queries per user session to update the different visualizations in real-time. However, as the volume of concurrent user engagement grows into the thousands, such as with widely adopted enterprise BI solutions, standard data warehouse systems such as AWS Redshift and Google BigQuery place incoming queries into a queue, thus delaying execution or forcing users’ queries to fail.

Enhanced Data Security for Power BI

Power BI professionals typically secure their solutions by implementing row-level security roles into data models and limiting the persons or groups who can access workspaces, applications, and datasets. However, given that Azure Synapse supports both rows- and column-level security for users and groups among its other layers of security features, including transparent data encryption, enterprise IT organizations would generally prefer to fully leverage their data warehouse for both query processing (that is, DirectQuery) and data security.

Given that authentication is handled through Azure Active Directory (Microsoft Entra ID) and Azure AD authentication is supported and recommended for Azure Synapse, organizations can enforce data security at the data tier layer in Azure Synapse for their Power BI solutions.

A new service that brings teams together

If you’re a business intelligence professional, your team is already working on data transformation processes. But what happens when they all use different tools?

Data teams and technologies are brought together by Azure Synapse, enhancing efficiency and transparency throughout businesses. Because all teams using Azure Synapse have access to the same user interface in the Azure Synapse studio, all users may see and analyze the same data independent of their primary tools or abilities.

Prepare Power BI Solutions For Data Growth with Azure Synapse

In many Power BI solutions, data transformation and integration processes are embedded into the solution. As a result, these transformations can introduce significant risks to the scalability and sustainability of the solution.

The robust data processing tools of Azure Synapse and the expertise of Azure Synapse data engineers can address the data preparation needs of Power Bi serverless application solutions. Azure Synapse includes the enterprise-grade data transformation and orchestration capabilities of Azure Data Factory. Data engineering teams can construct robust data pipelines, Synapse Spark jobs, or SQL stored procedures to address various data preparation needs, thereby eliminating the need for Power BI developers to handle these requirements within their solutions.

Integrating Power BI with Azure Synapse for Securely Hosting Power BI Models

In this section, we’ll walk through the essentials of obtaining access to an Azure Synapse resource, connecting Azure Synapse to Power BI workspaces, and developing content in either the Azure Synapse studio or utilizing Azure Synapse as a data source.

Azure Synapse Studio

Azure Synapse Studio is the integrated web-based development and management hub for all Azure Synapse resources. All development and management activities supported by Azure Synapse are carried out in the Azure Synapse studio via access to an Azure Synapse workspace. Additionally, joint development and management tools, such as SQL Server Data Tools (SSDT) for Visual Studio, SSMS, and APIs, can be used to interface with Azure Synapse resources.

Link Power BI to the Azure management portal

Once access has been granted to the Azure Synapse workspace, it is necessary to establish connections to relevant Power BI app workspaces. Connections to these workspaces are linked services in Azure Synapse and enable users to create and modify Power BI workspace content directly from within the Azure Synapse studio.

Conclusion

Since we have covered the technical capabilities of Azure Synapse and laid out the architecture, we will now dive into creating our serverless Power BI application. After all, there is no restriction on what you can build with Power BI, yet it never hurts to speak with someone who has built these applications. EPC GROUP will indeed have the expertise you need to get off the ground successfully with Power BI.

Errin O'Connor
About the Author

Errin O'Connor

With over 25 years of experience in Information Technology and Management Consulting, Errin O’Connor has led hundreds of large-scale enterprise implementations from Business Intelligence, Power BI, Office 365, SharePoint, Exchange, IT Security, Azure and Hybrid Cloud efforts for over 165 Fortune 500 companies.

Let's Get to Work Together!

Talk to our Microsoft Gold Certified Consultants

Contact EPC Group

Call for help:

(888) 381-9725

Email Us:

[email protected]

Head Office:

4900 Woodway Drive - Suite 830 Houston, Texas 77056