BI笔记之---合理处理SSAS数据库的几点建议

今天又有朋友遇到SSAS数据库处理速度慢的情况&＃xff0c;主要是由于数据聚合量确实很大&＃xff0c;每次处理都要超过三十分钟&＃xff0c;有没有什么方法能让处理的时间少一些呢&＃xff1f;

从事BI工作有七个年头了&＃xff0c;这样类似的问题绝对可以排在职业圈内TOP 10的FAQ当中。这样的问题往往都略有复杂&＃xff0c;在此根据遇到过的一些场景&＃xff0c;罗列一些自己的经验。

由于篇幅限制&＃xff0c;这里只介绍遇到问题时的解决思路&＃xff0c;详细的操作我会链接到我的其它随笔供大家实际操作的时候参考&＃xff0c;还有很多建议上的细节都尽量标出官方文档的出处供大家获取更多内容。

 

 

提升数据仓库层相关表的查询效率

SSAS数据库在处理时&＃xff0c;要向数据仓库层抛SQL查询。所以对相应的维表和事实表进行优化是这一步的关键。

我先前见过一个情况&＃xff0c;就是有一个项目的事实表是一个视图&＃xff0c;而这个视图里有比较复杂的运算和连接。所以每次处理多维数据集的时候&＃xff0c;都要等查询要准备好久才开始读取数据。后来我建议定期把视图里的数据放到一张表里&＃xff0c;保证每次读事实表的数据不用经过视图而是直接读已经处理好的数据。

这是最简单直接的方法&＃xff0c;将事实表的数据"实体"化&＃xff0c;让视图中的数据计算一次然后将结果保存到表中&＃xff0c;以保证后续的查询分析应用都可以快速的得到结果。

剩下的就是基本的数据库优化&＃xff0c;比如索引优化等&＃xff0c;此外还有大数据解决方案如HADOOP或者PDW等&＃xff0c;这部分的内容已经远远超出了本文所描述的范围&＃xff0c;这里不再做详细讲解。

 

 

增量更新

这是最常用的一个方法。假如每个周期产生的数据量是100mb&＃xff0c;那么在刚开始的几个处理周期里可能不会有问题&＃xff0c;但假如说你的处理周期是每周或者每天&＃xff0c;那么随着时间的推移你的历史数据会越来越多&＃xff0c;每次都全量处理就不是很明智。所以我们就需要用增量的方法来处理数据。

在SSAS中&＃xff0c;增量处理需要指定增量查询。也就是说&＃xff0c;需要你有一个严格的数据流程。首先&＃xff0c;增量处理之前&＃xff0c;你需要把增量数据预备好&＃xff0c;在增量处理完之后&＃xff0c;还需要妥善的处理增量数据&＃xff08;比如在表或者视图中&＃xff09;&＃xff0c;避免重复进行的增量处理导致数据翻番。

如果数据仓库有更新的情况&＃xff0c;可以在设计数据仓库的时候考虑1-1&＃43;1的方案。具体方法这里只说一个思路&＃xff0c;大家可以根据自己系统的情况进行设计。

具体的参考流程&＃xff0c;可以参考我先前的一个笔记&＃xff1a;

BI笔记之---增量方式处理多维数据集

这篇将介绍如何生成测试数据然后利用这些测试数据演示如何做基本的数据增量更新&＃xff0c;同时也会让你对多维数据集的增量更新有一个了解。

 

 

建立分区

跟数据库里的表一样&＃xff0c;SSAS的多维数据集也可以建立分区。理论上来说&＃xff0c;建立分区对数据的处理速度不会有太大的影响&＃xff0c;但是之所以放在这里&＃xff0c;是由于&＃xff0c;可以借助分区的方式&＃xff0c;来间接的实现"增量更新"。

上一步对增量更新的介绍&＃xff0c;你可以看到实际操作起来是有多复杂。借助分区的方式&＃xff0c;你就可以多少偷一下懒。具体的思路就是&＃xff0c;把多维数据集按照某一维度进行分区&＃xff0c;时间或者空间的方式均可。比如按照时间的方式&＃xff0c;以月为粒度进行分区。然后在每次处理的时候&＃xff0c;只处理增量数据点所在的那个分区。

这个方法的关键点就是如何自动的识别出那个待处理的分区。我个人认为主要在于多维数据集的设计要完全按照一个严格的标准。比如对分区名称有一个严格的命名规范&＃xff0c;以让代码可以很容易的找到这个分区。

具体的操作方法&＃xff0c;可以参考我先前的一个随笔&＃xff1a;

BI笔记之---Cube增量处理的一个场景的处理方案

里面主要介绍了用编程的方法来根据指定的规则&＃xff0c;找到待处理的分区&＃xff0c;然后对其进行处理。

Cube的分区大小到底设置多大才合适&＃xff0c;这个问题经常被问到。在这里文档中有一处可以参考&＃xff1a;

将超过 2 千万行或大小超过 250 MB 的大分区拆分为较小的分区以改进性能

出处&＃xff1a;

http://technet.microsoft.com/zh-cn/library/bb630302(v&＃61;sql.105).aspx

这里仅是一个大体的参考&＃xff0c;数据行数还需要具体考察每一行的数据两大小。

 

 

合理设置维度属性

合理设置维度属性关系&＃xff0c;设置刚性或者柔性关系类型。这里主要摘录微软文档中的内容进行简单的介绍。

关于属性维度属性的关系&＃xff0c;摘录文档中的一句话&＃xff1a;

属性关系具备以下优点&＃xff1a;

减少维度处理所需的内存量。 加快维度、分区和查询的处理速度。

提高查询性能&＃xff0c;因为存储访问速度更快而且执行计划更优化。

如果用户定义的层次结构是沿关系路径定义的&＃xff0c;则聚合设计算法会选择更有效的聚合。

引用地址&＃xff1a;

http://technet.microsoft.com/zh-cn/library/ms174878.aspx

关于刚性和柔性关系的说明&＃xff0c;摘录文档中的一句话&＃xff1a;

指示成员关系是否随时间而更改。 值为 Rigid 和 Flexible&＃xff0c;前者表示成员之间的关系不随时间而更改&＃xff0c;后者表示成员之间的关系随时间而更改。 默认值为 Flexible。 如果您将关系定义为 Flexible&＃xff08;柔性&＃xff09;&＃xff0c;则将删除聚合并作为增量更新的一部分重新计算&＃xff08;如果只添加了新成员&＃xff0c;则将不删除聚合&＃xff09;。 如果您将关系定义为 Rigid&＃xff08;刚性&＃xff09;&＃xff0c;则 Analysis Services 会在增量更新维度时保留聚合。 如果定义为刚性的关系发生了实际更改&＃xff0c;Analysis Services 会在增量处理过程中生成错误。 指定适当的关系和关系属性&＃xff0c;可提高查询和处理性能。

引用地址&＃xff1a;

http://technet.microsoft.com/zh-cn/library/ms176124.aspx

总体来说&＃xff0c;通过属性关系和关系类型的设置&＃xff0c;虽然对处理时间的影响不见得最明显&＃xff0c;但这都是设计SSAS数据库的一个很好的标准和习惯。

 

 

数据粒度提升

有很多项目为了能让数据仓库足够"大"&＃xff0c;会把数据的粒度收集的足够细。比如某系统一天收集的数据量就有一个G。而浏览了所有报表之后&＃xff0c;发现报表中大多数的时间粒度都是到月&＃xff0c;只有部分是到天的。

当然&＃xff0c;我不否认数据粒度越细&＃xff0c;越容易发现更有用的信息。但是对于SSAS数据库这层&＃xff0c;对于通常的统计分析&＃xff0c;对数据粒度要求不高&＃xff0c;可以考虑将事实数据GROUP到上一级的粒度&＃xff0c;比如秒到小时&＃xff0c;或者小时到天&＃xff0c;依次降低事实数据的数量。

对于确实需要小粒度统计分析的&＃xff0c;建议只保留近段时间的数据就可以&＃xff0c;这样通常都可以满足大部分需求。而粒度上升到什么层次才合适&＃xff0c;建议根据实际的需求然后重新考察数据粒度的确定是否合适。

总之&＃xff0c;原则就是&＃xff0c;在资源有限的情况下&＃xff0c;尽量"把钱用在刀刃上"&＃xff0c;然后根据不同需求的不同特点&＃xff0c;再去做单独的设计。

 

 

数据样本抽取

在开发和测试过程中&＃xff0c;没有必要直接把全部的历史数据拿过来做测试。这主要是因为在各个环节中都可能要消耗很多时间等待&＃xff0c;后续的开发和测试发现失败或者有错误后&＃xff0c;将流程进行修正&＃xff0c;还需要再重新完整的跑一遍。

你可以认为&＃xff0c;一个流程只要一个晚上能处理完&＃xff0c;到第二天上班时能看到结果就可以了。但是&＃xff0c;如果后续的测试验证数据流程有bug&＃xff0c;那么就意味着还要跑一个晚上&＃xff0c;这样项目进度很难保证。即使是一个要跑一个小时的流程&＃xff0c;你可以算算一天有几个小时可以反复的开发和测试然后又去验证这个过程呢&＃xff1f;

所以这里建议在开发和测试的过程中&＃xff0c;只拿一小部分数据&＃xff0c;比如在10年的数据中&＃xff0c;只取一年或者一个月的&＃xff0c;或者在所有产品品牌中&＃xff0c;只取一个或者几个品牌做整个项目的BI流程测试&＃xff0c;最后验证的也只是这一小部分数据&＃xff0c;等这些小数据处理成功后&＃xff0c;再去处理完整的数据。

数据的抽取方法&＃xff0c;可以在数据源视图中进行限制&＃xff0c;也可以通过分区来动态控制。我个人建议选择前者&＃xff0c;操作起来比较容易些&＃xff0c;不需要经常更改Cube的结构。

 

 

总结&＃xff1a;

解决处理慢的问题&＃xff0c;基本上就是从性能&＃xff0c;方法和设计上下手&＃xff0c;根据不同的场景可以选择不同的方案。

此外&＃xff0c;可以参考这篇《设计警告规则&＃xff08;Analysis Services - 多维数据&＃xff09;》

http://technet.microsoft.com/zh-cn/library/bb630321(v&＃61;sql.105).aspx

总之&＃xff0c;解决问题的方法很多&＃xff0c;这里只列举一些比较常见的问题以及我个人的建议&＃xff0c;其它有代表性的问题也欢迎大家列出来在这里做进一步的探讨。

最后&＃xff0c;希望这篇对大家有帮助。