Microsoft Excel is one of the most famous data tool on Windows platform, and has plenty of data analyzing functions. With Power Query installed as plug-in, excel can easily read data from ODBC data source and fill spreadsheets.
Microsoft Power BI is a business intelligence tool providing rich functionality and experience for data visualization and processing to user.
Apache Kylin currently doesn’t support query on raw data yet, some queries might fail and cause some exceptions in application. Patch KYLIN-1075 is recommended to get better look of query result.
Power BI and Excel do not support “connect live” model for other ODBC driver yet, please pay attention when you query on huge dataset, it may pull too many data into your client which will take a while even fail at the end.
Install ODBC Driver
Refer to this guide: Kylin ODBC Driver Tutorial.
Please make sure to download and install Kylin ODBC Driver v1.2. If you already installed ODBC Driver in your system, please uninstall it first.
Kylin and Excel
Download Power Query from Microsoft’s Website and install it. Then run Excel, switch to
Power Queryfast tab, click
From Other Sourcesdropdown list, and select
From ODBCdialog, just type Database Connection String of Apache Kylin Server in the
Connection Stringtextbox. Optionally you can type a SQL statement in
SQL statementtextbox. Click
OK, result set will run to your spreadsheet now.
Tips: In order to simplify the Database Connection String, DSN is recommended, which can shorten the Connection String like
DSN=[YOUR_DSN_NAME]. Details about DSN, refer to https://support.microsoft.com/en-us/kb/305599.
If you didn’t input the SQL statement in last step, Power Query will list all tables in the project, which means you can load data from the whole table. But, since Apache Kylin cannot query on raw data currently, this function may be limited.
Hold on for a while, the data is lying in Excel now.
If you want to sync data with Kylin Server, just right click the data source in right panel, and select
Refresh, then you’ll see the latest data.
To improve data loading performance, you can enable
Fast data loadin Power Query, but this will make your UI unresponsive for a while.
Run Power BI Desktop, and click
Get Databutton, then select
ODBCas data source type.
Same with Excel, just type Database Connection String of Apache Kylin Server in the
Connection Stringtextbox, and optionally type a SQL statement in
SQL statementtextbox. Click
OK, the result set will come to Power BI as a new data source query.
If you didn’t input the SQL statement in last step, Power BI will list all tables in the project, which means you can load data from the whole table. But, since Apache Kylin cannot query on raw data currently, this function may be limited.
Now you can start to enjoy analyzing with Power BI.
To reload the data and redraw the charts, just click