代码改变世界

使用 SQL Server 2012 Analysis Services Tabular Mode 表格建模 图文教程

2013-05-22 22:44  BIWORK  阅读(18099)  评论(5)  编辑  收藏  举报

以下内容为我个人对 MSDN 上提供的 "SQL Server 2012 Analysis Services Tabular Model 表格建模" 教程的实践, 包含了每一课中具体操作的图片实例, 一步一步图文记录了从数据源导入, 创建关系, 计算列, 度量值, 层次结构, 透视, 角色到部署等每一个阶段, 对这个教程相信是一个有益的补充.

如果觉得光看 MSDN 上文字教程比较枯涩, 对比着图片来一步一步操作也会帮助节省大家宝贵的学习时间, 可以快速浏览本文了解一下 Tabular Mode 下各对象创建的难易程度, 较之传统的 Multidimensional Mode 是否会更加简单易用一些.

MSDN 原教程 - http://msdn.microsoft.com/zh-cn/library/hh231691.aspx

以下正文开始

SQL Server 2012 Tabular Model 表格建模

目标

在表格建模的过程中我们需要能够熟练掌握的知识点包括以下内容 –

  • 使用 SSDT (SQL Server Data Tools) 创建 SQL Server 2012 Analysis Services 表格模型项目
  • 将数据从 SQL Server 关系型数据库导入表格模型
  • 创建和管理模型中表之间的关系
  • 创建和管理可帮助用户分析模型数据的计算, 度量值和关键绩效指标
  • 创建和管理透视和层次结构, 通过提供业务和应用程序特定的视点, 帮助用户更轻松的浏览模型数据
  • 创建分区将表数据划分为可独立于其它分区进行处理的更小逻辑部分
  • 创建角色以及用户成员来保护模型对象和数据安全
  • 将表格模型部署到在表格模式下运行的 Analysis Services 的 Sand Box 或 Production 环境中

 

实验条件

SQL Server 2012 Analysis Services 示例在安装时选择了表格模式 Tabular Mode安装, 另外一种模式就是我们之前一直熟悉的 Multidimensional and Data Mining Mode, 两者只能选一, 但是可以在下一次安装时把另外一种模式安装到另外的一个实例中.

下载http://msftdbprodsamples.codeplex.com/releases/view/105902 示例数据库

                        

WIN 7 下右键管理员身份运行 SQL Server 2012 并附加下载的Demo 数据库

CREATE DATABASE AdventureWorksDW2012 ON 
(FILENAME = 'M:\Data\AdventureWorksDW2012_Data.mdf'), 
(FILENAME = 'L:\Tlogs\AdventureWorksDW2012_Log.ldf') FOR ATTACH;

 

使用 SSDT (SQL Server Data Tools) 创建 SQL Server 2012 Analysis Services 表格模型项目

虽然是在 SQL Server 2012 安装的过程中安装的, 但是 SSDT BI 开发工具仍然使用的是 Visual Studio 2010 Shell, 之前BI 开发工具名称是我们所熟悉的 BIDS.

创建新项目

因为我本机就安装了这一个示例, 所以直接选择本机.

Compatibility Level – 我选择的是 SQL Server 2012 SP1, 之前选择另外一种默认模式发生了错误.

 

创建完项目后在 AW Internet Sales Tabular Model 工程中可以看到这个 Project 名称是 .smproj

并且默认情况下生成了一个Model.bim 的文件, 这个文件就是以后我们重点工作的内容,并且在之后的设计过程中会发现相对于以前的 SSAS 多维数据的Cube 设计 在 Model 模式下无论界面还是设计过程个人感觉更简洁和容易些.

 

选中项目右键属性 – 以后的部署过程和这里相关.

选择模型看它的属性中有一项 Direct Query 模式 – 指定是在内存模式还是在直接查询模式下部署模型, 我们选择默认的内存模式 Off 创建和部署模型.

 

注意到在 SSDT 的 Visual Studio 环境下多了 Model, Table, Column 这些菜单项.

Model 下的大致功能

  • 启动表导入向导
  • 查看和编辑现有连接
  • 刷新工作区数据
  • 使用 Excel 分析浏览模型
  • 创建透视和角色
  • 选择模型视图和设置计算选项

Table 的大致功能

  • 创建和管理表之前的关系
  • 创建和管理以及指定日期表设置
  • 创建分区以及编辑表属性

Column 的大致功能

  • 在表中添加和删除列
  • 冻结列以及指定排序顺序
  • 自动求和为选定列创建标准聚合度量值

这样我们就创建了第一个基于 Tabular Mode 的SSAS 项目模型并了解了一些基本的配置选项和相关的概念.

 

将数据从 SQL Server 关系型数据库导入表格模型

在Model 菜单中选择 Import From Data Source… 就会看到很多 Data source 可供我们选择, 以后我们可以一一去尝试不同的数据源下我们的模型是如何导入源数据的, 目前的示例中我们选择 Microsoft SQL Server.

 

接上图 Relational Database 还有以下数据库

Multidimensional Sources 和 Data Feeds

 

文本文件以及 Excel 文件作为数据源

 

选择好 Server Name 和 Database Name

 

 指定Windows 用户名和密码 我使用的是我安装虚拟机上系统的用户名和密码

 

 选择表或者视图

 

按照http://msdn.microsoft.com/zh-cn/library/hh231690.aspx完成所有表的重命名但不要急于点击NEXT 操作, 因为在选择数据源的过程中不是所有的数据都是我们需要的, 因此需要对表的列进行筛选.

选中 DimCustomer 表并点击 Preview & Filter

 

可以对这个表进行浏览, 这里大概显示了 DimCustomer 表的50条数据. 可以点击一下下拉按钮很容易明白这里的筛选器和Excel中的筛选操作一样.

 

清除这几个列, 因为在后面模型的设计中不需要.

 

完成后点击 OK 可以看到 DimCustomer 后面有一个 Filter Details

 

Filter Details 其实就列出了我们选择的列有哪些.

 

按照http://msdn.microsoft.com/zh-cn/library/hh231690.aspx 完成对其它表不需要的列进行清除操作, 操作完成后应该能看到Filter Details 的链接.

 

点击完成后看到所有表导入时的信息包括状态和导入的条数, 可以查看 Data preparation 的 Details 信息.

 

Details 信息中显示了表与表之前的关系 –

  • DimCustomer 引用了 DimGeography 表中的 GegraphyKey
  • DimProduct 引用了 DimProductSubcategory 表中的 ProductSubcategoryKey
  • DimProductSubcategory 引用 DimProductCategory 表中的 ProductCategoryKey
  • FactInternetSales 引用了 DimCustomer 表中的 CustomerKey 和 DimProduct 表中的 ProductKey

 

点击OK 保存好项目可以看到这个模型下所有已导入表的信息以及数据, 个人感觉从界面上看非常直观.

 

在设计模型的过程中往往要对表列进行重命名以便于客户端更好的展现和导航.

选中 Customer 表并点击CustomerKey 对它进行编辑重命名为 Customer ID.

 

请按照http://msdn.microsoft.com/zh-cn/library/hh231695.aspx 对其它表中的列重命名操作.

编辑完成后保存, 可以通过右下角的视图看看表与表之间的关系, 非常直观并且界面操作非常流畅.

 

从上图中也可以看到目前 Date 表还没有与其它的表进行关联, 但是在后面的使用中会对日期表进行关联, 以便于 DAX 时间智能函数的计算.

现在要做的是将这个Date 表标识为日期表, 尽管它确实包含了时间和日期的数据, 但是我也需要在设计中标识以下, 指定日期表和该表中唯一的标识符日期列, 最后再创建其它表与日期表之间的关系.

选中 Date 表并标识它为日期表

 

并选中 Date 列作为唯一标识列

在设计过程中如何知道某张表有没有设计为时间表, 可以通过查看 Table 下 Date 选项的内容. 如果 Mark As Date Table 已经被选中并且 Date Table Settings 选项显示可使用的状态时即这个表对象已经标识为日期表了.

 

创建和管理模型中表之间的关系

验证导入数据时自动创建的关系并在不同表之间添加新的关系, 确立表与表之间的数据应该如何相关.

在上面的操作中, 使用了表导入向导来导入数据, 总共有7张表被导入. 如果从关系源中导入数据, 则将自动导入现有关系以及数据.

在模型关系视图中单击 Customer 和 Geography 表之间的实线, 这个实线表示此关系处于活动状态, 即当计算 DAX 公式时, 默认情况下将使用此关系.

注意观察两个表中 Geography ID 都同时显示, 还有右侧的关系显示出来这两张表通过何种字段关联. 这个操作感觉非常灵活和直观, 非常容易用来检查表与表之间的关联关系是否正确, 特别在表很多的情况下, 检查起来非常方便.

还有一点要注意的就是右侧的RelationShip 中的 Active 状态为 True.

 

但是从上图中也可以看到 Date 表目前还没有被关联起来, 因此需要为它建立关联关系.

将 InternetSales 表中的 Order Date 拖放到 Date 表中的 Date 建立关联关系.

 

同样的操作, 将Due Date 和 Ship Date 拖放到 Date 表的 Date 上, 最终显示的结果如下图所示

注意看到三条关系线中有一条实心线和两条虚线, 实心线表示的是 Relationship 是Active 的, 虚线表示Relationship 是不活动的. 这里也称它们为活动关系和非活动关系.

这里有一个概念需要强调的是 – 表与表之间可以具有多个关系, 但一次只有一个关系可以处于活动状态.

创建计算列

在多维数据集 Cube 的构建过程中对于视图的创建也用到过计算列, 所以概念很容易理解.

在Date 表中创建 Month Calendar 计算列

 

编辑名称并在 fx 编辑栏中输入=RIGHT(" " & FORMAT([Month],"#0"), 2) & " - " & [Month Name] 并保存, 列名称修改为 Month Calendar

 

至于这个表达式具体的含义这里不做解释.

按照http://msdn.microsoft.com/zh-cn/library/hh231703.aspx 的要求完成对其它计算列的创建.

在操作过程中要理解对 Product 表的 Product Subcategory Name 计算列的创建.

按照我们的理解, 既然 Product 表已经可以引用到 Product Subcategory 表中的 Product Subcategory Name 这一列, 为什么还需要在这里创建这个计算列? 原因就在于这个计算列是用来在 Product 表中创建一个层次结构, 其中包含了来自 Product Subcategory 表中 Product Subcategory Name 列的数据, 层次结构不能跨多个表.

从=RELATED('Product Subcategory'[Product Subcategory Name]) 的用法直观的看就能明白这个列引用了 Product Subcategory 表中的 Product Subcategory Name 这一列.  要注意这些表名称中间的空格, 这里的引用名称应该完全和你所创建的表的名称和列的名称完全匹配.

而之所以能够这么引用的原因就是之前我们已经建立了这几张表之间的关系.

 

创建度量值

在模型中创建度量值与创建计算列比较类似, 度量值从本质上说是使用 DAX 公式创建的计算. 但是与计算列不一样的是, 度量值是基于筛选器进行计算的.

为了创建度量值, 需要使用度量值网格, 每个表都有一个空的度量值网格, 如下图所示.

 

按照http://msdn.microsoft.com/zh-cn/library/hh231688.aspx 完成所有度量值的创建

 

要注意的是在使用自动求和功能创建的度量值将自动放入关联下方度量值网格中的最顶部单元中.

 

 

创建关键绩效指标

关键绩效指标 KPI 用于根据目标值来度量基础度量值定义的值的性能. 在保镖客户端应用程序中, KPI 可以向业务专业人士提供一种快速简便的方法, 使他们了解业务绩效的摘要或确定趋势.

在Internet Sales中新增一个新的度量值

Internet Current Quarter Sales Performance:=IFERROR([Internet Current Quarter Sales]/[Internet Previous Quarter Sales Proportion to QTD],BLANK())

 

这个度量值作为 KPI 的基础度量值.

创建 KPI

 

使用 Absolute Value 并且可以调整 KPI 状态阀, 移动状态来调整 KPI 的目标.

 

 

创建透视

创建 Internet Sales 透视, 透视可定义模型的可查看子集, 我们可以将注意力集中在特定业务或特定应用程序上. 当用户使用透视连接到模型时, 将只能看到与该透视中定义的字段相同的那些模型对象(表, 列, 度量值, 层次结构和KPI )

在模型菜单中找到透视

 

这次的透视只和 Internet Sales 相关, 因此排除掉 Customer, 保存即可.

 

可以看到选择Internet Sales 透视后, 下面的表选项中 Customer 已经排除在视图之外了, 这样可以让我们更关注于需要关注的业务.

 

 

创建层次结构

为Product 创建层次结构

 

创建完成后只需要简单的拖拽就可以将需要的列拖拉到层次结构中

 

对它们重命名, 左边是重命名之后的名称, 括号中是实际引用的列的名称.

 

为Date 创建三个层次结构

 

 

创建分区将表数据划分为可独立于其它分区进行处理的更小逻辑部分

可以通过创建分区将 Internet Sales 表划分为可独立于其它分区进行处理的更小逻辑部分. 默认情况下, 在模型中的每个表都有一个分区, 所有的行和列都是在一个分区上.

对于 Internet Sales 我们可以通过分区将数据按年份划分,这样做可以减少查询的体积, 提高查询效率.

 

分区名称 – Internet Sales 2005 并且点击 SQL 编辑

 

输入以下SQL 语句 表示查询的是2005年到2006年间的数据

SELECT
[dbo].[FactInternetSales].[ProductKey],
[dbo].[FactInternetSales].[CustomerKey],
[dbo].[FactInternetSales].[PromotionKey],
[dbo].[FactInternetSales].[CurrencyKey],
[dbo].[FactInternetSales].[SalesTerritoryKey],
[dbo].[FactInternetSales].[SalesOrderNumber],
[dbo].[FactInternetSales].[SalesOrderLineNumber],
[dbo].[FactInternetSales].[RevisionNumber],
[dbo].[FactInternetSales].[OrderQuantity],
[dbo].[FactInternetSales].[UnitPrice],
[dbo].[FactInternetSales].[ExtendedAmount],
[dbo].[FactInternetSales].[UnitPriceDiscountPct],
[dbo].[FactInternetSales].[DiscountAmount],
[dbo].[FactInternetSales].[ProductStandardCost],
[dbo].[FactInternetSales].[TotalProductCost],
[dbo].[FactInternetSales].[SalesAmount],
[dbo].[FactInternetSales].[TaxAmt],
[dbo].[FactInternetSales].[Freight],
[dbo].[FactInternetSales].[CarrierTrackingNumber],
[dbo].[FactInternetSales].[CustomerPONumber],
[dbo].[FactInternetSales].[OrderDate],
[dbo].[FactInternetSales].[DueDate],
[dbo].[FactInternetSales].[ShipDate]
FROM [dbo].[FactInternetSales]
WHERE (([OrderDate] >= N'2005-01-01 00:00:00') AND ([OrderDate] < N'2006-01-01 00:00:00'))

依次创建完 2005,2006,2007,2008,2009 年分区的结果

创建完毕后要处理分区

 

选中所有分区进行处理

 

有可能需要输入密码

 

分区完毕 2009年分区中的数据可能没有.

 

 

创建角色以及用户成员来保护模型对象和数据安全

角色通过只限作为角色成员的那些 Windows 用户进行访问, 提供模型数据库对象和数据的安全性. 每个角色都使用单个权限进行定义 – 无, 读取, 读取和处理, 处理和管理员.

通过使用 SQL Server Data Tools 中的角色管理器在模型创建期间定义角色. 在部署模型后, 可以使用 SQL Server Management Studio 管理角色.

默认情况下, 当前登录的账户对于模型将具有管理员权限. 但是, 为了让其他的用户能够通过报表客户端应用程序浏览模型, 我们必须建立至少建立一个具有读取权限的角色, 并将这些用户添加为成员.

在本Tutorial 中, 我们将创建三个角色 –

Sales Manager – 对所有模型对象和数据具有读取权限的用户.

Sales Analyst US – 只能浏览与 US 美国的销售相关的数据的用户, 对于这个角色, 将使用 DAX 公式来定义行筛选器, 该筛选器将成员限制为只能浏览针对美国的数据.

Administrator – 具有管理员权限的用户, 管理员权限可让用户具有不受限制的访问权限, 从而对模型数据库执行管理任务.

打开模型-角色

 

创建角色 – 在Members 一栏可以添加Windows 用户组, 但是我的例子中就是本地账户, 所以未有添加.

对于 Sales Analyst US 角色, 做出了一些限制  =Geography[Country Region Code] = "US"

 

在 Excel 中分析

接下来将使用 Office 2013 中的Excel 连接模型在部署模型之前对模型设计的进行有效的检测, 但这里的Excel 必须和开发工具在一台机器上.

除了使用 Excel 之外也可以使用 PowerView 来连接和浏览部署的模型数据.

首先使用默认透视和 Internet Sales 透视进行浏览

 

当前用户和默认视图

 

在Excel 中将能看到所有的Date 和 InternetSales 度量值和所有表透视列 包括 Customer

 

选中 Internet Sales 透视

 

将看不到 Customer

 

查看角色 Internet Sales Manager 应该只具备读取权限

 

 

将表格模型部署到在表格模式下运行的 Analysis Services 的 Sand Box 或 Production 环境中

配置部署属性, 指定在表格模式下运行的 Analysis Services 的部署服务器实例以及要部署的模型指定一个名称. 然后将模型部署到该实例, 部署完成之后, 用户将可以使用报表客户端应用程序连接到该模型 .

右击项目 – 属性 并编辑部署的信息 最后部署该项目

 

部署状态 – 成功

 

查看Analysis Service

 

全文完！

总结

个人感觉SSDT 工具的操作非常的简单和直观, 处理的速度也非常的流畅, 界面也很友好！

通篇下来对于Tabular Mode 的从开始的数据源数据导入到模型设计到最后的部署也有了初步的认识和了解, 但是对其中的一些概念和细节还值得继续研究, 特别是 DAX 语句的设计感觉比较陌生. 和MDX在SSAS Cube的设计以及一些Calculated Member 还有Scope 等等设计上还有些对应不起来,因此还有进一步学习的空间! 并且在Tabular Mode 和之前的 Multidimensional Mode 具体的区别和各自优缺点上也值得进一步总结, 深入了解Analysis Service 在数据处理方式的差异和各自优缺点.

再次回顾以下知识点看看掌握了多少

  • 使用 SSDT (SQL Server Data Tools) 创建 SQL Server 2012 Analysis Services 表格模型项目
  • 将数据从 SQL Server 关系型数据库导入表格模型
  • 创建和管理模型中表之间的关系
  • 创建和管理可帮助用户分析模型数据的计算, 度量值和关键绩效指标
  • 创建和管理透视和层次结构, 通过提供业务和应用程序特定的视点, 帮助用户更轻松的浏览模型数据
  • 创建分区将表数据划分为可独立于其它分区进行处理的更小逻辑部分
  • 创建角色以及用户成员来保护模型对象和数据安全
  • 将表格模型部署到在表格模式下运行的 Analysis Services 的 Sand Box 或 Production 环境中
 

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)

如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。