Power BI

Getting Wialon data into Power BI

Power BI has no Wialon entry in its Get Data list, so every fleet that reports in Power BI ends up building a route of its own. This page lays out the four routes we see, what each costs to keep running, and how a PostgreSQL copy of the fleet connects with the connector Power BI already ships.

Connector: PostgreSQL, built into Power BI
Modes: Import or DirectQuery
Join key: unit_id on every table
Freshness: Synced from every 15 minutes

Four ways fleets get Wialon data into Power BI

Each of these works somewhere. They differ in how much history they can carry, how often a person has to touch them, and what happens on the morning they fail.

Route	How it works	Where it breaks
Emailed report files	A Wialon job sends a report as Excel or CSV on a schedule. Someone, or a Power Automate flow, drops the file into a folder that Power BI reads.	Every file is one report for one period. History is whatever files survived, a template change silently changes the columns, and Wialon switches to download links once a file passes 30 MB.
Power Query against the Remote API	Power BI calls Wialon's Remote API directly: log in with a token, execute a report, page through the result rows.	The API works in sessions and report executions, and Power BI's service is strict about refreshing queries whose address is assembled at run time. Rate limits and report timeouts land on the refresh itself.
A sync service you assemble	A script or a general-purpose integration tool pulls from the Remote API into a database, and Power BI reads the database.	It works, and then it is yours: retries, backfill, schema changes and the API limits all need an owner. The common ELT tools such as Fivetran and Airbyte carry no Wialon source.
A PostgreSQL copy	FleetSQL keeps a PostgreSQL database in sync with the Wialon account, starting with up to three years of history. Power BI connects to it as an ordinary database.	It is a copy on a schedule, so it trails live positions by up to one sync interval. Live tracking stays in Wialon.

Why the Remote API is awkward from Power Query

The Remote API is built for applications. A request first trades a token for a session id, a report is executed against a resource and a set of units, and its rows are then fetched page by page while the session stays alive. Power Query can make every one of those calls, and a Desktop refresh on one analyst's laptop will often succeed.

The trouble starts when the report moves to the Power BI service. A scheduled refresh has to call an address that is built from a session id it obtained a moment earlier, and the service refuses or struggles with that kind of dynamic source. Large periods run into the per-execution budget Wialon enforces on reports, which a refresh reads as missing data. Each refresh also re-pulls the whole period, so the same months are fetched again every night against the same rate limits your dispatch tools depend on.

The limits themselves are covered in working within Wialon's limits. The short version: extraction belongs in a job that runs incrementally and remembers what it already has, and Power BI should read the result.

Connecting Power BI to the FleetSQL database

Once the fleet is in PostgreSQL, the connection is the same form Power BI uses for any database. The console shows each fleet's host, database and connection URL, and each person or tool gets its own read-only PostgreSQL role.

Get Data → Database → PostgreSQL database. The connector ships with Power BI Desktop, so there is nothing to install.
Server is the host from the console, with the port after a colon if it differs from 5432. Database is the fleet's database name.
Data connectivity mode: Import for most fleets, DirectQuery when you want years of events to stay in Postgres. The next sections cover the choice.
Credentials: the Database tab, with the read-only role issued for Power BI. Keep the encrypted connection option on, because the server accepts TLS connections only.
Navigator: pick w_prop_unit, the w_evnt_* event tables you need, and any w_rprt_* tables for the Wialon report templates you enabled. _meta_data_sources lists every table with its grain and when it last synced.

A model that holds together

The schema is small on purpose, so the model is mostly one relationship drawn several times. w_prop_unit holds one row per vehicle, and unit_id appears on every event table, which gives a one-to-many relationship from units to trips, speedings, fuel events and EV charging. Group membership sits in w_prop_unit_group, which works as a bridge table when reports slice by Wialon group.

Timestamps are stored in UTC, and there is no local date column anywhere. That is deliberate, and it means the calendar day has to be derived in the zone the report is about. Doing it in a SQL view keeps the definition in one place that Power BI, Grafana and a notebook all share. Your own views sit beside the synced tables and are left alone by the sync.

The full column list for each table is in what lands in a PostgreSQL copy of your Wialon fleet.

A daily view for Power BI, in the fleet's local zone

CREATE VIEW pbi_unit_day AS
SELECT t.unit_id,
       (t.start_time AT TIME ZONE 'Europe/Warsaw')::date AS day,
       COUNT(*)                                          AS trips,
       SUM(t.distance_m) / 1000.0                        AS km,
       MAX(t.max_speed)                                  AS max_kmh
FROM   w_evnt_trips t
GROUP  BY 1, 2;

-- Then in Power BI: pbi_unit_day[unit_id] → w_prop_unit[unit_id],
-- and pbi_unit_day[day] → your date table.

Import or DirectQuery

Import copies the rows into the Power BI model. Visuals are fast, DAX works without restrictions, and refresh runs on the schedule your workspace allows. For most fleets, a year or two of trips and events fits comfortably, and this is the mode to start with.

DirectQuery leaves the data in PostgreSQL and sends each visual's query to the database when someone opens the report. It suits long histories and wide event tables you would rather not duplicate into a model, at the cost of slower visuals and a narrower set of DAX functions. A SQL view that pre-aggregates to the grain the report needs, like the daily view above, keeps DirectQuery responsive.

Whichever mode you pick, match the refresh to the sync. The database updates on the cadence set in the console, from every 15 minutes to once a day, and refreshing Power BI more often than that returns the same numbers.

Measures worth defining once

Because every table carries raw events, the measures are short and easy to check against a Wialon report for the same period. A few that most fleet reports start from:

Reconciling a number: run the equivalent Wialon report for the same range. Differences almost always come from the timezone or from comparing a sensor-based fuel figure with a rates-based one.
Your existing templates: a report your team already maintains in Wialon can be extracted as its own w_rprt_* table, so the Power BI version starts from the same definition.
Joining the business: fuel card transactions, cost centres or customer contracts can live as tables in the same database, which lets one measure combine telemetry with money.

DAX over the synced tables

Km =
    DIVIDE ( SUM ( w_evnt_trips[distance_m] ), 1000 )

Speedings per 1000 km =
    DIVIDE ( COUNTROWS ( w_evnt_speedings ), [Km] ) * 1000

Fuel drained (l) =
    SUM ( w_evnt_fuel[theft] )

Fuel filled (l) =
    SUM ( w_evnt_fuel[filled] )

Frequently asked questions

Is there an official Wialon connector for Power BI? +

Power BI's Get Data list has no Wialon source, and we know of no connector published by Gurtam. Fleets connect through files, through the Remote API, or through a database that holds a copy of the data. FleetSQL is the database route: Power BI reads it with its built-in PostgreSQL connector.

Can Power BI read the Wialon Remote API directly? +

It can make the calls from Power Query, and a Desktop refresh for a small fleet often works. Scheduled refresh in the Power BI service is where it gets fragile, because the API works in sessions and report executions and each refresh re-pulls the whole period under Wialon's rate limits and report timeouts.

Do we need an on-premises data gateway? +

That depends on how your Power BI tenant handles cloud PostgreSQL sources, and some organisations route every database through a gateway by policy. What FleetSQL provides is the part that has to be true either way: a host reachable over the internet, TLS required, and a read-only role that can be revoked on its own.

How fresh is the data in Power BI? +

As fresh as the last sync plus the last Power BI refresh. The sync runs on a cadence you set, from every 15 minutes to once a day, and DirectQuery reads the database on every view, so the gap is the sync interval. In Import mode, add the time to your next scheduled refresh.

Does the same database work in Excel? +

Yes. Excel for Windows has Get Data → From Database → From PostgreSQL Database, and it reads the same tables and views with the same read-only role. It is a good fit for the person who wants one table in a pivot without building a Power BI report.

Worth reading

What lands in a PostgreSQL copy of your Wialon fleet

The tables FleetSQL writes into your database — units, trips, speedings, fuel and EV events, your own Wialon reports — and the queries they answer.

Wialon data extraction: working within the limits

How to pull years of fleet history out of Wialon Hosting without hitting its rate limits, concurrency caps, or report timeouts.

Contact

Interested in FleetSQL?

Why FleetSQL

Your telematics data in a PostgreSQL database you control — queryable, joinable, and yours.

Managed, not installed: We run the extraction and the database. You get a connection string.
One database per client: Your own PostgreSQL, hosted in the EU, TLS required.
Your tools, unchanged: Power BI, Grafana, Metabase, psql, pandas — anything that speaks Postgres.
An evaluation first: A trial database on your own fleet before anything is signed.

Or write to us directly: [email protected]