Creating a Power BI Serverless Application for Azure Synapse

Creating a Power BI Serverless Application for Azure Synapse

Azure Synapse Analytics combined with Power BI's DirectQuery mode gives enterprises on-demand reporting against petabytes of data lake content — without provisioning dedicated compute infrastructure. Serverless SQL pools replace dedicated SQL pool costs ($1.20/DWU/hour) with pay-per-query pricing ($5/TB processed), reducing costs by 60–80% for intermittent reporting workloads. This guide walks through all 6 implementation steps.

Key facts

• Serverless SQL pools query Parquet, CSV, and JSON files directly in Azure Data Lake — no ETL into tables required.
• Pricing: $5 per terabyte of data processed (serverless) vs $1.20/DWU/hour for dedicated pools.
• Cost reduction for intermittent reporting workloads: 60–80% vs dedicated SQL pools.
• Power BI connects via DirectQuery using the Azure Synapse Analytics connector — no scheduled refresh required.
• Row-level security is implemented via external table views and Power BI workspace security.
• EPC Group has 1,500+ Power BI deployments and deep Azure Synapse Architecture experience.

Serverless Power BI Architecture Benefits

• No infrastructure management — Azure handles compute provisioning, scaling, and patching. Your team writes SQL queries and builds reports.
• Cost optimization — Pay only for data scanned. Ideal for reports run weekly or monthly rather than continuously.
• Data Lake integration — Query data where it lands in Azure Data Lake Storage Gen2. No copy step into a separate warehouse.
• Medallion architecture support — Query Bronze (raw), Silver (cleansed), or Gold (curated) layers directly from Power BI.
• DirectQuery mode — Reports query Synapse on demand. No Power BI scheduled refresh needed.

Step-by-Step Implementation Guide

Step 1: Configure Azure Data Lake Storage Gen2

• Create a storage account with hierarchical namespace enabled.
• Organize your data lake using the Bronze/Silver/Gold medallion architecture.
• Bronze = raw data, Silver = cleansed data, Gold = curated analytics-ready data.
• Set container-level access control using Azure RBAC and ACLs.

Step 2: Create an Azure Synapse Workspace

• Provision a Synapse workspace linked to your ADLS Gen2 account.
• The workspace automatically includes a built-in serverless SQL pool endpoint.
• No additional configuration is required to use the serverless pool — it is ready immediately.

Step 3: Define External Data Sources and File Formats

• Create database-scoped credentials for secure storage access.
• Create external data sources pointing to your ADLS containers.
• Create external file formats for Parquet, Delta, or CSV files as appropriate.
• Use Parquet format wherever possible — it provides column pruning and predicate pushdown for better query performance.

Step 4: Build SQL Views Over Lake Data

• Write OPENROWSET queries to read files directly from ADLS.
• Create external tables for frequently queried datasets.
• Build views that expose clean column names, data types, and business logic.
• Use CETAS (CREATE EXTERNAL TABLE AS SELECT) to materialize complex query results for better performance.

Step 5: Connect Power BI via DirectQuery

• In Power BI Desktop: Get Data → Azure Synapse Analytics (SQL DW).
• Enter your serverless SQL endpoint URL (found in Synapse workspace overview).
• Select DirectQuery mode — this queries Synapse on every report interaction.
• Build your data model using the SQL views you created in Step 4.
• Apply row-level security in Power BI using the same user roles defined in Synapse.

Step 6: Publish and Configure Security

• Publish reports to Power BI Service. No scheduled refresh is needed for DirectQuery.
• Configure workspace access — readers, contributors, and admins.
• Set up row-level security roles in Power BI Desktop before publishing.
• Apply sensitivity labels to the Power BI dataset and reports.
• Configure Power BI gateway only if Synapse is behind a private endpoint.

Data Pipeline Architecture with Synapse Pipelines

For automated data ingestion into your data lake before Power BI queries it, use Azure Synapse Pipelines (built-in, no Azure Data Factory license required).

• Copy activity — Moves data from source systems (SQL Server, Salesforce, SAP) into ADLS Bronze layer.
• Data flow activity — Transforms data from Bronze to Silver using code-free visual transformations.
• Notebook activity — Runs Apache Spark notebooks for complex transformations producing Gold-layer data.
• Trigger — Schedule pipelines on a recurring basis (hourly, daily) or trigger on file arrival.

Performance Optimization Strategies

• Use Parquet format — Parquet enables column pruning (Synapse reads only the columns you query) and predicate pushdown (filters applied at the storage layer).
• Partition data by date — Partition large datasets by year/month/day folders. Filter on the partition column to reduce data scanned per query.
• Create statistics — Run CREATE STATISTICS on frequently filtered columns in external tables to improve the query optimizer's execution plan.
• Use RESULT_SET_CACHING — Cache frequent, deterministic query results for up to 48 hours to eliminate redundant scans.
• Limit DirectQuery columns — Import only the columns Power BI needs. Fewer columns = less data scanned = lower cost and faster queries.

Security and Governance

• Authentication — Use Azure AD service principals for automated Power BI refresh. Use organizational accounts for interactive reports.
• Authorization — Grant minimum required permissions: CONTROL on the database for admins; SELECT on specific views for report users.
• Column-level security — Restrict access to sensitive columns (SSN, salary) via SQL views that exclude those columns for standard users.
• Row-level security — Implement RLS in SQL views using SESSION_CONTEXT or DAX row-level security in Power BI.
• Sensitivity labels — Apply Microsoft Purview sensitivity labels to Power BI reports built on Synapse data.

Frequently Asked Questions

What is a Power BI serverless architecture with Azure Synapse?

It is a reporting architecture where Power BI queries Azure Synapse serverless SQL pools via DirectQuery. The SQL pool reads data directly from Azure Data Lake on demand — no dedicated compute provisioned, no ETL into a separate warehouse. You pay per terabyte of data scanned rather than per compute hour.

How much does Azure Synapse serverless cost compared to dedicated pools?

Serverless pools charge $5 per terabyte of data processed. Dedicated SQL pools charge $1.20 per DWU per hour (a DW1000c costs $1,200/month at full utilization). For intermittent reporting workloads, serverless is 60–80% cheaper. For continuously queried, real-time dashboards, dedicated pools may be more cost-effective.

Can Power BI use DirectQuery with Azure Synapse serverless?

Yes. Use the Azure Synapse Analytics (SQL DW) connector in Power BI Desktop. Select DirectQuery mode. Connect to the serverless SQL endpoint. Build your data model using SQL views created over your data lake files. No scheduled refresh is needed — Synapse queries on every report interaction.

What file formats work best with Synapse serverless SQL pools?

Parquet is the best format. It supports column pruning (reads only queried columns) and predicate pushdown (applies filters at storage). Delta format is also supported and adds ACID transactions. CSV and JSON are supported but are significantly slower and more expensive to query at scale.

How do I implement row-level security in a serverless Synapse Power BI setup?

Use one of two approaches. In SQL: create views that filter rows using SESSION_CONTEXT to pass the Power BI user identity. In Power BI: define row-level security roles in Power BI Desktop before publishing. Apply the role to Power BI workspace members as needed.

Ready to Build a Serverless Power BI Solution?

EPC Group has 1,500+ Power BI deployments and deep Azure Synapse architecture experience across healthcare, financial services, and manufacturing. Call (888) 381-9725 or request a serverless architecture consultation.