Skip to content

Matomo Cloud to Power BI Integration

Overview

This documentation outlines three approaches for integrating Matomo Cloud analytics data with Microsoft Power BI for the 7N20 AgentHub platform. Each approach offers different trade-offs between complexity, cost, and real-time capabilities.

Integration Approaches Comparison

Approach Complexity Cost Data Freshness Scalability Best For
Direct API Low Low Scheduled (hourly/daily) Limited Small datasets, quick setup
Azure Data Lake + Synapse Medium Medium Near real-time High Large-scale analytics, historical data
Real-time Streaming High High Real-time (seconds) Medium Live dashboards, monitoring

Architecture Overview

graph TB
    subgraph "Data Sources"
        MC[Matomo Cloud]
    end

    subgraph "Integration Options"
        direction TB

        subgraph "Option 1: Direct API"
            API1[Matomo API] --> PBI1[Power BI Desktop]
            PBI1 --> PBIS1[Power BI Service]
        end

        subgraph "Option 2: Data Lake"
            API2[Matomo API] --> AF[Azure Function]
            AF --> ADL[Azure Data Lake Gen2]
            ADL --> SYN[Azure Synapse]
            SYN --> PBIS2[Power BI Service]
        end

        subgraph "Option 3: Streaming"
            API3[Matomo API] --> AF2[Azure Function]
            AF2 --> EH[Event Hubs]
            EH --> ASA[Stream Analytics]
            ASA --> PBIS3[Power BI Push Dataset]
        end
    end

    MC --> API1
    MC --> API2
    MC --> API3

    style MC fill:#f9f,stroke:#333,stroke-width:2px
    style PBIS1 fill:#9f9,stroke:#333,stroke-width:2px
    style PBIS2 fill:#9f9,stroke:#333,stroke-width:2px
    style PBIS3 fill:#9f9,stroke:#333,stroke-width:2px

Decision Criteria

Choose Direct API Integration When:

  • You need a quick, simple solution
  • Data volumes are relatively small (< 1GB per refresh)
  • Scheduled refresh (up to 48 times/day with Premium) is sufficient
  • Minimal Azure infrastructure is preferred
  • Budget is limited

Choose Azure Data Lake + Synapse When:

  • You have large volumes of historical data
  • You need to combine Matomo data with other data sources
  • SQL querying capabilities are required
  • Cost-effective storage for long-term data retention is needed
  • You already have Azure Synapse infrastructure

Choose Real-time Streaming When:

  • Live dashboards are required
  • Real-time monitoring and alerting is critical
  • Data freshness requirements are in seconds/minutes
  • You can accept the 5M row limitation of Power BI push datasets
  • Budget allows for continuous Azure resource consumption

Matomo API Capabilities

The Matomo Reporting API provides: - Data Formats: JSON, XML, CSV, TSV, HTML, RSS - Report Types: All standard and custom reports - Authentication: Token-based authentication - Rate Limits: Varies by Matomo Cloud plan - Bulk Operations: Multiple sites and date ranges in single request

Key API Endpoints

https://your-matomo-cloud.matomo.cloud/
?module=API
&method=<ReportModule>.<reportMethod>
&idSite=<siteId>
&period=<period>
&date=<date>
&format=<format>
&token_auth=<token>

Security Considerations

Authentication

  • Store Matomo API tokens in Azure Key Vault
  • Use Managed Identities for Azure resource authentication
  • Implement token rotation policies

Data Protection

  • Encrypt data at rest in Azure Data Lake
  • Use Private Endpoints for Azure services
  • Implement row-level security in Power BI

Compliance

  • Ensure GDPR compliance for user analytics data
  • Implement data retention policies
  • Audit data access and usage

Cost Estimation

Direct API Integration

  • Power BI Pro License: ~$10/user/month
  • Power BI Premium (if needed): Starting at $4,995/month
  • Total: $10-5,000/month depending on requirements

Azure Data Lake + Synapse

  • Azure Function: ~$20/month (consumption plan)
  • Azure Data Lake Storage: ~$50/month (1TB)
  • Azure Synapse Serverless: ~$5 per TB processed
  • Power BI Pro Licenses: ~$10/user/month
  • Total: ~$100-500/month depending on data volume

Real-time Streaming

  • Azure Function: ~$20/month (consumption plan)
  • Event Hubs: ~$100/month (1 million events/day)
  • Stream Analytics: ~$110/month (1 streaming unit)
  • Power BI Pro/Premium: $10-5,000/month
  • Total: ~$250-5,500/month

Implementation Timeline

Phase Duration Activities
Planning 1 week Requirements gathering, approach selection
Setup 1-2 weeks Azure resource provisioning, authentication
Development 2-4 weeks Pipeline implementation, data transformation
Testing 1 week End-to-end testing, performance optimization
Deployment 1 week Production deployment, monitoring setup

Next Steps

  1. Review the detailed documentation for each approach:
  2. Direct API Integration
  3. Azure Data Lake Integration

  4. Real-time Streaming

  5. Assess your specific requirements:

  6. Data volume and velocity
  7. Refresh frequency needs
  8. Budget constraints

  9. Existing Azure infrastructure

  10. Start with a proof of concept using the Direct API approach