Reports

The Reports feature enables you to embed and display PowerBI reports directly within your Toolkit project interface. By integrating your custom analytics and dashboards into the Toolkit, you create a unified workspace where project management, monitoring, and data analysis coexist, eliminating the need to switch between multiple tools and platforms.

Key Features

PowerBI Integration: Embed PowerBI reports directly into your Toolkit interface, providing seamless access to your analytics alongside your project management tools.
Custom Analytics: Build and display reports tailored to your specific project needs, whether tracking business metrics, operational data, user behaviour, or any other domain-specific analytics.
Environment-Specific Reporting: Configure different reports for each deployment environment (Development, QA, Production), allowing you to monitor environment-specific data and maintain proper data isolation.
Interactive Dashboards: Leverage PowerBI's full capabilities including interactive visualisations, date filtering, drill-down analytics, and real-time data updates.
Unified Interface: Access all your analytics within the Toolkit without switching to external tools, streamlining your workflow and improving productivity.

Understanding Your Data Architecture

Your Toolkit project automatically collects and stores data as your application runs. This data is stored in an MS SQL Database that forms the core of your project's data infrastructure. Key data sources include:

User and application data: Stored in the MS SQL Database
Communication logs: Email, SMS, WhatsApp, and InApp messaging records tracked by the Communications Server
Usage analytics: Web and mobile client interaction data
Media and files: Stored in Azure Blob Storage (referenced in the database)

Users with Microsoft Fabric capacity licensing can create Fabric Mirrors of their project databases (available from Toolkit version 24.5). This mirroring functionality allows you to replicate database tables into Microsoft Fabric, where you can connect PowerBI reports to the mirrored data instead of directly to the SQL Server database. This approach enables data transformation and querying in a separate mirrored environment without directly impacting the operational database. For complete details on setting up and using Fabric Mirrors, see the Microsoft Fabric integration documentation.

Prerequisites

Before configuring Reports in your Toolkit project, ensure you have:

PowerBI Workspace: An active PowerBI workspace in your Azure environment where you can create and publish reports. For guidance on creating and managing workspaces, see the official PowerBI workspace documentation.
PowerBI License: PowerBI Pro or Premium licenses for users who will create and publish reports. For detailed information about licensing options and requirements, refer to the PowerBI licensing documentation.
Database Connection Details: Access to your Toolkit project's MS SQL Database connection information, including:
- SQL Server hostname/address
- Database name
- Authentication credentials (username and password, or integrated authentication)
- Firewall rules configured to allow PowerBI to connect
For instructions on connecting PowerBI to SQL Server databases, consult the PowerBI SQL Server connector documentation.
Report Creation Skills: Familiarity with PowerBI Desktop or PowerBI Service for creating reports and dashboards. If you're new to PowerBI, start with the PowerBI getting started guide.
Data Schema Knowledge: Understanding of your project's database schema and the tables/views available for reporting. Consult your Toolkit project's database documentation or use SQL Server Management Studio to explore the available data structures.

Creating PowerBI Reports for Your Project

Before you can display reports in the Toolkit, you must first create them in PowerBI:

1. Connect PowerBI to Your MS SQL Database

Using PowerBI Desktop:

Open PowerBI Desktop
Click "Get Data" from the Home ribbon
Select "SQL Server" from the data sources list
Enter your connection details:
- Server: Your SQL Server hostname or IP address
- Database: Your Toolkit project's database name
- Data Connectivity mode: Choose "Import" or "DirectQuery" based on your needs
Click "OK" and provide authentication credentials when prompted
Select the tables and views you want to include in your report

Connection Tips:

Use DirectQuery for real-time data or Import for better performance with historical data
Ensure your SQL Server firewall allows connections from PowerBI service IP addresses
If connecting from PowerBI Service (cloud), you may need to configure an On-Premises Data Gateway

2. Explore Available Data

Common tables you might find in your Toolkit database include:

Users: Application user accounts and profiles
Communications: Logs of emails, SMS, WhatsApp, and InApp messages sent through the Communications Server
Sessions: User login and activity sessions
Transactions: Application-specific business transactions
Analytics/Events: Usage tracking and event logs from web and mobile clients
Custom Tables: Any domain-specific tables created for your application

3. Build Your Reports

Create visualisations relevant to your project's needs
Design dashboards that provide actionable insights
Add filters, drill-through capabilities, and interactive elements
Configure date ranges, slicers, and parameters for dynamic reporting
Test your report with sample data to ensure accuracy

4. Publish to PowerBI Workspace

Click "Publish" in PowerBI Desktop
Select your PowerBI workspace
Wait for the upload to complete
Note the Report ID and Dataset ID (you'll need these for Toolkit configuration)

To find Report ID and Dataset ID:

Open your report in PowerBI Service (app.powerbi.com)
The Report ID is in the URL: https://app.powerbi.com/groups/{workspace-id}/reports/{report-id}
Navigate to the dataset settings to find the Dataset ID

5. Configure Embedding and Permissions

In PowerBI Service, navigate to your report settings
Ensure embedding is enabled for the report
Configure Row-Level Security (RLS) if needed to restrict data access
Set up scheduled refresh for your dataset if using Import mode
Verify that users who will view the report in the Toolkit have appropriate PowerBI permissions

Reports Integration

The Toolkit's Reports feature is environment-specific, allowing you to configure different reports for each deployment environment of your project. Follow these steps to add and configure reports:

Log In: Access the Toolkit by entering your credentials.
Open Your Project: Locate and select your project within the Toolkit.
Navigate to Reports Settings using one of the following methods:
Quick Access (Recommended):
- Click the cog icon adjacent to the Reports menu item in the main navigation
- This takes you directly to the Reports settings for the Development environment
Via Project Settings:
- Click the settings icon to access your project's configuration options
- From the settings menu, select the deployment environment (Development, QA, or Production)
- Find and click on Reports to display the Reports settings interface
Select Environment: If you used the quick access method, you can switch between environments using the environment selector. Each environment is isolated, so you'll need to configure separate PowerBI reports for each environment.
Add a Report: Click the "Add a report" button to create a new report configuration.
Configure Report Details: In the "Edit Report" dialog, provide the following information:
- Report name: Enter a descriptive name for your report (e.g., "Sales Dashboard", "Communications Analytics", "User Engagement Metrics")
- Report Id: Enter the PowerBI Report ID from your PowerBI workspace (found in the report URL in PowerBI Service)
- Dataset Id: Enter the corresponding PowerBI Dataset ID (found in your PowerBI workspace dataset settings)
- Embedded URL: Provide the PowerBI embed URL for the report (optional, depending on your PowerBI configuration)
Save Configuration: Click "Save changes" to complete the report setup. The Toolkit will validate and store your configuration.
Repeat for Additional Reports: Add as many reports as needed for your project by repeating steps 6-8.

Accessing and Using Reports

Once configured, your reports become accessible through the Toolkit's main interface:

Navigate to Reports: From the main menu sidebar, click on Reports.
Select Environment: Ensure you're viewing the correct environment (Development, QA, or Production) using the environment selector at the top of the page.
Choose Report: Use the report dropdown menu to select which report you want to view.
Interact with Reports: Once displayed, you can:
- Filter by Date: Use date range selectors to focus on specific time periods
- Toggle Views: Switch between different views or tabs within the report (e.g., Summary vs. Details)
- Interact with Visuals: Click on charts, graphs, and tables to drill down or filter data
- Refresh Data: Reports will display data based on your PowerBI dataset refresh schedule
- Export Data: Use PowerBI's built-in export capabilities if configured in your report

Example Report Scenarios

While the Reports feature is completely customisable to your project's needs, here are some common report types that projects often implement:

Communications Analytics

Monitor messaging activity across all channels supported by the Communications Server:

Delivery Metrics: Track message delivery success rates across Email, SMS, WhatsApp, and InApp channels
Volume Trends: Visualise communication volume over time, identify peak usage periods
Failure Analysis: Identify and analyse delivery failures by channel, error type, and recipient
Channel Distribution: Compare usage across different communication channels
Response Times: Measure time-to-delivery for different message types

Sample SQL Query for Communications Data:

SELECT 
    Type as Channel,
    Created as Timestamp,
    ToAddress as Recipient,
    Subject,
    LastStatus as Status,
    LastError as ErrorMessage
FROM Communications
WHERE Created >= DATEADD(month, -1, GETDATE())

Usage Analytics

Understand how users interact with your application across web and mobile clients:

Active Users: Track daily, weekly, and monthly active users
Feature Adoption: Monitor which features are most frequently used
Session Analytics: Analyze session duration, frequency, and patterns
Geographic Distribution: Map user locations and regional usage trends
Device Breakdown: Understand the distribution of web vs. mobile client usage, device types, and operating systems
User Journey: Visualize common user paths through your application

Sample SQL Query for Usage Data:

SELECT 
    UserId,
    SessionStart,
    SessionEnd,
    DeviceType,
    Platform,
    Location
FROM UserSessions
WHERE SessionStart >= DATEADD(day, -30, GETDATE())

Performance and Health Monitoring

Track system performance and operational health:

Response Times: Monitor API response times and identify slow endpoints
Error Rates: Track application errors, exceptions, and their frequency
Resource Utilization: Monitor database query performance, storage usage
Uptime: Track system availability and downtime incidents
API Usage: Analyze API endpoint usage patterns and identify heavy consumers

Business Intelligence

Create domain-specific analytics relevant to your application:

Transaction Volumes: Track business transactions, revenue, conversions
Customer Metrics: Monitor user acquisition, retention, churn rates
Operational KPIs: Measure key performance indicators specific to your business
Trend Analysis: Identify patterns and trends in your application data
Forecasting: Use historical data to predict future trends and demands

Managing Reports

Editing Report Configurations

To update an existing report configuration:

Navigate to Reports in Project Settings
Select the appropriate environment
Click the edit icon (pencil) next to the report you want to modify
Update the configuration details (name, Report ID, Dataset ID, or Embedded URL)
Click "Save changes"

Removing Reports

To remove a report from the Toolkit:

Navigate to Reports in Project Settings
Select the appropriate environment
Click the delete icon (trash can) next to the report you want to remove
Confirm the deletion when prompted

Note: This only removes the report configuration from the Toolkit interface; it does not delete the actual PowerBI report from your workspace or affect your PowerBI dataset.

Environment-Specific Configurations

Reports are configured independently for each environment, allowing you to:

Display different reports in Development vs. Production
Connect to environment-specific databases (Dev database, QA database, Production database)
Use test data in Development environments without affecting production analytics
Maintain separate PowerBI workspaces per environment for better organization
Control which analytics are visible in each deployment stage
Test new reports in Development before promoting to Production

Recommended Approach:

Create separate PowerBI workspaces for each environment (e.g., "MyProject-Dev", "MyProject-QA", "MyProject-Prod")
Configure each workspace to connect to the corresponding environment's database
This ensures complete data isolation and prevents accidental data mixing

Best Practices

Report Design and Development

Start Simple: Begin with a few essential reports and expand your analytics over time as needs evolve and you become more familiar with your data.
Incremental Development: Build reports iteratively, starting with basic visualizations and adding complexity as requirements become clearer.
Data Model Optimization: Design efficient data models in PowerBI with proper relationships, measures, and calculated columns to ensure good performance.
Naming Conventions: Use clear, descriptive names for reports, measures, and visualizations that indicate their purpose and the data they display.

Data and Performance

Choose Import vs. DirectQuery Wisely:
- Use Import for better performance with historical data that doesn't need to be real-time
- Use DirectQuery when you need live data or when dataset size exceeds PowerBI limits
Scheduled Refresh: Configure appropriate refresh schedules for imported datasets (e.g., hourly, daily) based on how current your data needs to be.
Aggregations: For large datasets, use aggregation tables in your SQL database to improve query performance.
Date Tables: Create and use date dimension tables for consistent time-based filtering and analysis.
Query Optimization: Write efficient SQL queries and avoid pulling unnecessary columns or rows into PowerBI.

Security and Access Control

Row-Level Security: Implement RLS in PowerBI to ensure users only see data they're authorized to access.
Environment Separation: Use separate databases and PowerBI workspaces for each environment to maintain data isolation.
Credential Management: Securely store and manage database credentials; avoid hardcoding credentials in connection strings.
PowerBI Licensing: Ensure all users who need to view reports have appropriate PowerBI licenses.

Maintenance and Documentation

Version Control: Keep track of report changes and maintain versions of your .pbix files.
Documentation: Maintain documentation about:
- What each report displays and its business purpose
- Data sources and table schemas
- Refresh schedules and dependencies
- How to interpret key metrics and KPIs
- Known limitations or data quality issues
Regular Reviews: Periodically review your reports to ensure they still meet business needs and retire outdated reports.
Monitor Dataset Refresh: Set up alerts for dataset refresh failures and monitor refresh performance.