Some additional information in one line

Microsoft Excel is arguably the most indispensable piece of software in Microsoft's Office Suite, and its extreme flexibility and ease-of-use have made it a workhorse for nearly every area of business analysis. Unfortunately, as analytics operations mature, Excel is often replaced by more powerful tools like Tableau, Power BI, and other business intelligence (BI) offerings.

Investing in these new tools can be costly and can disrupt formally productive users by forcing them to adjust their workflows. In many cases, abandoning Excel may not be necessary, and this tutorial will show you how easy it is to get started using Excel as a BI tool for your analytics with the help of StarRocks.

 

Using Excel as a BI Tool with StarRocks

StarRocks is a next-gen, high-performance analytical database that enables real-time, multi-dimensional, and highly concurrent data analysis. StarRocks is compatible with MySQL protocols and can be easily used with the MySQL client and various BI tools.

You can directly use Excel to send queries and fetch results to and from StarRocks. The retrieved data can then be used for further downstream analysis within Excel. In this tutorial, we will go over how to use Microsoft Excel to connect to StarRocks on MacOS.

 

Pre-Requisites

  • StarRocks: V2.5+ recommended for better data type compatibility. Download the latest version here.

  • Microsoft Excel: tested version - V16.71 (23031200), other versions should also work.

  • MacOS: tested version - 12.2.1 (21D62), other versions should also work.

  • CPU architecture: X86 or Apple Silicon

 

Install Dependencies

First, you'll need to install a few dependencies on your Mac:

  • ODBC manager

  • iODBC

  • MySQL ODBC connector

 

How to install ODBC manager

Download link: http://www.odbcmanager.net/

 
ODBC Manager 1

 

ODBC Manager 2

 

How to install iODBC

Download link: https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads

Choose the appropriate version and set up the iODBC.

 

Install iODBC-1

 

How to install the MySQL ODBC connector and configure DSN

Download link: https://dev.mysql.com/downloads/connector/odbc/

 

Configurations

Set up DSN in ODBC Manager

Step 1: Open ODBC Manager.

 

ODBC Manager Icon

 

Step 2: In System DSN , choose myodbc and click on Configure

 

ODBC Configure

 

Step 3: Edit the corresponding values to the correct figure.

 

ODBC Correct Figure

 

Server: server address of StarRocks FE, make sure the network is accessible. If the IP is localhost, you need to write 127.0.0.1 for compatibility.

Port: query_port of StarRocks FE, the default port is 9030

Database (Optional): database you wish to query from.

 

Step 4: Copy related files

Now we need to copy some files. Depending on the ODBC version you are using, you need to:

  • ODBC 5: Please copy ibmyodbc5a.so, libmyodbc5w.so (under

/usr/local/mysql-connector-odbc-5.x.xx-macosxx-arm64/lib ) to /Library/ODBC/

  • ODBC 8 (required for Apple Scilicon): Please copy libcrypto.1.1.dylib, libmyodbc8a.so, libmyodbc8w.so, libssl.1.1.dylib (under /usr/local/mysql-connector-odbc-8.x.xx-macosxx-arm64/lib) to /Library/ODBC/

 

Step 5: Modify the /Library/ODBC/odbc.ini

Depending on the ODBC version you are using, you need to:

  • If you use ODBC 5, modify the Driver under [myodbc]to /Library/ODBC/libmyodbc5w.so

  • If you use ODBC 8, modify the Driver under [myodbc]to /Library/ODBC/libmyodbc8w.so

 

ODBC Library

ODBC 5

 

ODBC 8

ODBC 8

 

Note: every time you modify the DSN, please remember to check the Driver in /Library/ODBC/odbc.ini .

 

Test DSN in iODBC Administrator

Step 1: Open iODBC Administrator64 (x86) or iODBC Administrator (Apple Scilicon)

 

iODBC Administrator

 

Step 2: In System DSN, choose the myodbc

 

MYODBC

 

Step 3: Click Test, Enter the username and password of StarRocks.

 

SR Test

 

If the DSN was tested successfully. It's ready to use in Excel.

 

DSN MYODBC

 

Using Excel To Connect to StarRocks

Step 1: Open Excel, choose Data->New Database Query->From Database

 

Connect Excel Database

 

Step 2: Choose the DSN you set up earlier, myodbc, and click Test, enter your username and password for StarRocks.

 

Excel MYODBC

 

Excel DSN MYODBC

 

This indicates the connection is successful. Then you can click ok to enter your username and password and make the connection.

 

Step 3: Then you can enter SQLs in Microsoft Query to query StarRocks.

Now try to run some queries and export to excel:

 

Excel Run Query

 

Click Return Data to load data in Excel for further analysis.

 

Excel Return Data

 

You’re Now Ready To Start Using Excel as a Bi Tool

Following this tutorial, you're now ready to use Excel to connect to StarRocks and retrieve query results. Download StarRocks now to get started. And if you've found this guide useful and would like to discover more ways StarRocks can help you deliver fast, fresh, and flexible analytics, check out the StarRocks blog for more ideas.