Getting Started with the MariaDB Direct Query Adapter for Microsoft Power BI

By Todd Stoffel

16 Dec 20204 min read

We are excited to announce the availability of a new Microsoft Power BI Desktop integration with MariaDB. The MariaDB Direct Query Adapter enables Microsoft Power BI Desktop users to connect to, query, model and visualize their data without downloading the complete data set locally. The MariaDB Direct Query Adapter for Power BI supports data stored in MariaDB on prem and in the cloud on MariaDB SkySQL. The adapter is Microsoft certified and available as a menu option from within Power BI. Microsoft Power BI is a popular data visualization and BI tool with a global user base. Power BI lets analysts connect to multiple sources of data, such as MariaDB, and create data models and shareable visual representations such as charts and graphs.

Extending Your Capabilities

The MariaDB Direct Query Adapter lets Power BI users leverage the MariaDB database layer directly to handle operations, increasing query performance. MariaDB Platform can be configured for standard transactions, analytics or both. The easiest way to experience the power and versatility of MariaDB is through SkySQL. In the case of analytics, MariaDB uses a columnar smart engine designed to handle extremely large data sets and the ad hoc queries of analytical workloads. Data is written to disk by column rather than row and is automatically partitioned. Columnar data storage is designed to deliver real time analysis on billions of rows. SkySQL analytical service is stored within object storage, which is highly scalable and cost-efficient, making it extremely appealing, especially compared to having to set up and maintain a separate data warehouse solution. MariaDB Direct Query Adapter enables Power BI users to work with remote data without the need to download it onto the local machine. Downloading analytical data sets can present challenges such as download speed and inadequate memory on a local machine, and even a powerful laptop or desktop will be outperformed by the processing power of a high end database server or MPP system. MariaDB’s Direct Query Adapter avoids these limitations and provides robust querying and data visualization from any location.

Download and Install Software

Using the MariaDB Direct Query Adapter for Power BI, requires:

1. Download MariaDB ODBC Connector

a. Choose MariaDB ODBC for MS Windows 64-bit and click “Download”.

b. Choose “Open” to start the MariaDB ODBC Connector 64-bit Setup Wizard and click “Next”.

c. Read and accept the terms and agreement and click “Next”.

d. Choose “Typical” for installation type and click “Install”.

e. When asked if you want to allow this app to make changes to your device, click “Yes”.

f. If you have older versions of MariaDB ODBC Connector 64-bit, select “Make User DSN’s for older Connector version to use this version” and click “Finish”. Otherwise, just click “Finish”.

2. Download Microsoft Power BI Desktop using the Microsoft instructions.

Connect to Power BI Desktop

1. Open Power BI Desktop and choose “Get Data” and then “More”. Microsoft Power BI Desktop

2. Type MariaDB into the search box to quickly find the MariaDB Direct Query Adapter. Select “MariaDB” and click “Connect”. Power BI Search for MariaDB Query Adapter

Power BI Search for MariaDB Query Adapter

3. Connect to your remote MariaDB database, such as MariaDB SkySQL, by entering the connection details. Here is an example: servername:portnumber;databasename. Connect Power BI to MariaDB

a. Enter the data source. This will be an IP address and port number, or in the case of SkySQL, it will be the qualified domain name and port number listed in the connection details of your SkySQL service.

b. Choose “DirectQuery” and click “OK”. c. Enter your user name and password and click “Connect”. You are now connected to your remote MariaDB database and can choose your data source and run queries and create visuals with Microsoft Power BI. The Power BI Desktop Navigator, lets you view and select schemas that Power BI will use to create data models and analyze and aggregate data natively in MariaDB and rapidly deliver result sets for interactive, visual, and shareable reports. Microsoft Power BI Desktop Navigator

We want to hear your experience with the new MariaDB Direct Query Adapter for Power BI. Submit feedback by clicking the green button to open a new issue in the source repo.