Connect Power BI Desktop to SingleStoreDB

To connect Microsoft Power BI Desktop to SingleStoreDB:

  1. In the Home ribbon, from the Get Data list, select More.

  2. In the Get Data dialog, select SingleStore Direct Query Connector.

  3. In the SingleStore database dialog, enter the IP address or hostname of the cluster and the database name in the Server and Database boxes, respectively.

  4. Select the Data Connectivity mode (Import or DirectQuery), and then select the OK button.

  5. In the connector dialog, in the left pane, select the authentication type (Windows or Basic).

    • For Windows authentication, set up a SingleStoreDB server for Kerberos authentication. Then, in the connector dialog, select the Connect button.

      Note

      You need to run Power BI with the user account that maps to the SingleStoreDB user. So, if the Windows user is 'administrator'@domain, then the database user should be 'administrator'. You may need to create the database user.

    • For Basic authentication, enter the username and password used to connect to SingleStoreDB, and select the Connect button.

  6. After authentication, select the tables to import in the Navigator dialog, and then select the Load button. Alternatively, to specify a custom SQL query to import data, see Create a Custom SQL Report.

    16083b578b4dbc.png

You can now use Power BI Desktop to explore your data.

Create a Custom SQL Report

Note

To create a custom SQL report using the connector, the user must only have read-only access to the SingleStoreDB databases.

SingleStoreDB does not support creating a custom SQL report with DirectQuery mode as per Microsoft's requirements.

To create a new custom SQL report:

  1. Open Power BI Desktop.

  2. In the Home ribbon, from the Get Data list, select Blank query.

    custom_get_data_powerbi.png
  3. In the Power Query Editor dialog, specify the query in the following format:

    SingleStoreODBC.Query("<ip_address_or_hostname>", "<database>", "<query>")

    Submit the query.

    Note

    If you're using the server for the first time, select Edit Credentials and enter the credentials. See Step 5 above for more information.

    edit_credentials_powerbi.png
  4. Verify the data to load, and select Close & Apply.

  5. If you've worked with the dataset before and it's cached in your memory, you need to refresh the report to reset the local cache. On the Home ribbon, select Refresh.

To update the existing custom SQL reports, select the Refresh button on the Home ribbon.

Modify Credentials

To modify the credentials used to connect to SingleStoreDB.:

  1. In the File ribbon, select Options and settings > Data source settings.

  2. In the Data source settings dialog, select the connector, and then select Edit Permissions…