Connect Power BI Desktop to SingleStoreDB

To connect Microsoft Power BI Desktop to SingleStoreDB Cloud:

  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 workspace 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 Basic. Enter the username and password used to connect to SingleStoreDB Cloud, and select the Connect button.

    Note

    SingleStoreDB Cloud users can only use Basic authentication.

  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 Cloud databases.

SingleStoreDB Cloud 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 Cloud.:

  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…