Repository: kylin Updated Branches: refs/heads/document 08ab05ec4 -> c1af8222f
add tutorial for MicroStrategy Signed-off-by: shaofengshi <shaofeng...@apache.org> Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/6555f6d2 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/6555f6d2 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/6555f6d2 Branch: refs/heads/document Commit: 6555f6d2628f190cfd3442732875b83120e88312 Parents: 08ab05e Author: joannahe <hejin...@gmail.com> Authored: Thu Aug 31 16:30:58 2017 +0800 Committer: shaofengshi <shaofeng...@apache.org> Committed: Thu Aug 31 16:35:00 2017 +0800 ---------------------------------------------------------------------- website/_docs21/tutorial/microstrategy.md | 80 +++++++++++++++++++ website/images/tutorial/2.1/MicroStrategy/1.png | Bin 0 -> 207356 bytes .../images/tutorial/2.1/MicroStrategy/10.png | Bin 0 -> 185351 bytes website/images/tutorial/2.1/MicroStrategy/2.png | Bin 0 -> 154312 bytes website/images/tutorial/2.1/MicroStrategy/3.png | Bin 0 -> 345509 bytes website/images/tutorial/2.1/MicroStrategy/4.png | Bin 0 -> 187026 bytes website/images/tutorial/2.1/MicroStrategy/5.png | Bin 0 -> 158694 bytes website/images/tutorial/2.1/MicroStrategy/6.png | Bin 0 -> 143233 bytes website/images/tutorial/2.1/MicroStrategy/7.png | Bin 0 -> 125824 bytes website/images/tutorial/2.1/MicroStrategy/8.png | Bin 0 -> 283974 bytes website/images/tutorial/2.1/MicroStrategy/9.png | Bin 0 -> 357212 bytes 11 files changed, 80 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/_docs21/tutorial/microstrategy.md ---------------------------------------------------------------------- diff --git a/website/_docs21/tutorial/microstrategy.md b/website/_docs21/tutorial/microstrategy.md new file mode 100644 index 0000000..8a7cc45 --- /dev/null +++ b/website/_docs21/tutorial/microstrategy.md @@ -0,0 +1,80 @@ +--- +layout: docs21 +title: MicroStrategy 10 +categories: tutorial +permalink: /docs21/tutorial/microstrategy.html +--- + +## Integration from MicroStrategy 10.X + +### Install ODBC Driver + +Refer to this guide: [Kylin ODBC Driver Tutorial](./odbc.html). +Please make sure to download and install Kylin ODBC Driver __v1.6__ 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. + +### Connect to Kylin Server + +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 `Administration` -> `Configuration manager` -> `Database Instance`, create a new database instance with local ODBC connection that you created in the previous step. Under database connection type, please choose Generic DBMS. + + + +### Setting Database instance + +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` -> `database instance`. + + + +### Import logical table + +Open up your project, go to `schema` -> `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 datasource + +1. 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 `Data`-> `VLDB property`-> `Tables`-> `Intermediate Table Type` + +2. 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 + +3. 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 I am concern there is no setting MicroStrategy that can escape the keyword. [https://calcite.apache.org/docs/reference.html#keywords](https://calcite.apache.org/docs/reference.html#keywords) + +4. 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: + + [https://community.microstrategy.com/s/article/ka1440000009GrQAAU/KB17514-Using-the-Preserve-all-final-pass-result-elements-VLDB](https://community.microstrategy.com/s/article/ka1440000009GrQAAU/KB17514-Using-the-Preserve-all-final-pass-result-elements-VLDB) + +5. 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, + + 1. go to `Instance` -> `Administration` -> `Configuration Manager` -> `Database Instance`. + 2. Then right click on the database, choose VLDB properties. + 3. On the top menu choose `Tools` -> `show Advanced Settings`. + 4. Go to `select/insert` -> `date format`. + 5. Change the date format to follow date format in kylin, for example 'yyyy-mm-dd'. + 6. Restart MicroStrategy Intelligence Server so that change can be effective. \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/1.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/1.png b/website/images/tutorial/2.1/MicroStrategy/1.png new file mode 100644 index 0000000..cc22454 Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/1.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/10.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/10.png b/website/images/tutorial/2.1/MicroStrategy/10.png new file mode 100644 index 0000000..59d215e Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/10.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/2.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/2.png b/website/images/tutorial/2.1/MicroStrategy/2.png new file mode 100644 index 0000000..9d75e70 Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/2.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/3.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/3.png b/website/images/tutorial/2.1/MicroStrategy/3.png new file mode 100644 index 0000000..4222c4e Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/3.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/4.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/4.png b/website/images/tutorial/2.1/MicroStrategy/4.png new file mode 100644 index 0000000..fb2fadd Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/4.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/5.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/5.png b/website/images/tutorial/2.1/MicroStrategy/5.png new file mode 100644 index 0000000..58a92db Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/5.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/6.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/6.png b/website/images/tutorial/2.1/MicroStrategy/6.png new file mode 100644 index 0000000..992207a Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/6.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/7.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/7.png b/website/images/tutorial/2.1/MicroStrategy/7.png new file mode 100644 index 0000000..d240ed5 Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/7.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/8.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/8.png b/website/images/tutorial/2.1/MicroStrategy/8.png new file mode 100644 index 0000000..f45d316 Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/8.png differ http://git-wip-us.apache.org/repos/asf/kylin/blob/6555f6d2/website/images/tutorial/2.1/MicroStrategy/9.png ---------------------------------------------------------------------- diff --git a/website/images/tutorial/2.1/MicroStrategy/9.png b/website/images/tutorial/2.1/MicroStrategy/9.png new file mode 100644 index 0000000..aacee60 Binary files /dev/null and b/website/images/tutorial/2.1/MicroStrategy/9.png differ
