Power BI, Dataflows & Dataverse for Analytics

Enterprise analytics with Power BI integration, Dataflows Gen2, and Dataverse for data engineering

Power Platform Architecture

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────┐
│                    POWER PLATFORM ANALYTICS ARCHITECTURE            │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  DATA SOURCES                                                       │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │ ADLS Gen2 │ Synapse │ Cosmos DB │ SQL DB │ REST APIs        │   │
│  └───────────────┬─────────────────────────┬───────────────────┘   │
│                  │                         │                       │
│                  ▼                         ▼                       │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │                    DATAFLOWS GEN2                            │   │
│  │                                                               │   │
│  │  Power Query Online                                          │   │
│  │  ┌─────────────────────────────────────────────────────┐    │   │
│  │  │ Source ──> Clean ──> Transform ──> Load              │    │   │
│  │  │                                                       │    │   │
│  │  │ Output: Dataverse / ADLS / Synapse                   │    │   │
│  │  └─────────────────────────────────────────────────────┘    │   │
│  │                                                               │   │
│  │  Refresh: Scheduled / Incremental / On-demand               │   │
│  └─────────────────────────────────────────────────────────────┘   │
│                  │                                                 │
│                  ▼                                                 │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │                    DATAVERSE                                  │   │
│  │                                                               │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │   │
│  │  │ Tables       │  │ Relationships│  │ Business     │     │   │
│  │  │ (Entities)   │  │              │  │ Rules        │     │   │
│  │  └──────────────┘  └──────────────┘  └──────────────┘     │   │
│  │                                                               │   │
│  │  Tables: accounts, contacts, opportunities, custom           │   │
│  └─────────────────────────────────────────────────────────────┘   │
│                  │                                                 │
│                  ▼                                                 │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │                    POWER BI                                   │   │
│  │                                                               │   │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │   │
│  │  │ Datasets     │  │ Reports      │  │ Dashboards   │     │   │
│  │  │ (Semantic    │  │              │  │              │     │   │
│  │  │  Models)     │  │              │  │              │     │   │
│  │  └──────────────┘  └──────────────┘  └──────────────┘     │   │
│  │                                                               │   │
│  │  DirectQuery │ Import │ Composite │ Live Connection          │   │
│  └─────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘

Dataflows Gen2 Configuration

# Power BI Dataflows API operations
import requests
import json

# Get Dataflow from Power BI workspace
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}

# List dataflows
response = requests.get(
    "https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/dataflows",
    headers=headers
)

dataflows = response.json()["value"]
for df in dataflows:
    print(f"Dataflow: {df['name']}, ID: {df['id']}")

# Trigger dataflow refresh
response = requests.post(
    f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/dataflows/{dataflow_id}/refreshes",
    headers=headers,
    json={
        "notifyOption": "MailOnFailure"
    }
)

# Get refresh history
response = requests.get(
    f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/dataflows/{dataflow_id}/refreshes",
    headers=headers
)

Power BI Dataset (Semantic Model) Integration

# Power BI Dataset operations
from powerbiclient import Report, models

# Connect to Power BI dataset
dataset = models.Dataset(
    dataset_id="dataset-id",
    auth_token=access_token,
    workspace_id=workspace_id
)

# Execute DAX query
result = dataset.execute_dax_query(
    query="""
    EVALUATE
    SUMMARIZE(
        Sales,
        Sales[ProductCategory],
        "Total Revenue", SUM(Sales[TotalAmount]),
        "Total Transactions", COUNTROWS(Sales)
    )
    """
)

# Power BI XMLA endpoint for direct connectivity
# Connection string: 
# Data Source=powerbi://api.powerbi.com/v1.0/myorg/{workspace-name};
# Initial Catalog={dataset-name}

Dataverse Tables for Data Engineering

# Dataverse operations
from dataverse import DataverseClient
from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential()
client = DataverseClient(
    instance_url="https://org-name.crm.dynamics.com",
    credential=credential
)

# Query Dataverse table
query = """
<fetch>
    <entity name="account">
        <attribute name="name"/>
        <attribute name="revenue"/>
        <attribute name="industry"/>
        <filter>
            <condition attribute="createdon" operator="last-x-days" value="30"/>
        </filter>
    </entity>
</fetch>
"""

results = client.query(query)
for record in results:
    print(f"Account: {record['name']}, Revenue: {record['revenue']}")

# Upsert record
client.upsert(
    entity_name="custom_salesdata",
    entity_id="record-guid",
    data={
        "name": "Sales Record 2024",
        "totalamount": 150000.00,
        "statuscode": 1
    }
)

ℹ️

Pro Tip: Use Power BI DirectQuery for large datasets (ADLS, Synapse) to avoid data duplication. Use Import mode for frequently accessed, smaller datasets for better performance.

Power BI Connection Modes

Architecture Diagram

┌─────────────────────────────────────────────────────────────────┐
│                    POWER BI CONNECTION MODES                     │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  IMPORT MODE                                                    │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ Data copied to Power BI dataset (in-memory)              │   │
│  │ Latency: Fast (in-memory)                                │   │
│  │ Size limit: 1 GB (Pro), 400 GB (Premium)                │   │
│  │ Refresh: Scheduled (8x/day Pro, 48x/day Premium)        │   │
│  │ Best for: Smaller datasets, complex DAX                  │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  DIRECTQUERY MODE                                               │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ Query sent directly to source (no data copied)           │   │
│  │ Latency: Depends on source performance                   │   │
│  │ Size limit: No limit                                     │   │
│  │ Refresh: Real-time (on every interaction)                │   │
│  │ Best for: Large datasets, real-time dashboards           │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  COMPOSITE MODE                                                 │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ Mix of Import and DirectQuery                            │   │
│  │ Hot data: Import (fast access)                           │   │
│  │ Cold data: DirectQuery (no duplication)                  │   │
│  │ Best for: Large datasets with hot/cold separation        │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  LIVE CONNECTION                                                │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │ Connect to existing dataset (no data movement)           │   │
│  │ Sources: Power BI dataset, Analysis Services             │   │
│  │ Best for: Shared datasets, enterprise reporting          │   │
│  └─────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

Interview Questions

Q1: How do you connect Power BI to Synapse Analytics? A: 1) DirectQuery: Connect to Synapse SQL pool via SQL endpoint, 2) Import: Use Power Query to load data into Power BI dataset, 3) Composite: Import hot data, DirectQuery for cold data. Use Synapse Serverless for ad-hoc exploration.

Q2: What are the best practices for Power BI dataset optimization? A: 1) Use star schema (fact/dimension tables), 2) Minimize cardinality of columns, 3) Use DAX measures instead of calculated columns, 4) Implement row-level security (RLS), 5) Use aggregation tables for large datasets.

Q3: How does Dataflows Gen2 differ from ADF for data transformation? A: Dataflows Gen2 is for Power BI self-service analytics (Power Query Online). ADF is for enterprise ETL/ELT pipelines. Use Dataflows for business user transformations; ADF for complex, production data engineering.