Skip to content

Power BI Integration

Overview

This document details the integration strategy for connecting Power BI to the metrics platform, combining business events from Azure Data Lake, frontend analytics from Matomo, and error tracking from Sentry into unified reporting dashboards.

Integration Goals

  • Unified Analytics: Single pane of glass for all metrics
  • Real-time Insights: Near real-time data refresh for key metrics
  • Cost Efficiency: Direct Lake mode for optimal performance and cost
  • Self-Service: Enable business users to create their own reports

Power BI Architecture

Data Integration Overview

graph LR
    subgraph "Data Sources"
        SYNAPSE[Synapse Serverless<br/>Business Events]
        MATOMO[Matomo Cloud API<br/>Usage Analytics]
        SENTRY[Sentry API<br/>Error Tracking]
    end

    subgraph "Power BI Dataset"
        DM[Unified Data Model]
        REL[Relationships]
        MEAS[Calculated Measures]
    end

    subgraph "Consumption"
        EXEC[Executive Dashboard]
        OPS[Operations Dashboard]
        DOMAIN[Domain Reports]
        ADHOC[Ad-hoc Analysis]
    end

    SYNAPSE -->|Direct Lake| DM
    MATOMO -->|API Refresh| DM
    SENTRY -->|API Refresh| DM

    DM --> REL
    REL --> MEAS

    MEAS --> EXEC
    MEAS --> OPS
    MEAS --> DOMAIN
    MEAS --> ADHOC

    style SYNAPSE fill:#DDA0DD
    style MATOMO fill:#FF6B6B
    style SENTRY fill:#4ECDC4
    style DM fill:#FFD93D

Data Model Architecture

erDiagram
    DimDate {
        date_key int PK
        date date
        year int
        month int
        day int
        day_of_week int
        is_weekend boolean
    }

    DimDomain {
        domain_key int PK
        domain_name string
        domain_description string
    }

    DimEventType {
        event_type_key int PK
        event_type string
        aggregate_type string
        domain_key int FK
    }

    DimUser {
        user_key int PK
        user_id string
        user_email string
        user_role string
    }

    FactBusinessEvents {
        event_key bigint PK
        date_key int FK
        domain_key int FK
        event_type_key int FK
        user_key int FK
        event_count int
        event_timestamp timestamp
        correlation_id string
    }

    FactMatomoMetrics {
        matomo_key bigint PK
        date_key int FK
        page_views int
        unique_visitors int
        bounce_rate decimal
        avg_time_on_site int
        page_url string
    }

    FactSentryEvents {
        sentry_key bigint PK
        date_key int FK
        error_count int
        error_type string
        error_message string
        project string
        environment string
    }

    DimDate ||--o{ FactBusinessEvents : "date_key"
    DimDomain ||--o{ FactBusinessEvents : "domain_key"
    DimEventType ||--o{ FactBusinessEvents : "event_type_key"
    DimUser ||--o{ FactBusinessEvents : "user_key"
    DimDate ||--o{ FactMatomoMetrics : "date_key"
    DimDate ||--o{ FactSentryEvents : "date_key"

Business Events Integration

Direct Lake Configuration

let
    // Connect to Synapse Serverless using Direct Lake
    Source = Sql.Database(
        "synapse-metrics-platform-ondemand.sql.azuresynapse.net", 
        "metrics_platform",
        [
            Query = "SELECT * FROM gold.business_metrics",
            ConnectionTimeout = #duration(0, 0, 10, 0),
            CommandTimeout = #duration(0, 0, 5, 0)
        ]
    ),

    // Add calculated columns
    AddedCustom = Table.AddColumn(
        Source, 
        "MetricCategory", 
        each if Text.StartsWith([aggregate_type], "Contact") then "CRM"
             else if Text.StartsWith([aggregate_type], "Match") then "Matching"
             else if Text.StartsWith([aggregate_type], "Profile") then "Profile"
             else "Other"
    ),

    // Data type conversions
    ChangedType = Table.TransformColumnTypes(
        AddedCustom,
        {
            {"timestamp", type datetimezone},
            {"date", type date},
            {"metric_value", type number},
            {"year", Int64.Type},
            {"month", Int64.Type},
            {"day", Int64.Type}
        }
    )
in
    ChangedType

Business Events Measures

-- Key Performance Indicators
TotalEvents = SUM(FactBusinessEvents[event_count])

EventsThisMonth = 
CALCULATE(
    [TotalEvents],
    DATESINPERIOD(DimDate[date], TODAY(), -1, MONTH)
)

EventGrowthRate = 
VAR LastMonth = 
    CALCULATE(
        [TotalEvents],
        DATESINPERIOD(DimDate[date], TODAY(), -2, MONTH),
        DATESINPERIOD(DimDate[date], TODAY(), -1, MONTH)
    )
VAR ThisMonth = [EventsThisMonth]
RETURN
    DIVIDE(ThisMonth - LastMonth, LastMonth, 0)

-- Domain-specific metrics
CRMEvents = 
CALCULATE(
    [TotalEvents],
    DimDomain[domain_name] = "CRM"
)

MatchingEvents = 
CALCULATE(
    [TotalEvents],
    DimDomain[domain_name] = "Matching"
)

ConversionRate = 
VAR ContactsCreated = 
    CALCULATE(
        [TotalEvents],
        DimEventType[event_type] = "ContactCreated"
    )
VAR DeclarationsCompleted = 
    CALCULATE(
        [TotalEvents],
        DimEventType[event_type] = "DeclarationCompleted"
    )
RETURN
    DIVIDE(DeclarationsCompleted, ContactsCreated, 0)

Matomo Integration

Matomo API Connection

// Secure Matomo API function (using POST method as documented)
(method as text, optional parameters as record) =>
let
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    DefaultParams = [
        module = "API",
        idSite = "1",
        period = "day",
        date = "last30",
        format = "JSON",
        token_auth = MatomoApiToken
    ],

    MergedParams = Record.Combine({DefaultParams, parameters ?? []}),
    UpdatedParams = Record.AddField(MergedParams, "method", method),

    // Convert parameters to form data string
    ParamPairs = List.Transform(
        Record.FieldNames(UpdatedParams),
        each _ & "=" & Uri.EscapeDataString(Text.From(Record.Field(UpdatedParams, _)))
    ),

    RequestBody = Text.ToBinary(Text.Combine(ParamPairs, "&")),

    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded"
        ],
        Content = RequestBody
    ],

    Source = Json.Document(Web.Contents(ApiUrl, Options))
in
    Source

Matomo Data Processing

let
    // Fetch multiple Matomo reports
    VisitsSummary = FetchMatomoData("VisitsSummary.get", [date = "last90"]),
    PageUrls = FetchMatomoData("Actions.getPageUrls", [date = "last90", filter_limit = "100"]),
    Countries = FetchMatomoData("UserCountry.getCountry", [date = "last90", filter_limit = "50"]),

    // Transform visits summary
    VisitsTable = Table.FromRecords(
        List.Transform(
            Record.FieldNames(VisitsSummary),
            each [
                Date = Date.FromText(_),
                Visits = Record.Field(VisitsSummary, _)[nb_visits]?,
                UniqueVisitors = Record.Field(VisitsSummary, _)[nb_uniq_visitors]?,
                PageViews = Record.Field(VisitsSummary, _)[nb_pageviews]?,
                BounceRate = Record.Field(VisitsSummary, _)[bounce_rate]?,
                AvgTimeOnSite = Record.Field(VisitsSummary, _)[avg_time_on_site]?
            ]
        )
    ),

    // Add calculated columns
    EnhancedVisits = Table.AddColumn(
        VisitsTable,
        "Month",
        each Date.MonthName([Date])
    ),

    FinalVisits = Table.AddColumn(
        EnhancedVisits,
        "PageViewsPerVisit",
        each if [Visits] > 0 then [PageViews] / [Visits] else 0,
        type number
    )
in
    FinalVisits

Matomo Measures

-- Website Analytics KPIs
TotalPageViews = SUM(FactMatomoMetrics[page_views])

UniqueVisitorsThisMonth = 
CALCULATE(
    MAX(FactMatomoMetrics[unique_visitors]),
    DATESINPERIOD(DimDate[date], TODAY(), -1, MONTH)
)

AverageBounceRate = 
AVERAGE(FactMatomoMetrics[bounce_rate])

TopPages = 
TOPN(
    10,
    SUMMARIZE(
        FactMatomoMetrics,
        FactMatomoMetrics[page_url],
        "TotalViews", [TotalPageViews]
    ),
    [TotalViews],
    DESC
)

-- User journey analysis
ConversionFunnel = 
VAR HomePage = 
    CALCULATE(
        [TotalPageViews],
        FactMatomoMetrics[page_url] = "/"
    )
VAR ProductPages = 
    CALCULATE(
        [TotalPageViews],
        SEARCH("/products", FactMatomoMetrics[page_url], 1, 0) > 0
    )
VAR ContactPages = 
    CALCULATE(
        [TotalPageViews],
        SEARCH("/contact", FactMatomoMetrics[page_url], 1, 0) > 0
    )
RETURN
    "Home: " & FORMAT(HomePage, "#,0") & 
    " | Products: " & FORMAT(ProductPages, "#,0") & 
    " | Contact: " & FORMAT(ContactPages, "#,0")

Sentry Integration

Sentry API Connection

let
    // Sentry API configuration
    SentryBaseUrl = "https://sentry.io/api/0/projects/your-org/your-project/",
    SentryToken = Parameter_SentryToken,

    // Fetch error events
    ErrorsUrl = SentryBaseUrl & "events/?statsPeriod=30d",

    Options = [
        Headers = [
            Authorization = "Bearer " & SentryToken,
            #"Content-Type" = "application/json"
        ]
    ],

    Source = Json.Document(Web.Contents(ErrorsUrl, Options)),

    // Convert to table
    EventsList = Source[data],
    EventsTable = Table.FromList(EventsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Expand event data
    ExpandedEvents = Table.ExpandRecordColumn(
        EventsTable,
        "Column1",
        {"id", "title", "culprit", "dateCreated", "userCount", "tags"},
        {"EventId", "Title", "Culprit", "DateCreated", "UserCount", "Tags"}
    ),

    // Data type conversions
    TypedEvents = Table.TransformColumnTypes(
        ExpandedEvents,
        {
            {"DateCreated", type datetimezone},
            {"UserCount", Int64.Type}
        }
    ),

    // Add calculated columns
    FinalEvents = Table.AddColumn(
        TypedEvents,
        "ErrorCategory",
        each if Text.Contains([Title], "HTTP") then "Network"
             else if Text.Contains([Title], "JavaScript") then "Frontend"
             else if Text.Contains([Title], "SQL") then "Database"
             else "Other"
    )
in
    FinalEvents

Sentry Measures

-- Error tracking KPIs
TotalErrors = SUM(FactSentryEvents[error_count])

ErrorsThisWeek = 
CALCULATE(
    [TotalErrors],
    DATESINPERIOD(DimDate[date], TODAY(), -1, WEEK)
)

CriticalErrors = 
CALCULATE(
    [TotalErrors],
    FactSentryEvents[error_type] IN {"Error", "Fatal"}
)

ErrorRate = 
VAR TotalEvents = [TotalEvents]
VAR TotalErrors = [TotalErrors]
RETURN
    DIVIDE(TotalErrors, TotalEvents, 0) * 100

ErrorTrend = 
VAR CurrentWeek = [ErrorsThisWeek]
VAR LastWeek = 
    CALCULATE(
        [TotalErrors],
        DATESINPERIOD(DimDate[date], TODAY(), -2, WEEK),
        DATESINPERIOD(DimDate[date], TODAY(), -1, WEEK)
    )
RETURN
    CurrentWeek - LastWeek

MostFrequentErrors = 
TOPN(
    5,
    SUMMARIZE(
        FactSentryEvents,
        FactSentryEvents[error_message],
        "ErrorCount", [TotalErrors]
    ),
    [ErrorCount],
    DESC
)

Dashboard Design

Executive Dashboard

graph TD
    subgraph "Executive Dashboard Layout"
        HEADER[Header: Key Metrics Cards]

        subgraph "Top Row"
            KPI1[Total Events<br/>This Month]
            KPI2[Active Users<br/>This Week]
            KPI3[Error Rate<br/>Today]
            KPI4[Conversion Rate<br/>This Month]
        end

        subgraph "Middle Row"
            TREND[Events Trend<br/>Line Chart]
            DOMAIN[Domain Breakdown<br/>Donut Chart]
        end

        subgraph "Bottom Row"
            TOP_PAGES[Top Pages<br/>Table]
            ERROR_SUMMARY[Error Summary<br/>Bar Chart]
        end
    end

    HEADER --> KPI1
    HEADER --> KPI2
    HEADER --> KPI3
    HEADER --> KPI4

    KPI1 --> TREND
    KPI2 --> DOMAIN

    TREND --> TOP_PAGES
    DOMAIN --> ERROR_SUMMARY

Operations Dashboard

graph TD
    subgraph "Operations Dashboard"
        subgraph "System Health"
            PIPELINE[Pipeline Status]
            DATA_FRESH[Data Freshness]
            ERROR_RATE[Error Rates]
        end

        subgraph "Business Metrics"
            EVENTS_VOLUME[Event Volume]
            DOMAIN_ACTIVITY[Domain Activity]
            USER_ACTIVITY[User Activity]
        end

        subgraph "Performance"
            RESPONSE_TIMES[API Response Times]
            PAGE_LOAD[Page Load Times]
            DATABASE_PERF[Database Performance]
        end

        subgraph "Alerts"
            ALERT_LIST[Active Alerts]
            INCIDENT_LOG[Incident Log]
            SLA_STATUS[SLA Status]
        end
    end

Refresh Strategy

Refresh Schedule Configuration

gantt
    title Data Refresh Schedule
    dateFormat HH:mm
    axisFormat %H:%M

    section Business Events
    Synapse Refresh  :active, synapse, 01:00, 01:15

    section Matomo
    API Refresh      :matomo, 01:30, 01:45

    section Sentry
    Error Data       :sentry, 02:00, 02:10

    section Power BI
    Dataset Refresh  :powerbi, 02:30, 03:00
    Report Cache     :cache, 03:00, 03:05

Incremental Refresh Setup

let
    // Define RangeStart and RangeEnd parameters for incremental refresh
    Source = Sql.Database(
        "synapse-metrics-platform-ondemand.sql.azuresynapse.net", 
        "metrics_platform",
        [
            Query = "
                SELECT * 
                FROM gold.business_metrics 
                WHERE date >= '" & Date.ToText(RangeStart, "yyyy-MM-dd") & "'
                  AND date < '" & Date.ToText(RangeEnd, "yyyy-MM-dd") & "'
            "
        ]
    ),

    // Filter for incremental refresh
    FilteredRows = Table.SelectRows(
        Source,
        each [date] >= RangeStart and [date] < RangeEnd
    )
in
    FilteredRows

Security and Governance

Row-Level Security

-- RLS for multi-tenant access
[DomainAccess] = 
VAR UserEmail = USERPRINCIPALNAME()
VAR UserDomains = 
    CALCULATETABLE(
        VALUES(UserDomainAccess[domain_name]),
        UserDomainAccess[user_email] = UserEmail
    )
RETURN
    DimDomain[domain_name] IN UserDomains

Data Classification

-- Implement data classification in source
ALTER TABLE gold.business_metrics 
ADD COLUMN data_classification VARCHAR(20) 
DEFAULT 'Internal';

-- Update classifications
UPDATE gold.business_metrics 
SET data_classification = 'Confidential'
WHERE metric_name IN ('revenue', 'profit_margin', 'conversion_rate');

UPDATE gold.business_metrics 
SET data_classification = 'Public'
WHERE metric_name IN ('page_views', 'session_count');

Performance Optimization

Query Optimization

-- Optimized measure using SUMMARIZECOLUMNS
EventsbyDomain = 
SUMMARIZECOLUMNS(
    DimDomain[domain_name],
    DimDate[month],
    "TotalEvents", [TotalEvents],
    "GrowthRate", [EventGrowthRate]
)

-- Use KEEPFILTERS for better performance
FilteredEvents = 
CALCULATE(
    [TotalEvents],
    KEEPFILTERS(DimDate[year] = 2024)
)

-- Avoid iterating functions where possible
AverageEventsPerDay = 
DIVIDE(
    [TotalEvents],
    DISTINCTCOUNT(DimDate[date])
)

Dataset Optimization

// Enable query folding by pushing operations to source
let
    Source = Sql.Database("server", "database"),

    // These operations will be pushed to SQL Server
    FilteredData = Table.SelectRows(Source, each [date] >= #date(2024, 1, 1)),
    GroupedData = Table.Group(FilteredData, {"domain"}, {{"count", Table.RowCount, type number}}),
    SortedData = Table.Sort(GroupedData, {{"count", Order.Descending}})
in
    SortedData

Monitoring and Troubleshooting

Performance Metrics

// Power BI dataset refresh monitoring
PowerBIDatasets
| where TimeGenerated >= ago(7d)
| where OperationName == "DatasetRefresh"
| summarize 
    SuccessCount = countif(Status == "Success"),
    FailureCount = countif(Status == "Failed"),
    AvgDuration = avg(DurationMs)
    by bin(TimeGenerated, 1h)
| order by TimeGenerated desc

// Query performance tracking
PowerBIQueries
| where TimeGenerated >= ago(1d)
| where QueryType == "DAX"
| summarize 
    QueryCount = count(),
    AvgDuration = avg(DurationMs),
    P95Duration = percentile(DurationMs, 95)
    by bin(TimeGenerated, 1h)

Troubleshooting Guide

Issue Symptoms Solution
Slow Refresh Long dataset refresh times Optimize queries, reduce data volume, use incremental refresh
API Timeouts Matomo/Sentry connection failures Implement retry logic, check rate limits
Memory Errors Dataset import failures Use Direct Query mode, optimize data model
Stale Data Old data in reports Check refresh schedule, verify source data
Permission Errors Access denied errors Review RLS settings, check service principal permissions

Best Practices

  1. Data Modeling
  2. Use star schema design
  3. Implement proper relationships
  4. Optimize for query performance

  5. Minimize model size

  6. Refresh Strategy

  7. Use incremental refresh for large tables
  8. Schedule refreshes during off-peak hours
  9. Implement error handling and retries

  10. Monitor refresh performance

  11. Security

  12. Implement row-level security
  13. Use service principals for API access
  14. Classify sensitive data

  15. Regular access reviews

  16. Performance

  17. Use Direct Lake when possible
  18. Optimize DAX measures
  19. Enable query caching
  20. Monitor usage patterns

Next Steps