Power BI

Power BI Desktop connects to Syntra through the built-in ODBC data source. You don’t need any extra connector, add-in, or custom data gateway — the Syntra installer already registered the driver and a default DSN on the machine.

Prerequisites

• Syntra installed and running (the default Syntra QuickBooks System DSN is present).
• Power BI Desktop (current version).
• The account running Power BI Desktop must be able to read the System DSN. If Power BI shows an empty DSN list, make sure the DSN exists under System DSN (not only User DSN) in odbcad32.

Connect

Step 1 — open the ODBC connector

1. Open Power BI Desktop.
2. On the Home ribbon, click Get data → More….
3. In the Get Data dialog, pick Other in the left pane, select ODBC, and click Connect.

Step 2 — pick the DSN

1. In Data source name (DSN), choose Syntra QuickBooks from the dropdown.
2. Leave the Advanced options section collapsed unless you need to pass a custom connection string; the DSN already has everything.
3. Click OK.

If the DSN isn’t listed, use Advanced options → Connection string (non-credential properties) and paste:

Driver={Syntra ODBC - QuickBooks ODBC};Server=127.0.0.1;Port=5433;Database=qbconnect;

Step 3 — authenticate

Power BI prompts for credentials the first time you connect:

1. Click Database on the left.
2. Enter the username and password from the [auth] section of config.toml (installer defaults are qbconnect / changeme — change them in production).
3. Click Connect.

Step 4 — pick tables

The Navigator window lists every QuickBooks entity (customers, invoices, items_inventory, bills, accounts, etc.). Tick the tables you need, then click Load to import or Transform data to stage them in Power Query first.

Import vs. DirectQuery

Mode	Behaviour	When to use
Import	Power BI pulls the data once into its in-memory engine and refreshes on a schedule. Fast visuals, offline-capable.	The normal choice for dashboards and monthly reporting.
DirectQuery	Power BI sends each visual’s query back to Syntra on every click. Near-real-time but slower.	When freshness matters more than visual responsiveness.

For most QuickBooks reporting scenarios, Import is the right pick. Schedule refreshes in the Power BI Service to keep the dataset current.

Custom SQL

Rather than loading entire tables, you can write a SQL query that Syntra executes once and Power BI treats as a single result set:

1. In the ODBC data source dialog, expand Advanced options.
2. Paste the query into SQL statement (optional):

SELECT
    c.full_name                AS Customer,
    SUM(i.balance_remaining)   AS TotalOutstanding,
    COUNT(*)                   AS InvoiceCount
FROM invoices i
JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.is_paid = false
GROUP BY c.full_name
ORDER BY TotalOutstanding DESC

3. Click OK.

Scheduled refresh in the Power BI Service

To refresh after publishing to the Power BI Service:

1. Install the On-premises data gateway (standard mode) on the machine running Syntra (or any machine that can reach it on port 5433).
2. Configure an ODBC data source in the gateway pointing at the same DSN (Syntra QuickBooks) and the same credentials.
3. Publish your Power BI dataset and bind it to the gateway under Settings → Datasets → Gateway connection.
4. Set a refresh schedule on the dataset.

The gateway must have the Syntra ODBC driver installed (re-run the Syntra installer on the gateway host if needed).

Tips

• Relationships. Power BI can auto-detect relationships using list_id ↔ *_ref_list_id columns. Review them in the Model view; Syntra exposes all foreign-key references as plain columns, so the auto-detector catches most joins but occasionally picks the wrong pair (especially for templates and terms). Edit relationships in the Model view when in doubt.
• Date tables. Build a dedicated date dimension in Power BI for time intelligence measures (YTD, MTD, YoY). QuickBooks’ own txn_date columns are transactional, not a continuous calendar.
• Import filters. Limit the transaction-table imports with a WHERE clause on txn_date (last 24 months, last fiscal year, etc.). Pulling the full history of a 10-year file slows both the initial import and every refresh.
• Custom fields. QuickBooks user-defined fields appear as custom_* columns. See Custom Fields.
• Numeric types. Money and quantities come through as Decimal and preserve precision end-to-end. Avoid the Double type for financial measures — use Fixed decimal number instead.