Power BI

Power BI Desktop can connect to Syntra ODBC using its built-in PostgreSQL connector. This gives you full access to QuickBooks data for building interactive dashboards and reports.

Prerequisites

• Syntra ODBC installed and running
• Power BI Desktop (latest version recommended)

Connecting Power BI

Step 1: Get Data

1. Open Power BI Desktop.
2. Click Get Data on the Home ribbon.
3. Search for PostgreSQL and select PostgreSQL database.
4. Click Connect.

Step 2: Enter Connection Details

Field	Value
Server	localhost:5433
Database	quickbooks

Leave Data Connectivity mode set to Import for best performance.

Step 3: Authenticate

1. Click the Database tab on the left.
2. Enter your username (syntra) and password.
3. Click Connect.

Step 4: Select Tables

The Navigator window displays all available QuickBooks tables. Check the tables you want to import, then click Load or Transform Data.

Using DirectQuery vs. Import

Mode	Description
Import	Data is loaded into Power BI’s in-memory engine. Best for performance and offline use. Refresh on a schedule.
DirectQuery	Queries are sent to Syntra on each interaction. Use for near-real-time dashboards, but expect slower visuals.

For most QuickBooks reporting scenarios, Import mode is recommended. Schedule refreshes in the Power BI Service to keep data current.

Writing Custom SQL

To use a custom query instead of loading entire tables:

1. In the Get Data dialog, expand Advanced options.
2. Paste your SQL statement:

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

3. Click OK to load the query results.

Scheduled Refresh (Power BI Service)

To set up automatic data refresh after publishing to the Power BI Service:

1. Install the On-premises data gateway on the machine running Syntra.
2. Register the gateway in the Power BI Service.
3. Configure the data source credentials in the gateway settings.
4. Set a refresh schedule on your published dataset.

Tips

• Relationships: Power BI can auto-detect relationships between tables using list_id and *_ref_list_id columns. Review them in the Model view.
• Date tables: Create a dedicated date table in Power BI for time intelligence calculations rather than relying on QuickBooks date columns.
• Performance: Use Import mode and limit the date range of imported data with WHERE clauses to keep datasets manageable.