Connecting PowerBI

Microsoft PowerBI is a powerful business intelligence tool that works excellently with the ED Dataset Service. This guide shows you how to connect and create your first reports.

Prerequisites

PowerBI Desktop (download from Microsoft PowerBI)
MariaDB ODBC Connector (required for database connection)
ED Dataset Service credentials (host, database, username, password)
Basic familiarity with PowerBI data modeling and DAX
Network access to datasets.edcontrols.com on port 3306

Installation Requirements

Step 1: Install PowerBI Desktop

Navigate to the Microsoft PowerBI download page
Click the Download button
Select PBIDesktopSetup_x64.exe from the list of options
Click Next to begin the download
Once downloaded, run the installer:
- Double-click PBIDesktopSetup_x64.exe
- Accept the license agreement and click Next
- Choose installation directory (or use default) and click Next
- Click Install and wait for completion
- Click Finish when installation is complete

Step 2: Install MariaDB ODBC Connector

Important: You must install the MariaDB ODBC Connector to connect to the Ed Controls database.

Navigate to the MariaDB Downloads page
Select the Connectors tab
Choose ODBC connector from the Product dropdown
Select version 3.2.6-GA (or latest stable version)
Choose MS Windows 64-bit as the Operating System
Click Download to get the installer file: mariadb-connector-odbc-3.2.6-win64.msi (8.80 MB)
Run the downloaded installer:
- Double-click the .msi file
- Follow the on-screen instructions
- When prompted, select Typical installation
- Click Finish to complete installation

Note: The MariaDB Connector/ODBC is a standard ODBC 3.5 driver for building cross-platform applications on top of MariaDB, complete with encrypted connections via TLS/SSL. The connector is LGPL licensed.

Connection Steps

3. Connect to MariaDB in PowerBI

Now that the MariaDB connector is installed, follow these steps to connect:

Open PowerBI Desktop
Click Get Data in the top menu
In the pop-up window, click More... to see the full list of data sources
In the search bar, type MariaDB
Select MariaDB from the results and click Connect
Enter the connection details:
- Server: datasets.edcontrols.com
- Database: [your_dataset_name]
Click OK
When prompted for credentials:
- Username: [your_dataset_name]
- Password: [your_generated_password]
Click Connect to establish the connection

4. Select Tables

PowerBI will show all available tables. The Ed Controls database is transformed from a document-based system into a relational model optimized for reporting. Data is updated several times per day to ensure current information.

Core Business Tables:

contracts - Contract details with pricing and license information
projects - Project information, timelines, and locations
users - User profiles and contact information
tickets - Issues, tasks, and work items with status tracking
audits - Audit instances with completion tracking

Relationship Tables:

contracts_projects - Links contracts to projects
project_participants - User assignments and roles in projects
tickets_roles - User assignments for specific tickets
audit_roles - User permissions and assignments for audits

Detailed Data Tables:

audits_questions - Individual audit questions
audits_answer - Responses to audit questions
audits_category - Question groupings and categories
audits_tags - Labels/tags assigned to audits
audits_maps - Links audits to site maps
audit_answers_tickets - Links audit responses to tickets
tickets_tags - Categorization tags for tickets
tickets_progress_labels - Sub-status workflow tracking for tickets
tickets_custom_fields - Custom field data for tickets
tickets_custom_field_options - Available options for custom fields
tickets_custom_field_selections - Selected values for multi-choice fields
maps - Site maps and spatial references
map_groups - Grouping and categorization of maps
template_groups - Grouping and categorization of audit templates
audittemplates - Reusable audit templates

Performance Tracking:

Tables include calculated time fields (e.g., created_started, created_completed)
Geographic coordinates for mapping (latitude, longitude, x, y)
Status tracking and completion dates

Select the tables you need and click Load to import the data, or Transform Data to modify the data structure first.

Recommended Data Model

Understanding the Ed Controls Data Model

The Ed Controls database uses an InnoDB structure with optimized indexes for reporting. The data originates from a document-based system but is transformed into a relational model for PowerBI compatibility.

Core Relationships

Set up these key relationships in PowerBI:

contracts (1) ──→ contracts_projects (*) ──→ projects (1)
projects (1) ──→ tickets (*)
projects (1) ──→ audits (*)
projects (1) ──→ project_participants (*)
projects (1) ──→ maps (*)
audits (1) ──→ audits_answer (*)
audits (1) ──→ audits_questions (*)
audits (1) ──→ audits_category (*)
audits (1) ──→ audits_tags (*)
audits (1) ──→ audit_roles (*)
tickets (1) ──→ tickets_roles (*)
tickets (1) ──→ tickets_tags (*)
tickets (1) ──→ tickets_progress_labels (*)
tickets (1) ──→ tickets_custom_fields (*)
users (1) ──→ project_participants (*) [via email]

1. Create Relationships

Go to Model view in PowerBI
Create these primary relationships:
- contracts.id → contracts_projects.contractid
- projects.id → contracts_projects.projectid
- projects.id → tickets.projectid
- projects.id → audits.projectid
- projects.id → project_participants.projectid
- users.email → project_participants.email
- audits.id → audits_answer.audit
- audits.id → audits_questions.audit
- audits.id → audits_tags.auditid
- tickets.id → tickets_roles.ticketid
- tickets.id → tickets_progress_labels.ticketid
- tickets.id → tickets_custom_fields.ticketid

Important Notes About the Schema

Primary Keys: Most tables use VARCHAR(255) IDs as primary keys
User References: All user fields reference users.email (not user IDs)
Geographic Data: Coordinates stored as VARCHAR (convert to decimal for mapping)
Time Calculations: Pre-calculated time differences available (e.g., created_completed)
Document Legacy: couchdbid fields maintain compatibility with source system

2. Set Up Date Tables

Create a date table for time-based analysis:

Calendar = 
ADDCOLUMNS(
    CALENDAR(
        DATE(2020,1,1),
        DATE(2030,12,31)
    ),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & QUARTER([Date]),
    "Weekday", WEEKDAY([Date]),
    "WeekdayName", FORMAT([Date], "dddd")
)

Essential Measures

Create these DAX measures for common analytics:

Ticket Metrics

Total Tickets = COUNTROWS(tickets)

Open Tickets = 
CALCULATE(
    COUNTROWS(tickets),
    tickets[status] <> "completed",
    ISBLANK(tickets[archived])
)

Completed Tickets = 
CALCULATE(
    COUNTROWS(tickets),
    tickets[status] = "completed"
)

Average Resolution Time = 
AVERAGE(tickets[created_completed])

Overdue Tickets = 
CALCULATE(
    COUNTROWS(tickets),
    tickets[dueDate] < TODAY(),
    tickets[status] <> "completed",
    ISBLANK(tickets[archived])
)

Audit Metrics

Total Audits = COUNTROWS(audits)

Completed Audits = 
CALCULATE(
    COUNTROWS(audits),
    audits[status] = "completed"
)

Audit Completion Rate = 
DIVIDE([Completed Audits], [Total Audits], 0)

Average Audit Duration = 
CALCULATE(
    AVERAGE(audits[dueDate_completed]),
    NOT(ISBLANK(audits[completiondate]))
)

Project Metrics

Active Projects = 
CALCULATE(
    COUNTROWS(projects),
    ISBLANK(projects[archived])
)

Project Completion Rate = 
CALCULATE(
    COUNTROWS(projects),
    projects[enddate] <= TODAY(),
    ISBLANK(projects[archived])
) / [Active Projects]

Sample Reports

1. Executive Dashboard

Create visuals showing:

Card: Total Projects, Active Tickets, Completed Audits
Gauge: Project Completion Rate, Audit Completion Rate
Line Chart: Tickets Created vs. Completed Over Time
Bar Chart: Tickets by Status
Map: Projects by Location

2. Project Performance Report

Table: Project list with completion status
Gantt Chart: Project timelines (using custom visual)
Scatter Plot: Project Duration vs. Ticket Count
Slicer: Filter by Contract, Project Status

3. Ticket Management Dashboard

Bar Chart: Tickets by Responsible Person
Funnel: Ticket Status Distribution
Line Chart: Ticket Creation Trends
Heat Map: Tickets by Category and Priority

4. Audit Compliance Report

Matrix: Audit Status by Project and Type
Waterfall: Audit Completion Progress
Calendar: Audit Due Dates
KPI: Compliance Percentage

5. Common Business Questions & Solutions

"Which projects are behind schedule?"

Behind Schedule Projects = 
CALCULATE(
    COUNTROWS(projects),
    projects[enddate] < TODAY(),
    ISBLANK(projects[archived]),
    projects[status] <> "completed"
)

"What's our average ticket resolution time by project?"

Avg Resolution by Project = 
CALCULATE(
    AVERAGE(tickets[created_completed]),
    NOT(ISBLANK(tickets[dateCompleted]))
)

"How many audits are overdue?"

Overdue Audits = 
CALCULATE(
    COUNTROWS(audits),
    audits[dueDate] < TODAY(),
    audits[status] <> "completed"
)

"Which users are most active?"

User Activity Score = 
CALCULATE(COUNTROWS(tickets), tickets[author] = SELECTEDVALUE(users[email])) +
CALCULATE(COUNTROWS(audits), audits[author] = SELECTEDVALUE(users[email]))

Data Refresh

Automatic Refresh (PowerBI Service)

Publish your report to PowerBI Service
Go to Settings → Datasets
Configure Scheduled refresh:
- Frequency: Daily or hourly
- Time zone: Your local time zone
- Notify on failure: Enable email notifications

Manual Refresh (PowerBI Desktop)

Click Refresh on the Home ribbon
Or press F5

Performance Optimization

1. Query Folding

Use Transform Data to filter at source
Apply date filters to reduce data volume
Remove unnecessary columns

2. Import Mode Only (No DirectQuery)

DirectQuery is not supported

The ED Dataset Service database is exclusively intended for Import mode connections. DirectQuery sends live queries to the database on every user interaction, which can overload the shared server and degrade performance for all users. We monitor query patterns and may restrict or revoke access for connections that use DirectQuery or generate excessive query load.

Import Mode is the correct approach:

Faster performance for dashboards
Full DAX functionality
Data is cached locally in your Power BI model
Set up scheduled refresh to keep data current (see Data Refresh Schedule below)

3. Incremental Refresh

For large datasets, set up incremental refresh:

In Power Query, create date parameters
Filter data using parameters
Configure incremental refresh policy

Data Refresh Schedule

Server-Side Sync

The ED Dataset Service automatically synchronizes data from Ed Controls to your database on a fixed schedule:

Sync Type	Schedule	Description
Incremental sync	Every 4 hours (00:34, 04:34, 08:34, 12:34, 16:34, 20:34 UTC)	Fetches only data that changed since the last sync
Glacier check	Daily at 02:00 UTC	Detects projects that were archived/un-archived and triggers a full reindex when needed

This means your database is updated 6 times per day. In most cases, data is no more than 4 hours old.

PowerBI Refresh Recommendations

Since the database is updated every 4 hours, configure your Power BI scheduled refresh accordingly:

Recommended: Refresh 2-4 times per day, aligned with business hours (e.g., 07:00, 12:00, 17:00)
Refreshing more often than every 4 hours provides no benefit, as the source data hasn't changed
For weekly/monthly reports, a daily refresh is sufficient
Use Power BI Service's scheduled refresh, not manual refresh, for consistent data

Manual Sync

If you need an immediate refresh of the underlying data (e.g., after a large data import in Ed Controls), contact the Ed Controls support team to trigger a manual sync for your dataset.

Troubleshooting

ODBC Driver Issues

MariaDB connector grayed out or unavailable in Get Data

You must install the 64-bit MariaDB ODBC Connector (e.g., mariadb-connector-odbc-3.2.6-win64.msi). The 32-bit version will not work with Power BI Desktop.
Restart Power BI Desktop completely after installing the driver.
Verify the driver is registered in the 64-bit ODBC Data Source Administrator: C:\Windows\System32\odbcad32.exe (note: the 64-bit version is in System32, not SysWOW64).
If the driver is listed but Power BI still can't see it, re-run the MSI installer with the "Repair" option and check that the Visual C++ Redistributable is installed.

Using the wrong connector type

Use the MariaDB connector in Power BI (Get Data > MariaDB), not the "MySQL database" connector. The MySQL connector uses Oracle's MySQL Connector/NET, which may have compatibility issues with MariaDB-specific features.

Driver stops working after a Windows Update

Windows updates occasionally reset ODBC driver registrations. Re-run the MariaDB ODBC Connector installer with the "Repair" option to fix this.

Connection Issues

"Can't connect to server"

Check hostname: datasets.edcontrols.com
Verify port: 3306
Check that your firewall allows outbound connections on port 3306
If connecting over VPN, verify the VPN does not have a short idle timeout that drops the connection

"Authentication failed"

Always select Database authentication (not Windows). MariaDB does not support Windows/Kerberos authentication.
Verify username matches your dataset name
Check password accuracy
If you recently changed your password or switched datasets, clear cached credentials: File > Options and settings > Data source settings > find the connection > "Clear Permissions"

"SSL connection error" or "The handshake failed"

If your connection does not require SSL, uncheck "Use Encrypted Connection" in the Power BI connection dialog
Ensure you are using MariaDB ODBC Connector 3.1+ which supports TLS 1.2/1.3

"No data in tables" or Navigator shows empty table list

The dataset may still be processing its initial conversion. Wait for it to complete.
Verify the database name is correct (it is case-sensitive on the Linux server).
Test with a SQL client like MySQL Workbench or DBeaver first to confirm tables exist and contain data.

Data Type Pitfalls

VARCHAR coordinates not showing on maps

Geographic coordinates (latitude, longitude) are stored as VARCHAR(255). Power BI map visuals require numeric values. Convert them in Power Query:

if [latitude] = null or [latitude] = "" then null else Number.From([latitude])

Then in Model view, set the Data Category to "Latitude" or "Longitude" on the column.

DATETIME columns and time zones

All dates (creationdate, lastmodifieddate, dueDate) are stored as DATETIME without time zone info. When using DAX functions like TODAY(), be aware this uses the report viewer's local time zone, which may differ from the data's time zone.

Relationship creation fails on text keys

All primary keys are VARCHAR(255). Power BI's auto-detect relationships works best with integer keys and often creates incorrect relationships with text keys.

Disable auto-detect: File > Options > Current File > Data Load > uncheck "Autodetect new relationships after data is loaded"
Create all relationships manually as documented in the Create Relationships section above
If relationship creation fails, ensure both sides have the exact same data type (Text) and use Text.Trim() on ID columns to remove any whitespace

Performance Issues

Slow report loading or memory errors

The MariaDB connector only supports Import mode (not DirectQuery). All data is loaded into Power BI's in-memory model. To manage this:

Only select the tables you actually need in the Navigator
Remove large TEXT columns you don't need (e.g., tickets.body) in Power Query before loading
Truncate text columns you do need: Text.Start([body], 500) in Power Query
Apply date range filters early in Power Query to reduce data volume
Disable loading for reference tables only used in Power Query merges: right-click table > uncheck "Enable load"

Query folding breaks with complex transformations

Power Query's ability to push logic to the database (query folding) can break with MariaDB. Right-click a step in Power Query Editor and check if "View Native Query" is available. If grayed out, query folding broke at that step.

Keep transformations simple: filters, column removal, and type changes fold well
Avoid complex merges, pivots, or custom M functions before simpler filter steps
Use the "SQL statement" advanced option in the connection dialog to push complex filtering directly to the database

Command timeout during large loads

The default Power BI timeout is 10 minutes. For large datasets, expand "Advanced options" in the connection dialog and increase "Command timeout in minutes" to 30 or 60.

Scheduled Refresh Issues

Gateway requirements

The ED Dataset Service is publicly accessible at datasets.edcontrols.com:3306, so try configuring scheduled refresh in Power BI Service without a gateway first (select "Basic" authentication)
If a gateway is required (e.g., for corporate network policies), use the standard mode on-premises data gateway (personal mode does not support MariaDB)
Install the 64-bit MariaDB ODBC Connector on the gateway machine and restart the gateway service after installation

Refresh schedule disabled automatically

If scheduled refresh fails four times consecutively, Power BI automatically disables it. Fix the underlying issue, then manually re-enable the schedule in dataset settings.

Credentials out of sync

Power BI Desktop and Power BI Service store credentials independently. After any password change, update credentials in both:

Desktop: File > Options > Data source settings > Edit Permissions
Service: Settings > Manage connections and gateways > Edit credentials

Character Encoding

Special characters display as ???? or garbled text

The database uses utf8mb4 encoding to support full Unicode including Dutch diacritics. If characters display incorrectly:

When using the MariaDB connector directly, encoding is negotiated automatically
When using a custom ODBC DSN, add CHARSET=utf8mb4 to the DSN configuration
Spot-check the users table (names with diacritics) and projects table (Dutch location names) after loading to verify correct display

Data Quality Considerations

Understanding Ed Controls Data

Data Source: Transformed from document-based database to relational model
Update Frequency: Data refreshed several times per day
Date Fields: Some date fields may be NULL for incomplete items
Status Values: Status values may vary between organizations (created, started, completed, archived)
Geographic Data: Coordinates stored as VARCHAR - convert to decimal for mapping
User References: All user references use email addresses as identifiers
Time Calculations: Pre-calculated time differences in seconds (e.g., created_started, created_completed)
Legacy Fields: couchdbid fields maintain compatibility with source document database

Common Data Cleaning Steps

Filter Active Records: Use archived IS NULL to exclude archived items
Handle Missing Dates: Use COALESCE or IF statements for NULL dates
Standardize Status Values: Create calculated columns for consistent status grouping
Convert Data Types: Change text coordinates to decimal for mapping visuals
Validate Relationships: Ensure foreign keys exist before creating relationships

Data Validation Queries

-- Check for orphaned tickets (tickets without projects)
Orphaned Tickets =
CALCULATE(
    COUNTROWS(tickets),
    ISBLANK(RELATED(projects[id]))
)

-- Validate date ranges
Invalid Dates =
CALCULATE(
    COUNTROWS(projects),
    projects[enddate] < projects[startdate]
)

Best Practices

Start Simple: Begin with basic visuals and add complexity gradually
Use Filters: Always filter by date ranges and project scope
Optimize Queries: Push filters to the database level
Document Measures: Add descriptions to custom DAX measures
Test Performance: Regularly check report performance with production data
Schedule Refreshes: Set up automatic data refresh for current information
Validate Data: Regularly check for data quality issues and inconsistencies
Archive Old Data: Consider excluding very old data to improve performance
Disable Auto-Detect Relationships: Manually create relationships for reliable VARCHAR key joins
Remove Unused Columns: Especially large TEXT columns like tickets.body to reduce model size

Prerequisites​

Installation Requirements​

Step 1: Install PowerBI Desktop​

Step 2: Install MariaDB ODBC Connector​

Connection Steps​

3. Connect to MariaDB in PowerBI​

4. Select Tables​

Recommended Data Model​

Understanding the Ed Controls Data Model​

Core Relationships​

1. Create Relationships​

Important Notes About the Schema​

2. Set Up Date Tables​

Essential Measures​

Ticket Metrics​

Audit Metrics​

Project Metrics​

Sample Reports​

1. Executive Dashboard​

2. Project Performance Report​

3. Ticket Management Dashboard​

4. Audit Compliance Report​

5. Common Business Questions & Solutions​

Data Refresh​

Automatic Refresh (PowerBI Service)​

Manual Refresh (PowerBI Desktop)​

Performance Optimization​

1. Query Folding​

2. Import Mode Only (No DirectQuery)​

3. Incremental Refresh​

Data Refresh Schedule​

Server-Side Sync​

PowerBI Refresh Recommendations​

Manual Sync​

Troubleshooting​

ODBC Driver Issues​

Connection Issues​

Data Type Pitfalls​

Performance Issues​

Scheduled Refresh Issues​

Character Encoding​

Data Quality Considerations​

Understanding Ed Controls Data​

Common Data Cleaning Steps​

Data Validation Queries​

Best Practices​