This is an automated email from the ASF dual-hosted git repository.
xxyu pushed a commit to branch document
in repository https://gitbox.apache.org/repos/asf/kylin.git
commit b640f0a29b03a35f1b79faa11ecc750e7c0386c1
Author: Mukvin <boyboys...@163.com>
AuthorDate: Mon Jun 13 16:59:18 2022 +0800
Add new blog: kylin on cloud EN version
---
.../blog/2022-04-20-kylin4-on-cloud-part1.md | 319 +++++++++++++++++++++
.../blog/2022-04-20-kylin4-on-cloud-part2.md | 265 +++++++++++++++++
2 files changed, 584 insertions(+)
diff --git a/website/_posts/blog/2022-04-20-kylin4-on-cloud-part1.md
b/website/_posts/blog/2022-04-20-kylin4-on-cloud-part1.md
new file mode 100644
index 0000000000..62e17ccad2
--- /dev/null
+++ b/website/_posts/blog/2022-04-20-kylin4-on-cloud-part1.md
@@ -0,0 +1,319 @@
+---
+layout: post-blog
+title: Kylin on Cloud — Build A Data Analysis Platform on the Cloud in Two
Hours Part 1
+date: 2022-04-20 11:00:00
+author: Yaqian Zhang
+categories: blog
+---
+
+## Video Tutorials
+
+[Kylin on Cloud — Build A Data Analysis Platform on the Cloud in Two Hours
Part 1](https://youtu.be/5kKXEMjO1Sc)
+
+## Background
+
+Apache Kylin is a multidimensional database based on pre-computation and
multidimensional models. It also supports standard SQL query interface. In
Kylin, users can define table relationships by creating Models, define
dimensions and measures by creating Cubes, and run data aggregation with Cube
building. The pre-computed data will be saved to answer user queries and users
also can perform further aggregation on the pre-computed data, significantly
improving the query performance.
+
+With the release of Kylin 4.0, Kylin can now be deployed without a Hadoop
environment. To make it easier for users to deploy Kylin on the cloud, Kylin
community recently developed a cloud deployment tool that allows users to
obtain a complete Kylin cluster by executing just one line of command,
delivering a fast and efficient analysis experience for the users. Moreover, in
January 2022, the Kylin community released MDX for Kylin to enhance the
semantic capability of Kylin as a multidimen [...]
+
+With all these innovations, users can easily and quickly deploy Kylin clusters
on the cloud, create multi-dimensional models, and enjoy the short query
latency brought by pre-computation; what's more, users can also use MDX for
Kylin to define and manage business metrics, leveraging both the advantages of
data warehouse and business semantics.
+
+With Kylin + MDX for Kylin, users can directly work with BI tools for
multidimensional data analysis, or use it as the basis to build complex
applications such as metrics platforms. Compared with the solution of building
a metrics platform directly with computing engines such as Spark and Hive that
perform Join and aggregated query computation at runtime, Kylin, with our
multidimensional modeling, pre-computation technology, and semantics layer
capabilities empowered by MDX for Kylin, pr [...]
+
+This tutorial will start from a data engineer's perspective to show how to
build a Kylin on Cloud data analysis platform, which will deliver a
high-performance query experience for hundreds of millions of rows of data with
a lower TCO, the capability to manage business metrics through MDX for Kylin,
and direct connection to BI tools for quick reports generating.
+
+Each step of this tutorial is explained in detail with illustrations and
checkpoints to help newcomers. All you need to start is to an AWS account and 2
hours. Note: The cloud cost to finish this tutorial is around 15$.
+
+
+
+## Business scenario
+
+Since the beginning of 2020, COVID-19 has spread rapidly all over the world,
which has greatly changed people’s daily life, especially their travel habits.
This tutorial wants to learn the impact of the pandemic on the New York taxi
industry based on the pandemic data and New York taxi travel data since 2018
and indicators such as positive cases, fatality rate, taxi orders, and average
travel mileage will be analyzed. We hope this analysis could provide some
insights for future decision-making.
+
+### Business issues
+
+- The severity of the pandemic in different countries and regions
+- Travel metrics of different blocks in New York City, such as order number,
travel mileage, etc.
+- Does the pandemic have a significant impact on taxi orders?
+- Travel habits change after the pandemic (long-distance vs. short-distance
travels)
+- Is the severity of the pandemic strongly related to taxi travel?
+
+### Dataset
+
+#### COVID-19 Dataset
+
+The COVID-19 dataset includes a fact table `covid_19_activity` and a dimension
table `lookup_calendar`.
+
+`covid_19_activity` contains the number of confirmed cases and deaths reported
each day in different regions around the world. `lookup_calendar` is a date
dimension table that holds time-extended information, such as the beginning of
the year, and the beginning of the month for each date. `covid_19_activity` and
`lookup_calendar` are associated by date.
+COVID-19 数据集相关信息如下:
+
+|
------------------------|------------------------------------------------------------------------------------------------------------------|
+| Data size | 235 MB
|
+| Fact table row count | 2,753,688
|
+| Data range | 2020-01-21~2022-03-07
|
+| Download address provided by the dataset provider |
https://data.world/covid-19-data-resource-hub/covid-19-case-counts/workspace/file?filename=COVID-19+Activity.csv
|
+| S3 directory of the dataset |
s3://public.kyligence.io/kylin/kylin_demo/data/covid19_data/
|
+
+#### NYC taxi order dataset
+
+The NYC taxi order dataset consists of a fact table `taxi_trip_records_view`,
and two dimension tables, `newyork_zone` and `lookup_calendar`.
+
+Among them, each record in `taxi_trip_records_view` corresponds to one taxi
trip and contains information like the pick-up ID, drop-off ID, trip duration,
order amount, travel mileage, etc. `newyork_zone` records the administrative
district corresponding to the location ID. `taxi_trip_records_view` are
connected with `newyork_zone` through columns PULocationID and DOLocationID to
get the information about pick-up and drop-off blocks. `lookup_calendar` is the
same dimension table as in th [...]
+
+NYC taxi order dataset information：
+
+|
------------------------|----------------------------------------------------------------------|
+| Data size | 19 G
|
+| Fact table row count | 226,849,274
|
+| Data range | 2018-01-01~2021-07-31
|
+| Download address provided by the dataset provider |
https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page |
+| S3 directory of the dataset |
s3://public.kyligence.io/kylin/kylin_demo/data/trip_data_2018-2021/ |
+
+
+
+#### ER Diagram
+
+The ER diagram of the COVID-19 dataset and NYC taxi order dataset is as
follows:
+
+
+
+### Metrics design
+
+Based on what we try to solve with this model, we designed the following
atomic metrics and business metrics:
+
+###### 1. Atomic metrics
+
+Atomic metrics refer to measures created in Kylin Cube, which are relatively
simple, as they only run aggregated calculations on one column.
+
+- Covid19 case count: `sum(covid_19_activity.people_positive_cases_count)`
+- Covid19 fatality: `sum(covid_19_activity. people_death_count)`
+- Covid19 new positive case count: `sum(covid_19_activity.
people_positive_new_cases_count)`
+- Covid19 new death count: `sum(covid_19_activity. people_death_new_count)`
+- Taxi trip mileage: `sum(taxi_trip_records_view. trip_distance)`
+- Taxi order amount: `sum(taxi_trip_records_view. total_amount)`
+- Taxi trip count: `count()`
+- Taxi trip duration: `sum(taxi_trip_records_view.trip_time_hour)`
+
+###### 2. Business metrics
+
+Business metrics are various compound operations based on atomic metrics that
have specific business meanings.
+
+- MTD, YTD of each atomic metric
+- MOM, YOY of each atomic metric
+- Covid19 fatality rate: death count/positive case count
+- Average taxi trip speed: taxi trip distance/taxi trip duration
+- Average taxi trip mileage: taxi trip distance/taxi trip count
+
+## Operation Overview
+
+The diagram below is the main steps to build a cloud data analysis platform
with Apache Kylin and how to perform data analysis:
+
+
+
+## Cluster architecture
+
+Here is the architecture of the Kylin cluster deployed by the cloud deployment
tool:
+
+
+
+## Kylin on Cloud deployment
+
+### Prerequisites
+
+- GitHub Desktop: for downloading the deployment tool;
+- Python 3.6.6: for running the deployment tool
+
+### AWS permission check and initialization
+
+Log in to AWS with your account to check the permission status and then create
the Access Key, IAM Role, Key Pair, and S3 working directory according to the
document
[Prerequisites](https://github.com/apache/kylin/blob/kylin4_on_cloud/readme/prerequisites.md).
Subsequent AWS operations will be performed with this account.
+
+### Configure the deployment tool
+
+1. Execute the following command to clone the code for the Kylin on AWS
deployment tool.
+
+ ```shell
+ git clone -b kylin4_on_cloud --single-branch
https://github.com/apache/kylin.git && cd kylin
+ ```
+
+2. Initialize the virtual environment for your Python on local machine.
+
+ Run the command below to check the Python version. Note: Python 3.6.6 or
above is needed:
+
+ ```shell
+ python --version
+ ```
+
+ Initialize the virtual environment for Python and install dependencies:
+
+ ```shell
+ bin/init.sh
+ source venv/bin/activate
+ ```
+
+3. Modify the configuration file `kylin_configs.yaml`
+
+Open kylin_configs.yaml file, and replace the configuration items with the
actual values:
+
+- `AWS_REGION`: Region for EC2 instance, the default value is `cn-northwest-1`
+- `${IAM_ROLE_NAME}`: IAM Role just created, e.g. `kylin_deploy_role`
+- `${S3_URI}`: S3 working directory for deploying Kylin, e.g.
s3://kylindemo/kylin_demo_dir/
+- `${KEY_PAIR}`: Key pairs just created, e.g. kylin_deploy_key
+- `${Cidr Ip}`: IP address range that is allowed to access EC2 instances, e.g.
10.1.0.0/32, usually set as your external IP address to ensure that only you
can access these EC2 instances
+
+As Kylin adopts a read-write separation architecture to separate build and
query resources, in the following steps, we will first start a build cluster to
connect to Glue to create tables, load data sources, and submit build jobs for
pre-computation, then delete the build cluster but save the metadata. Then we
will start a query cluster with MDX for Kylin to create business metrics,
connect to BI tools for queries, and perform data analysis. Kylin on AWS
cluster uses RDS to store metadat [...]
+
+### Kylin build cluster
+
+#### Start Kylin build cluster
+
+1. Start the build cluster with the following command. The whole process may
take 15-30 minutes depending on your network conditions.
+
+ ```shell
+ python deploy.py --type deploy --mode job
+ ```
+
+2. You may check the terminal to see if the build cluster is successfully
deployed:
+
+
+
+#### Check AWS Service
+
+1. Go to CloudFormation on AWS console, where you can see 7 stacks are created
by the Kylin deployment tool:
+
+ 
+
+2. Users can view the details of EC2 nodes through the AWS console or use the
command below to check the names, private IPs, and public IPs of all EC2 nodes.
+
+```shell
+python deploy.py --type list
+```
+
+
+
+#### Spark-SQL query response time
+
+Let's first check the query response time in Spark-SQL environment as a
comparison.
+
+1. First, log in to the EC2 where Kylin is deployed with the public IP of the
Kylin node, switch to root user, and execute `~/.bash_profile` to implement the
environment variables set beforehand.
+
+ ```shell
+ ssh -i "${KEY_PAIR}" ec2-user@${kylin_node_public_ip}
+ sudo su
+ source ~/.bash_profile
+ ```
+
+2. Go to `$SPARK_HOME` to modify configuration file
`conf/spark-defaults.conf`, change spark_master_node_private_ip to a private IP
of the Spark master node:
+
+ ```shell
+ cd $SPARK_HOME
+ vim conf/spark-defaults.conf
+
+ ## Replace spark_master_node_private_ip with the private IP of the real
Spark master node
+ spark.master spark://spark_master_node_private_ip:7077
+ ```
+
+ In `spark-defaults.conf`, the resource allocation for driver and executor
is the same as that for Kylin query cluster.
+
+3. Create table in Spark-SQL
+
+ All data from the test dataset is stored in S3 bucket of `cn-north-1` and
`us-east-1`. If your S3 bucket is in `cn-north-1` or `us-east-1`, you can
directly run SQL to create the table; Or, you will need to execute the
following script to copy the data to the S3 working directory set up in
`kylin_configs.yaml`, and modify your SQL for creating the table:
+
+ ```shell
+ ## AWS CN user
+ aws s3 sync s3://public.kyligence.io/kylin/kylin_demo/data/ ${S3_DATA_DIR}
--region cn-north-1
+
+ ## AWS Global user
+ aws s3 sync s3://public.kyligence.io/kylin/kylin_demo/data/ ${S3_DATA_DIR}
--region us-east-1
+
+ ## Modify create table SQL
+ sed -i
"s#s3://public.kyligence.io/kylin/kylin_demo/data/#${S3_DATA_DIR}#g"
/home/ec2-user/kylin_demo/create_kylin_demo_table.sql
+ ```
+
+ Execute SQL for creating table:
+
+ ```shell
+ bin/spark-sql -f /home/ec2-user/kylin_demo/create_kylin_demo_table.sql
+ ```
+
+4. Execute query in Spark-SQL
+
+ Go to Spark-SQL:
+
+ ```shell
+ bin/spark-sql
+ ```
+
+ Run query in Spark-SQL:
+
+ ```sql
+ use kylin_demo;
+ select TAXI_TRIP_RECORDS_VIEW.PICKUP_DATE, NEWYORK_ZONE.BOROUGH, count(*),
sum(TAXI_TRIP_RECORDS_VIEW.TRIP_TIME_HOUR),
sum(TAXI_TRIP_RECORDS_VIEW.TOTAL_AMOUNT)
+ from TAXI_TRIP_RECORDS_VIEW
+ left join NEWYORK_ZONE
+ on TAXI_TRIP_RECORDS_VIEW.PULOCATIONID = NEWYORK_ZONE.LOCATIONID
+ group by TAXI_TRIP_RECORDS_VIEW.PICKUP_DATE, NEWYORK_ZONE.BOROUGH;
+ ```
+
+ We can see that with the same configuration as Kylin query cluster, direct
query using Spark-SQL takes over 100s:
+
+ 
+
+5. After the query is successfully executed, we should exit the Spark-SQL
before proceeding to the following steps to save resources.
+
+#### Import Kylin metadata
+
+1. Go to `$KYLIN_HOME`
+
+ ```shell
+ cd $KYLIN_HOME
+ ```
+
+2. Import metadata
+
+ ```shell
+ bin/metastore.sh restore /home/ec2-user/meta_backups/
+ ```
+
+3. Reload metadata
+
+Type `http://${kylin_node_public_ip}:7070/kylin` (relace the IP with the
public IP of the EC2 node) in your browser to log in to Kylin web UI, and log
in with the default username and password ADMIN/KYLIN:
+
+
+
+Reload Kylin metadata by clicking System - > Configuration - > Reload Metadata:
+
+
+
+If you'd like to learn how to manually create the Model and Cube included in
Kylin metadata, please refer to [Create model and cube in
Kylin](https://cwiki.apache.org/confluence/display/KYLIN/Create+Model+and+Cube+in+Kylin).
+
+#### Run build
+
+Submit the Cube build job. Since no partition column is set in the model, we
will directly perform a full build for the two cubes:
+
+
+
+
+
+#### Destroy build cluster
+
+After the building Job is completed, execute the cluster delete command to
close the build cluster. By default, the RDS stack, monitor stack, and VPC
stack will be kept.
+
+```shell
+python deploy.py --type destroy
+```
+
+Cluster is successfully closed:
+
+
+
+#### Check AWS resource
+
+After the cluster is successfully deleted, you can go to the `CloudFormation`
page in AWS console to confirm whether there are remaining resources. Since the
metadata RDS, monitor nodes, and VPC nodes are kept by default, you will see
only the following three stacks on the page.
+
+
+
+The resources in the three stacks will still be used when we start the query
cluster, to ensure that the query cluster and the build cluster use the same
set of metadata.
+
+#### Intro to next part
+
+That’s all for the first part of Kylin on Cloud —— Build A Data Analysis
Platform on the Cloud in Two Hours, please see part 2 here: [Kylin on Cloud ——
Quickly Build Cloud Data Analysis Service Platform within Two
Hours](../kylin4-on-cloud-part2/) (Part 2)
diff --git a/website/_posts/blog/2022-04-20-kylin4-on-cloud-part2.md
b/website/_posts/blog/2022-04-20-kylin4-on-cloud-part2.md
new file mode 100644
index 0000000000..0f27341c08
--- /dev/null
+++ b/website/_posts/blog/2022-04-20-kylin4-on-cloud-part2.md
@@ -0,0 +1,265 @@
+---
+layout: post-blog
+title: Kylin on Cloud — Build A Data Analysis Platform on the Cloud in Two
Hours Part 2
+date: 2022-04-20 11:00:00
+author: Yaqian Zhang
+categories: blog
+---
+
+
+This is the second part of the blog series, for part 1, see ：[Kylin on Cloud —
Build A Data Analysis Platform on the Cloud in Two Hours Part
1](../kylin4-on-cloud-part1/)
+
+### Video Tutorials
+
+[Kylin on Cloud — Build A Data Analysis Platform on the Cloud in Two Hours
Part 2](https://youtu.be/LPHxqZ-au4w)
+
+
+### Kylin query cluster
+
+#### Start Kylin query cluster
+
+1. Besides the `kylin_configs.yaml` file for starting the build cluster, we
will also enable MDX with the command below:
+
+ ```
+ ENABLE_MDX: &ENABLE_MDX 'true'
+ ```
+
+2. Then execute the deploy command to start the cluster:
+
+ ```
+ python deploy.py --type deploy --mode query
+ ```
+
+#### Query with Kylin
+
+1. After the query cluster is successfully started, first execute `python
deploy.py --type list` to get all node information, and then type in your
browser `http://${kylin_node_public_ip}:7070/kylin` to log in to Kylin web UI:
+
+ 
+
+2. Execute the same SQL on Insight page as what we have done with spark-SQL:
+
+ ```
+ select TAXI_TRIP_RECORDS_VIEW.PICKUP_DATE, NEWYORK_ZONE.BOROUGH, count(*),
sum(TAXI_TRIP_RECORDS_VIEW.TRIP_TIME_HOUR),
sum(TAXI_TRIP_RECORDS_VIEW.TOTAL_AMOUNT)
+ from TAXI_TRIP_RECORDS_VIEW
+ left join NEWYORK_ZONE
+ on TAXI_TRIP_RECORDS_VIEW.PULOCATIONID = NEWYORK_ZONE.LOCATIONID
+ group by TAXI_TRIP_RECORDS_VIEW.PICKUP_DATE, NEWYORK_ZONE.BOROUGH;
+ ```
+
+ 
+
+As we can see, when the query hits the cube, that is, the query is directly
answered by the pre-computed data, the query result is returned in about 4s, a
great reduction from the over 100s of query latency.
+
+### Pre-computation reduces query cost
+
+In this test, we used the New York taxi order data with fact table containing
200+ million entries of data. As we can see from the result, Kylin has
significantly improved the query efficiency in this big data analysis scenario
against hundreds of millions of data entries. Moreover, the build data could be
reused to answer thousands of subsequent queries, thereby reducing query cost.
+
+### Configure semantic layer
+
+#### Import Dataset into MDX for Kylin
+
+With `MDX for Kylin`, you can create `Dataset` based on the Kylin Cube, define
Cube relations, and create business metrics. To make it easy for beginners, you
can directly download Dataset file from S3 and import it into `MDX for Kylin`:
+
+1. Download the dataset to your local machine from S3.
+
+ ```
+ wget
https://s3.cn-north-1.amazonaws.com.cn/public.kyligence.io/kylin/kylin_demo/covid_trip_project_covid_trip_dataset.json
+ ```
+
+2. Access `MDX for Kylin` web UI
+
+ Enter `http://${kylin_node_public_ip}:7080` in your browser to access `MDX
for Kylin` web UI and log in with the default username and password
`ADMIN/KYLIN`:
+
+ 
+
+3. Confirm Kylin connection
+
+ `MDX for Kylin` is already configured with the information of the Kylin
node to be connected. You only need to type in the username and password
(`ADMIN/KYLIN`) for the Kylin node when logging in for the first time.
+
+ 
+
+ 
+
+4. Import Dataset
+
+ After Kylin is successfully connected, click the icon in the upper right
corner to exit the management page:
+
+ 
+
+ Switch to the `covid_trip_project` project and click `Import Dataset` on
`Dataset` page:
+
+ 
+
+ Select and import the `covid_trip_project_covid_trip_dataset.json` file we
just download from S3.
+
+ `covid_trip_dataset` contains specific dimensions and measures for each
atomic metric, such as YTD, MTD, annual growth, monthly growth, time hierarchy,
and regional hierarchy; as well as various business metrics including COVID-19
death rate, the average speed of taxi trips, etc. For more information on how
to manually create a dataset, see Create dataset in `MDX for Kylin` or [MDX for
Kylin User Manual](https://kyligence.github.io/mdx-kylin/).
+
+## Data analysis with BI and Excel
+
+### Data analysis using Tableau
+
+Let's take Tableau installed on a local Windows machine as an example to
connect to MDX for Kylin for data analysis.
+
+1. Select Tableau's built-in `Microsoft Analysis Service` to connect to `MDX
for Kylin`. (Note: Please install the [`Microsoft Analysis Services`
driver](https://www.tableau.com/support/drivers?_ga=2.104833284.564621013.1647953885-1839825424.1608198275)
in advance, which can be downloaded from Tableau).
+
+ 
+
+2. In the pop-up settings page, enter the `MDX for Kylin` server address, the
username and password. The server address is
`http://${kylin_node_public_ip}:7080/mdx/xmla/covid_trip_project`:
+
+ 
+
+3. Select covid_trip_dataset as the dataset:
+
+ 
+
+4. Then we can run data analysis with the worksheet. Since we have defined the
business metrics with `MDX for Kylin`, when we want to generate a business
report with Tableau, we can directly drag the pre-defined business metrics into
the worksheet to create a report.
+
+5. Firstly, we will analyze the pandemic data and draw the national-level
pandemic map with the number of confirmed cases and mortality rate. We only
need to drag and drop `COUNTRY_SHORT_NAME` under `REGION_HIERARCHY` to the
Columns field and drop and drop `SUM_NEW_POSITIVE_CASES` and `CFR_COVID19`
(fatality rate) under Measures to the Rows field, and then select to display
the data results as a map:
+
+ 
+
+ The size of the symbols represents the level of COVID-19 death count and
the shade of the color represents the level of the mortality rate. According to
the pandemic map, the United States and India have more confirmed cases, but
the mortality rates in the two countries are not significantly different from
the other countries. However, countries with much fewer confirmed cases, such
as Peru, Vanuatu, and Mexico, have persistently high death rates. You can
continue to explore the reaso [...]
+
+ Since we have set up a regional hierarchy, we can break down the
country-level situation to the provincial/state level to see the pandemic
situation in different regions of each country:
+
+ 
+
+ Zoom in on the COVID map to see the status in each state of the United
States:
+
+ 
+
+ It can be concluded that there is no significant difference in the
mortality rate in each state of the United States, which is around 0.01. In
terms of the number of confirmed cases, it is significantly higher in
California, Texas, Florida, and New York City. These regions are economically
developed and have a large population. This might be the reason behind the
higher number of confirmed COVID-19 cases. In the following part, we will
combine the pandemic data with the New York taxi [...]
+
+6. For the New York taxi order dataset, we want to compare the order numbers
and travel speed in different boroughs.
+
+Drag and drop `BOROUGH` under `PICKUP_NEWYORK_ZONE` to Columns, and drag and
drop `ORDER_COUNT` and `trip_mean_speed` under Measures to Rows, and display
the results as a map. The color shade represents the average speed and the size
of the symbol represents the order number. We can see that taxi orders
departing from Manhattan are higher than all the other boroughs combined, but
the average speed is the lowest. Queens ranks second in terms of order number
while Staten Island has the low [...]
+
+
+
+Then we will replace the field `BOROUGH` from `PICKUP_NEWYORK_ZONE` with
`BOROUGH` from `DROPOFF_NEWYORK_ZONE`, to analyze the number of taxi orders and
average speed by drop-off ID:
+
+
+
+The pick-up and drop-off data of Brooklyn, Queens, and Bronx differ greatly,
for example, the taxi orders to Brooklyn or Bronx are much higher than those
departing from there, while there are much fewer taxi trips to Queens than
those starting from it.
+
+- Travel habits change after the pandemic (long-distance vs. short-distance
travels)
+
+To analyze the average trip mileage we can get the residents’ travel habit
changes, drag and drop dimension `MONTH_START` to Rows, and drag and drop the
metric `trip_mean_distance` to Columns:
+
+
+
+Based on the histogram we can see that there have been significant changes in
people’s travel behavior before and after the outbreak of COVID-19, as the
average trip mileage has increased significantly since March 2020 and in some
months is even several times higher, and the trip mileage of each month
fluctuated greatly. We can combine these data with the pandemic data in the
month dimension, so we drag and drop `SUM_NEW_POSITIVE_CASES` and
`MTD_ORDER_COUNT` to Rows and add `PROVINCE_STA [...]
+
+
+
+It is interesting to see that the number of taxi orders decreased sharply at
the beginning of the outbreak while the average trip mileage increased,
indicating people have cut unnecessary short-distance travels or switched to a
safer means of transportation. By comparing the data curves, we can see that
the severity of the pandemic and people’s travel patterns are highly related,
taxi orders drop and average trip mileage increases when the pandemic worsens,
while when the situation impro [...]
+
+### Data analysis via Excel
+
+With `MDX for Kylin`, we can also use Kylin for big data analysis with Excel.
In this test, we will use Excel installed on a local Windows machine to connect
MDX for Kylin.
+
+1. Open Excel, select `Data` -> `Get Data` -> `From Database` -> `From
Analysis Services`:
+
+ 
+
+2. In `Data Connection Wizard`, enter the connection information as the server
name:`http://${kylin_node_public_ip}:7080/mdx/xmla/covid_trip_project`:
+
+ 
+
+ 
+
+3. Then create a PivotTable for this data connection. We can see the data
listed here is the same as that when we are using Tableau. So no matter whether
analysts are using Tableau or Excel, they are working on identical sets of data
models, dimensions, and business metrics, thereby realizing unified semantics.
+
+4. We have just created a pandemic map and run a trend analysis using
`covid19` and `newyork_trip_data` with Tableau. In Excel, we can check more
details for the same datasets and data scenarios.
+
+- For COVID-19 related data, we add `REGION_HIERARCHY` and pre-defined
`SUM_NEW_POSITIVE_CASES` and mortality rate `CFR_COVID19` to the PivotTable:
+
+
+
+The highest level of the regional hierarchy is `CONTINENT_NAME`, which
includes the number of confirmed cases and mortality rate in each continent. We
can see that Europe has the highest number of confirmed cases while Africa has
the highest mortality rate. In this PivotTable, we can easily drill down to
lower regional levels to check more fine-grained data, such as data from
different Asian countries, and sort them in descending order according to the
number of confirmed cases:
+
+
+
+The data shows that India, Turkey, and Iran are the countries with the highest
number of confirmed cases.
+
+- Regarding the problem, does the pandemic have a significant impact on taxi
orders, we first look at the YTD and growth rate of taxi orders from the year
dimension by creating a PivotTable with `TIME_HIERARCHY`, `YOY_ORDER_COUNT`,
and `YTD_ORDER_COUNT` as the dimension for time hierarchy:
+
+
+
+It can be seen that since the outbreak of the pandemic in 2020, there is a
sharp decrease in taxi orders. The growth rate in 2020 is -0.7079, that is, a
reduction of 70% in taxi orders. The growth rate in 2021 is still negative, but
the decrease is not so obvious compared to 2020 when the pandemic just started.
+
+Click to expand the time hierarchy to view the data at quarter, month, and
even day levels. By selecting `MOM_ORDER_COUNT` and `ORDER_COUNT`, we can check
the monthly order growth rate and order numbers in different time hierarchies:
+
+
+
+The order growth rate in March 2020 was -0.52, which is already a significant
fall. The rate dropped even further to -0.92 in April, that is, a 90% reduction
in orders. Then the decreasing rate becomes less obvious. But taxi orders were
still much lower than before the outbreak.
+
+### Use API to integrate Kylin with data analysis platform
+
+In addition to mainstream BI tools such as Excel and Tableau, many companies
also like to develop their in-house data analysis platforms. For such
self-developed data analysis platforms, users can still use Kylin + MDX for
Kylin as the base for the analysis platform by calling API to ensure a unified
data definition. In the following part, we will show you how to send a query to
MDX for Kylin through Olap4j, the Java library similar to JDBC driver that can
access any OLAP service.
+
+We also provide a simple demo for our users, you may click [mdx query
demo](https://github.com/apache/kylin/tree/mdx-query-demo) to download the
source code.
+
+1. Download jar package for the demo:
+
+ ```
+ wget
https://s3.cn-north-1.amazonaws.com.cn/public.kyligence.io/kylin/kylin_demo/mdx_query_demo.tgz
+ tar -xvf mdx_query_demo.tgz
+ cd mdx_query_demo
+ ```
+
+2. Run demo
+
+Make sure Java 8 is installed before running the demo:
+
+
+
+Two parameters are needed to run the demo: the IP of the MDX node and the MDX
query to be run. The default port is 7080. The MDX node IP here is the public
IP of the Kylin node.
+
+```
+java -cp
olap4j-xmla-1.2.0.jar:olap4j-1.2.0.jar:xercesImpl-2.9.1.jar:mdx-query-demo-0.0.1.jar
io.kyligence.mdxquerydemo.MdxQueryDemoApplication "${kylin_node_public_ip}"
"${mdx_query}"
+```
+
+Or you could just enter the IP of the MDX node, the system will automatically
run the following MDX statement to count the order number and average trip
mileage of each borough according to the pickup ID:
+
+```
+SELECT
+{[Measures].[ORDER_COUNT],
+[Measures].[trip_mean_distance]}
+DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_ORDINAL],[MEMBER_CAPTION] ON
COLUMNS,
+NON EMPTY [PICKUP_NEWYORK_ZONE].[BOROUGH].[BOROUGH].AllMembers
+DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_ORDINAL],[MEMBER_CAPTION] ON
ROWS
+FROM [covid_trip_dataset]
+```
+
+We will also use the default query in this tutorial. After the execution is
completed, we can get the query result in the command line:
+
+
+
+As you can see, we have successfully obtained the data needed. The result
shows that the largest number of taxi orders are from Manhattan, with an
average order distance of only about 2.4 miles, which is reasonable if we
consider the area and dense population in Manhattan; while the average distance
of orders departing from Bronx is 33 miles, much higher than any other
boroughs, probably due to Bronx's remote location.
+
+As with Tableau and Excel, the MDX statement here can directly use the metrics
defined in Kylin and MDX for Kylin. Users can do further analysis of the data
with their own data analysis platform.
+
+### Unified data definition
+
+We have demonstrated 3 ways to work with Kylin + MDX for Kylin, from which we
can see that with the help of Kylin multi-dimensional database and MDX for
Kylin semantic layer, no matter which data analytic system you are using, you
can always use the same data model and business metrics and enjoy the
advantages brought by unified semantics.
+
+## Delete clusters
+
+### Delete query cluster
+
+After the analysis, we can execute the cluster destruction command to delete
the query cluster. If you also want to delete metadata database RDS, monitor
node and VPC of Kylin and MDX for Kylin, you can execute the following cluster
destroy command:
+
+```
+python deploy.py --type destroy-all
+```
+
+### Check AWS resources
+
+After all cluster resources are deleted, there should be no Kylin deployment
tool-related Stack on `CloudFormation`. If you also want to delete the
deployment-related files and data from S3, you can manually delete the
following folders under the S3 working directory:
+
+
+
+## Summary
+
+You only need an AWS account to follow the steps in this tutorial to explore
our Kylin deployment tool on the Cloud. Kylin + MDX for Kylin, with our
pre-computation technology, multi-dimensional models, and basic metrics
management capabilities, enables users to build a big data analysis platform on
the cloud in a convenient way. In addition, we also support seamless connection
to mainstream BI tools, helping our users to better leverage their data with
higher efficiency and the lowest TCO.
