Advanced Power BI: Performance Tuning, Composite Models, and Embedding

What You Will Learn

• Optimize report and dataset performance (VertiPaq engine internals)
• Build composite models and use Direct Lake mode
• Implement Deployment Pipelines (Dev → Test → Prod)
• Use Object-Level Security (OLS) beyond row-level
• Connect Power BI to Analysis Services (SSAS/Azure AS)
• Embed Power BI in applications using Power BI Embedded
• Automate Power BI with the REST API and PowerShell
• Use AI features: Copilot, Smart Narratives, Q&A tuning
• Apply enterprise governance: endorsement, lineage, impact analysis

---

7.1 Performance Optimization

Understanding the VertiPaq Engine

Power BI uses the VertiPaq in-memory columnar storage engine. Understanding it helps you build fast models.

Key principles:

• Data is stored column-by-column, not row-by-row
• Each column is compressed individually using dictionary encoding
• Cardinality (number of unique values) determines compression and scan speed
• The engine uses multi-threading across CPU cores

What Slows Down a Report

Measuring Performance

Performance Analyzer (built into Desktop):

1. View → Performance Analyzer → Start recording
2. Click Refresh visuals
3. Expand each visual to see:
   • DAX query time — time to run the DAX measure
   • Visual display time — time to render
   • Other — overhead

Export the trace and paste the slowest DAX query into DAX Studio for deep analysis.

VertiPaq Analyzer (free external tool):

• Open your .pbix in DAX Studio → Advanced → View Metrics
• See table/column sizes, cardinality, and compression ratios
• Identify which columns are consuming the most memory

DAX Optimization Techniques

-- AVOID: Re-computing the same sub-expression twice
YoY % Slow = 
DIVIDE(
    [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(dim_Date[Date])),
    CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(dim_Date[Date]))
)

-- BETTER: Use VAR to evaluate once
YoY % Fast = 
VAR Current = [Total Revenue]
VAR Prior   = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(dim_Date[Date]))
RETURN DIVIDE(Current - Prior, Prior)

-- AVOID: FILTER over the whole table
Slow Measure = 
CALCULATE([Total Revenue], FILTER(fact_Sales, fact_Sales[Status] = "Complete"))

-- BETTER: Use table filter argument (more efficient)
Fast Measure = 
CALCULATE([Total Revenue], fact_Sales[Status] = "Complete")

---

7.2 Composite Models and DirectQuery

Import vs. DirectQuery Recap

Composite Models

Composite models mix Import and DirectQuery tables in the same model:

dim_Product  (Import) ──── fact_Sales (DirectQuery)
dim_Date     (Import)      (queries SQL Server live)

This gives you:

• Fast filtering from dimension tables (Import)
• Live, always-fresh fact data (DirectQuery)

Setting up:

1. Load your dimension tables normally (Import)
2. For the fact table: Get Data → SQL Server → DirectQuery
3. Power BI auto-configures the dual storage mode for dimension tables

Direct Lake Mode (Microsoft Fabric)

Direct Lake is a new mode exclusive to Microsoft Fabric that reads data directly from OneLake (Parquet files) without Import or DirectQuery overhead. It has near-Import performance with always-fresh data.

Requirements:

• Data must be in OneLake as a Delta Lake table
• Semantic model must be in a Fabric workspace
• Assign the model to Direct Lake mode in Fabric portal

---

7.3 Deployment Pipelines

Deployment pipelines give you a Dev → Test → Production workflow for Power BI content.

Requires: Premium capacity or PPU

Setting Up a Pipeline

1. Workspaces pane → Deployment pipelines → Create a pipeline
2. Assign workspaces to each stage (Dev, Test, Prod)
3. Configure deployment rules (override data source connections per stage)

Deploying Content

1. Develop your report in the Dev workspace
2. Click Deploy to Test — Power BI copies the content
3. Test validates the report with test data
4. Click Deploy to Production — the production workspace is updated

Deployment Rules

Override specific settings per stage without modifying the .pbix:

• Data source rules: point Dev at dev DB, Prod at prod DB
• Parameter rules: change Power Query parameters per stage
• Power BI dataset rules: swap the source dataset

---

7.4 Object-Level Security (OLS)

OLS hides specific tables or columns from certain roles — unlike RLS which hides rows.

Use cases:

• Hide a "Salary" column from non-HR roles
• Hide an entire "Raw Transactions" table from business users

Setting up OLS (requires Tabular Editor — free tool):

1. Download Tabular Editor 2 from GitHub
2. In Power BI Desktop: External Tools → Tabular Editor
3. Navigate to a table/column → Object-Level Security → set permission per role

// In Tabular Editor's permission settings
{
  "role": "Sales",
  "table": "dim_HRData",
  "permission": "None"   // hides the entire table
}

---

7.5 XMLA Endpoint — Read/Write Access

The XMLA endpoint exposes your Power BI dataset as an Analysis Services endpoint, enabling:

• Third-party tools (Excel, Tableau, etc.) to connect to your dataset
• Tabular Editor for model development and ALM (Application Lifecycle Management)
• SQL Server Profiler for query tracing
• Automated deployment via SSAS deployment wizards

Access the endpoint: Workspace → Settings → Premium → copy the XMLA endpoint URL

Format: powerbi://api.powerbi.com/v1.0/myorg/MyWorkspace

---

7.6 Power BI Embedded

Embed Power BI reports into your own web applications, portals, or SaaS products.

Two Embedding Scenarios

Basic Embedding Flow (for customers)

Step 1 — Register an Azure AD App

1. Azure Portal → Azure Active Directory → App registrations → New registration
2. Note the Application ID and Tenant ID
3. Create a client secret → note the Secret value

Step 2 — Get the Embed Token (server-side)

// Node.js example using the Power BI REST API
const PowerBIClient = require('powerbi-client');

async function getEmbedToken(reportId, workspaceId) {
    const tokenRequest = {
        reports: [{ id: reportId }],
        datasets: [{ id: datasetId }],
        targetWorkspaces: [{ id: workspaceId }]
    };
    const response = await client.embeds.generateToken(tokenRequest);
    return response.token;
}

Step 3 — Embed in the Browser (client-side)

<script src="https://cdn.jsdelivr.net/npm/powerbi-client/dist/powerbi.min.js"></script>

<div id="reportContainer" style="height:600px;"></div>

<script>
    var models = window['powerbi-client'].models;

    var embedConfig = {
        type: 'report',
        id: '<REPORT_ID>',
        embedUrl: '<EMBED_URL>',
        accessToken: '<EMBED_TOKEN>',
        tokenType: models.TokenType.Embed,
        settings: {
            filterPaneEnabled: false,
            navContentPaneEnabled: true
        }
    };

    var reportContainer = document.getElementById('reportContainer');
    var report = powerbi.embed(reportContainer, embedConfig);

    // Apply a filter programmatically
    report.on('loaded', function() {
        report.setFilters([{
            $schema: "http://powerbi.com/product/schema#basic",
            target: { table: "dim_Customer", column: "CustomerID" },
            operator: "In",
            values: [customerId]
        }]);
    });
</script>

---

7.7 Power BI REST API

The REST API lets you automate Power BI administration and management.

Base URL: https://api.powerbi.com/v1.0/myorg/

Authentication: Azure AD OAuth 2.0 Bearer token

Common Operations

import requests

headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}

# List all workspaces
r = requests.get("https://api.powerbi.com/v1.0/myorg/groups", headers=headers)
workspaces = r.json()["value"]

# Trigger a dataset refresh
dataset_id = "your-dataset-id"
workspace_id = "your-workspace-id"
r = requests.post(
    f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/refreshes",
    headers=headers
)

# Export a report as PDF
r = requests.post(
    f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/reports/{report_id}/ExportTo",
    headers=headers,
    json={"format": "PDF"}
)

# List refresh history
r = requests.get(
    f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/refreshes",
    headers=headers
)

PowerShell Automation

# Install module
Install-Module -Name MicrosoftPowerBIMgmt

# Connect
Connect-PowerBIServiceAccount

# Get all workspaces
Get-PowerBIWorkspace -Scope Organization

# Trigger refresh
Invoke-PowerBIRestMethod -Url "groups/{workspaceId}/datasets/{datasetId}/refreshes" -Method Post

# Export usage metrics
Get-PowerBIActivityEvent -StartDateTime "2024-01-01" -EndDateTime "2024-01-31" | 
    ConvertTo-Json | Out-File "activity_log.json"

---

7.8 AI and Copilot Features

Copilot for Power BI

Copilot (available in Fabric / Premium) lets you:

• Generate reports from a natural language description
• Create measures by describing the calculation
• Auto-generate insights summaries as text visuals
• Answer questions about your data in natural language

Enabling Copilot: Workspace must be in a Fabric capacity (F64 or higher) or PPU.

Smart Narratives

Automatically generates a text summary of your visual: Insert → Smart narrative
The text updates dynamically as users filter the report.

Customize the narrative with dynamic references:

Revenue this year was **[Total Revenue]**, which is **[YoY Growth %]** vs. last year.
The top performing region was **[Top Region]** with **[Top Region Revenue]** in sales.

Q&A Visual Tuning

Improve how Power BI interprets natural language questions:

1. Modeling → Q&A setup
2. Review suggested terms, add synonyms for column names
3. Example: Teach Q&A that "revenue" = Total Revenue, "clients" = dim_Customer

Anomaly Detection

Automatically find unexpected spikes and dips in time-series charts:

1. Select a line chart
2. Visualizations → Analytics tab → Add anomaly detection
3. Power BI highlights anomalies with a bubble and generates an explanation

---

7.9 Enterprise Governance

Endorsement

Mark datasets and reports as reliable sources:

• Promoted — anyone can promote their own content
• Certified — requires a designated certifier; signals organizational trust

How to certify: Dataset/report → Settings → Endorsement and discovery → Certified

Lineage View

See how data flows from source to report: Workspace → Lineage (network diagram icon)

Shows: Data source → Dataflow → Dataset → Report → Dashboard

Impact Analysis

Before making a breaking change to a dataset, check what depends on it: Dataset → More options ("...") → View lineage or Analyze in Excel

Sensitivity Labels (Microsoft Purview)

Apply data classification labels (Confidential, Internal, Public) to datasets and reports. Labels travel with exported files (Excel, PDF).

Setup: Requires Microsoft Purview Information Protection (M365 E3/E5).

---

7.10 Paginated Reports

Paginated reports are designed for pixel-perfect, print-ready documents — invoices, statements, operational reports with many rows.

Created with: Power BI Report Builder (free download)

Key features:

• Tables and matrices that span multiple printed pages
• Parameterized reports (user inputs filter the output)
• Export to PDF, Excel, Word, CSV, XML
• Subscriptions for automated email delivery

When to Use Paginated vs. Interactive Reports

---

7.11 Working with Analysis Services

If your organization uses SQL Server Analysis Services (SSAS) or Azure Analysis Services (Azure AS), Power BI can connect in Live Connection mode:

1. Get Data → Analysis Services
2. Enter the server URL
3. Choose Connect live (recommended) or Import

In Live Connection mode, Power BI Desktop is essentially a reporting layer — all DAX and data model logic lives in the Analysis Services model. You cannot add measures or relationships in Desktop.

Composite models from AS: New feature — you can now add Import tables on top of a Live Connection to Analysis Services, enabling hybrid models.

---

7.12 Expert Checklist — Production-Ready Models

Data Model

• Star schema — no snowflake beyond one level
• All relationships are *:1 (many-to-one) unless specifically justified
• No bi-directional relationships without documented reason
• Dedicated Date table, marked as date table
• Surrogate integer keys for all relationships
• All foreign key columns hidden from report view
• Measures organized in display folders in a dedicated measure table
• No unused columns or tables loaded into the model

DAX

• All measures use VAR / RETURN for readability and efficiency
• DIVIDE used instead of / to handle divide-by-zero
• Time intelligence measures verified against expected values
• No circular dependencies
• Performance Analyzer run — no visual exceeds 500ms DAX time

Report

• Report theme applied
• All visuals have descriptive titles (no "Sum of Revenue")
• RLS tested in Desktop with "View as role"
• Drill-through pages have a back button
• Bookmarks tested for correct state capture
• Mobile layout configured for key pages

Service

• Deployment pipeline set up (Dev → Test → Prod)
• Scheduled refresh configured and tested
• Endorsement applied (Promoted or Certified)
• Sensitivity labels applied
• Gateway health monitored

---

Module 7 Summary

You are now an expert. You can:

• Profile and optimize model and DAX performance
• Build composite models mixing Import and DirectQuery
• Set up Deployment Pipelines for ALM
• Implement OLS to hide columns from specific roles
• Embed Power BI in external applications
• Automate administration with the REST API and PowerShell
• Leverage AI features: Copilot, Smart Narratives, Anomaly Detection
• Apply enterprise governance: endorsement, lineage, sensitivity labels

---

Where to Go from Here

---

Final Exam — Capstone Project

Build an end-to-end Power BI solution:

1. Connect to at least two different data sources (e.g., an Excel file and a SQL table)
2. Transform the data in Power Query — clean, rename, and shape
3. Model it as a star schema with a proper Date table
4. Create 10 DAX measures including at least 2 time intelligence measures and 1 using CALCULATE with ALL
5. Build a 3-page report:
   • Page 1: Executive Dashboard (KPIs + trends)
   • Page 2: Breakdown Analysis (matrix + bar charts with drill-through)
   • Page 3: Drill-through Detail page
6. Apply RLS with at least one dynamic role
7. Publish to the Service and set up a scheduled refresh
8. Share as an App to at least one colleague

Completing this project means you have mastered Power BI from zero to expert.