Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

Connecting to Power BI

This topic describes how to connect Microsoft Power BI to SingleStore DB.

The certification matrix below shows the latest versions tested for SingleStore DB and Looker:

Certification Matrix Versions
Power BI Desktop 2.6x, 2.7x, 2.8x
MemSQL/SingleStore DB 6.x, 7.x

Data Connectivity Modes

Using the SingleStore DB (MemSQL) Direct Query Connector in Power BI, you can connect to a SingleStore DB data source in Import or DirectQuery mode.

When you use Import mode, PowerBI imports the data from the data source. If the data in the data source changes, you will need to refresh the data source to see the changes.

When you use DirectQuery mode, you work directly with the data source.

For a further explanation of the Import and DirectQuery modes, see this article.

Prerequisites

Follow these steps to download and install the prerequisite software.

  1. Download and install the latest version of Power BI Desktop listed in the certification matrix above.

  2. Download the SingleStore DB (MemSQL) Direct Query Connector 1.0 file. After you have downloaded the file, put it in directory C:\Users\YourUserName\Documents\Microsoft Power BI Desktop\Custom Connectors.

  3. Download and install the MySQL ODBC driver as explained in the following section. The MySQL ODBC 5.3 driver requires that the Visual C++ Redistributable Packages for Visual Studio 2013 be installed first.

Download and Install ODBC Connector for MySQL

  1. Navigate to the MYSQL ODBC Connector site.

  2. Select Product Version as 5.3.12 and then choose your Operating System and OS Version from the drop-down lists. Newer 8.x versions of the ODBC connector may require additional configuration changes when connecting to a cluster. See the SingleStore DB documentation for more information.

    image

  3. Pick the appropriate installer option for your system and then click Download. After the download finishes, install the driver.

Configure MySQL ODBC Connector

You need an existing ODBC connection before connecting Tableau with SingleStore DB.

  1. If you are using Windows, open the search bar and type ODBC.

  2. Click Set up data sources (ODBC).

  3. Configure a new ODBC data source for SingleStore DB.

    a. Specify TCP/IP Server and Port as 3306.

    b. Specify User, Password, and Database.

    image

  4. Click Test to test the connectivity and click OK.

Connecting Power BI to SingleStore DB

  1. Open Power BI Desktop. On the menu bar, select File | Options and Settings | Options. Then, on the Options screen, select Security on the left pane. In the Data Extensions section, select (Not recommended) Allow any extension to load without validation or warning. This will allow PowerBI to use the SingleStore (MemSQL) Direct Query Connector 1.0 file that you installed previously. image

  2. In Power BI Desktop, navigate to the ribbon and click Get Data. image

  3. Select More. image

  4. In the Get Data window, select MemSQL Direct Query Connector 1.0 (Beta) on the right. image

  5. The MemSQLDirect Query Connector 1.0 screen appears. On the left pane, select Basic. Then specify the user name and password used to connect to SingleStore DB and click Connect. image

  6. For server, specify the IP address or host name where the master aggregator node resides. Then, select a Data Connectivity mode and click OK. image

  7. Select the table(s) that you want to import and click Load. image

You can now use Power BI to explore your SingleStore DB data.

To change the credentials used to connect to SingleStore DB, select File | Options and settings, followed by Data source settings. On the Data source settings screen, select MemSQL DirectQuery Connector 1.0 and click Edit Permissions….