多维数据查询OLAP及MDX语言笔记整理
转载：https://cloud.tencent.com/developer/article/1441101
Cube是联机分析的关键。他们是一种多维结构，包含原始事实数据、聚合数据，这些数据聚合允许用户快速进行复杂的查询，Mdx语言就应用它更是如鱼得水。 Cube包含两个基本概念：维度和度量
- 维度（Dimension）:维度提供了分类描述，表示一类分析角度，用户通过维度分析度量数据。比如上图中的三个维度：时间、产品、地域
- 度量（Measures）:度量表示用来聚合分析的数字信息，度量的集合组合成了一个特殊的维度。如数量、销售额等。
- 级别（Level）:一个维度上可以包含的层次结构，表示特定的分类。如上图中地域维度可以包含的级别层次级：国家、省、市；时间维度包含的级别层次包含：年、季度、月、日等。
- 成员，是最重要的概念之一，一个成员是维度（包括度量<Measures>）上的项目值。如上图时间维度上”年“级别的成员就包含：2000,2001,2002,2003...月成员包含：1、2、3等。
- 计算成员，是一种运行通过特殊表示式动态计算的成员。也就形成了度量（Measures）的结果。计算成员不影响现有的Cube数据，它基于cube数据，通过各种数学表达式和各种函数定义，可以创建复杂的表达式。任何动态分析功能，都可以通过计算成员实现，比如实现占比，同期比等等。
维度的概念 例如一个维度(Dimension)：Region 该维度下有四个级别（Levels）:country、province、city、county，他们属于层次集合（Hierarchy） 通过维度和级别我们可以定义一个元素（Member）如： Region.Levels(1).members(2)=china Region.Levels(2).members(3)=shanghai
http://www.cnblogs.com/zhijia...
二、 OLAP的基本概念 （1）度量、指标） 是数据度量的指标，是数据的实际意义，即描述数据“是什么”。像上面示例中的人数。 （2）维度 维度是描述与业务主题相关的一组属性，单个属性或属性集合可以构成一个维。如上面示例中的学历、民族、性别等都是维度。 （3）维的层次 一个维往往可以具有多个层次，例如时间维度分为年、季度、月和日等层次，地区维可以是国家、地区、省、市等层次。这里的层次表示数据细化程度，对应概念分层。后面介绍的上钻操作就是由低层概念映射到高层概念。概念分层可除根据概念的全序和偏序关系确定外，还可以通过对数据进行离散化和分组实现。 （4）维的成员 若维是多层次的，则不同的层次的取值构成一个维成员。部分维层次同样可以构成维成员，例如“某年某季度”、“某季某月”等都可以是时间维的成员。 （5）多维数组 多维数组用维和度量的组合表示。一个多维数组可以表示为（维1，维2，……，维n，变量），例如（部门，职系、民族、性别，人数）组成一个多维数组。 （6）数据单元（单元格） 多维数组的取值。当多维数组中每个维都有确定的取值时，就唯一确定一个变量的值。数据单元可以表示为（维1成员，维2成员，……，维N成员，变量的值），例如（人事教育部，技能，回族，男，1人）表示一个数据单元，表示人事教育部职系是技能的回族男性有1人。 （7）事实 事实是不同维度在某一取值下的度量，例如上述人事教育部职系是技能的回族男性有1人就表示在部门、职系、民族、性别四个维度上企业人数的事实度量，并且在为人数事实中包含部门维度人事教育部这一个维度层次，如果将人数事实的所有维度考虑在内，就构成有关人数的多维分析立方体。 三、 OLAP的特点 电子数据表与OLAP相比，不具备OLAP的多维性、层次、维度计算以及结构与视图分离等特点。 多维。维是OLAP的核心概念，多维性是OLAP的关键属性，这与数据仓库的多维数据组织正好相互补充。为了使用户能够从多个维度、多个数据粒度查看数据，了解数据蕴含的信息， 系统需要提供对数据的多维分析功能，包括切片、旋转和钻取等多种操作 四、 OLAP的操作 OLAP比较常用的操作包括对多维数据的切片与切块、上钻（drill-up）与下钻（drill-down）以下旋转（rotate）等。此外，OLAP还能对多维数据进行深加工。 OALP的这些操作使用户能够从多个视角观察数据，并以图形、报表等多种形式展示，从而获取隐藏在数据中的信息。 （1）切片与切块。 选定多维数组的一个维成员做数据分割的操作称为该维上的一个切片。通常把多维数组中选定一个二维子集的操作视为切片，假设选定的维i上的某个维成员Vi，则此多维数组子集可以定义为（维V1……，维Vi，维N,变量）。当某维只取一个维成员时，便得到一个切片，而切块则是某一维取值范围下的多个切片的叠合。通过对数据立方体的切片或切块分割，可以从不同的视角得到各种数据。 （2）钻取 钻取包括上钻和下钻。争取能够帮助用户获得更多的细节性数据，逐层的分析问题的所在和原因。 上钻又称为上卷（roll-up）。上钻操作是指通过一个维的概念分层向上攀升或者通过维归约在数据立方体上进行数据汇总。例如在上面的示例中，可以按学历汇总数据，如把各种学历的都归约为所有学历，便可以得到沿学历维上钻的数据汇总。 下钻是上钻的逆操作，通过对某一汇总数据进行维层次的细分（沿维的概念分层向下）分析数据。下钻使用用户对数据能够获得更深入的了解，更容易发现问题本质，从而做出正确的决策。 钻取使用户不会再被海量的数据搞得晕头转向：上钻让用户站在更高层次观察数据，下钻则可以细化到用户所判决的详细数据。钻取的尝试与维度与维所划分的层次相对应，根据用户关心的数据粒度合理划分。 （3）旋转 旋转又称转轴，是一种视图操作，通过旋转变换一个报告或页面显示的维度方向，在表格中重新安排维的位置，例如行列转换。这种对立方体的重定位可以得到不同视角的信息。 （4）其他OLAP操作 除以上常用多维操作外，还有其他多维操作。 钻过（drill-across）。钻过操作涉及多个事实表的查询并把结果合并为单个数据集，一个典型的例子就是预测数据与当前数据的结合：通常预测数据与当前数据存在于不同的表中，当用户比较预测销售与当月销售时，需要跨多个事实表查询。 钻透（drill-through）。钻透使用关系SQL,查询数据立方体的底层，一直到后羰的关系表。 五、 OLAP的分类 OLAP分类 按处理方式分类 Server OLAP:绝大多数的OLAP系统都属于此类，Server OLAP在服务端的数据库上建立多维数据立方体，由服务端提供多维分析，并把最终结果呈现给用户 Client OLAP：所相关立方体数据下载一本地，由本地为用户提供多维分析，从而保证在网络故障时仍然能正常工作。 按存储方式分类 ROLAP。ROLAP使用关系数据库或扩充关系数据库（XRDBMS）存储管理数据仓库，以关系表存储多维数据，有较强的可伸缩性。其中维数据存储在维表中，而事实数据和维ID则存储在事实表中，维表和事实表通过主外键关联。 MOLAP。MOLAP支持数据的多维视图，采用多维数据组存储数据，它把维映射到多维数组的下标或下标的范围，而事实数据存储在数组单元中，从而实现了多维视图到数组的映射，形成了立方体的结构。大容量的数据使立方体稀疏化，此时需要稀疏矩阵压缩技术处理，由于MOLAP是从物理上实现，故又称为物理OLAP（Physical OLAP）。 DOLAP。DOLAP是属于单层架构，它是基于桌面的客户端OLAP，主要特点是由服务器生成请求数据相关的立方体并下载到本地，由本地提供数据结构与报表格式重组，为用户提供多维分析，此时无需任何的网络连接，灵活的存储方式方便了移动用户的需求，但支持数据有限，使用范围有限。
什么是聚合表（Aggregate Table）
下描述了一个数据库的结构。该数据库中共有五张表，分别是Sales表，Customer表，Time表，Product表和Mfr表。这个数据库的作用是存储每一笔交易：包括这笔交易发生在什么时间，交易的产品类型，进行交易的客户信息，交易方式，交易了多少件产品以及成交金额是多少。 模型中有一张事实表（Sales），两个度量列（units和dollars），四个维度表（Product, Mfr, Customer, Time）。在这个星型模型的最顶层，我们创建了以下多维模型：
- [Sales]立方体包含[Unit sales]和[Dollar sales]两个度量值；
- [Product]维度包含[All Products]，[Manufacturer]，[Brand]，[Prodid]四个级别；
- [Time]维度包含[All Time]，[Year]，[Quarter]，[Month]，[Day]五个级别；
- [Customer]维度包含[All Customers]，[State]，[City]，[Custid]四个级别；
- [Payment Method]维度包含[All Payment Methods]，[Payment Method]两个级别。 假设现在我们要对交易做一些统计，例如，某一件特定产品在某一个时间段内以某种特定方式总共卖出多少件或多少钱，这时成交产品数和成交金额是我们最终关注的内容，其他的因素例如时间、产品、方式等都只是对我们最终关注内容进行统计的限制条件。
在上面的例子中，限制条件有时间、产品类型、用户类型和交易方式， 有时我们并不需要同时使用所有的限制条件，例如，当我们只想知道指定产品的成交总金额时，那么除了产品类型之外其他三个限制条件都是多余的，而在查询时，需要在整个事实表中执行查询，找出产品类型为指定类型的所有产品然后再做统计，为了提高查询效率，我们可以新建一张表，这张表按照产品类型把事实表中的行合并到一起，合并的方式是抛弃其他维，把度量值按特定的方式（max，min，sum，count或avg）整合到一起。这种表被叫做聚合表(Aggregate Table)。
聚合表的应用场景 事实表中的行构成了一个集合，每一维（或若干维）按照其取值的不同可以将事实表这个全集划分成若干个不相交的子集。聚合表所做的工作实际上就是把划分出的子集归为数据库表中的一行，这样做一方面可以减少数据库表的行数，另一方面也省去了查询时所需要做的一些统计工作，从而提高查询时的效率。
- 使用Mondrian做大数据量（如>100W行）的OLAP分析时，考虑是否可以使用聚合表进行优化。
- 然而Mondrian的优化方式又不限于聚合表这一种，是否要进行聚合表优化，要根据实际情况来决定。
- Mondrian目前并不提供对聚合表的数据同步机制，如果要做实时OLAP，需要自己实现聚合表和事实表中的数据同步。 聚合表的定义见：http://www.cnblogs.com/panfen...
6、集合操作
NON EMPTY
在多维空间，数据很多时候是稀疏的。比如：比如，不是每一个产品都销售给了所有的客户，不是每一个客户在每个时期都购买了产品。如果按维度所有成员交叉得出报表，就会有很多空行、空列。 要从查询结果去掉这些空行 SELECT { [Time].[Jan,2005],[Time].[Feb,2005] } ON COLUMNS , NON EMPTY { [Product].[Toys], [Product].[Toys].Children } ON ROWS FROM Sales WHERE ([Measures].[Dollar Sales], [Customer].[TX]) 这样空行就去掉了。non empty 可用于任何轴上。
CROSS JOIN
很多时候，我们需要对两个不同的集合进行交叉，也就是要得到两个集合成员的所有组合。CrossJoin()函数就是用来得到组合的最直接方式，它的语法是 CrossJoin (set1, set2) 以下语句在每个季度下分出两个度量 SELECT CrossJoin ( { [Time].[Q1, 2005], [Time].[Q2, 2005]}, { [Measures].[Dollar Sales], [Measures].[Unit Sales] } ) ON COLUMNS, { [Product].[Tools], [Product].[Toys] } ON ROWS FROM Sales CrossJoin 的结果是一个集合。因此支持CrossJoin 嵌套。
FILTER
Filter 函数用来筛选一个集合，它以一个集合和一个 boolean 表达式为参数 Filter (set，boolean-expression)。 例如，以下表达式会返回关联的产品销售额至少为500 的产品分类的集合。 Filter ( { [Product].[Product Category].Members }, [Measures].[Dollar Sales] >= 500 ) 要求销售额至少为 150 并且销售额要至少在成本的1.2 倍以上 Filter ( { [Product].[Product Category].Members }, ([Measures].[Dollar Sales] >= 1.2 *[Measures].[Dollar Costs]) AND [Measures].[Dollar Sales] >= 150 )
ORDER
Order()函数用于对一个集合进行排序，语法： Order (set1, expression[,ASC| DESC | BASC | BDESC]) SELECT { [Measures].[Dollar Sales] } on columns， Order ( [Product].[Product Category].Members, [Measures].[Dollar Sales], BDESC ) on rows FROM [Sales] WHERE [Time].[2004]
7、计算成员
在 sql 中可以增加计算出来的列，MDX 中同样也可以，在 MDX 中叫计算成员（CalculatedMember）。因为MDX 操作的是多维数据，计算成员实际是给一个维度增加成员。 语法： with member 成员标识 as ‘表达式’ [, 属性...] select ... 表达式用单引号引注。 以下例子增加一个新的度量[Avg Sales Price] WITH MEMBER [Measures].[Avg Sales Price] AS '[Measures].[Dollar Sales] / [Measures].[Unit Sales]' SELECT { [Measures].[Dollar Sales], [Measures].[Unit Sales], [Measures].[Avg Sales Price] } on columns, { [Time].[Q1, 2005], [Time].[Q2, 2005] } on rows FROM Sales WHERE ([Customer].[MA])
公式优先级（Solve Order）
当不止一个维度增加了计算成员时，由于每个维度的成员都有计算公式，在这些维度的交叉点上，就可以有多种计算顺序。这时候就不需要考虑公式优先级的问题。因此引入了 SOLVE_ORDER 属性 WITH MEMBER [Measures].[Avg Sales Price] AS ‘[Measures].[Dollar Sales] / [Measures].[Unit Sales]', SOLVE_ORDER=0 MEMBER [Time].[Q1 to Q2 Growth] AS ‘[Time].[Q2, 2005]- [Time].[Q1, 2005]’, SOLVE_ORDER=1 SELECT { [Measures].[Dollar Sales], [Measures].[Unit Sales], [Measures].[Avg Sales Price] } on columns, { [Time].[Q1, 2005], [Time].[Q2, 2005], [Time].[Q1 to Q2 Growth] } on rows FROM [Sales] WHERE ([Customer].[MA])
8、命名集合
命名集合（Named Set）允许预先定义的一个集合，供后面的语句使用。语法和计算成员类似。 with set 集合标识 as ‘集合表达式’ select ...
WITH SET [User Selection] AS ‘{ [Product].[Action Figures], [Product].[Dolls] }' MEMBER [Product].[UserTotal] AS ‘Sum ( [User Selection] )’ SELECT { [Time].[Jan, 2005], [Time].[Feb, 2005] } ON COLUMNS, { [Product].[Toys], [User Selection], [Product].[UserTotal] } ON ROWS FROM Sales WHERE ([Measures].[Unit Sales])
9、函数
http://mondrian.pentaho.com/d...
列出一些重要的，按返回类型来分类。
成员函数
.currentMember .parent .prevMember/.nextMember .firstChild/.lastChild .firstSibling/.lastSibling Ancestor(<Member>, <Level>) Ancestor(<Member>, <Numeric Expression>)
LAG 返回当前成员开始往前数的本层的第几个成员. <Member>.Lag(n) n是索引，0 是它本身，1是前一个(.prevMember)
LEAD 类似 Lag()，但方向相反
OpeningPeriod 返回某个层次上第一个后代成员 语法：OpeningPeriod([<Level>[, <Member>]])。 ClosingPeriod 返回某个层次上最后一个后代成员
PARALLELPERIOD 返回一个成员同层次对应位置的成员 ParallelPeriod([<Level>[, <n>[, <Member>]]])在时间维度上取同期(如上年同期)等的时候需要用到它。
集合函数
前面介绍的 members、children、descendants、crossJoin、filter、order 都是集合函数 union 合并两个集合。语法：Union(set1,set2[, ALL]) All 标志指示保留重复元素 Except 从set1里去除set2的元素，即求两个集合的差。Except(set1,set2[, ALL]) Head/Tail 返回集合Head/Tail元素 。 Head/Tail(set[, <count>))。 .SIBLINGS 返回成员的兄弟成员，包括它自己。<Member>.Siblings。 .MEMBERS 返回维度/层次的成员。<Dimemsion>.Members DESCENDANTS 返回成员的后代成员。Descendants (member, [level[,flag]]) flag 可以是：SELF、BEFORE、SELF_BEFORE_AFTER、LEAVES、AFTER、SELF_AND_BEFORE、SELF_AND_AFTER。
DrillDownLevel(set,[level]) 下钻（一级）成员。 DrillDownLevelBottom(set,index,level) 下钻最下一级成员。 DrillDownLevelBTop(set,index,level) 下钻最上一级成员。 DrillDownMember 下钻集合2中的成员.DrillDownMember(set1,set2[,Recursive])
TopCount 返回前n个数据的集合
<Set> TopCount(<Set>, <Numeric Expression>, <Numeric Expression>)
<Set> TopCount(<Set>, <Numeric Expression>)
（TopCount, BottomCount, TopPercent, Hierarchize ,etc.）
统计函数
count (set [,INCLUDEEMPTY]) 可选标记指定是包含无数据的元组 Sum (set [,数值表达式]]) max/min/median/avg(set [,数值表达式]])
逻辑函数
IS object is object2。 例如： [Jan 2000].PrevMember IS NULL [Jan 2000].Level IS [Time].[Month]
ISEMPTY 判断一个值是否为空。语法：IsEmpey(表达式)。
字符串函数
NAME 返回维度、层次等的名称。语法：<Dimension/Hierarchy/Level/Member>.Name PROPERTIES 返回成员的属性值。语法：<Member>.properties(<属性名>)
其他函数
这里是一些返回类型不定的函数。 iiF 根据条件返回值，类似Excel 的If 函数。语法：iif(<布尔表达式>, <值1>, <值2>)。 ITEM 根据索引返回集合中元素。语法：item(set, <index>)。 返回类型一般为元组。
