《SQL SERVER 商业智能维护与管理》读书笔记(七): SSAS CUBE进阶

本章讨论如何建立供报表使用的最终用户层，它由KPI、行为、视图、翻译组件，以及如何建立新的度量组和用户特征。

学习目标：

l         建立和维度度量组与分区

Ø         建立度量组

Ø         增加度量到度量组

Ø         修改度量属性

l         建立关键性能指标

Ø         建立值和目标表达式

Ø         建立趋势指示器和表达式

Ø         建立状态指示器和表达式

l         建立行为

Ø         建立行为目标

Ø         建立行为内容

Ø         建立行为类型和行为表达式

l         使用MDX建立查询和计算

Ø         使用MDX查询多维数据

Ø         建立计算

Ø         在度量维度中建立计算成员

Ø         在其它维度中建立计算成员

Ø         建立命名集

l         建立CUBE

Ø         定义翻译

Ø         定义视图

 

一、建立度量组和度量

1、建立新的度量组

1）另外建立度量组的两种情况：

l         需要建立与事实表一样数据的度量组

l         建立计数度量

2）步骤

A）确保使用到的事实表加入到DSV中

B）在新建度量组向导中选择底层事实表

2、理解度量组的属性

3、增加和配置度量

1）增加一个新的度量

l         全加性度量

聚集跨跃全部维度 ，如计算销售总额

l         半加性度量

聚集只使用部分维度，如计算帐户余额和库存余额

l         无加性度量

不跨跃任何维度，如比率

2）配置度量

度量表达式：表达式的第二个操作数必须是来自其它度量组的度量

二、建立关键性能指标

许多组织使用KPI来测试业绩。因此KPI被用来表示关键成功因素的可测量值，比如销售利润、收入增长率、客户增长率等。

1、理解KPI目标、值、趋势、状态属性

KPI是一种扩展度量，由四个表达式属性组成，其中值是必填的， 其它可选。

1）值

2）目标

如下面的表达式，指定为比去年同期销售额增长40%

1.4*([Order Date].[Calender].PrevMember, [Measures].[Reseller Sales Amount])

PrevMember是MDX函数，返回当前层次的前一个成员

3）状态

指定值和目标间如何比较。

返回值：-1    未达标的业绩

           0     可接受的业绩

        1     优良业绩

CASE

WHEN KPIValue("Reseller Sales")/ KPIGoal("Reseller Sales")>=1

THEN 1

WHEN KPIValue("Reseller Sales")/ KPIGoal("Reseller Sales")<1

       AND KPIValue("Reseller Sales")/ KPIGoal("Reseller Sales")>=0.85

THEN 0

ELSE -1

END

4）趋势

取值范围：trend>=-1，trend<=1

([Measures].[Reseller Sales Amount] –

([Order Date].[Calendar].PrevMember, [Measures]. [Reseller Sales Amount]))/

[Measures].[Reseller Sales Amount]

2、其它KPI属性

l         关联度量组

与哪一个度量组关联

l         Display Folder

l         Parent KPI

常用于将一组KPI显示在一个计分卡内。

l         Current Time Member

指定适用于当前成员的时间维成员

l         Weight——权重

l         状态指示器和趋势指示器图标

3、察看KPI

可以使用对KPI感知的应用程序浏览KPI，如EXCEP2007，REPORT SERVICES 2005，也可以在SSMS中用MDX查询，如：

Select { KPIValue(“Reseller Sales”),

       KPIGoal(“Reseller Sales”),

       KPIStatus(“Reseller Sales”),

       KPITrend(“Reseller Sales”)} on Columns,

       [DimPoduct].[Product Category].Members on Rows

From [Adventure Works DW]

Where [Order Date].[Calendar].[Calendar year].&[2003]

三、定义行为、翻译和视图

1、实现“动作”

场景：如果用户想查看与某个产品相关的所有定单详细信息，而CUBE中又没有存储定单的详细信息，这时可以实现一个报表动作，请求一份SSRS报表，显示来自其它系统中的数据。

注意：并不是所有的客户端都支持Actions。如EXCEL2007支持ACTION，REPORT SERVICE 2055不支持，因此在选择使用ACTION前，认真考虑所使用的客户端工具。

1）理解动作类型

l         常规动作

基于动作内容又可以进一步分类

内容类型	目的
数据集	动作内容是MDX语句
私有	动作内容是客户端指定的；客户端负责解释动作玉语义
行集	动作内容是接收数据的命令语句。目标是OLE DB数据源
语句	动作内容是一条OLE DB命令；区别是它不产生任何结果集，只返回成功还是失败
URL	动作内容是URL；需要指定使用的协议

l         下挖CUBE中的详细数据。这是客户端可以发送给SSAS执行的唯一一个动作。

只适用于维度组。

l         报表动作。用于请求执行SSRS报表，命令行是SSRS的URL和参数。

2）理解动作发现

配置Action：Action Type ，Action Object（可以是MDX表达式）

原理：

客户端将用户上下文以坐标的形式发送给服务器；服务器端使用坐标匹配动作范围，识别适用于那个范围的动作，如果找到了这样的动作，解析它的命令并发送给客户端。

2、使用翻译实现本地化

翻译是指CUBE元数据的译本

1）翻译CUBE

Translation Tab->New Translation

2）翻译维度

Dimension Designer->Translation Tab->New Translation

维度成员的本地化

默认情况下，SSAS根据线程的语言决语言

3、实现CUBE视图

注意：CUBE视图不是一种安全机制，只是用于简化用户使用

四、使用MDX建立计算和查询

1、MDX基础

1）元组

CUBE空间中的一个多维坐标

&[key]    使用KEY来查找成员

2）集合

具有相同维数的元组的集合

2、基本的MDX查询

1）坐标轴

2）计算成员

With Member [Gross Profit] As

       ‘[Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]’

Select {[Measures].Internet Sales Amount},[Gross Profit]}

From [Adventure Works DW]

Where [Order Date].[Calendar Year].&[2003]

3、使用MDX函数

1）Current Member

指维度成员的Default  属性

2）层次导航函数

l         PrevMember

l         Members

返回层次中的所有成员

l         Childrens

返回指定成员下的所有成员

l         Parents

返回指定成员的父成员

3）时间导航函数

l         Lag

返回指定成员的偏移成员

[Order Date].[Calendar].Lag(1)

l         ParallelPeriod

返回上一期间中与指定成员具有相同的相对位置的成员。

ParallelPeriod 函数具有下列默认值：

如果既没有指定级别表达式，也没有指定成员表达式，则默认成员值为具有某种时间类型 (Time.CurrentMember) 的维度的当前成员。

如果指定了级别表达式，但未指定成员表达式，则默认成员值为 Level_Expression.Hierarchy.CurrentMember。

默认索引值为 1。

默认级别为指定成员的父级别。

ParallelPeriod 函数等效于下面的 MDX 语句：

Cousin(Member_Expression, Lag(Ancestor(Member_Expression, Level_Expression), Numeric_Expression)

示例：

下面的示例以季度级别为基准并以三个期间为间隔，返回了 October 2003（2003 年 10 月）的并行期间，即 2003 年 1 月。

SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]

   , 3

   , [Date].[Calendar].[Month].[October 2003])

   ON 0

   FROM [Adventure Works]

下面的示例以半期级别为基准并以三个期间为间隔，返回了 October 2003（2002 年 10 月）的并行期间，即 2002 年 4 月。

SELECT ParallelPeriod ([Date].[Calendar].[Calendar Semester]

   , 3

   , [Date].[Calendar].[Month].[October 2003])

   ON 0

   FROM [Adventure Works]

4、建立计算成员

计算成员在运行时动态计算

1）语法

CREATE [ SESSION ] [HIDDDEN] [ CALCULATED ] MEMBER CURRENTCUBE | Cube_Name.Member_Name

      AS MDX_Expression

      [,Property_Name = Property_Value, ...n]

......[,SCOPE_ISOLATION = CUBE]

[format_string=…],

[NON_EMPTY_BEHEVIOR= …]

如果NON_EMPTY_BEHEVIOR的结果为NULL，则计算成员的结果也为NULL，一般是从性能上考虑

2）什么时候使用计算成员

l         需要建立不属于某个度量组的度量

l         半加性度量

 

5、定义命名集——维度成员的集合

CREATE [SESSION] [HIDDEN] SET

   CURRENTCUBE | Cube_Name

            .Set_Name AS 'Set_Expression'

CREATE SET [Adventure Works].[Core Products] AS '{[Product].[Category].[Bikes]}'

示例：

SELECT [Core Products] ON 0

  FROM [Adventure Works]

五、总结

1、度量组是度量的容器。它最重要的属性是AggreationFunction

2、KPI是可量化的度量

3、使用ACTION扩展CUBE的功能

4、使用Translation本地化CUBE和维度的元数据及维度标题

5、视图是CUBE元数据的一个子集，减少了CUBE的复杂性

6、使用计算成员和命名集实现业务逻辑