Connecting to Power BI
This topic describes how to connect Microsoft Power BI to SingleStore.
The certification matrix below shows the latest versions tested for SingleStore and Power BI:
|Power BI Desktop||2.6x, 2.7x, 2.8x|
Data Connectivity Modes
Using the SingleStore Direct Query Connector in Power BI, you can connect to a SingleStore 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.
Note: SingleStore connector only supports 64-bit versions of Power BI
Download and install the SingleStore Power BI Bundle file. It will install SingleStore ODBC driver and set up Power BI connector.
Connecting Power BI to SingleStore
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 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 SingleStore Direct Query Connector 1.0 (Beta) on the right.
For server, specify the IP address or host name where the SingleStore master aggregator node resides. Then, select a Data Connectivity mode and click OK.
The SingleStore 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.
Select the table(s) that you want to import and click Load.
You can now use Power BI to explore your SingleStore data.
To change the credentials used to connect to SingleStore, select File | Options and settings, followed by Data source settings. On the Data source settings screen, select SingleStore DirectQuery Connector 1.0 and click Edit Permissions….