Author: lidong
Date: Thu Mar 31 14:09:40 2022
New Revision: 1899440
URL: http://svn.apache.org/viewvc?rev=1899440&view=rev
Log:
# add blog: how to use excel to query kylin
Added:
kylin/site/blog/2022/03/31/
kylin/site/blog/2022/03/31/how-to-use-excel-to-query-kylin/
kylin/site/blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
kylin/site/cn_blog/2022/03/31/
kylin/site/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/
kylin/site/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
kylin/site/images/blog/how_to_use_excel_to_query_kylin/
kylin/site/images/blog/how_to_use_excel_to_query_kylin/10_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/10_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/11_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/11_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/12_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/12_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/13_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/13_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/14_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/14_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/15_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/15_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/16_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/16_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/17_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/17_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/18_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/18_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/1_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/1_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/2_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/2_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/3_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/3_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/4_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/4_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/5_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/5_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/6_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/6_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/7_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/7_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/8_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/8_use_excel_to_query_kylin.en.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/9_use_excel_to_query_kylin.cn.png
(with props)
kylin/site/images/blog/how_to_use_excel_to_query_kylin/9_use_excel_to_query_kylin.en.png
(with props)
Modified:
kylin/site/blog/index.html
kylin/site/cn/blog/index.html
kylin/site/feed.xml
Added: kylin/site/blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
URL:
http://svn.apache.org/viewvc/kylin/site/blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html?rev=1899440&view=auto
==============================================================================
--- kylin/site/blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
(added)
+++ kylin/site/blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html Thu
Mar 31 14:09:40 2022
@@ -0,0 +1,677 @@
+<!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+<!doctype html>
+<html>
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<head>
+ <meta charset="utf-8">
+ <meta http-equiv="X-UA-Compatible" content="IE=edge">
+ <meta name="viewport" content="width=device-width, initial-scale=1">
+
+ <title>Apache Kylin | How to use Excel to query Kylin? MDX for Kylin!</title>
+ <meta name="description" content="Abstract">
+ <meta name="author" content="Apache Kylin">
+ <link rel="shortcut icon" href="fav.png" type="image/png">
+
+
+
+<link rel="stylesheet" href="/assets/css/animate.css">
+<!-- Bootstrap -->
+<link rel="stylesheet" href="/assets/css/bootstrap.min.css">
+
+<!-- Fonts -->
+<!-- <link rel="stylesheet"
href="http://fonts.googleapis.com/css?family=Alice|Open+Sans:400,300,700"> -->
+
+<!-- Icons -->
+<link rel="stylesheet" href="/assets/css/font-awesome.min.css">
+
+ <!-- Custom styles -->
+ <link rel="stylesheet" href="/assets/css/styles.css">
+ <link rel="stylesheet" href="/assets/css/docs.css">
+ <link rel="stylesheet" href="/assets/css/pygments.css">
+
+ <link rel="canonical"
href="http://kylin.apache.org/blog/2022/03/31/how-to-use-excel-to-query-kylin/";>
+ <link rel="alternate" type="application/rss+xml" title="Apache Kylin"
href="http://kylin.apache.org/feed.xml"; />
+
+<!--[if lt IE 9]> <script src="assets/js/html5shiv.js"></script> <![endif]-->
+<!-- Global site tag (gtag.js) - Google Analytics -->
+<script async
src="https://www.googletagmanager.com/gtag/js?id=UA-120788561-1";></script>
+<script>
+ window.dataLayer = window.dataLayer || [];
+ function gtag(){dataLayer.push(arguments);}
+ gtag('js', new Date());
+
+ gtag('config', 'UA-120788561-1');
+</script>
+<script type="text/javascript" src="/assets/js/jquery-1.9.1.min.js"></script>
+<script type="text/javascript" src="/assets/js/nside.js"></script> </script>
+<script type="text/javascript" src="/assets/js/nnav.js"></script> </script>
+<script>
+var _hmt = _hmt || [];
+(function() {
+ var hm = document.createElement("script");
+ hm.src = "https://hm.baidu.com/hm.js?bdc5e03add430c0b72cc0eb91eabfa99";;
+ var s = document.getElementsByTagName("script")[0];
+ s.parentNode.insertBefore(hm, s);
+})();
+</script>
+
+</head>
+
+ <body>
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<header id="header" >
+
+ <!-- Main Menu -->
+ <nav class="navbar navbar-default" role="navigation" id="nav-wrapper">
+ <div class="container-fluid" id="nav">
+ <!--
+ <img class="img-circle" width="40px" height="40px" id="circlelogo"
src="/assets/images/kylin_logo.jpg">
+ -->
+ <!-- Brand and toggle get grouped for better mobile display -->
+ <div class="navbar-header">
+ <img class="navbar-logo" width="46"
src="/assets/images/kylin_logo.png" ></img>
+ <button type="button" class="navbar-toggle collapsed"
data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
+ <span class="sr-only">Toggle navigation</span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ </button>
+ <ul class="nav icon-navbar">
+ <li><a href="https://twitter.com/apachekylin"; target="_blank"
class="fa fa-twitter fa-lg" title="Twitter: @ApacheKylin" ></a></li>
+ <li><a href="https://github.com/apache/kylin"; target="_blank"
class="fa fa-github-alt fa-lg" title="Github: apache/kylin" ></a></li>
+ <li><a href="https://www.facebook.com/kylinio"; target="_blank"
class="fa fa-facebook fa-lg" title="Facebook: kylin.io" ></a></li>
+ </ul>
+ </div>
+
+ <!-- Collect the nav links, forms, and other content for toggling -->
+ <div class="navbar-collapse collapse" id="bs-example-navbar-collapse-1">
+
+ <ul class="nav navbar-nav">
+
+ <li><a href="/">Home</a></li>
+ <li>
+ <a href="/docs" class="dropdown-toggle" data-toggle="dropdown"
role="button" aria-haspopup="true" aria-expanded="false">Docs<span
class="caret"></span></a>
+ <ul class="dropdown-menu">
+ <li><a href="/docs/">Latest Release(Kylin 4.0.1)</a></li>
+ <li><a href="/docs31/">Kylin 3.1.3</a></li>
+ <li><a href="/docs24/">Kylin 2.4.0</a></li>
+ <li><a href="/archive/">Archive</a></li>
+ </ul>
+ </li>
+ <li><a href="/download">Download</a></li>
+ <li><a href="/community" >Community</a></li>
+ <li>
+ <a href="/development" class="dropdown-toggle"
data-toggle="dropdown" role="button" aria-haspopup="true"
aria-expanded="false">Development<span class="caret"></span></a>
+ <ul class="dropdown-menu">
+ <li><a href="/development40/">Kylin 4.x</a></li>
+ <li><a href="/development/">Kylin 3.x And Older Versions</a></li>
+ </ul>
+ </li>
+ <li><a href="/blog">Blog</a></li>
+ <li><a href="/cn" >ä¸æç</a></li>
+ </ul>
+ </div><!-- /.navbar-collapse -->
+ </div><!-- /.container-fluid -->
+ </nav>
+
+ <div id="head" class="parallax normal-header" >
+ <div class="text-center header-apache">
+ <a href="http://apache.org/foundation/contributing.html"; title="Support
Apache" style="margin-left: 150px;">
+ <div>
+ <img src="https://www.apache.org/images/SupportApache-small.png"; >
+ </div>
+ </a>
+ </div>
+ </div>
+
+ </header>
+
+ <div class="page-content main">
+ <header style=" padding:2em 0 0 ">
+ <div class="container" >
+ <div style=" padding:0 4em">
+ <div class="blog-icon">
+ <img width="30" src="/assets/images/icon_blog_w.png">
+ </div>
+ <h4 class="index-title" style="
float:left;"><span>Apache Kylin⢠Technical Blog</span></h4>
+ </div>
+ </div>
+ </div>
+
+ <div class="container blog">
+ <div>
+ <article class="post-content" >
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<div class="post" style=" padding:2em 4em 4em 4em">
+
+ <header class="post-header">
+ <h1 class="post-title">How to use Excel to query Kylin? MDX for Kylin!</h1>
+ <p class="post-meta" >Mar 31, 2022 ⢠Xiaoxiang Yu</p>
+ </header>
+
+ <article class="post-content" >
+ <h2 id="abstract"><strong>Abstract</strong></h2>
+
+<p>During the <a
href="https://lists.apache.org/thread/4fkhyw1fyf0jg5cb18v7vxyqbn6vm3zv";>Kylin
community discussion</a> at the beginning of this year, we talked about the
positioning of multidimensional databases and the idea of building a
Kylin-based business semantic layer. After some development efforts, we are
delighted to announce the beta release of the <strong>MDX</strong> <strong>for
Kylin</strong> <strong>, an MDX query engine for Apache Kylin</strong> to allow
Kylin users to use <strong>Excel</strong> for data analysis.</p>
+
+<h4 id="target-audiences"><strong>Target audiences</strong></h4>
+
+<ul>
+ <li>
+ <p>Kylin users who are not familiar with <strong>MDX</strong></p>
+ </li>
+ <li>
+ <p>Data engineers who are interested in building a <strong>metrics
platform</strong> based on Kylin</p>
+ </li>
+ <li>
+ <p>Data analysts who are interested in massive data analysis with
<strong>Excel</strong></p>
+ </li>
+</ul>
+
+<h4 id="what-you-will-learn"><strong>What you will learn</strong></h4>
+
+<ul>
+ <li>
+ <p>Basic concepts of MDX and MDX for Kylin</p>
+ </li>
+ <li>
+ <p>Quickstart tutorial for MDX for Kylin</p>
+ </li>
+ <li>
+ <p>Demonstration of how to use MDX for Kylin to define complex business
metrics</p>
+ </li>
+</ul>
+
+<h2 id="why-kylin-need-mdx"><strong>Why Kylin need MDX?</strong></h2>
+
+<h3
id="multidimensional-database-and-business-semantic-layer"><strong>Multidimensional
database and business semantic layer</strong></h3>
+
+<p>The primary difference between multidimensional databases and relational
databases lies in business semantics. As the must-have skill of data analysts,
SQL (Structured Query Language) is extremely expressive, but if we are talking
in the context of âevery professional will be an analystâ, it is still too
complex for non-technical users. For them, data lakes and data warehouses are
like dark rooms that hold a huge amount of data; they cannot see, understand,
or use the data for lack of the fundamental knowledge of databases and SQL
syntax.</p>
+
+<p>How to make data lakes and data warehouses <strong>âeasyâ</strong> for
a non-technical user to use? One solution is to introduce a more user-friendly
<strong>ârelational data model - multidimensional data modelâ</strong>. If
relational models are to provide a technique-oriented description of the data,
multidimensional models intend to provide a business-oriented description of
the data. In multidimensional databases, measures correspond to the business
metrics that everyone is familiar with. Measures provide the analytic
perspective to check and compare these business metrics. For example, it is
like comparing the KPIs between this month and last month, or the performance
of different business departments. By mapping the relational model to a
multidimensional model, we add a business semantic layer on top of the
technical data, thus helping non-technical users understand, explore, and use
data.</p>
+
+<p>In Kylin Roadmap, support to multidimensional query languages (such as MDX
and DAX) is an important part, as we aim to enhance the business semantic
capability of Kylin as a multi-dimensional database. Users can use MDX to
convert the Kylin data model into business-friendly language, so they can
perform multidimensional analysis with Excel, Tableau and other BI tools and
understand the business values from their data.</p>
+
+<h3 id="build-a-business-metrics-platform-with-mdx"><strong>Build a business
metrics platform with MDX</strong></h3>
+
+<p>When building complex business metrics, MDX provides the following
advantages if compared to SQL:</p>
+
+<ol>
+ <li><strong>Better support for complex analysis scenarios</strong>, such as
semi-accumulation, many-to-many, and time window analysis;</li>
+ <li><strong>More BI support</strong>: âKylin + MDXâ can be exposed as
relational database tables through the SQL interface, or XMLA-compliant data
source with business semantics. It allows MDX queries and integration with
Excel and other BI tools through the XMLA protocol;</li>
+ <li><strong>Flexible defining of MDX semantic model based on Kylin data
model</strong>, it will convert the underlying data structure into a
business-friendly language and add business value to data. With MDX model, we
offer users a unified business semantic layer, they no longer need to worry
about the underlying technology or implementation complexity when analyzing
data. For more information, see <em><a
href="https://lists.apache.org/thread/4fkhyw1fyf0jg5cb18v7vxyqbn6vm3zv";>The
future of Apache Kylin</a></em>, <em><a
href="https://kyligence.io/blog/opportunities-for-ssas-in-the-cloud/";>SSAS
Disadvantages: Opportunities for SSAS in the Cloud Era</a></em>, and <em><a
href="https://kyligence.io/blog/semantic-layer-the-bi-trend-you-dont-want-to-miss-in-2020/";>Semantic
Layer: The BI Trend You Donât Want to Miss</a>**.</em></li>
+</ol>
+
+<h3 id="mdx-overview"><strong>MDX Overview</strong></h3>
+
+<h4 id="what-is-mdx"><strong>What is MDX?</strong></h4>
+
+<p>MDX (Multi Dimensional eXpression) is a query language for OLAP Cube. It
was first introduced by Microsoft in 1997 as part of the OLEDB for OLAP
specification and later integrated into SSAS. Since then, it has been widely
adopted by OLAP databases.</p>
+
+<p>MDX is similar to SQL in many ways and also offers some SQL features though
maybe not as intuitive or effective as SQL. For example, you can include
SELECT, FROM, or WHERE clause in your MDX queries. But it is not an extension
of SQL. You can use these keywords to dig into specific parts of the Cube.</p>
+
+<p><a
href="https://docs.microsoft.com/en-us/sql/mdx/mdx-syntax-elements-mdx?view=sql-server-ver15";>MDX
query syntax </a>are as follows:</p>
+
+<div class="highlighter-rouge"><pre class="highlight"><code><span
class="k">select</span> <span class="o"><</span><span
class="n">axis_specification</span><span class="o">></span><span
class="p">[,</span> <span class="o"><</span><span
class="n">axis_specification</span><span class="o">></span><span
class="p">]</span>
+ <span class="k">from</span> <span class="o"><</span><span
class="n">cube_specification</span><span class="o">></span>
+ <span class="k">where</span> <span class="o"><</span><span
class="n">slicer_specification</span><span class="o">></span>
+</code></pre>
+</div>
+
+<h4 id="key-concepts-of-mdx"><strong>Key concepts of MDX</strong></h4>
+
+<p>Please learn some basic MDX concepts before we continue.</p>
+
+<ol>
+ <li>Dimensions, Levels, Members, and Measures</li>
+ <li>Cell, Tuple, and Set</li>
+ <li>Query Axis and Slicer Axis</li>
+</ol>
+
+<p>For detailed information about these concepts, see <a
href="https://docs.microsoft.com/en-us/sql/mdx/mdx-syntax-elements-mdx?view=sql-server-ver15";>MDX
Syntax Elements (MDX)</a>.</p>
+
+<h4 id="comparison-of-mdx-and-sql"><strong>Comparison of MDX and
SQL</strong></h4>
+
+<p>The query objects are different. MDX is to query the cube, with data
already joined and aggregated, so users neednât specify the join relation
when querying. SQL is to query a table with detailed records. Users need to
specify the join relation among the tables when querying.</p>
+
+<p><a
href="https://dba.stackexchange.com/questions/138311/good-example-of-mdx-vs-sql-for-analytical-queries";>Another
difference is the query result</a>. SQL returns a 2d data subset, while MDX
returns the cubes.</p>
+
+<h3 id="mdx-for-kylin-overview"><strong>MDX for Kylin</strong>
<strong>Overview</strong></h3>
+
+<h4 id="what-is-mdx-for-kylin"><strong>What is</strong> <strong>MDX for
Kylin</strong>?</h4>
+
+<p><strong>MDX for Kylin</strong> is an MDX query engine which developed based
on <strong>Mondrian</strong>, contributed by <strong>Kyligence,</strong> and
with <strong>Apache Kylin</strong> as data source. Like Microsoft SSAS, MDX for
Kylin can also integrate many data analysis tools, including Microsoft Excel
and Tableau, to provide a better user experience for big data analysis.</p>
+
+<h4 id="how-to-create-business-metrics"><strong>How</strong> <strong>to create
business metrics</strong></h4>
+
+<h5 id="atomic-metrics-and-business-metrics"><strong>Atomic metrics and
business metrics</strong></h5>
+
+<p>In Kylin Cube, we will perform certain aggregate calculations (such as
Sum/Max/Min/Count/Count Distinct, exclude TopN) on a single column when
creating measures, and the measures created are called atomic metrics.</p>
+
+<p>In actual business scenarios, we can run complex calculations based on
these atomic metrics to create composite metrics with business implications,
and these metrics are called business metrics.</p>
+
+<h5 id="hierarchy-calculated-measure-and-namedset"><strong>Hierarchy,
Calculated Measure, and NamedSet</strong></h5>
+
+<p><strong>Hierarchy:</strong> Hierarchies are collections of dimension-based
hierarchies that can empower data analysts with advanced analytical
capabilities. For example, you can create a time hierarchy with year, quarter,
month, week, and day as its hierarchy. Then data analysts can do a YOY analysis
on the sales volume, or dig into the âQuarter > Month > Week >
Dayâ hierarchy for more detailed analysis.</p>
+
+<p><strong>Calculated Measure:</strong> Calculated Measure are metrics/indexes
acquired by running composite computing on the <strong>atomic metrics</strong>
with MDX expressions. We mainly use calculated measures to create
<strong>business metrics</strong>.</p>
+
+<p><strong>NamedSet:</strong> Namedset is for the scenario when you need to
reuse a set of members in MDX for Kylin. A NamedSet uses specified expressions
to get the set members. It can be placed directly on the axis or used in
expressions of Calculated Measure for or other Namedset.</p>
+
+<h4 id="dataset-as-semantic-model"><strong>Dataset as</strong>
<strong>semantic model</strong></h4>
+
+<p>In Kylin 4, we create a data model based on the relationship among tables,
and define different dimensions and measures on the Cube. These measures are
<strong>atomic metrics</strong>.</p>
+
+<p>In MDX for Kylin, we join related Kylin Cubes to create datasets and create
<strong>business metrics</strong> based on atomic metrics.</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/1_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h4 id="process-of-calculating"><strong>Process of calculating</strong></h4>
+
+<p>The client(BI/Excel) sends an MDX query to MDX for Kylin, which will then
be parsed into SQL and sent to Kylin. After that, Kylin will answer the SQL
query based on the pre-computed Cuboid and return the result to MDX for Kylin.
Then, MDX for Kylin will do some derived metrics calculation, and return the
multidimensional data results to the client.</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/2_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h4 id="summary"><strong>Summary</strong></h4>
+
+<p><strong>MDX for</strong> <strong>Kylin</strong> supports MDX interface
enhancing the semantic capability and creates a unified data analysis and
management user experience. Now users can better leverage the value of data.
The figure below shows the process of how raw data is processed into business
metrics.</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/3_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h3 id="technical-advantages-of-mdx-for-kylin"><strong>Technical advantages
of</strong> <strong>MDX for Kylin</strong></h3>
+
+<p>If compared with other open-source MDX query engines, MDX for Kylin has the
following advantages:</p>
+
+<ul>
+ <li>
+ <p>Better support to BIs (Excel/Tableau/Power BI, etc.)and compliance with
XMLA protocol</p>
+ </li>
+ <li>
+ <p>Optimize the MDX Query for BIs</p>
+ </li>
+ <li>
+ <p>Accelerate MDX queries with Kylinâs pre-computing capability</p>
+ </li>
+ <li>
+ <p>Easy-to-use interface for metrics definition and management</p>
+ </li>
+</ul>
+
+<h2 id="quick-start-with-docker"><strong>Quick start with Docker</strong></h2>
+
+<h4 id="test-environment"><strong>Test environment</strong></h4>
+
+<ul>
+ <li>MacBook Pro: Docker Desktop (latest version)</li>
+ <li>Windows 10 virtual machine: Microsoft Excel (for Windows)</li>
+</ul>
+
+<h4 id="start-the-container"><strong>Start the container</strong></h4>
+
+<pre><code class="language-she">docker run -d \
+ -m 8g \
+ -p 7070:7070 \
+ -p 7080:7080 \
+ -p 8088:8088 \
+ -p 50070:50070 \
+ -p 8032:8032 \
+ -p 8042:8042 \
+ -p 2181:2181 \
+ --name kylin-4.0.1 \
+ apachekylin/apache-kylin-standalone:kylin-4.0.1-mondrian
+</code></pre>
+
+<h4 id="environment-check">Environment Check</h4>
+
+<p>Wait for a few minutes, then check if you can visit the web UI of HDFS,
YARN, Kylin, and MDX for Kylin.</p>
+
+<table>
+ <thead>
+ <tr>
+ <th><strong>Components</strong></th>
+ <th><strong>Web UI</strong></th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>HDFS</td>
+ <td>http://localhost:50070/dfshealth.html</td>
+ </tr>
+ <tr>
+ <td>YARN</td>
+ <td>http://localhost:8088/cluster</td>
+ </tr>
+ <tr>
+ <td>Kylin</td>
+ <td>http://localhost:7070/kylin</td>
+ </tr>
+ <tr>
+ <td>MDX for Kylin</td>
+ <td>http://localhost:7080/overview</td>
+ </tr>
+ </tbody>
+</table>
+
+<h4 id="build-a-sample-cube">Build a sample Cube</h4>
+
+<p>In this tutorial, we will use Kylinâs built-in sample Cube: <code
class="highlighter-rouge">kylin_sales_cube</code></p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/4_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h4 id="create-a-mdx-dataset">Create a MDX dataset</h4>
+
+<h5 id="log-in-to-mdx-for-kylin"><strong>Log in to</strong> <strong>MDX for
Kylin</strong></h5>
+
+<p>Log in to MDX for Kylin through the web UI: http://localhost:7080. The
default account/password is <strong>ADMIN/KYLIN</strong>, the same as Kylin.</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/5_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h5 id="define-the-dataset-and-relations">Define the dataset and relations</h5>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/6_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/7_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h5 id="create-a-time-hierarchy"><strong>Create a time hierarchy</strong></h5>
+
+<ul>
+ <li>Configure the dimension table <code
class="highlighter-rouge">KYLIN_CAL_DT</code>.</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/8_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<ul>
+ <li>Configure the column <code class="highlighter-rouge">YEAR_BEG_DT</code>,
and set <strong>Type</strong> to <strong>Year</strong>.</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/9_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/10_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<ul>
+ <li>Configure <code class="highlighter-rouge">MONTH_BEG_DT</code>and <code
class="highlighter-rouge">WEEK_BEG_DT</code>, set them to the correspondent
hierarchy.</li>
+ <li>Create a time hierarchy <code class="highlighter-rouge">Calendar</code>.
Please be noted the time hierarchy should be in a âyear-month-weekâ
order.</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/11_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h5 id="rename-the-atomic-metrics"><strong>Rename the atomic
metrics</strong></h5>
+
+<p>Rename the atomic metric <code class="highlighter-rouge">GMV_SUM</code> to
some names with business implications. In this tutorial, we named it as
<strong>Sales volume</strong>, and renamed <code
class="highlighter-rouge">SELLER_CNT_HLL</code> as <strong>Retailer
numbers</strong>.</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/12_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h5 id="create-business-metrics-calculated-measures"><strong>Create business
metrics (calculated measures)</strong></h5>
+
+<ul>
+ <li>Create the business metric <strong>Av</strong><strong>erage sales volume
of</strong> <strong>retailers</strong>.</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/13_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<ul>
+ <li>Build âYoY growth rate of sales volumeâ and âMoM growth rate of
sales volumeâ sequentially.</li>
+</ul>
+
+<table>
+ <thead>
+ <tr>
+ <th>Metrics</th>
+ <th><strong>MDX Expression</strong></th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>Average sales volume of retailers</td>
+ <td>[Measures].[Sales volume]/[Measures].[Retailer number]</td>
+ </tr>
+ <tr>
+ <td>YoY growth rate of sales volume</td>
+ <td>[Measures].[Sales volume] / SUM( ParallelPeriod(
[KYLIN_CAL_DT].[Calendar-Hierarchy].[YEAR_BEG_DT], 1,
[KYLIN_CAL_DT].[Calendar-Hierarchy].CurrentMember ), [Measures].[Sales
volume] ) - 1</td>
+ </tr>
+ <tr>
+ <td>MoM growth rate of sales volume</td>
+ <td>[Measures].[Sales volume] / SUM( ParallelPeriod(
[KYLIN_CAL_DT].[Calendar-Hierarchy].[MONTH_BEG_DT], 1,
[KYLIN_CAL_DT].[Calendar-Hierarchy].CurrentMember ), [Measures].[Sales
volume] ) - 1</td>
+ </tr>
+ <tr>
+ <td>Total sales volume</td>
+ <td>Fixed([KYLIN_CAL_DT].[YEAR_BEG_DT], [Measures].[Sales volume])</td>
+ </tr>
+ <tr>
+ <td>Proportion in total annual sales volume</td>
+ <td>[Measures].[Sales volume]/[Measures].[Total sales volume]</td>
+ </tr>
+ </tbody>
+</table>
+
+<h4 id="access-business-metrics-through-excel-pivot-tables"><strong>Access
business metrics through Excel pivot tables</strong></h4>
+
+<h5 id="connect-mdx-for-kylin"><strong>Connect</strong> <strong>MDX for
Kylin</strong><strong>!</strong></h5>
+
+<ul>
+ <li>Open Microsoft Excel (for Windows)</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/14_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<ul>
+ <li>Configure the MDX for Kylin server address. Please update the IP_Adress
with your IP address, and use Kylinâs account and password(ADMIN/KYLIN in
this case).</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/15_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h5 id="check-sales-volume-with-pivot-tables">Check sales volume with pivot
tables</h5>
+
+<ul>
+ <li>Configure pivot table</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/16_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<ul>
+ <li>Check YoY growth rate of sales volume</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/17_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<ul>
+ <li>Check MoM growth rate of sales volume</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/18_use_excel_to_query_kylin.en.png"
alt="" /></p>
+
+<h4 id="call-api-to-query-mdx-for-kylin">Call API to query MDX for Kylin</h4>
+
+<p>If you do NOT have a windows version Excel, you can also use REST API to
test the business metrics just created. Note: please change the variables in
the <code class="highlighter-rouge"><Statement></code> <code
class="highlighter-rouge"></Statement></code> section based on your
setting and update the value of <code class="highlighter-rouge">Catalog</code>
if needed.</p>
+
+<pre><code class="language-she">curl --location --request POST
'http://localhost:7080/mdx/xmla/learn_kylin' \
+--header 'Authorization: Basic QURNSU46S1lMSU4=' \
+--header 'Connection: Keep-Alive' \
+--header 'SOAPAction: "urn:schemas-microsoft-com:xml-analysis:Execute"' \
+--header 'User-Agent: MSOLAP' \
+--header 'Content-Type: text/xml' \
+--header 'Accept: */*' \
+--header 'Cookie: JSESSIONID=22BF2B6D889F183D7F7E898D4D769398;
MDXAUTH=ZUt6V1VBRE1JTjoyYTk3Zjg2NTdiNjk0NTE5NzA0NjFiN2ZjYTNkYzg2OToxNjQ2NjMxNDkw'
\
+--data-raw '<soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
+ <soap:Header>
+ <Session xmlns="urn:schemas-microsoft-com:xml-analysis"
SessionId="8nblet191q"/>
+ </soap:Header>
+ <soap:Body>
+ <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
+ <Command>
+ <Statement>
+SELECT {[Measures].[Sales volume],
+ [Measures].[Retailer number],
+ [Measures].[Average sales volume of retailers],
+ [Measures].[Proportion in total annual sales volume],
+ [Measures].[YoY growth rate of sales volume],
+ [Measures].[MoM growth rate of sales volume]}
+ DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
+NON EMPTY
Hierarchize(AddCalculatedMembers({DrilldownLevel({[KYLIN_CAL_DT].[Calendar-Hierarchy].[All]})}))
+ DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
+FROM [demo0]
+CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR,
FONT_FLAGS
+ </Statement>
+ </Command>
+ <Properties>
+ <PropertyList>
+ <Catalog>demo0</Catalog>
+ <Timeout>0</Timeout>
+ <Content>SchemaData</Content>
+ <Format>Multidimensional</Format>
+ <AxisFormat>TupleFormat</AxisFormat>
+
<DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>
+ <SafetyOptions>2</SafetyOptions>
+ <Dialect>MDX</Dialect>
+
<MdxMissingMemberMode>Error</MdxMissingMemberMode>
+
<DbpropMsmdOptimizeResponse>9</DbpropMsmdOptimizeResponse>
+
<DbpropMsmdActivityID>6C94075F-65AD-4B9E-B3EB-4536A191A6AB</DbpropMsmdActivityID>
+
<DbpropMsmdRequestID>9FA20B8A-ACA0-414E-98EA-14649F20CF75</DbpropMsmdRequestID>
+ <LocaleIdentifier>1033</LocaleIdentifier>
+
<DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>
+ </PropertyList>
+ </Properties>
+ </Execute>
+ </soap:Body>
+</soap:Envelope>'
+</code></pre>
+
+<h2 id="contact-us">Contact us</h2>
+
+<p>If you want to check the official documentation, please check the manual :
https://kyligence.github.io/mdx-kylin/en. For developers who want to
contribute, please check our Github page :
https://github.com/Kyligence/mdx-kylin .</p>
+
+<p>Feel free to leave your suggestion, ask a question or report a bug by
referring https://kyligence.github.io/mdx-kylin/en/contact/ .</p>
+
+ </article>
+
+</div>
+
+
+
+
+
+ </article>
+ </div>
+ </div>
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<footer id="underfooter">
+ <div>
+ <div class="row">
+ <div class="col-md-12 widget">
+ <div class="widget-body">
+ <div class="footer-img">
+ <a href="http://www.apache.org";>
+ <img id="asf-logo" height="78px" alt="Apache
Software Foundation" src="/assets/images/apache_footer.png">
+ </a>
+ </div>
+ <p style="padding-top: 11px;">
+ The contents of this website are © 2015 Apache
Software Foundation under the terms of the
+ <a href="http://www.apache.org/licenses/LICENSE-2.0";>
Apache License v2 </a>.
+ </p>
+ <p style="margin-bottom: 11px;">
+ Apache Kylin and its logo are trademarks of the Apache
Software Foundation.
+ </div>
+
+ </div>
+ </div>
+ </div>
+ <!-- /row of widgets -->
+
+ </div>
+ <div></div>
+
+</footer>
+
+ <script src="/assets/js/jquery-1.9.1.min.js"></script>
+ <script src="/assets/js/bootstrap.min.js"></script>
+ <script src="/assets/js/main.js"></script>
+ </body>
+</html>
+
+
+
+
Modified: kylin/site/blog/index.html
URL:
http://svn.apache.org/viewvc/kylin/site/blog/index.html?rev=1899440&r1=1899439&r2=1899440&view=diff
==============================================================================
--- kylin/site/blog/index.html (original)
+++ kylin/site/blog/index.html Thu Mar 31 14:09:40 2022
@@ -197,6 +197,16 @@ var _hmt = _hmt || [];
<div class="col-md-6 col-lg-6 col-xs-12">
+ <a class="blog-card"
href="/blog/2022/03/31/how-to-use-excel-to-query-kylin/">
+ <div class="blog-pic">
+ <img width="20" src="../assets/images/icon_blog_w.png" />
+ </div>
+ <p class="blog-title">How to use Excel to query Kylin? MDX for
Kylin!</p>
+ <p align="left" class="post-meta">posted: Mar 31, 2022</p>
+ </a>
+ </div>
+
+ <div class="col-md-6 col-lg-6 col-xs-12">
<a class="blog-card"
href="/blog/2022/03/17/kylin4-now-supporting-aws-glue-catalog/">
<div class="blog-pic">
<img width="20" src="../assets/images/icon_blog_w.png" />
Modified: kylin/site/cn/blog/index.html
URL:
http://svn.apache.org/viewvc/kylin/site/cn/blog/index.html?rev=1899440&r1=1899439&r2=1899440&view=diff
==============================================================================
--- kylin/site/cn/blog/index.html (original)
+++ kylin/site/cn/blog/index.html Thu Mar 31 14:09:40 2022
@@ -199,6 +199,16 @@ var _hmt = _hmt || [];
<div class="col-md-6 col-lg-6 col-xs-12">
+ <a class="blog-card"
href="/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/">
+ <div class="blog-pic">
+ <img width="20" src="/assets/images/icon_blog_w.png" />
+ </div>
+ <p class="blog-title">å¦ä½ä½¿ç¨ Excel æ¥è¯¢ Kylinï¼MDX
for Kylinï¼</p>
+ <p align="left" class="post-meta">posted: Mar 31, 2022</p>
+ </a>
+ </div>
+
+ <div class="col-md-6 col-lg-6 col-xs-12">
<a class="blog-card"
href="/cn_blog/2022/03/17/kylin4-now-supporting-aws-glue-catalog/">
<div class="blog-pic">
<img width="20" src="/assets/images/icon_blog_w.png" />
Added: kylin/site/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
URL:
http://svn.apache.org/viewvc/kylin/site/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html?rev=1899440&view=auto
==============================================================================
--- kylin/site/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
(added)
+++ kylin/site/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/index.html
Thu Mar 31 14:09:40 2022
@@ -0,0 +1,711 @@
+<!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+<!doctype html>
+<html>
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<head>
+ <meta charset="utf-8">
+ <meta http-equiv="X-UA-Compatible" content="IE=edge">
+ <meta name="viewport" content="width=device-width, initial-scale=1">
+
+ <title>Apache Kylin | å¦ä½ä½¿ç¨ Excel æ¥è¯¢ Kylinï¼MDX for
Kylinï¼</title>
+ <meta name="description" content="Kylin 为ä»ä¹éè¦ MDXï¼">
+ <meta name="author" content="Apache Kylin">
+ <link rel="shortcut icon" href="fav.png" type="image/png">
+
+
+
+<link rel="stylesheet" href="/assets/css/animate.css">
+<!-- Bootstrap -->
+<link rel="stylesheet" href="/assets/css/bootstrap.min.css">
+
+<!-- Fonts -->
+<!-- <link rel="stylesheet"
href="http://fonts.googleapis.com/css?family=Alice|Open+Sans:400,300,700"> -->
+
+<!-- Icons -->
+<link rel="stylesheet" href="/assets/css/font-awesome.min.css">
+
+ <!-- Custom styles -->
+ <link rel="stylesheet" href="/assets/css/styles.css">
+ <link rel="stylesheet" href="/assets/css/docs.css">
+ <link rel="stylesheet" href="/assets/css/pygments.css">
+
+ <link rel="canonical"
href="http://kylin.apache.org/cn_blog/2022/03/31/how-to-use-excel-to-query-kylin/";>
+ <link rel="alternate" type="application/rss+xml" title="Apache Kylin"
href="http://kylin.apache.org/feed.xml"; />
+
+<!--[if lt IE 9]> <script src="assets/js/html5shiv.js"></script> <![endif]-->
+<!-- Global site tag (gtag.js) - Google Analytics -->
+<script async
src="https://www.googletagmanager.com/gtag/js?id=UA-120788561-1";></script>
+<script>
+ window.dataLayer = window.dataLayer || [];
+ function gtag(){dataLayer.push(arguments);}
+ gtag('js', new Date());
+
+ gtag('config', 'UA-120788561-1');
+</script>
+<script type="text/javascript" src="/assets/js/jquery-1.9.1.min.js"></script>
+<script type="text/javascript" src="/assets/js/nside.js"></script> </script>
+<script type="text/javascript" src="/assets/js/nnav.js"></script> </script>
+<script>
+var _hmt = _hmt || [];
+(function() {
+ var hm = document.createElement("script");
+ hm.src = "https://hm.baidu.com/hm.js?bdc5e03add430c0b72cc0eb91eabfa99";;
+ var s = document.getElementsByTagName("script")[0];
+ s.parentNode.insertBefore(hm, s);
+})();
+</script>
+
+</head>
+
+ <body>
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<header id="header" >
+
+ <!-- Main Menu -->
+ <nav class="navbar navbar-default" role="navigation" id="nav-wrapper">
+ <div class="container-fluid" id="nav">
+ <!--
+ <img class="img-circle" width="40px" height="40px" id="circlelogo"
src="/assets/images/kylin_logo.jpg">
+ -->
+ <!-- Brand and toggle get grouped for better mobile display -->
+ <div class="navbar-header">
+ <img class="navbar-logo" width="46"
src="/assets/images/kylin_logo.png" ></img>
+ <button type="button" class="navbar-toggle collapsed"
data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
+ <span class="sr-only">Toggle navigation</span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ </button>
+ <ul class="nav icon-navbar">
+ <li><a href="https://twitter.com/apachekylin"; target="_blank"
class="fa fa-twitter fa-lg" title="Twitter: @ApacheKylin" ></a></li>
+ <li><a href="https://github.com/apache/kylin"; target="_blank"
class="fa fa-github-alt fa-lg" title="Github: apache/kylin" ></a></li>
+ <li><a href="https://www.facebook.com/kylinio"; target="_blank"
class="fa fa-facebook fa-lg" title="Facebook: kylin.io" ></a></li>
+ </ul>
+ </div>
+
+ <!-- Collect the nav links, forms, and other content for toggling -->
+ <div class="navbar-collapse collapse" id="bs-example-navbar-collapse-1">
+
+ <ul class="nav navbar-nav">
+
+ <li><a href="/">Home</a></li>
+ <li>
+ <a href="/docs" class="dropdown-toggle" data-toggle="dropdown"
role="button" aria-haspopup="true" aria-expanded="false">Docs<span
class="caret"></span></a>
+ <ul class="dropdown-menu">
+ <li><a href="/docs/">Latest Release(Kylin 4.0.1)</a></li>
+ <li><a href="/docs31/">Kylin 3.1.3</a></li>
+ <li><a href="/docs24/">Kylin 2.4.0</a></li>
+ <li><a href="/archive/">Archive</a></li>
+ </ul>
+ </li>
+ <li><a href="/download">Download</a></li>
+ <li><a href="/community" >Community</a></li>
+ <li>
+ <a href="/development" class="dropdown-toggle"
data-toggle="dropdown" role="button" aria-haspopup="true"
aria-expanded="false">Development<span class="caret"></span></a>
+ <ul class="dropdown-menu">
+ <li><a href="/development40/">Kylin 4.x</a></li>
+ <li><a href="/development/">Kylin 3.x And Older Versions</a></li>
+ </ul>
+ </li>
+ <li><a href="/blog">Blog</a></li>
+ <li><a href="/cn" >ä¸æç</a></li>
+ </ul>
+ </div><!-- /.navbar-collapse -->
+ </div><!-- /.container-fluid -->
+ </nav>
+
+ <div id="head" class="parallax normal-header" >
+ <div class="text-center header-apache">
+ <a href="http://apache.org/foundation/contributing.html"; title="Support
Apache" style="margin-left: 150px;">
+ <div>
+ <img src="https://www.apache.org/images/SupportApache-small.png"; >
+ </div>
+ </a>
+ </div>
+ </div>
+
+ </header>
+
+ <div class="page-content main">
+ <header style=" padding:2em 0 0 ">
+ <div class="container" >
+ <div style=" padding:0 4em">
+ <div class="blog-icon">
+ <img width="30" src="/assets/images/icon_blog_w.png">
+ </div>
+ <h4 class="index-title" style="
float:left;"><span>Apache Kylin⢠Technical Blog</span></h4>
+ </div>
+ </div>
+ </div>
+
+ <div class="container blog">
+ <div>
+ <article class="post-content" >
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<div class="post" style=" padding:2em 4em 4em 4em">
+
+ <header class="post-header">
+ <h1 class="post-title">å¦ä½ä½¿ç¨ Excel æ¥è¯¢ Kylinï¼MDX for
Kylinï¼</h1>
+ <p class="post-meta" >Mar 31, 2022 ⢠Xiaoxiang Yu</p>
+ </header>
+
+ <article class="post-content" >
+ <h2 id="kylin--mdx">Kylin 为ä»ä¹éè¦ MDXï¼</h2>
+
+<h3 id="section">å¤ç»´æ°æ®åºåä¸å¡è¯ä¹å±</h3>
+
+<p>å¤ç»´æ°æ®åºä¸å
³ç³»åæ°æ®åºçå
³é®åºå«å¨äºä¸å¡è¡¨è¾¾è½åã尽管 SQL
表达è½åå¾å¼ºï¼æ¯æ°æ®åæå¸çåºæ¬æè½ï¼ä½å¦æ以
â人人é½æ¯åæå¸â 为ç®æ ï¼SQL åå
³ç³»æ°æ®åºå¯¹éææ¯äººåè¿æ¯å¤ªé¾äºãä»éææ¯äººåçè§è§ï¼æ°æ®æ¹åæ°æ®ä»åºå°±å¥½ä¼¼ä¸ä¸ªé»æçæ¿é´ï¼ç¥éå
¶ä¸æå¾å¤æ°æ®ï¼å´å 为ä¸ææ°æ®åºç论å SQLï¼æ æ³çæ¸
ãç解ãå使ç¨è¿äºæ°æ®ã</p>
+
+<p>å¦ä½è®©æ°æ®æ¹(åæ°æ®ä»åº)对éææ¯äººåä¹ â<strong>æ¸
æ¾è§åº</strong>âï¼è¿å°±éè¦å¼å
¥ä¸ä¸ªå¯¹éææ¯äººåæ´å
å好çâ<strong>å
³ç³»æ°æ®æ¨¡å</strong> â
<strong>å¤ç»´æ°æ®æ¨¡å</strong>âãå¦æ说å
³ç³»æ¨¡åæè¿°äºæ°æ®çææ¯å½¢æï¼é£ä¹å¤ç»´æ¨¡ååæè¿°äºæ°æ®çä¸å¡å½¢æãå¨å¤ç»´æ°æ®åºä¸ï¼åº¦é对åºäºæ¯ä¸ªäººé½æçä¸å¡ææ
ï¼ç»´åº¦åæ¯æ¯è¾ãè§å¯è¿äºä¸å¡ææ
çè§åº¦ãè¦ä¸ä¸ä¸ªææ¯è¾ KPIï¼è¦å¨å¹³è¡äºä¸é¨ä¹é
´æ¯è¾ç»©æï¼è¿äºæ¯æ¯ä¸ªéææ¯äººåé½ç解çæ¦å¿µãéè¿å°å
³ç³»æ¨¡åæ
å°å°å¤ç»´æ¨¡åï¼æ¬è´¨æ¯å¨ææ¯æ°æ®ä¹ä¸å¢å¼ºäºä¸å¡è¯ä¹ï¼å½¢æä¸å¡è¯ä¹å±ï¼å¸®å©éææ¯äººåä¹è½çæãæ¢ç´¢ã使ç¨æ°æ®ã</p>
+
+<p>为äºå¢å¼º Kylin
ä½ä¸ºå¤ç»´æ°æ®åºçè¯ä¹å±è½åï¼æ¯æå¤ç»´æ¥è¯¢è¯è¨æ¯ Kylin
Roadmap ä¸çéç¹å
容ï¼æ¯å¦ MDX å DAXãéè¿ MDX å¯ä»¥å° Kylin
ä¸çæ°æ®æ¨¡å转æ¢ä¸ºä¸å¡å好çè¯è¨ï¼èµäºæ°æ®ä¸å¡ä»·å¼ï¼æ¹ä¾¿å¯¹æ¥
<strong>Excel</strong>ãTableau ç BI å·¥å
·è¿è¡å¤ç»´åæã</p>
+
+<h3 id="mdx-">åºäº MDX æé ä¸å¡ææ å¹³å°</h3>
+
+<p>ä½¿ç¨ MDX æ¥å建å¤æçä¸å¡ææ ï¼ç¸å¯¹ SQL
çä¸äºç¹ç¹åä¼å¿å¦ä¸ï¼</p>
+
+<ol>
+ <li>æ´å¥½ç<strong>æ¯æå¤æåæåºæ¯</strong>ï¼å¦åç´¯å
ãå¤å¯¹å¤ãæ¶é´çªå£åæçï¼</li>
+ <li>广æ³æ¯æåç§ BIï¼âKylin + MDXâ ä¸ä»
è½å¤éè¿ SQL
æ¥å£æ´é²ä¸ºç±»ä¼¼äºå
³ç³»åæ°æ®åºç表ï¼ä¹å¯ä»¥æ´é²ä¸ºå
¼å®¹
XMLA åè®®ç带æè¯ä¹ä¿¡æ¯çæ°æ®æºï¼å¯éè¿ MDX
è¯è¨è¿è¡æ¥è¯¢ï¼æ¯æå¯¹æ¥ <strong>Excel</strong> çåªè½éè¿ XMLA
å议对æ¥æ°æ®æºç BIï¼</li>
+ <li>åºäº Kylin ä¸çæ°æ®æ¨¡åçµæ´»å®ä¹ MDX
è¯ä¹æ¨¡åï¼å°åºå±æ°æ®ç»æ转æ¢ä¸ºä¸å¡å好çè¯è¨ï¼èµäºæ°æ®ä¸å¡ä»·å¼ï¼ä½¿å¾ä¸å¡äººåå¨åææ¶æ
éå
³å¿åºå±çææ¯å¤æ度åå®ç°ï¼éè¿ MDX
模ååå¤æ´é²ç»ä¸è¯ä¹ï¼å¸®å©ç¨æ·<strong>æ建ç»ä¸çä¸å¡è¯ä¹å±</strong>ã(详ç»ä¿¡æ¯å¯ä»¥åèé¾æ¥
1/6/7)</li>
+</ol>
+
+<h2 id="mdx--1">MDX ä»ç»</h2>
+
+<h3 id="mdx">ä»ä¹æ¯ MDXï¼</h3>
+
+<p>MDX (Multi Dimensional eXpression) æ¯ä¸ç§ OLAP
å¤ç»´æ°æ®éçæ¥è¯¢è¯è¨ï¼æåç± Microsoft äº 1997 å¹´ä½ä¸º OLEDB
for OLAP è§èå¼å
¥ï¼éåéæå¨ SSAS ä¸ãç®åï¼MDX å¨ OLAP
æ°æ®åºä¸è¢«å¹¿æ³éç¨ã</p>
+
+<p>MDX å¨å¾å¤æ¹é¢ä¸ç»æåæ¥è¯¢è¯è¨ (SQL)
è¯æ³ç¸ä¼¼ï¼ä½å®ä¸æ¯ SQL è¯è¨çæ©å±ï¼äºå®ä¸ï¼MDX
ææä¾çä¸äºåè½ä¹å¯ç± SQL
æä¾ï¼å°½ç®¡ä¸æ¯é£ä¹æææç´è§ãå¦å SQL æ¥è¯¢ä¸æ ·ï¼MDX
æ¥è¯¢å¯ä»¥å
æ¬SELECT åå¥)ãFROM åå¥å WHERE åå¥ãè¿äºå
³é®å以åå
¶å®å
³é®åæä¾äºåç§å·¥å
·ï¼ç¨æ¥ä»å¤ç»´æ°æ®éæåæ°æ®çç¹å®é¨åã</p>
+
+<p>MDX æ¥è¯¢è¯æ³ç¤ºä¾å¦ä¸(详ç»ä¿¡æ¯å¯ä»¥åèé¾æ¥ 3)ï¼</p>
+
+<div class="highlighter-rouge"><pre class="highlight"><code><span
class="k">select</span> <span class="o"><</span><span
class="n">axis_specification</span><span class="o">></span><span
class="p">[,</span> <span class="o"><</span><span
class="n">axis_specification</span><span class="o">></span><span
class="p">]</span>
+<span class="k">from</span> <span class="o"><</span><span
class="n">cube_specification</span><span class="o">></span>
+<span class="k">where</span> <span class="o"><</span><span
class="n">slicer_specification</span><span class="o">></span>
+</code></pre>
+</div>
+
+<h3 id="mdx--2">MDX åºæ¬æ¦å¿µ</h3>
+
+<p>å¨äºè§£å¦ä¹ MDX ä¹åï¼è¯·è³å°ææ¡ä»¥ä¸æ¦å¿µãéäºç¸å
³æ¦å¿µå¨ Microsoft ç SSAS
å®ç½ææ¯è¾è¯¦ç»çä»ç»ï¼ä¸äºè§£çåå¦å¯ä»¥éè¿æç«
æ«å°¾çåèé¾æ¥å¦ä¹ ã(详ç»ä¿¡æ¯å¯ä»¥åèé¾æ¥ 2/3/4)</p>
+
+<ol>
+
<li>维度(Dimensions)ã级å«(Levels)ãæå(Members)å度éå¼(Measures)</li>
+ <li>åå
(Cell)ãå
ç»(Tuple)åéå(Set)</li>
+ <li>轴维度(Query Axis)ååçå¨ç»´åº¦(Slicer Axis)</li>
+</ol>
+
+<h3 id="mdx--sql-">MDX å SQL çæ¯è¾</h3>
+
+<p><strong>æ¥è¯¢å¯¹è±¡</strong>ä¸åï¼MDX
çæ¥è¯¢å¯¹è±¡æ¯<strong>å¤ç»´æ°æ®é</strong>(Cube)ï¼æ¯æå Join
åèå好çæ°æ®ï¼æ¥è¯¢æ¶ä¸éè¦æå® Join å
³ç³»ãSQL
æ¥è¯¢å¯¹è±¡æ¯<strong>å
³ç³»è¡¨</strong>(Table)ï¼æ¯ä¸æ¡æ¡çæç»è®°å½ï¼æ¥è¯¢æ¶éè¦æå®è¡¨ä¹é´ç
Join å
³ç³»ã</p>
+
+<p><strong>æ¥è¯¢ç»æ</strong>ä¸åï¼SQL
è¿å<strong>äºç»´æ°æ®åé</strong>ï¼è MDX
è¿å<strong>å¤ç»´æ°æ®é</strong>ã(详ç»ä¿¡æ¯å¯ä»¥åèé¾æ¥ 5)</p>
+
+<h1 id="mdx-for-kylin-">MDX for Kylin ä»ç»</h1>
+
+<h3 id="mdx-for-kylin--1">ä»ä¹æ¯ MDX for Kylin ?</h3>
+
+<p><strong>MDX for Kylin</strong> æ¯åºäº <strong>Mondrian</strong>
äºæ¬¡å¼åçï¼ç± <strong>Kyligence</strong> è´¡ç®çï¼ä½¿ç¨
<strong>Apache Kylin 4</strong> ä½ä¸ºæ°æ®æºç MDX æ¥è¯¢å¼æ ãMDX for
Kylin ç使ç¨ä½éªæ¯è¾æ¥è¿ Microsoft
SSASï¼å¯ä»¥éæå¤ç§æ°æ®åæå·¥å
·ï¼å
æ¬ Microsoft ExcelãTableau
çï¼å¯ä»¥ä¸ºå¤§æ°æ®åæåºæ¯ä¸æä¾æ´æè´çä½éªã</p>
+
+<h3 id="mdx-for-kylin--2">MDX for Kylin å建ä¸å¡ææ </h3>
+
+<h4 id="section-1">ååææ åä¸å¡ææ </h4>
+
+<p>å¨ Kylin Cube
æ们å建çåç§åº¦éï¼æ¯å¨åç¬çä¸åä¸çè¿è¡çèå计ç®(TopN
é¤å¤)ï¼åªå
å«äºæéçå ç§èåå½æ°ï¼å³
Sum/Max/Min/Count/Count
Distinctï¼ç¸å¯¹æ¯è¾ç®åï¼æ们称ä¹ä¸º<strong>ååææ
</strong>ã</p>
+
+<p>å¨å®é
ä¸å¡åºæ¯ä¸ï¼åºäºååææ
æ们å¯ä»¥å¯¹<strong>ååææ
</strong>çåç§å¤æè¿ç®ï¼æ¥å建æä¸å¡å«ä¹çå¤åææ
ï¼è¿æ ·çææ æ们称ä¹ä¸º<strong>ä¸å¡ææ ã</strong></p>
+
+<h4 id="section-2">å±çº§ç»æã计ç®åº¦éåå½åé</h4>
+
+<p><strong>å±çº§ç»æï¼</strong>å±çº§ç»ææ¯åºäºç»´åº¦ç级å«éåï¼å¯ç¨äºæé«æ°æ®åæ人åçåæè½åãä¾å¦ï¼ä½
å¯ä»¥å建ä¸ä¸ªæ¶é´å±çº§ç»æï¼å
å«äºå¹´ãå£ãæãå¨åæ¥çº§å«ãè¿æ
·åæ人åå¨å®¢æ·ç«¯ä¸å¯ä»¥å
éå¹´åæéå®é¢ï¼å¨éè¦æ¶å¯ä»¥åå«å±å¼âå£åº¦ > æ >
å¨ > æ¥âæ¥è¿è¡æ´ç»ç²åº¦çåæã</p>
+
+<p><strong>计ç®åº¦éï¼</strong>计ç®åº¦éæ¯å¯¹<strong>ååææ
</strong>ä½¿ç¨ MDX 表达å¼è¿è¡å¤å计ç®å½¢æçæ°ç度é/ææ
ï¼æ们主è¦ä½¿ç¨è®¡ç®åº¦éæ¥å建<strong>ä¸å¡ææ </strong>ã</p>
+
+<p><strong>å½åéï¼</strong>å¨ MDX for Kylin
ç使ç¨ä¸ï¼ç»å¸¸ä¼åºç°éè¦éå¤ä½¿ç¨ä¸ç»æåçéæ±ï¼è¿ç§éæ±å¯ä»¥éè¿å®ä¹å½åéæ¥æ»¡è¶³ãå½åé(NamedSet)
æ¯æ ¹æ®æå®ç表达å¼
计ç®å¾å°çä¸ä¸ªæåSetï¼å³ä¸ç»æåçéåï¼å½åéå¯ä»¥ç´æ¥ç½®äºè½´ä¸ç¨äºå±ç¤ºï¼ä¹å¯ä»¥å¨è®¡ç®åº¦éæå
¶ä»å½åéç表达å¼ä¸ä½¿ç¨ã</p>
+
+<h4 id="section-3">å建è¯ä¹æ¨¡å</h4>
+
+<p>å¨ Kylin 4 æ ¹æ®è¡¨ä¸è¡¨ä¹é´çå
³ç³»å建æ°æ®æ¨¡åï¼å¹¶ä¸å¨
Cube
ä¸å®ä¹ç»´åº¦å度éï¼è¿äºåº¦éæ们å¯ä»¥è®¤ä¸ºæ¯<strong>ååææ
</strong>ã</p>
+
+<p>å¨ MDX for Kylinï¼å°ç¸å
³èç Kylin Cube ç¸å
³èï¼æ¥å建æ°æ®éï¼å¹¶ä¸åºäºååææ
ï¼æ¥å建æä¸å¡å«ä¹ç<strong>ä¸å¡ææ ã</strong></p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/1_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h4 id="section-4">æ°æ®åæ</h4>
+
+<p>使ç¨æ¶ï¼å®¢æ·ç«¯åé MDX æ¥è¯¢ç» MDX for Kylinï¼MDX for Kylin
å解æ MDX æ¥è¯¢ç¿»è¯ä¸º SQL 并ä¸åéç» Kylin ï¼ç¶å Kylin
éè¿é¢è®¡ç®ç Cuboid åç SQL æ¥è¯¢å¹¶æç»æ交è¿ç» MDX for
Kylinï¼MDX for Kylin ä¼ååä¸äºè¡çææ
ç计ç®ï¼æç»å°å¤ç»´æ°æ®ç»æè¿åç»å®¢æ·ç«¯ã</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/2_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h4 id="section-5">æµç¨æ»ç»</h4>
+
+<p>æ»çæ¥è¯´ï¼æ¯æ MDX æ¥å£è½å¤å¢å¼º Kylin
çè¯ä¹å±è½åï¼ä¸ºç¨æ·å¸¦æ¥ç»ä¸çæ°æ®åæå管çä½éªï¼æ´å¥½å°åæ¥æ°æ®çä»·å¼ãä¸å¾å°±æ¯å°ä»ä¸èä¸ï¼å±ç¤ºä»åå§æ°æ®å
å·¥ä¸å¡ææ çè¿ç¨ã</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/3_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h3 id="mdx-for-kylin--3">MDX for Kylin çææ¯ä¼å¿</h3>
+
+<p>MDX for Kylin ç¸å¯¹å
¶å®å¼æº MDX æ¥è¯¢å¼æï¼å
·æ以ä¸ä¼å¿ï¼</p>
+
+<ul>
+ <li>æ´å¥½æ¯æBI(Excel/Tableau/Power BIç) 产åï¼éé
XMLA
åè®®ï¼</li>
+ <li>é对 BI ç MDX Query è¿è¡äºç¹å®ä¼åéåï¼</li>
+ <li>éé
Kylin æ¥è¯¢ï¼éè¿ Kylin çé¢è®¡ç®è½åå é MDX
æ¥è¯¢ï¼</li>
+ <li>éè¿ç®æ´ææçæä½çé¢ï¼æä¾äºç»ä¸çææ
å®ä¹å管çè½åã</li>
+</ul>
+
+<h1 id="docker-">ä» Docker å¿«éå¼å§</h1>
+
+<h3 id="section-6">æµè¯ç¯å¢</h3>
+
+<ul>
+ <li>Macbook Pro ç¬è®°æ¬
+ <ul>
+ <li>Docker Desktop (latest version)</li>
+ </ul>
+ </li>
+ <li>Windows 10 èææº
+ <ul>
+ <li>Microsoft Excel (for Windows)</li>
+ </ul>
+ </li>
+</ul>
+
+<h3 id="section-7">å¯å¨å®¹å¨</h3>
+
+<p>è¿ä¸ªå®¹å¨å
å«äº YarnãHDFSãMySQLãKylinãMDX for Kylin
çè¿ç¨ã</p>
+
+<pre><code class="language-she">docker run -d \
+ -m 8g \
+ -p 7070:7070 \
+ -p 7080:7080 \
+ -p 8088:8088 \
+ -p 50070:50070 \
+ -p 8032:8032 \
+ -p 8042:8042 \
+ -p 2181:2181 \
+ --name kylin-4.0.1 \
+ apachekylin/apache-kylin-standalone:kylin-4.0.1-mondrian
+</code></pre>
+
+<h3 id="section-8">æ£æ¥ç¯å¢</h3>
+
+<p>çå¾
ä¸æ®µæ¶é´ï¼è¯·ä¾æ¬¡æ£æ¥ HDFS/YARN/Kylin/MDX for Kylin ç Web
UI æ¯å¦å¯ä»¥è®¿é®ã</p>
+
+<table>
+ <thead>
+ <tr>
+ <th><strong>ç»ä»¶</strong></th>
+ <th><strong>Web UI å°å</strong></th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>HDFS</td>
+ <td>http://localhost:50070/dfshealth.html</td>
+ </tr>
+ <tr>
+ <td>YARN</td>
+ <td>http://localhost:8088/cluster</td>
+ </tr>
+ <tr>
+ <td>Kylin</td>
+ <td>http://localhost:7070/kylin</td>
+ </tr>
+ <tr>
+ <td>MDX for Kylin</td>
+ <td>http://localhost:7080/overview</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="cube">æå»ºæ ·ä¾ Cube</h3>
+
+<p>请ç´æ¥ä½¿ç¨ Kylin èªå¸¦çæ ·ä¾ Cubeï¼<code
class="highlighter-rouge">kylin_sales_cube</code>ã</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/4_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h3 id="mdx--3">å建 MDX æ°æ®é</h3>
+
+<h5 id="mdx-for-kylin">ç»å½ MDX for Kylin</h5>
+
+<p>é»è®¤è´¦å·/å¯ç æ¯ ADMIN/KYLINï¼MDX for Kylin çè´¦æ·ä¸ KYLIN
çåæ¥ã</p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/5_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h5 id="section-9">å建æ°æ®éåå®ä¹å
³ç³»</h5>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/6_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/7_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h5 id="section-10">å建æ¶é´å±çº§</h5>
+
+<ul>
+ <li>ä¿®æ¹<code
class="highlighter-rouge">KYLIN_CAL_DT</code>ç表å±æ§</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/8_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<ul>
+ <li>ä¿®æ¹ <code class="highlighter-rouge">YEAR_BEG_DT</code>
çç±»å为âå¹´â</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/9_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/10_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<ul>
+ <li>åçä¿®æ¹<code class="highlighter-rouge">MONTH_BEG_DT</code>å<code
class="highlighter-rouge">WEEK_BEG_DT</code> ï¼å¹¶ä¸éæ©å¯¹åºçå±çº§</li>
+ <li>å建æ¶é´å±çº§<code class="highlighter-rouge">Calendar</code>
ï¼è¯·æ³¨æ设置å±çº§ç»æçåå顺åºä¿æ为âå¹´-æ-å¨â</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/11_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h5 id="section-11">ä¿®æ¹ååææ å称</h5>
+
+<ul>
+ <li>ä¿®æ¹ <code class="highlighter-rouge">GMV_SUM</code> 为
âéå®é¢âï¼ä¿®æ¹<code
class="highlighter-rouge">SELLER_CNT_HLL</code>为âå家æ°éâ</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/12_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h5 id="section-12">å建ä¸å¡ææ (计ç®åº¦é)</h5>
+
+<ul>
+ <li>å建ä¸å¡ææ âåæ·å¹³åæ¶è´¹é¢â</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/13_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<ul>
+ <li>ä¾æ¬¡å建ä¸å¡ææ
âéå®é¢å¹´åæå¢é¿çâåâéå®é¢æåæå¢é¿çâ</li>
+</ul>
+
+<table>
+ <thead>
+ <tr>
+ <th><strong>ææ å称</strong></th>
+ <th><strong>MDX 表达å¼</strong></th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>åæ·å¹³åæ¶è´¹é¢</td>
+ <td>[Measures].[éå®é¢]/[Measures].[å家æ°é]</td>
+ </tr>
+ <tr>
+ <td>éå®é¢å¹´åæå¢é¿ç</td>
+ <td>[Measures].[éå®é¢] / SUM( ParallelPeriod(
[KYLIN_CAL_DT].[Calendar-Hierarchy].[YEAR_BEG_DT], 1,
[KYLIN_CAL_DT].[Calendar-Hierarchy].CurrentMember ), [Measures].[éå®é¢] )
- 1</td>
+ </tr>
+ <tr>
+ <td>éå®é¢æåæå¢é¿ç</td>
+ <td>[Measures].[éå®é¢] / SUM( ParallelPeriod(
[KYLIN_CAL_DT].[Calendar-Hierarchy].[MONTH_BEG_DT], 1,
[KYLIN_CAL_DT].[Calendar-Hierarchy].CurrentMember ), [Measures].[éå®é¢] )
- 1</td>
+ </tr>
+ <tr>
+ <td>æ»éå®é¢</td>
+ <td>Fixed([KYLIN_CAL_DT].[YEAR_BEG_DT], [Measures].[éå®é¢])</td>
+ </tr>
+ <tr>
+ <td>å
¨å¹´éå®é¢å æ¯</td>
+ <td>[Measures].[éå®é¢]/[Measures].[æ»éå®é¢]</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="http-api--mdx-">HTTP API æµè¯ MDX æ¥è¯¢</h3>
+
+<p>å¦æä½ æ²¡æ ä¸ä¸ª Windows ç¯å¢ç Excelï¼å¹¶ä¸ä½ æ³æµè¯ä½
å¨ä¸ä¸æ¥æµè¯å建çä¸å¡ææ ï¼è¯·éè¿ MDX æ´é²çæ¥è¯¢ç¸å
³ç REST API æ¥éªè¯æ¥è¯¢ç»æãå¦ææ³ä¿®æ¹ MDX
æ¥è¯¢è¯å¥ï¼è¯·ä¿®æ¹ä»¥ä¸<code
class="highlighter-rouge"><Statement></Statement></code> éé¢ç
MDX è¯å¥ï¼å¹¶ä¸è¯·æ ¹æ®æ
åµä¿®æ¹<code
class="highlighter-rouge">Catalog</code>å段çå¼ã</p>
+
+<pre><code class="language-she">curl --location --request POST
'http://localhost:7080/mdx/xmla/learn_kylin' \
+--header 'Authorization: Basic QURNSU46S1lMSU4=' \
+--header 'Connection: Keep-Alive' \
+--header 'SOAPAction: "urn:schemas-microsoft-com:xml-analysis:Execute"' \
+--header 'User-Agent: MSOLAP' \
+--header 'Content-Type: text/xml' \
+--header 'Accept: */*' \
+--header 'Cookie: JSESSIONID=22BF2B6D889F183D7F7E898D4D769398;
MDXAUTH=ZUt6V1VBRE1JTjoyYTk3Zjg2NTdiNjk0NTE5NzA0NjFiN2ZjYTNkYzg2OToxNjQ2NjMxNDkw'
\
+--data-raw '<soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
+ <soap:Header>
+ <Session xmlns="urn:schemas-microsoft-com:xml-analysis"
SessionId="8nblet191q"/>
+ </soap:Header>
+ <soap:Body>
+ <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
+ <Command>
+ <Statement>
+SELECT {[Measures].[éå®é¢],
+ [Measures].[å家æ°é],
+ [Measures].[åæ·å¹³åæ¶è´¹é¢],
+ [Measures].[å
¨å¹´éå®é¢å æ¯],
+ [Measures].[éå®é¢å¹´åæå¢é¿ç],
+ [Measures].[éå®é¢æåæå¢é¿ç]}
+ DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
+NON EMPTY
Hierarchize(AddCalculatedMembers({DrilldownLevel({[KYLIN_CAL_DT].[Calendar-Hierarchy].[All]})}))
+ DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
+FROM [demo0]
+CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR,
FONT_FLAGS
+ </Statement>
+ </Command>
+ <Properties>
+ <PropertyList>
+ <Catalog>demo0</Catalog>
+ <Timeout>0</Timeout>
+ <Content>SchemaData</Content>
+ <Format>Multidimensional</Format>
+ <AxisFormat>TupleFormat</AxisFormat>
+
<DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>
+ <SafetyOptions>2</SafetyOptions>
+ <Dialect>MDX</Dialect>
+
<MdxMissingMemberMode>Error</MdxMissingMemberMode>
+
<DbpropMsmdOptimizeResponse>9</DbpropMsmdOptimizeResponse>
+
<DbpropMsmdActivityID>6C94075F-65AD-4B9E-B3EB-4536A191A6AB</DbpropMsmdActivityID>
+
<DbpropMsmdRequestID>9FA20B8A-ACA0-414E-98EA-14649F20CF75</DbpropMsmdRequestID>
+ <LocaleIdentifier>1033</LocaleIdentifier>
+
<DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>
+ </PropertyList>
+ </Properties>
+ </Execute>
+ </soap:Body>
+</soap:Envelope>'
+</code></pre>
+
+<h3 id="excel-">éè¿ Excel éè§è¡¨è®¿é®ä¸å¡ææ </h3>
+
+<h5 id="mdx-for-kylin-1">è¿æ¥ MDX for Kylin</h5>
+
+<ul>
+ <li>æå¼ Microsoft Excel (for Windows)</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/14_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<ul>
+ <li>é
ç½® MDX for Kylin å°åï¼è¯·æ¿æ¢ IP_Adress ä¸ºä½ ç¬è®°æ¬ç IP
å°åï¼ç¨æ·ååå¯ç ä½¿ç¨ Kylin çè´¦å·åå¯ç ã</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/15_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h5 id="section-13">éè¿æ°æ®éè§è¡¨åæéå®é¢</h5>
+
+<ul>
+ <li>é
ç½®æ°æ®éè§è¡¨</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/16_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<ul>
+ <li>æ¥çå¹´åæéå®é¢å¢é¿ç</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/17_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<ul>
+ <li>æ¥çæåæéå®é¢å¢é¿ç</li>
+</ul>
+
+<p><img
src="/images/blog/how_to_use_excel_to_query_kylin/18_use_excel_to_query_kylin.cn.png"
alt="" /></p>
+
+<h2 id="section-14">åèé¾æ¥</h2>
+
+<table>
+ <thead>
+ <tr>
+ <th><strong>ç¼å·</strong></th>
+ <th><strong>é¾æ¥</strong></th>
+ <th><strong>注é</strong></th>
+ <th><strong>产å</strong></th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>1</td>
+ <td>https://lists.apache.org/thread/4fkhyw1fyf0jg5cb18v7vxyqbn6vm3zv</td>
+ <td>Kylin 社åºåèµ·å¼åè¯ä¹å±ç讨论</td>
+ <td>Apache Kylin</td>
+ </tr>
+ <tr>
+ <td>2</td>
+ <td>https://mondrian.pentaho.com/documentation/mdx.php</td>
+ <td>Mondrian çå®ç½ææ¡£</td>
+ <td>Mondrian</td>
+ </tr>
+ <tr>
+ <td>3</td>
+ <td>https://docs.microsoft.com/en-us/sql/mdx/mdx-syntax-elements-mdx</td>
+ <td>SSAS å
³äº MDX æ¥è¯¢çè¯æ³è§è</td>
+ <td>Microsoft SSAS</td>
+ </tr>
+ <tr>
+ <td>4</td>
+
<td>https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=76692713</td>
+ <td>SmartBI å
³äº MDX åºæ¬æ¦å¿µçä»ç»</td>
+ <td>SmartBI</td>
+ </tr>
+ <tr>
+ <td>5</td>
+
<td>https://dba.stackexchange.com/questions/138311/good-example-of-mdx-vs-sql-for-analytical-queries</td>
+ <td>å¯¹æ¯ SQL å MDX</td>
+ <td>N/A</td>
+ </tr>
+ <tr>
+ <td>6</td>
+ <td>https://kyligence.io/blog/opportunities-for-ssas-in-the-cloud/</td>
+ <td>Kyligence MDX ææ¯å客</td>
+ <td>Kyligence</td>
+ </tr>
+ <tr>
+ <td>7</td>
+
<td>https://kyligence.io/blog/semantic-layer-the-bi-trend-you-dont-want-to-miss-in-2020/</td>
+ <td>Kyligence MDX ææ¯å客</td>
+ <td>Kyligence</td>
+ </tr>
+ <tr>
+ <td>8</td>
+ <td>https://docs.kyligence.io/books/mdx/v1.3/zh-cn/index.html</td>
+ <td>Kyligence MDX ç¨æ·æå</td>
+ <td>Kyligence</td>
+ </tr>
+ <tr>
+ <td>9</td>
+
<td>https://medium.com/airbnb-engineering/how-airbnb-achieved-metric-consistency-at-scale-f23cc53dea70</td>
+ <td>Airbnb Tech Blog</td>
+ <td>Airbnb Minerva</td>
+ </tr>
+ </tbody>
+</table>
+
+
+ </article>
+
+</div>
+
+
+
+
+
+ </article>
+ </div>
+ </div>
+ <!--
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, software
+* distributed under the License is distributed on an "AS IS" BASIS,
+* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+* See the License for the specific language governing permissions and
+* limitations under the License.
+-->
+
+<footer id="underfooter">
+ <div>
+ <div class="row">
+ <div class="col-md-12 widget">
+ <div class="widget-body">
+ <div class="footer-img">
+ <a href="http://www.apache.org";>
+ <img id="asf-logo" height="78px" alt="Apache
Software Foundation" src="/assets/images/apache_footer.png">
+ </a>
+ </div>
+ <p style="padding-top: 11px;">
+ The contents of this website are © 2015 Apache
Software Foundation under the terms of the
+ <a href="http://www.apache.org/licenses/LICENSE-2.0";>
Apache License v2 </a>.
+ </p>
+ <p style="margin-bottom: 11px;">
+ Apache Kylin and its logo are trademarks of the Apache
Software Foundation.
+ </div>
+
+ </div>
+ </div>
+ </div>
+ <!-- /row of widgets -->
+
+ </div>
+ <div></div>
+
+</footer>
+
+ <script src="/assets/js/jquery-1.9.1.min.js"></script>
+ <script src="/assets/js/bootstrap.min.js"></script>
+ <script src="/assets/js/main.js"></script>
+ </body>
+</html>
+
+
+
+
![https://www.apache.org/images/SupportApache-small.png"](https://www.apache.org/images/SupportApache-small.png"){kind=link}