title: 'Microsoft Power BI Connector' sidebar_label: 'Microsoft Power BI' sidebar_position: 11 description: 'Use Microsoft Power BI to access, visualize and analyze Spice datasets.' pagination_prev: 'clients/index' pagination_next: null

import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';

Use the instructions below to get started with the Spice.ai Power BI Connector (opens in a new tab)—an ADBC (opens in a new tab)-based connector that enables Microsoft Power BI (opens in a new tab) users to easily connect to and visualize data loaded in Spice.ai Enterprise (opens in a new tab) and Spice Cloud Platform (opens in a new tab) instances.

Manual Connector Installation

Power BI Desktop

1. Download the latest spice_adbc.mez file from the releases page (opens in a new tab)

2. Copy to your Power BI Custom Connectors directory: C:\Users\[USERNAME]\Documents\Microsoft Power BI Desktop\Custom Connectors

   Invoke-WebRequest -Uri "https://github.com/spiceai/powerbi-connector/releases/latest/download/spice_adbc.mez" -OutFile "C:\Users\[USERNAME]\Documents\Microsoft Power BI Desktop\Custom Connectors\spice_adbc.mez"
   

   Invoke-WebRequest -Uri "https://github.com/spiceai/powerbi-connector/releases/latest/download/spice_adbc.mez" -OutFile "C:\Users\[USERNAME]\Documents\Microsoft Power BI Desktop\Custom Connectors\spice_adbc.mez"
   

3. Enable Uncertified Connectors (opens in a new tab) in Power BI Desktop settings and restart Power BI Desktop.

Adding Spice as a Data Source

1. Open Power BI Desktop.

2. Click on Get Data → More....

3. In the dialog, select Spice.ai connector.

4. Click Connect.

5. Enter the ADBC (Arrow Flight SQL) Endpoint:

   • For Spice Cloud Platform:
     grpc+tls://flight.spiceai.io:443
     (Use the region-specific address if applicable.)
   • For on-premises/self-hosted Spice.ai:
     • Without TLS (default): grpc://<server-ip>:50051
     • With TLS: grpc+tls://<server-ip>:50051

6. Select the Data Connectivity mode:
   • Import: Data is loaded into Power BI, enabling extensive functionality but requiring periodic refreshes and sufficient local memory to accommodate the dataset.
   • DirectQuery: Queries are executed directly against Spice in real-time, providing fast performance even on large datasets by leveraging Spice's optimized query engine.
7. Click OK.
8. Select Authentication option:
   • Anonymous: Select for unauthenticated on-premises deployments.
   • API Key: Your Spice.ai API key for authentication (required for Spice Cloud). Follow the guide (opens in a new tab) to obtain it from the Spice Cloud portal.

9. Click Connect to establish the connection.

Working with Spice datasets

After establishing a connection, Spice datasets appear under their respective schemas, with the default schema being spice.public. When writing native queries, use the PostgreSQL dialect, as Spice is built on this standard.

Supported Data Types

The following Apache Arrow / DataFusion SQL types are supported. Other types will result in a Unable to understand the type for column error. Please report an issue (opens in a new tab) if support for additional types is required.

Limitations

LargeUtf8 Data Type Is Not Supported

To work around this limitation, use views (opens in a new tab) to convert LargeUtf8 columns to Utf8 by casting them with arrow_cast(<column>, 'Utf8'). A ::TEXT cast does not produce Utf8: it plans as Utf8View.

Example:

views:
  - name: taxi_zone_lookup
    sql: |
      SELECT
        LocationID,
        arrow_cast(Borough, 'Utf8') AS Borough,
        arrow_cast(Zone, 'Utf8') AS Zone,
        arrow_cast(service_zone, 'Utf8') AS service_zone
      FROM taxi_zone_lookup_temp

views:
  - name: taxi_zone_lookup
    sql: |
      SELECT
        LocationID,
        arrow_cast(Borough, 'Utf8') AS Borough,
        arrow_cast(Zone, 'Utf8') AS Zone,
        arrow_cast(service_zone, 'Utf8') AS service_zone
      FROM taxi_zone_lookup_temp

Date Time Arithmetic Operations Are Not Supported

Due to lack of support for the timestampdiff function in the DataFusion query engine (opens in a new tab), date and time arithmetic operations—such as subtracting or adding timestamps and intervals—are not supported and will result in an error similar to Invalid function 'timestampdiff'.\nDid you mean 'to_timestamp'? (Internal; ExecuteQuery). For example:

(parameter) =>
let
    Sorted = Table.Sort(parameter[taxi_table], {"RecordID"}),
    T2 = Table.SelectColumns(Sorted, {"PULocationID","lpep_pickup_datetime"}),
    T3 = Table.Sort(T2, {"PULocationID"}),
    T4 = Table.AddColumn(T3, "Diff1", each [lpep_pickup_datetime] - #datetime(1999,1,5,0,0,0))
    TA = Table.FirstN(T6, 4)
in
    TA

(parameter) =>
let
    Sorted = Table.Sort(parameter[taxi_table], {"RecordID"}),
    T2 = Table.SelectColumns(Sorted, {"PULocationID","lpep_pickup_datetime"}),
    T3 = Table.Sort(T2, {"PULocationID"}),
    T4 = Table.AddColumn(T3, "Diff1", each [lpep_pickup_datetime] - #datetime(1999,1,5,0,0,0))
    TA = Table.FirstN(T6, 4)
in
    TA

ADBC: InternalError [] [FlightSQL] [FlightSQL] Error during planning: Invalid function 'timestampdiff'.\nDid you mean 'to_timestamp'? (Internal; ExecuteQuery)

ADBC: InternalError [] [FlightSQL] [FlightSQL] Error during planning: Invalid function 'timestampdiff'.\nDid you mean 'to_timestamp'? (Internal; ExecuteQuery)

Please report an issue (opens in a new tab) if support for date or time arithmetic operations is required.