Running Cube Queries
Running Cube Queries from BI Tools
You can run queries against Cube using BI tools that supports ODBC/JDBC data source connection.
ODBC Driver Installation and Setup
To install ODBC driver and set up ODBC connections, follow these steps:
- Click here to download the ODBC driver.
- Open ODBCAD to configure DSN.
- For 32 bit driver, use the 32bit version in C:\Windows\SysWOW64\odbcad32.exe
- For 64 bit driver, use the default "Data Sources (ODBC)" in Control Panel/Administrator Tools
- On the ODBC Data Source Administrator dialog box, click System DSN tab and click Add.
- Create New Data Source dialog box is displayed as shown below:
- Select KylinODBCDriver and click Finish. Kylin DSN Configuration Dialog box is displayed as shown below.
- Enter all the required values and click Done. On the ODBC Data Source Administrator box, you can now see the new DSN listed in the System Data Sources, as shown below.
- JDBC Driver Installation and Setup
- Download JDBC driver from here.
- Add downloaded JDBC driver jar to your application/Client classpath
- Use below jdbc connection details to connect to Cubes:
Running Cube Queries from Tableau
To access cubes through Tableau, follow these steps:
- Connect to REST server using the ODBC driver. Start Tableau desktop, click Other Database(ODBC) in the left panel and choose KylinODBCDriver in the pop-up window, as shown below.
- On the Kylin Driver Connect Dialog box, as shown below, enter the required Sever location, credentials and project.
If you click Connect, you can see the list of projects that you have permission to access.
- In the left panel, select defaultCatalog as Database.
- Click Search in Table search box. All the tables will be listed. If you drag and drop the tables to the right panel, tables will become data sources, as shown below.
NOTE: Make sure JOINs are configured correctly.
- To connect live, select the Live option under Connection shown in the figure above.
- To use customized SQL, click New Custom SQL and type the SQL statement in the dialog box that pops up.
- The Visualization screen displays.
- To publish the local dashboard to a Tableau Server, click Server menu and select Publish Workbook, as shown below.
Running Cube Queries from MS Excel
Microsoft Excel has plenty of data analyzing functions. With Power Query installed as a plugin, Excel can easily read data from ODBC data source and fill spreadsheets.
You can access cube through Kylin ODBC driver from Excel with Power Query included as a plugin.
Prerequisites
Following are the prerequisites to access Cube from Excel:
Operating Systems
The following operating systems support the Power Query:
- Windows Server 2008 R2
- Windows Server 2012
- Windows 7
- Windows 8
- Windows 8.1
Microsoft Office Versions
- Microsoft Office 2010 Professional Plus with Software Assurance
- Microsoft Office 2013:
Power Query Premium: All Power Query features available for Professional Plus, Office 365 ProPlus, or Excel 2013 Standalone.
Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following:
- Corporate Power BI Data Catalog
- Azure-based data sources
- Active Directory
- HDFS
- SharePoint Lists
- Oracle
- DB2
- MySQL
- PostgreSQL
- Sybase
- Teradata
- Exchange
- Dynamics CRM
- SAP BusinessObjects
- Salesforce
Microsoft Power Query for Excel requires Internet Explorer 9 or greater. Microsoft Power Query for Excel is available for 32-bit (x86) and 64-bit (x64) platforms, your selection must match the architecture of the installed version of Office.
Microsoft Visual C++ 2012 Redistributable
- ODBC driver internally gets results from a REST server, make sure you have access to it.
Procedure
- To access cubes from MS Excel, follow these steps:
- Open MS Excel.
- On the Power Query tab, click From Other Sources ribbon, and select From ODBC, as shown below.
- The From ODBC dialog box is displayed, as shown below.
- In the Connection string text box, type in the Database Connection String of Apache Kylin Server. Optionally, you can type an SQL statement in SQL statement textbox.
- Click OK. The results would be shown in the spreadsheet.