BI tools

BI tools connect to Vectro the way they connect to a database. vectro serve answers as a PostgreSQL 16 database (read only), so tools use their built-in PostgreSQL connector and query live; spreadsheets and notebooks can also read plain URLs over HTTP. Nothing needs to be installed in the tool.

Tool	Connect with	Live queries
Power BI Desktop	PostgreSQL database (or OData feed, or Web)	yes, with DirectQuery
Tableau Desktop	PostgreSQL (or OData)	yes, with Live
Metabase	PostgreSQL	yes
Grafana	PostgreSQL data source	yes
Excel	From Web (CSV URL) or OData feed; ODBC for PostgreSQL	refresh
Google Sheets	IMPORTDATA with a CSV URL	refresh
pandas, Polars	a CSV or Arrow URL, or Arrow Flight SQL	each call

The servers themselves are described in HTTP, OData, PostgreSQL, Flight SQL.

1. Start the server

Put your CSV, TSV, JSON-lines, Arrow or .vectro files in one folder. Each file becomes a table named after it.

export VECTRO_PG_PASSWORD='choose-a-password'
npx vectro serve --root ~/data --pg 5433 --user bi --database shop

This starts HTTP on port 8787 and PostgreSQL on 5433. Connection details for every tool below:

• From the same machine: host localhost, port 5433, database shop, user bi, and the password.
• From Docker containers or other machines: add --host 0.0.0.0 (a password is then required) and connect to host.docker.internal or the machine’s address.
• Encryption: start with --tls-cert cert.pem --tls-key key.pem. Without them, turn the tool’s “encrypt” or “require SSL” option off.
• When something fails: --log-queries prints every statement a tool sends, which shows what it asked that Vectro doesn’t answer yet.

To try it on sample data, the Vectro repository’s examples/bi folder makes a shop of 10M orders (a .vectro dataset), 100K customers and 500 products in about 10 seconds:

git clone https://github.com/vectrojs/vectro && cd vectro
node examples/bi/make-data.mjs bi-data
npx vectro serve --root bi-data --pg 5433 --user bi --database shop

Power BI Desktop

1. Get Data > PostgreSQL database.
2. Server localhost:5433 (or machine:5433), Database shop.
3. Choose Import to load the rows into Power BI (fine up to a few million rows), or DirectQuery to leave them in Vectro and send a query for every visual. DirectQuery is where Vectro’s speed shows on large data.
4. Credentials: the Database tab, user bi and the password.
5. If Power BI says the connection is not encrypted, start Vectro with --tls-cert / --tls-key (a certificate Windows trusts), or uncheck “Encrypt connection” in File > Options and settings > Data source settings for this source.
6. The Navigator lists public.orders, public.customers and public.products.

Power BI’s PostgreSQL connector uses the Npgsql 4.0.17 driver, which Vectro is checked against, including Power BI’s own catalog queries. Power BI itself hasn’t been tried yet. For the Power BI Service (scheduled refresh, DirectQuery in the browser), the on-premises data gateway connects to Vectro the same way; the gateway machine must reach Vectro’s port.

Without a driver: Get Data > OData feed, URL http://localhost:8787/odata, for filtered or summarised data in Import mode. Or Get Data > Web with a SQL URL such as http://localhost:8787/sql?q=SELECT region, COUNT(*) AS n FROM orders GROUP BY 1&format=csv; Refresh runs the query again.

Tableau Desktop

1. Connect > To a Server > PostgreSQL. If Tableau asks for a driver, it links to the PostgreSQL JDBC driver; put the .jar in Tableau’s Drivers folder.
2. Server localhost, Port 5433, Database shop, Authentication “Username and Password”, user bi and the password. Leave “Require SSL” off unless Vectro has a certificate.
3. Drag orders onto the canvas, and customers and products to relate them on customer_id and product_id.
4. Live sends queries to Vectro; Extract copies the rows into a Tableau extract.

Tableau uses the pgJDBC driver, which Vectro is checked against (schemas, tables, columns, prepared statements, cursors). Tableau itself hasn’t been tried yet. Tableau’s temporary tables for some filters are refused (Vectro is read only); it falls back to plain queries. Tableau’s OData connector also works, with http://localhost:8787/odata.

Metabase

1. Admin > Databases > Add database > PostgreSQL.
2. Host localhost (host.docker.internal if Metabase runs in Docker), port 5433, database shop, user bi, the password.
3. Save. Metabase reads the tables and columns with their types, and the questions its editor builds become SQL that Vectro answers, joins included.

On the 10M-order sample, Metabase v0.64 questions answer in 80 to 600 ms end to end, a join to products included.

Grafana

Add a PostgreSQL data source: host localhost:5433 (or host.docker.internal:5433), database shop, user bi, the password, TLS/SSL mode disable (unless Vectro has a certificate), version 16. Time-series panels can use Grafana’s macros ($__timeFilter, $__timeGroupAlias), which Grafana turns into plain SQL that Vectro runs. One of the sample dashboard’s panels, revenue per day and sales channel:

SELECT $__timeGroupAlias(ts, '1d'), channel AS metric,
       SUM(qty * unit_price * (1 - COALESCE(discount, 0))) AS revenue
FROM orders
WHERE $__timeFilter(ts)
GROUP BY 1, 2
ORDER BY 1

On the sample, Grafana 13’s eight panels (a moving average included) answer in 20 to 220 ms each.

Metabase and Grafana in Docker

examples/bi has a Docker Compose file that starts both, with the Grafana data source and a “Shop: 10M orders on Vectro” dashboard already set up:

npx vectro serve --root bi-data --no-http --pg 5433 --host 0.0.0.0 --user bi --database shop
cd examples/bi
VECTRO_PG_PASSWORD="$VECTRO_PG_PASSWORD" docker compose up -d

Grafana is then at http://localhost:3001 (anonymous view access), and Metabase at http://localhost:3000: create the first admin account, then add the database as above with host host.docker.internal.

Excel

• From a URL (Windows and Mac): Data > Get Data > From Web, then a SQL URL with format=csv: http://localhost:8787/sql?q=SELECT region, COUNT(*) AS n FROM orders GROUP BY 1&format=csv
• OData: Data > Get Data > OData feed, http://localhost:8787/odata. Every table appears with its column types.
• ODBC: with the PostgreSQL ODBC driver installed, connect to port 5433 like any PostgreSQL database.

With --token, OData and Web connections sign in with Basic: any user name, the token as the password.

Google Sheets

=IMPORTDATA("https://your-host/sql?q=SELECT region, COUNT(*) AS n FROM orders GROUP BY 1&format=csv&token=secret")

Google fetches the URL from its own servers, so Vectro needs a public address (behind HTTPS, with --host 0.0.0.0 and a --token). localhost won’t work here, and neither will the Power BI Service without a gateway.

pandas and Polars

import pandas as pd
import pyarrow.ipc
from urllib.parse import quote
from urllib.request import urlopen

url = 'http://localhost:8787/sql?q=' + quote('SELECT region, SUM(qty) AS qty FROM orders GROUP BY 1')
df = pd.read_csv(url + '&format=csv')                                    # or polars.read_csv
table = pyarrow.ipc.open_stream(urlopen(url + '&format=arrow')).read_all()  # about ten times faster to fetch

For large results, Arrow Flight SQL is faster still (start the server with --flight 31337):

import adbc_driver_flightsql.dbapi as flight      # pip install adbc-driver-flightsql
with flight.connect('grpc://localhost:31337') as conn:
    df = conn.cursor().execute('SELECT * FROM orders WHERE qty > 5').fetch_df()

Packaged connectors (experimental)

The Vectro repository’s connectors/ folder has two connectors that put Vectro by name in each tool’s list. They are experimental: not signed, and not yet loaded in the real applications. The built-in PostgreSQL connectors above need nothing installed and are the safer choice.

Power BI: connectors/dist/Vectro.mez. It reads tables through the OData feed (Power Query folds filters, chosen columns and sorting into Vectro) or runs a SQL query of your own. Import mode only.

1. Start the HTTP server: npx vectro serve --root ~/data (add --token secret to require a token).
2. Copy Vectro.mez to Documents\Power BI Desktop\Custom Connectors (create the folder; older versions use Documents\Microsoft Power BI Desktop\Custom Connectors).
3. File > Options and settings > Options > Security > Data Extensions: allow extensions to load without validation, then restart Power BI Desktop.
4. Get Data > search Vectro (Beta). Server http://localhost:8787; leave the SQL box empty to choose tables, or write a SELECT.
5. Sign in with Anonymous, or Key with the token.

Tableau: connectors/dist/vectro.taco. A connector plugin for Vectro’s PostgreSQL protocol, using Tableau’s PostgreSQL SQL and the PostgreSQL JDBC driver.

1. Start Vectro with npx vectro serve --root ~/data --pg 5433 --user me --password secret.
2. Put the PostgreSQL JDBC driver (postgresql-42.x.jar) in C:\Program Files\Tableau\Drivers (Windows) or ~/Library/Tableau/Drivers (Mac).
3. Copy vectro.taco to Documents/My Tableau Repository/Connectors.
4. Start Tableau Desktop with signature checks off, since the connector isn’t signed:
   • Windows: "C:\Program Files\Tableau\Tableau <version>\bin\tableau.exe" -DDisableVerifyConnectorPluginSignature=true
   • Mac: open -n "/Applications/Tableau Desktop <version>.app" --args -DDisableVerifyConnectorPluginSignature=true
5. Connect > To a Server > Vectro: server localhost, port 5433, user and password.

The Power Query code parses with Microsoft’s parser, and the Tableau files validate against the Connector SDK’s schemas. Rebuild both with node connectors/build.mjs.

What to expect

• Tools can’t write back: INSERT, CREATE TABLE and the like are refused.
• Catalog questions about keys, indexes and comments get empty answers, since there are none.
• If a tool shows an error, run Vectro with --log-queries and report the statement it prints.