Connecting to Power BI
This topic describes how to connect Microsoft Power BI to MemSQL.
The certification matrix below shows the latest versions tested for MemSQL and Looker:
|Power BI Desktop||2.6x, 2.7x, 2.8x|
|MemSQL Database||6.x, 7.x|
Data Connectivity Modes
Using the MemSQL Direct Query Connector in Power BI, you can connect to a MemSQL 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 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.
Connecting Power BI to MemSQL
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 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 MemSQL and click Connect.
For server, specify the IP address or host name where the MemSQL 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 MemSQL data.
To change the credentials used to connect to MemSQL, 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….