SSAS (MS SQL Server Analysis Service) 学习笔记

xblues

现在数据分析的工具已经不再是石器时代的那种纯 T-SQL 方式了，有公司后来使用 T-SQL + Excel 组合也可以干不少分析的工作。不过，更多公司的转向使用更加高级的数据分析工具，例如 MSAS，SAS 等。

简要介绍在这里
http://en.wikipedia.org/wiki/Microsoft_Analysis_Services

视频学习看这里
http://www.youtube.com/watch?v=yawq4SGoRoY

[ 本帖最后由 xblues 于 20-2-2010 18:13 编辑 ]

melbourner1978

虫版到底是作什么行业的？

xblues

我什么都做过了，最开始做桌面支持，硬件维护，网管，网页开发，程序员，项目管理，测试，过程改进，数据管理，数据分析，项目调节。

近期领域是Business Analyst （包括项目调节，过程改进，测试，数据管理和分析，简单应用开发，以及需求文档化）

xblues

我现在在本机上装了一个简化版本的SQL Server，可以用VS建立Cubes，可是没办法链接MSAS进行编译处理Cubes。

唯一的办法是下载一个标准版的SQL Server安装。

xblues

比较表格
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

看来 Enterprise 和 Developer 版本的特性没有区别，不过SQL已经变得好大，动辄3.5G啊。
Express 则只有基本功能，如果学习一些高级功能比如这个MSAS，就没有了。

看来要学习和自己实验MSAS，我只好下一个Enterprise 版本了。

xblues

看了上面那个视频录像，真的很好，从头至尾，讲解了一个使用MSAS的流程。

先使用VS BI开发用具，建立 Data Sources, 然后再DS基础上建立 Data Sources Views，然后在DSV基础上建立Cubes，然后编译处理这个Cube，就可以从各个维度来浏览数据了。最后因为，一般非开发人员不会去使用这个开发工具，一般人会使用MS Excel链接Cube，然后使用Excel浏览Cube，做分析。

原来这么简单啊，看似神秘的，早学习一下不就好了？原来用纯SQL写语句，每一个维度分析都需要用手工SQL代码写出来，最终的分析表出来是需要时间的，比如你凭直接用这几个字段分析数据，用Excel出来的图标也是有限的，客户想要临时看你成品表格以外的字段，你就需要重新写代码。

使用MSAS的好处是，软件自动分析数据表格，把所有可能的字段都拉进来，一次性把所有字段都分析出来了，任你随便摆弄数据，而且Cube一次性建立好，就在那里了，任何人都可以随时通过Excel链接Cube，随时分析。

xblues

传统方式： T-SQL +　Excel
BI方式：　Cube　＋　Excel

传统方式，你要按需求，从一系列原始表格，生成一个成品表，包含一些关键性字段，然后用Excel去链接这个成果表格，用Excel去分析各个维度之间的关系。

BI方式，MSAS按数据关系，自动生成一个Cube，Cube与你手工用SQL语句生成的成品表格的区别是Cube里面包含了所有的关系，也可以理解成是一个超大的成品表，包含了几乎需要用到的所有关键字段。然后用Excel去链接这个Cube，分析个维度之间的关系。

因为Cube包含了所有可能的关键字段，而且是瞬间完成了，机器代替手工才做，所以原来的Analytics的大部分时间花费在手工生成这个成品表格上，现在大部分时间可以花费在真正的使用Excel去分析数据了。

这可以说是数据分析的一次解放性的成果。

SAS的原理也差不多，不过因为SAS和SQL　数据库不兼容，所以，把大量的表格直接导入SAS的做法不现实，一般还是分析人员手工生成成品表格，然后转成Access方式，再倒入SAS，然后用SAS的图形界面代替Excel进行数据分析。

看来MSAS和SAS的侧重点还是有些不同的。

jingsun

可能你接触BI不久，其实这东西10几年前就有了，很多BI软件都可以做的，比如BO，congnos等等。

微软只从MS SQL 2005开始也加入抢市场的行列，而且和excel进行了较好的集成，但只是具体用法不一样，基本原理都差不多，都是免写SQL语句的友好界面。

总的来说，微软做的还不错的，更加平民化了。。。

[ 本帖最后由 jingsun 于 19-2-2010 14:01 编辑 ]

melbourner1978

原帖由 xblues 于 19-2-2010 11:43 发表

                               
登录/注册后可看大图

传统方式： T-SQL +　Excel
BI方式：　Cube　＋　Excel

传统方式，你要按需求，从一系列原始表格，生成一个成品表，包含一些关键性字段，然后用Excel去链接这个成果表格，用Excel去分析各个维度之间的关系。

...

SAS上也可以直接跑SQL，like：
proc sql...

melbourner1978

其实虫版的那两种方式都是BI流行的玩意，SAS的MDDB十几年前就有了，我感觉现在用star schema的方式比较流行，其实偶的理解就是空间（存储）与时间（效率）之间的取舍。SAS的很多项目都是用data warehouse维护，局部的多维查询才用cube，因为cube虽然快但是太占存储空间了

xblues

后来又想了一下，大多数数据库里保存的都是基础数据，靠基础数据形成的Cube，能够看出来的关系有限。即便是有了Cube，还是需要在建立Cube以前，把基础表的基础数据在一定程度上形成Super表，不一定是成品表，但是有些基础数据肯定是要被Rollup，才有看看价值的。

把基础表用SQL语句形成一些Super表以后，再生成Cube。所以SQL的手工操作还是不能够完全避免的，但是毫无疑问，Cube在一定程度上减轻了Analytic的手工写SQL的代码量。

xblues

具体介绍一下 star schema 可以么？

melbourner1978

原帖由 xblues 于 19-2-2010 14:21 发表

                               
登录/注册后可看大图

后来又想了一下，大多数数据库里保存的都是基础数据，靠基础数据形成的Cube，能够看出来的关系有限。即便是有了Cube，还是需要在建立Cube以前，把基础表的基础数据在一定程度上形成Super表，不一定是成品表，但是有些 ...

BI工具比如SAS的reporting即可以基于MDDB，又可以基于star schema，在model和ETL作完以后，都不用多少coding。我目前看到澳洲大型的BI项目，基本都是star schema的data warehouse，感觉Cube用起来局限性大，不适合大数据量的enterprise level。

xblues

看出来LS是高人，可以针对你所说的这些技术开几个专题贴么？向我一样的不够专业的同学肯定受益匪浅。我目前找的工作范围很多设计BI，可是我的BI的实际经验有限。想LS一样经历过实际项目，讲解起来肯定很明了。

melbourner1978

原帖由 xblues 于 19-2-2010 14:37 发表

                               
登录/注册后可看大图

看出来LS是高人，可以针对你所说的这些技术开几个专题贴么？向我一样的不够专业的同学肯定受益匪浅。我目前找的工作范围很多设计BI，可是我的BI的实际经验有限。想LS一样经历过实际项目，讲解起来肯定很明了。

虫版太抬举俺了，民工一个，离开BI好多年了，大家分享分享好了

xblues

一定要开贴，你提到的Star Schema 等概念最近我在找工广告中经常看到，希望能够开贴不吝赐教。

melbourner1978

原帖由 xblues 于 19-2-2010 14:43 发表

                               
登录/注册后可看大图

一定要开贴，你提到的Star Schema 等概念最近我在找工广告中经常看到，希望能够开贴不吝赐教。

如果虫版有兴趣进军BI，可以Google一下star schema，或者买本书。star schema大概在8，9年前，忽悠过一时，其实跟普通的database design没有什么差别，不过一个是以transaction为核心，一个是以汇总（fact）为核心，为了方便汇总而减少数据量的DB design而已。

xblues

这个和SQL Server里面那个Schema，就是可以自己拖拽一些表建立一个包含好多表以及表之间关系的链接的图标有什么区别么？SQL Server里面的这个自己建立的Schema是不是Star Schedma的一种啊？Star Schema是不是仅仅是一个概念呢？


  问：     给出在STAR   SCHEMA中的两种表及它们分别含有的数据  
   
  解答：Fact   tables   和dimension   tables.     fact   table   包含大量的主要的信息而   dimension   tables   存放对fact   table   某些属性描述的信息

melbourner1978

就是个概念而已，是以transaction为驱动，还是以reporting驱动，不过BI好啊，现在经济不好的情况下，澳洲还有大批的BI项目开工，我都想回到BI了

xblues

sql2000的分法 星型和雪花。 实际运用中星型使用更多，也更有效率。
sql2005的分法 常规（等同于星型）、事实（FactTable直接放上维度字段）、1对多（雪花）、多对多（适用多FactTable的情况）。 所以也是常规和多对多用的多些。

xblues

你现在干嘛呢？

melbourner1978

原帖由 xblues 于 19-2-2010 15:05 发表

                               
登录/注册后可看大图

你现在干嘛呢？

CRM

xblues

用美国法院的一个案例组略介绍了SQL Server，Star Schema，MSAS，MSIS，MSRS，Excel。值得一看。

http://www.youtube.com/watch?v=-j5J7lXav7Y

xblues

教你如何建立Cubes
http://www.sqlcoffee.com/AnalysisServices_0001.htm

不过教程里面指定的那几个表，我在我自己安装的AdvantureWork数据库里都找不到，我就随便自己抓了几个表做实验，可是最后建立的Cube编译不了，也不能够发布。

xblues

SSAS中Cube的结构
　　在SSAS(SQL Server Analysis Services)中构建Cube和编写MDX的时候，我们很容易被一些名词弄糊涂，比如：Dimension(维度)，Measures Dimension(度量维度)，Measure(度量)，Hierarchy(层次结构)，Attribute hierarchy(属性层次结构)，Level(级别)，Cell(单元)，Member(成员)，Member Property(成员属性)，Set(集)，Turple(元组)等等。要想弄清楚这些名词，就必须理解Cube的结构。
　　上述名词的解释详见：http://msdn2.microsoft.com/en-us/library/ms144884.aspx
　　Cube、Dimension和Measure
　　Cube就象一个坐标系，每一个Dimension代表一个坐标轴，要想得到一个点，就必须在每一个坐标轴上取的一个值，而这个点就是Cube中的Cell。见下图(来源于http://msdn2.microsoft.com/zh-cn/library/ms144884.aspx)：
　　

                               
登录/注册后可看大图

　　上图很好的说明了Cube、Dimension、Measure之间的关系。这里需要注意的是：其实Measure也属于一个维度，即Measures Dimension。所有的Measure构成了Measures Dimension，这个维度的只有一个Hierarchy，而且这个Hierarchy只有一个层次(Level)。
Hierarchy、Level和Memeber
　　在上节的图中，每个Dimension只有一个Hierarchy，而在实际的环境中，一个Dimension往往有很多Hierarchy。因此，上一小节中关于“Cube就象一个坐标系，每一个Dimension代表一个坐标轴”这句话其实不够准确，准确的说应该是每一个Hierarchy代表了一个坐标轴，而Hierarchy中每一个Member代表了坐标轴上的一个值。下图以时间维度为例展示了Dimension的内部结构。
　　

                               
登录/注册后可看大图

　　此外，我们需要说明的是：
　　1) 上图中说明的是一般Dimension的结构，在实际的模型中，其实可以做很多自定义的工作。比如：我们可以修改Hierarchy的默认Member。
　　2) 一般情况下，SSAS中Hierarchy的默认Member是All(在你的模型中，可能叫其他名称)。换句话说在MDX中[时间].[财政]等价于[时间].[财政].[All]，[时间].[财政].Children等价于[时间].[财政].[All].Children。
　　3) Dimension_Name.Hierarchy_Name.Level_Name等价于Dimension_Name.Hierarchy_Name.Level_Name.Members。比如：[时间].[财政].[半年]等价于[时间].[财政].[半年].Members。Level的Members是该级别的所有元素(对于[时间].[财政].[半年].Members={[上半年]，[下半年]，[上半年]，[下半年]}，其中前两个是2001年下的，后两个属于2002年)，而Hierarchy的Members包含了该Hierarchy下所有的内容。
　　4) 当且仅当一个Dimension下只有一个Hierarchy，则Dimension_Name等价于Dimension_Name.Hierarchy_Name纬度。比方说：时间维度只有一个财务Hierarchy，则[时间]等价[时间].[财务]。
　　5) Attribute Hierarchy中Members的层次是两层(MSDN的说法更加准确，这里简化了一些)：第一层：All，第二层：叶子节点。也就是说它和多层的Hierarchy相比，两者结构完全相同，这是统一维度模型(Unified Dimensional Model)一个方面的体现。

注意：采用Attribute Hierarchy能够使编写MDX更加容易，但同时也增加了Cube的容量，加大了Cells的个数，对性能有负面影响。因此，在建模的时候，我们可以把一些Attribute Hierarchy的AttributeHierarchyEnabled属性设置成False，同时在编写MDX时，以Member Property的方式来引用，这样可以在满足需求的前提下提高性能。
　　6) Measures Dimension是一个特殊的维度，它的Members中没有All这个成员，它的默认Member可以在建模时指定。
　　7)对于一般的维度，其第一层Level的默认是“(All)”。
　　Turple和Set
　　如果说Cube好像一个坐标系，那么Turple、Set的关系就好比点和面的关系。Turple由Cube中每个Hierarchy的一个Member组成。由于Hierarchy的个数非常多，所以一般不可能在Turple表达式中把所有的Member都明确指定，故此，为了简化开发，所有没有明确指定Member的Hierarchy，用该Hierarchy的默认Member代替。也就是说：([时间].[财政].[2001].[上半年]) 等价于([时间].[财政].[2001].[上半年]，[时间].[日历].[All])。另外我们需要注意的：
　　1) 有的说法认为：Turple是“Cube 上的一个子集(不断开的子Cube)，这个看法是不准确的，因为Turple只是一个点，不是面，它仅仅由每个Hierarchy的一个Member组成的。
　　2) 外面()起来的表达式不一定是Turple。比如：([时间].[财政].[半年].Members，[时间].[日历].[2001].[上半年])就不是一个Turple，而是一个Set，其原因在于，Turple是点，它仅仅由每个Hierarchy的一个Member组成，如果在任何一个Hierarchy上有两个成员，则其就变成Set了。
　　注意：([时间].[财政].[半年].Members，[时间].[日历].[2001].[上半年])等价于Crossjoin([时间].[财政].[半年].Members，[时间].[日历].[2001].[上半年])或{[时间].[财政].[半年].Members}*{[时间].[日历].[2001].[上半年]}，在SSAS的MDX中，我们可以在()中定义多个用逗号分隔开的表达式，编译器会进行分析，如果发现是Set的话，就把它转化成多个Set相乘的形式。
　　3) Set中的Turple可以重复。比如：{[时间].[日历].[2001].[上半年]，[时间].[日历].[2001].[上半年]}并不等于{[时间].[日历].[2001].[上半年]}，因为前者有两个Turple，后者只有一个。
　　4) SSAS能够根据上下文的需要，自动把Turple变成Set，单个Member变成Turple，多个Member变成Set。这也是我们常常混淆Turple和Set的原因。详细的例子如下：
　　a)上下文需要Set时，([时间].[日历].[2001].[上半年])自动转化成{[时间].[日历].[2001].[上半年]}。
　　b)上下文需要Turple时，[时间].[日历].[2001].[上半年]自动转化成([时间].[日历].[2001].[上半年])。
　　c)上下文需要Set时，[时间].[日历].[2001].Children自动转化成{[时间].[日历].[2001].Children}。
　　总结
　　总体来看，SSAS中的Cube的内部结构非常的清晰，在实际开发中，只要多注意一下默认的一些转化，使用起来是很容易的。