Install ODBC Driver
Refer to this guide: Kylin ODBC Driver Tutorial.
Please make sure to download and install Kylin ODBC Driver v1.6 64 bit or above. If you already installed ODBC Driver in your system, please uninstall it first. already installed ODBC Driver in your system, please uninstall it first.
The Kylin ODBC driver needs to be installed in the machine or virtual environment where your Microstrategy Intelligenec Server is installed.
###Create Local DSN
Open your window ODBC Data Source Administrator (64bit) and create a system DSN that point to your kylin instance.
Setting Database Instance
Connect Kylin using ODBC driver: open your MicroStrategy Developer and connect to the project source where your are going to connect Kylin data source using a user account with administrative privilege.
Once logged in, go to
Configuration manager ->
Database Instance, create a new database instance with system DSN that you created in the previous step. Under database connection type, please choose Generic DBMS.
Depending on your business scenario, you may need to create a new project and set Kylin database instance as your primary database instance or if there is an existing project, set Kylin database instance as one of your primary or non-primary database instance. You can achieve this by right click on your project, and go to
project configuration ->
Import Logical Table
Open up your project, go to
warehouse catalog to import the tables your need.
Building Schema and Public Objects
Create Attribute, Facts and Metric objects
Create a Simple Report
Now you can start creating reports with Kylin as data source.
Best Practice for Connecting MicroStrategy to Kylin Data Source
Kylin does not work with multiple SQL passes at the moment, so it is recommended to set up your report intermediate table type as derived, you can change this setting at report level using
Intermediate Table Type
Avoid using below functionality in MicroStrategy as it will generate multiple sql passes that can not be bypassed by VLDB property:
Creation of datamarts
Query partitioned tables
Reports with custom groups
Dimension named with Kylin keywords will cause sql to error out. You may find Kylin keywords here, it is recommended to avoid naming the column name as Kylin keywords, especially when you use MicroStrategy as the front-end BI tool, as far as we know there is no setting in MicroStrategy that can escape the keyword. https://calcite.apache.org/docs30/reference.html#keywords
If underlying Kylin data model has left join from fact table to lookup table, In order for Microstrategy to also generate the same left join in sql, please follow below MicroStrategy TN to modify VLDB property:
By default, MicroStrategy generate SQL query with date filter in a format like ‘mm/dd/yyyy’. This format might be different from Kylin’s date format, if so, query will error out. You may follow below steps to change MicroStrategy to generate the same date format SQL as Kylin,
- go to
- Then right click on the database, choose VLDB properties.
- On the top menu choose
show Advanced Settings.
- Go to
- Change the date format to follow date format in Kylin, for example ‘yyyy-mm-dd’.
- Restart MicroStrategy Intelligence Server so that change can be effective.
- go to