Using Excel as a Bi Tool for StarRocks on Mac
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.
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
First, you'll need to install a few dependencies on your Mac:
MySQL ODBC connector
How to install ODBC manager
Download link: http://www.odbcmanager.net/
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.
How to install the MySQL ODBC connector and configure DSN
Download link: https://dev.mysql.com/downloads/connector/odbc/
Set up DSN in ODBC Manager
Step 1: Open ODBC Manager.
Step 2: In
System DSN , choose
myodbc and click on
Step 3: Edit the corresponding values to the 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.
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
/usr/local/mysql-connector-odbc-5.x.xx-macosxx-arm64/lib ) to
ODBC 8 (required for Apple Scilicon): Please copy
Step 5: Modify the
Depending on the ODBC version you are using, you need to:
If you use ODBC 5, modify the
If you use ODBC 8, modify the
Note: every time you modify the DSN, please remember to check the
Test DSN in iODBC Administrator
Step 1: Open iODBC Administrator64 (x86) or iODBC Administrator (Apple Scilicon)
Step 2: In
System DSN, choose the
Step 3: Click Test, Enter the
password of StarRocks.
If the DSN was tested successfully. It's ready to use in Excel.
Using Excel To Connect to StarRocks
Step 1: Open Excel, choose
Data->New Database Query->From Database
Step 2: Choose the DSN you set up earlier,
myodbc, and click
Test, enter your username and password for StarRocks.
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:
Return Data to load data in Excel for further analysis.
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.