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:
|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.
Follow these steps to download and install the prerequisite software.
Download and install the latest version of Power BI Desktop listed in the certification matrix above.
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.
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
Navigate to the MYSQL ODBC Connector site.
5.3.12and then choose your
OS Versionfrom 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.
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.
If you are using Windows, open the search bar and type
Set up data sources (ODBC).
Configure a new ODBC data source for SingleStore DB.
Testto test the connectivity and click
Connecting Power BI to SingleStore DB
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.
In Power BI Desktop, navigate to the ribbon and click Get Data.
In the Get Data window, select MemSQL Direct Query Connector 1.0 (Beta) on the right.
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.
For server, specify the IP address or host name where the master aggregator node resides. Then, select a Data Connectivity mode and click OK.
Select the table(s) that you want to import and click Load.
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….