Connect to MatrixOne with Power BI

Power BI Desktop connects to MatrixOne through the preview MatrixOne.mez custom connector, which uses ODBC with DirectQuery enabled and default port 6001. This guide covers connector placement, enabling unsigned extensions, Import and DirectQuery modes, and diagnostics.

Overview

The MatrixOne.mez connector is a Power BI custom connector that uses Odbc.DataSource. Its data-source kind is MatrixOne and its entry point is MatrixOne.Contents. The connector enables DirectQuery, uses username/password authentication, and requests opportunistic TLS with SSLMODE=PREFERRED. TLS certificate-verification modes have not yet been validated for this release. The MEZ is an unsigned Power Query extension; Power Query’s signed/trusted distribution flow uses PQX. Separately, the v9.7.0-mo.3 MSI and both driver DLLs are not Authenticode-signed; see the ODBC installation security notice.

Before you start

  • You have completed Install and Start MatrixOne.

  • The matching 64-bit MatrixOne ODBC 9.7 Unicode Driver is installed.

  • Power BI Desktop is installed.

Syntax

The connector expects a server string in the form server[:port][;database]:

server[:port][;database]

Arguments

Argument

Required

Description

server

Yes

MatrixOne host name or IP address.

port

No

MatrixOne listener port. Defaults to 6001 when omitted.

database

No

The database to connect to, separated from the server by ;.

Examples

Connect to MatrixOne on the default port:

localhost:6001

Connect to a specific database on the default port:

127.0.0.1:6001;test

Install the connector

Place MatrixOne.mez in a Power BI custom connectors directory. A default Windows MSI install deploys it to both:

  • Documents\Power BI Desktop\Custom Connectors

  • Documents\Microsoft Power BI Desktop\Custom Connectors

The two locations cover the traditional and Microsoft Store Power BI Desktop layouts. When scheduled refresh uses an on-premises data gateway, the gateway must have the same connector and driver versions as Power BI Desktop.

Enable the unsigned connector

The preview connector is unsigned. In Power BI Desktop, enable loading of unvalidated custom extensions under Options and settings > Options > Security > Data Extensions, restart Power BI, and then select MatrixOne Database with DirectQuery Support from Get data. Production distribution should sign the connector and restore the validated-extension policy.

Import and DirectQuery modes

  • Import imports the selected tables into the model.

  • DirectQuery pushes grouped aggregates to MatrixOne and keeps the data live.

The connector disables ODBC parameter binding to avoid a MatrixOne result-mapping defect with parameter-array SELECT emulation (matrixone#27034), and disables CAST-based decimal conversion folding so Power Query decimal semantics are not pushed down incorrectly. It also disables ClientConnectionPooling so a DirectQuery session can open a fresh physical connection after MatrixOne restarts.

Diagnostics

Isolate failures in this order:

  1. Connect with the MySQL command-line client. A failure here is below ODBC.

  2. Connect with isql/iusql or an ODBC API test. Record SQLSTATE, native error, driver version, and server version.

  3. Call SQLGetInfo, SQLGetTypeInfo, SQLTables, and SQLColumns. Navigator failures usually appear here.

  4. Test Import mode to separate connector discovery and type conversion from DirectQuery SQL generation.

  5. Test DirectQuery, capture the generated SQL, and replay it against MatrixOne.

For Power Query details, make a development build with EnableTraceOutput = true in pbi/MatrixOne.pq, reproduce once, then restore it to false. Only native authentication errors 1044 and 1045 invalidate stored Power BI credentials; syntax, metadata, and query errors are returned unchanged so they do not trigger a misleading password prompt.

Known limitations

This section is scoped to connector/driver v9.7.0-mo.3 (commit 5cc5e1297f93809b1ea7352a1881b67c22b3a3ff) tested with MatrixOne commit 01ae8eca88110dc29d2f51e421570bb3d850d328.

  • PAD_CHAR_TO_FULL_LENGTH does not pad CHAR results over ODBC (matrixone#27036, open).

  • The prepared COUNT(?) defect is tracked by matrixone#26994, not #27034, and is closed upstream. The parameter-array UNION ALL branch-order defect is matrixone#27034, also closed upstream; the mo.3 connector still carries its parameter-binding workaround. Verify server commit ancestry before removing that workaround.

On-premises data gateway scheduled refresh, signed trusted-extension policy, and TLS certificate verification modes remain not yet validated.

matrixone#27640 is not listed as an mo.3 limitation: it was reproduced with a newer MatrixOne main and ODBC PR #24, while mo.3 sets Config_UseParameterBindings = false.