SQL ServerBI项目教程

SQL ServerBI项目教程

文章出处:MSSQLTips

作者: Date: 2016-05-11

概览

每次我们第一次冒险进入时,我们都会同时感到兴奋和紧张。在第一次处理职业生涯中的情况时尤其如此。人们渴望通过将所有精力投入其中来使其成功。他们说“知识才是力量”,在新企业中取得成功的潜力与日常工作中做出的明智决策成正比。如果您正在阅读本教程,这意味着您希望将您的第一次冒险投入BI项目并使其成功。因此,让我们开始并了解我们将要学习的内容。

要了解任何性质的项目,需要了解项目类型的典型体系结构和最佳实践。我们将首先了解BI(BI)架构,以开发我们对BI项目关键点的了解。一旦你进入一个项目,你将被分配你需要理解的职责来分析你的工作,并提供你工作多久的估计 – 对于许多人来说,这是你工作中最令人不安的部分,因为你没有想要求太多或太少的时间。

在BI项目中,数据是货币,分析是商品。如果没有正确的数据类型,数量和形状,就无法获得正确的分析。在本教程中,我们将介绍不同类型的数据源以及关系数据建模。在任何BI解决方案体系结构中,数据都可以从解决方案的一个层到另一个解决方 在从源到目的地的数据移动期间,数据的形状不断变换。这种方式促进数据移动和转换通常称为提取,转换和加载(ETL)。我们将查看典型的ETL需求以及数据分段和主数据管理概念等相关主题。

一旦我们从各种来源收集大量数据用于我们所需的分析,就需要以正确的方式存储以便更好地检索。为了简单地理解它,它类似于在图书馆中组织书籍以便于检索。我们将讨论数据仓库和开发高性能的数据模型。

当项目需要GB / TB规模的大量数据时,我们需要一个超出在线事务处理(OLTP)数据库范围的数据的鹰眼视图。例如,假设一家国家银行每天有1000万笔交易,首席执行官需要知道过去五年这些交易的平均数量。这些高容量计算更适合在线分析处理(OLAP)数据库。为了满足这些需求,我们需要了解数据集市,多维数据集,OLAP以及维度建模。

最后,我们到达了一个阶段,我们准备以视觉上吸引人的方式(即报表和分析)报表统计计算的值。我们需要了解不同类型的报表和不同的分析选项。

在本教程中,我们将每个主题与Microsoft Business Intelligence技术堆栈中的相应工具/技术相关联,并提供有关如何实施解决方案的示例用例。

本教程的大纲是:

  1. 需求分析
    • BI架构和SDLC
    • 需求收集与估算
  2. 数据源 – OLTP,基于文件等
    • 分析数据源
    • 数据库建模
  3. 提取变换加载
    • 暂存数据
    • 主数据管理
  4. 数据仓库
    • DW注意事项
    • 尺寸建模
  5. 数据库
    • OLAP和多维数据集
  6. 报表
    • 报表类型 – 运营,分析和战略
    • 可视化分析

BI需求分析 – 架构

概览

每个项目都应从一个完整的需求分析阶段开始。如果项目外包,在项目授予供应商之前,会发生不同的预售活动,这些活动超出了本教程的范围。一旦项目启动,我们将考虑将其交给技术团队,并且在那个时候开发团队被要求启动项目的技术方面。业务分析师通常与客户保持联系,以了解功能需求,技术团队与业务分析师合作,将需求转化为技术要求。在本教程的这一部分中,我们将讨论需求分析的不同方面。

说明

需求分析被认为只是业务分析师的工作,但实际上这是一个集体团队的努力。每个人都需要收集他们自己的任务和可交付成果的要求。在BI项目中,需要对标准BI架构有一定程度的了解。对架构的清晰理解有助于确定需求分析的正确区域。我们将考虑将作为本教程其余部分的项目需求使用的业务场景,并且与此相对应,我们将开始研究满足项目需求的典型BI架构。

业务场景

AdventureWorks是一家虚构的公司,生产自行车以及相关的服装和配件。该公司在全球开展业务,并在欧洲,北美和亚太地区销售。AdventureWorks希望摆脱基于纸张和手动的流程,并希望拥有一个将数据存储在关系数据库中的在线系统。来自每个地理位置的每个业务单元将访问在线系统并管理将存储在中央数据存储库中的销售和订单相关数据。随着公司的发展和未来增加更多的数据来源,这个集中的数据存储库应该作为公司所有数据相关需求的一站式服务。

不同国家有不同的法规和合规要求。因此,必须制定不同类型的报表,以实现即时,短期和长期报表要求。由于公司有许多经常出差开展业务的销售主管,因此应该可以从智能设备访问报表。公司的首席执行官是报表的主要赞助商和用户,并且有兴趣从数据中获取销售分析。根据公司的CXO的要求,数据分析师团队将访问数据以按需创建报表。他们需要能够对数据进行切片和切块以进行详细分析。

BI架构

现在,让我们记住业务场景并查看典型的端到端BI架构。我们将在下一节讨论此场景的需求收集和时间估计。下图显示了典型的BI体系结构。图中的每个实体代表解决方案的不同层和/或功能。通常,技术/BI架构师将根据项目要求开发解决方案/技术架构。

常见的SQL ServerBI体系结构

  1. 源系统 – 通常从OLTP源系统提取数据。使用应用程序前端将数据存储在这些系统中。在某些情况下,数据也存储在基于文件的数据源(如Excel电子表格)中。
  2. 登台/登陆区域 – 从源系统提取数据,并在通常称为登台区域的集中平台上收集相同或转换的数据副本。在临时区域中收集的数据可以通过应用业务规则进行清理和转换,并存储在另一个称为着陆区的存储区域中。从事务系统读取数据会对性能产生负面影响。还有其他因素需要将报表系统中的事务系统解耦,因此会定期分阶段复制数据。
  3. ETL – 从一个系统提取,转换和加载数据的过程,通常称为ETL,通常用作BI项目中的数据移动工具。SQL Server Integration Services是满足ETL要求的Microsoft BI技术。
  4. 数据仓库 – 随着数据的不断积累和不同类型的数据需求在企业中不断发展,有必要对数据进行仓库管理。数据仓库充当企业的任何数据相关需求的中央数据存储库。企业中可以有多个源系统,但数据理想地存储在单个集中式数据仓库中。
  5. 数据集市 – 根据任何给定业务部门的数据分析需求,将创建特定于业务的数据集市。简化术语中的数据集市是指用于分析的数据仓库的子部分。通常采用OLAP(在线分析处理)技术来创建数据集市,如SQL Server Analysis Services。大量数据的计算量要求使用OLAP技术,因为关系数据库不能很好地满足这种解决方案的性能需求。OLAP最容易识别的实现是多维数据集的形式,与关系数据库类似。
  6. 报表 – 任何应用程序中的数据流可以从不同形式的数据收集方法开始,并且主要以某种报表结束。SQL Server Reporting服务是Microsoft BI堆栈中常用的报表技术。报表可以是运营的,分析性的或战略性的,也可以由台式机,平板电脑和移动设备使用。报表可以像由几行和几列组成的操作报表一样简单,并且可以像由记分卡,地理空间分析,图表,图形,具有Drill-down和Drill-through的数据矩阵组成的仪表板一样复杂。我们将在本教程的报表部分中查看这些方面。

附加信息

  • 请阅读 本文, 以了解BI系统如何以及为何发展以及传统OLTP数据库的局限性。

BI要求和估计

概览

在SDLC方法的典型 BI 项目中,项目分以下阶段:计划,分析,设计,构建,测试,部署和支持。通常在计划阶段,详细列出项目要求,并在分析阶段结束前准备估算。在本节中,我们将讨论如何识别技术要求并估计工作量。

说明

仔细阅读业务案例的字里行间,就能识别解决方案的主要领域。我们就按给定的业务场景来剖析并确定主要领域:

数据建模 – 来自每个地理位置的每个业务单位都将访问在线系统并管理将存储在中央数据存储库中的销售和订单相关数据。

数据仓库 – 随着公司的发展jiu和未来增加更多的数据来源,这个集中的数据存储库应该作为公司所有数据相关需求的一站式服务。

数据集市 – 公司的CEO是报表的关键用户,并且有兴趣从数据中获取销售分析。

报表 – Data analyst team 将按照公司的CXO的要求访问数据以按需创建报表。他们需要能够对数据进行切片和切块以进行详细分析。

ETL – 虽然任何业务需求都不能直接转化为ETL解决方案的需求,但需要一种机制来移动数据。因此,需要在这些层之间移动数据的ETL批处理作业。t

我们将查看适用于这些领域的一些常见问题解答,然后我们将讨论如何估算工作量。

需求分析常见问题

问题:什么是技术架构文档?
答:技术架构文档定义了解决方案的技术蓝图。它涵盖了技术,基础设施,集成,安全性,操作等细节。

问题:如何为解决方案选择技术堆栈?
答:基于许可成本,资本支出,运营支出,技能可用性,产品路线图,社区采用,上市时间,功能比较,外部产品集成支持等参数,对各种技术进行了详细比较。详细说明分析是选择最符合需求的技术堆栈。

问题:我们如何知道业务实体涉及哪些内容以及它们如何组织以及相互关联?
答案:涉及概念模型,数据字典和适当的用例图的需求文档将提供所需的信息。

问题:什么是OLTP以及作为OLTP系统的一部分开发的内容?
答:OLTP 是一个维护事务数据的系统,也称为记录系统。各种数据库对象从安装数据库服务器开始到部署数据库对象,如表,存储过程,视图,函数,索引等。

问题:数据仓库是否与OLTP类似?
答:部分数据库对象可以作为数据仓库的一部分,但数据仓库的建模方式与OLTP数据建模不同。

问题: ETL Packages / Jobs 是否用来将数据从一个数据库导入导出到另一个数据库?
答: ETL是一种数据提取,转换和加载技术。ETL包的功能可能因环境而异。例如,OLTP系统和暂存区域之间的ETL可能只是具有一些变换的差分数据提取。但是,staging/landing area和data warehouse之间的ETL可能会有很大的不同,因为它可以根据仓库的维度模型的需要对数据进行转换。

问题:数据集市和数据仓库在设计方面是否相同,唯一的区别是它们是不同的数据库?
答:理论上,数据集市应该是数据仓库的一个子部分。数据集市的设计将特定于用户的报表和分析要求。

问题:什么是cube?
答案:cube是一种多维数据结构,以最佳方式存储数据,以支持大量数据的分析需求。下一章详细了解data mart。

问题:针对数据分析我们要开发什么?
答:报表[reports]和仪表板[dashboards]是提供有意义的数据分析的手段之一。分析是数据分析和报表的综合过程。

时间估算建议

在开始阶段之前,项目可交付成果将根据工作量和进度进行估算,然后根据交付情况进行相应计划。作为开发人员,人们经常面临为他/她的任务集提供工作量估计的情况。一般来说,项目采用标准的估算工作机制,并按照他们的期望向团队成员传达。但是,如果您面临提供估算的任务,以下是在估算分配给您的任务的工作量时您可以记住的一些因素。

  1. 如果您接受过相关的技术培训,你便可以提供工作量估算。如果您自己对技术还不熟悉,就要寻求精通该技术的SME(Subject Matter Expert行业专家)的帮助,跟SME一起审核估算逻辑,确定估算值。
  2. 考虑开发用于开发任务的算法逻辑。根据将用于逻辑的步骤/对象的数量,创建阈值并创建简单,中等和复杂等类别。例如。如果存储过程加入少于5个表少于3个循环,则认为它是简单的。如果它加入超过5个但少于10个表和4到6个循环,则将其视为中等,并且除此之外的任何内容都是复杂的。这只是一个示例,不应被视为估计存储过程开发的一般经验法则。一旦定义了类别,就可以为每个任务类别分配一个广义的工作量。这也称为工作分解结构。
  3. 任务和时间表可能有出入。例如,如果一组任务需要40小时并且每天考虑8小时,则任务将在5天内完成。实际情况并不是这样,因为还要考虑其他因素。人们应该考虑任务间的依赖性,让任务无法同时进行。例如,如果您没有可用表的架构,则可能无法开发存储过程。因此,在为您的任务传达可交付成果的时间表时,要考虑到相互依赖性。
  4. 通常,开发人员倾向于仅通过考虑构建工作来估计开发时间。任务有一个计划 – >分析 – >设计 – >构建 – >单元测试 – >缺陷修复 – >重新测试 – >部署阶段。因此,考虑估算每个阶段的工作量。有时,您的任务可能是一个重要的集成点,可能是其他任务的输入。因此,您可能还需要考虑回归测试工作。

BI分析数据源

概览

考虑到我们的业务案例,我们首先要求的是创建一个存储销售和订单相关数据的在线交易系统。关于解决方案前端的讨论超出了本教程的范围,因此我们将专注于开发后端数据模型。此数据模型将成为我们打算派生的销售分析的主要数据源。在本章中,我们将研究分析数据源以获得预期分析的注意事项。

说明

无论是开发新数据源还是需要从现有事务或非事务数据源中提取数据,都需要仔细考虑提取数据。我们将在下一章为我们的业务案例开发OLTP模型。在此之前,我们需要了解在数据建模和数据提取策略之前分析数据源的一些关键考虑因素。

  1. 数据的性质 – 数据可以是扁平的(flat),连续的,离散的,数字的,文本的等等。数据的性质是数据建模和数据提取的重要考虑因素。它在设计数据模型和数据提取策略时提供关键输入。
  2. 数据模型的性质 – 数据模型可以是不同类型的,具体取决于数据的域。例如,分层,Entity-Attribute-Value(EAV),关系,维度,网络等。数据模型的性质提供了数据模型中数据拓扑的清晰度。
  3. 数据量 – 数据量及其增长率有助于估计数据规模,这是开发数据提取设计时的重要输入。
  4. 数据修改的频率 – 数据变化的频率有助于估计数据的读取与写入。在规划数据模型的性能时,这是一个需要考虑的重要输入。
  5. Delta检测功能 – 数据提取面临的第一个挑战是源系统在任何给定时间点检测数据变化的能力,也称为BI术语中的delta。如果源系统没有此功能,则必须设置其他数据快照以启用更改检测。作为最后一个选项,必须始终提取整个数据集,这在大型数据库中根本不是一个可行的选择。因此,如果有机会设计数据模型,则应始终捕获数据模型中的审计信息,以便轻松检测数据变化。实现此目的的一种简单方法是包括CreatedBy,ModifiedBy,CreatedDateTime,LastModifiedDataTime等字段。

我们需要依据业务场景设计一个数据模型,作为下一层解决方案的源系统。我们使用AdventureWorks数据库,因为它具有所有数据模型以及练习所需的测试数据。这里下载数据库 。在您的计算机上安装OLTP和DW数据库。您可以安装 2008 R2 / 2012,因为它们具有相同的架构。

安装后,SQL Server Management Studio(SSMS)就可以访问这些数据库, 如下所示。

SSMS对象资源管理器

BI数据建模

概览

业务场景的一些摘录如下 – “不同地理位置的各个业务部门都将访问在线系统并管理中央数据库中的销售和订单数据。数据分析师团队据此中央数据库创建报表。他们需要能够对数据进行切片和切块以进行详细分析。“这将转化为一个独特的系统要求,我们需要开发一个OLTP数据模型来存储来自在线前端应用程序的数据。使其独特的方面是我们还必须保持报表和分析要求,因为此数据模型将作为其余解决方案的源系统之一。我们将为此要求派生和开发数据库模型。

说明

数据模型的开发通常始于概念数据模型的开发。在此建模阶段,定义了业务实体及其关系。让我们考虑几点来推导出概念模型。

  1. 第一个开始的实体是Products。产品可以分为类别,并进一步分为子类别。
  2. 客户使用在线系统下订单,系统生成销售记录。
  3. 销售在地理层面进行管理,并按大陆,国家,州和城市进行分类。
  4. 销售由销售专业人员和经理操作和支持。
  5. 员工的绩效在地区层面进行管理。员工的地址由公司维护。
  6. 地址可以是两种类型 – 工作地址和住宅地址。
  7. 产品从商店销售给附近忠实的客户。
  8. 商店可能有特别优惠或定期优惠,产品价格受到监管。

在推导概念数据模型时,可能需要考虑许多业务场景。如果您有兴趣考虑更复杂的业务场景,请考虑阅读 Adventure Works Cycles Business Scenarios。这将有助于您更详细地了解近实时业务场景。

使用实体-关系图(ER-Diagram)表示业务实体和关系 。ER Diagram是开发概念数据模型的标准方法之一。尝试自己创建实体关系图。您甚至可以使用PowerPoint并创建几个对象来表示实体,并至少显示表示概念模型的关系。您可以从此视频中了解有关ER图表的更多信息。

一旦开发了概念数据模型(conceptual data model),它就被用作开发逻辑数据模型(logical data model)的输入 。逻辑数据模型包含具有其所有属性的规范化数据实体。该数据模型独立于将用于实现数据模型的底层数据库管理系统。

我们可以从概念模型中识别初始表格列表 – 产品,订单,国家,地址。我们知道产品会有类别和子类别,我们可以将Products表规范化为 类别 – >子类别 – >产品。同样,地理信息可以分类并归一化为国家和州。如果您没有建模工具可以用PowerPoint或Excel,并尝试创建实体列表以及您认为应该作为表的一部分的属性。

通常,在向逻辑实体添加属性之前,会创建一个数据字典(Data dictionary),用于解释属性的业务含义。例如,ProductNumber是产品的唯一标识符,该标识符由条形码的25位数字形成,该数字源自SAP系统。类似地,列出所有这些属性并解释数据字典。为了更好地了解我们可以作为业务场景一部分的属性和表,请考虑阅读 AdventureWorks数据字典作为参考。一旦开发了逻辑数据模型,就可以使用称为物理数据模型的数据库管理系统来实现。物理数据模型由所有数据库对象组成,如表,视图,存储过程,触发器等。AdeventureWorks OLTP数据库是物理数据模型的输出。AdventureWorks 数据模型包含与我们的需求相关的表,如逻辑设计中所示。请阅读数据库开发教程以了解有关OLTP数据库开发的更多信息,这将有助于您更详细地了解任何数据库的物理设计。您可以在AdventureWorks OLTP 数据库中找到许多数据库对象。要理解相同的内容,请阅读文档 AdventureWorks中的SQL Server对象

下面是AdventureWorks中与我们的业务案例对应的逻辑数据模型的小型表示,以数据库图的形式。我们的业务场景可能需要的大多数实体都在物理模型中表示。将您开发的逻辑模型与下面的逻辑图进行比较。如果您已经将逻辑/物理数据模型开发到这个详细程度甚至更多,那么您已准备好进行下一章。另外,考虑投入更多时间思考概念和逻辑数据模型,并尝试理解下面数据模型中包含的可能遗漏的点。

在现实世界中,会有大量的业务实体和更复杂的关系。我们使用AdventureWorks的一小部分来限制复杂性,并更多地关注开发解决方案的概念。

数据模型

BI暂存数据 (staging data)

概览

在上一章中,我们开发了第一层解决方案开发–OLTP数据库,它将作为我们分析需求的源系统。根据我们的体系结构,解决方案的下一部分是从源系统中提取数据并在暂存区域中累积数据。虽然我们不一定需要为我们的练习实施临时区域,但我们将讨论分段数据和数据提取策略的一些关键考虑因素。了解这个过程是很好的,因为大多数真实世界的项目都将临时区域作为解决方案的一部分。

说明

在我们的场景中,我们需要从AdventureWorks OLTP读取数据并为暂存区域创建数据库。暂存区域的模式可以与源系统的模式相同。为了限制复杂性并轻松理解概念,请考虑我们有一个名为AdventureWorks_Staging的AdventureWorks克隆模式,目的是从OLTP数据库读取数据并将更改的数据加载到Staging数据库中。

  1. 增量检测 (delta detection) – 要检查的第一点是系统检测数据变化的能力。在AdventureWorks数据库表中,大多数表都有审计字段。只要数据发生任何变化,这些字段就会更新。因此,通过使用记录最后截止时间的计数器,可以检查截止日期之后的所有记录,并将其视为添加/更新记录。
  2. 数据存档 – OLTP系统中的记录应始终在逻辑上标记为已删除,不应从表中物理删除。如果物理删除记录,则使用审计字段的增量检测技术将不起作用,因为永远不会检测到已删除的记录。处理源系统限制的唯一选择是在暂存区域中设置数据库的副本,并在数据提取期间对暂存区域中的数据与源系统中的数据进行完全比较。
  3. 数据量 – 数据量将提供网络和ETL服务器上预期负载的估计值。大量数据意味着网络应支持高速传输大量数据以获得最佳性能。如果网络能够跟上性能要求并从源系统传输数据,则必须为ETL服务器配置足够的内存,存储和处理资源,以便在登台服务器上读取和写入大量数据。
  4. 数据刷新频率 – 从源系统到暂存区域的数据处理频率将在很大程度上决定ETL的性能要求。例如,如果将数据从源系统传输到暂存区域需要2小时的1 TB数据,并且数据每1小时刷新一次,则2小时的处理窗口将不可接受,因为之前第一个周期完成后,下一个周期就已经开始。
  5. 主数据加载 – ETL过程可以由一组ETL包组成,这些包从多个表中提取数据。这些包可以并行运行,也可以根据数据顺序运行。例如,我们无法在Products表中加载数据而不加载Categories和Sub-categories表中的更改。如果我们这样做,我们可能会遇到这样的情况:我们在Products表中找到记录,在父表中有一个缺少的子类别,因为我们在Sub-categories表之前加载了Products表。因此,必须在详细级别数据之前检测并加载主数据依赖性。
  6. 操作控制 – ETL包以批处理方式运行,其中一组包并行执行并按顺序执行。任何ETL程序包可能会失败,如果不以托管方式处理,这可能会导致数据差异。因此,应在ETL过程的每个阶段对ETL过程进行仔细审核并优雅地处理异常管理。

SQL Server Integration Services(SSIS)是Microsoft BI 技术堆栈中用于开发ETL解决方案的技术。考虑阅读 SSIS教程以学习和发展您的技能。

在暂存区域中暂存数据后,对数据质量进行验证并相应地进行清理。 数据质量服务 是Microsoft BI堆栈中用于此目的的技术。可能想要根据业务规则验证数据和/或转换数据的项目还可以创建另一个称为着陆区的数据存储库。ETL从暂存区域读取验证和/或转换的数据,然后加载到着陆区。

附加信息

  • ETL用于BI解决方案的多个部分,并且集成可以说是BI解决方案中最常用的解决方案领域。考虑使用SSIS创建ETL包只是为了从AdventureWorks OLTP数据库中读取数据,并在尽可能短的时间内将其写入Staging区域。这将有助于您使用SSIS开发ETL实现。

BI主数据管理

概览

在数据从OLTP / Staging区域移动到数据仓库之前,它会通过ETL层进行数据的分级,清理,甚至在需要时进行转换。我们在上一章中看到,在设计ETL包的执行顺序时,主数据是一个重要因素。在BI解决方案的大局中,主数据可以存储在外部集中式 主数据管理(MDM)解决方案中。主数据可以由整个企业中的多个事务系统使用,并且维护它们自己的主数据版本的每个系统既不是无差错也不是处理主数据的有效方式。如果企业不管理 主数据BI解决方案应该以有效的方式寻求将MDM作为解决方案的一部分。我们将在更高层面讨论MDM的体系结构,并讨论它如何适应当前的业务场景。

说明

在我们开始详细介绍 SQL Server主数据服务之前,了解MDM是一个广泛的主题非常重要。考虑阅读 本文以开发MDM的一些背景知识。如果您打算深入了解MDS, 本文 可以是您开始学习的好地方。如果您只想介绍MDS以及它如何适应业务场景,下面是对此的解释。

假设AdventureWorks维护着每个国家/地区的标准化国家/地区代码列表。公司使用的任何应用程序都应该使用相同的方法。MDS中保留了每个国家和国家/地区代码的映射。我们需要在从暂存区域加载数据仓库中的数据时使用相同的方法。

OLTP,MDS和数据仓库的流程图
在尝试理解我们如何在上述场景中使用MDS之前,让我们了解一些关于MDS的观点,以便快速了解我们对MDS的理解。这些要点是:

  • Master Data Services是一种独立于域的主数据管理技术,用于支持组织的主数据管理工作。
  • Master Data Services将数据存储在由实体,属性和成员组织的数据模型中。
  • Master Data Services Configuration Manager是用于创建和配置Master Data Services数据库和Web应用程序的工具。
  • Master Data Manager是一个Web应用程序,用于执行管理任务(如创建模型或业务规则),以及用户访问更新数据的权限。
  • MDSModelDeploy.exe是用于创建模型对象和数据包的工具,因此您可以将它们部署到其他环境。
  • 主数据服务Web服务可用于开发人员扩展或开发主数据服务的自定义解决方案。
  • Excel的主数据服务外接程序可用于管理数据和创建新实体和属性。

MDS中以最简单的形式保存的国家/地区代码列表如下所示。数据管理员和数据治理团队可以使用MDS维护此类主数据。

SQL Server主数据服务数据
一旦数据准备好被订阅系统(如我们的ETL包)使用,将使用MDS中的订阅视图发布相同的数据,如下面的屏幕截图所示。ETL包可以查询视图并查找国家/地区代码主数据的相应映射。

SQL Server结果集,包括来自Master Data Services的数据

附加信息

  • 有关MDS的更多信息,请考虑阅读 MDS提示

BI设计注意事项

概览

解决方案的下一层是通过设计和实现维模型来开发数据仓库。在我们开始设计维度模型之前,我们需要了解开发数据仓库的关键考虑因素。在本章中,我们将理解为什么需要数据仓库以及如何在BI解决方案体系结构中增加价值。

说明

在BI(BI)流程中创建数据仓库的第一个原因是在线事务处理(OLTP)系统上的负载增加。随着时间的推移,数据不断累积在数据库中。如果数据在某些离线系统中存档,则可能无法用于分析。如果OLTP系统没有归档数据,它将继续增长,并将开始需要更多资源来保持系统的性能。数据仓库充当企业中所有事务系统的活动归档数据存储。

随着来自不同事务系统的大量数据不断累积到数据仓库中,数据需要以非规范化格式存储,以便顺利地检索数据。典型的维度模型使用星型或雪花式设计,易于理解并与业务需求相关,支持简化的业务查询,并通过最小化表连接提供卓越的查询性能。

当数据从数据仓库中的各种事务系统合并和收集时,它提供了企业中各种业务活动的多维视图。从这一点可以很容易地看出,考虑到数据仓库中数据的数量和种类,这些数据可用于获得比任何单个OLTP系统更广泛的分析。例如,可能有一个OLTP系统用于销售,生产,人力资源,财务,客户关系管理等。当来自所有这些单独的OLTP系统的数据被收集到数据仓库中时,每个数据部分都可以与其余维度相关联。 。诸如在过去10年中在30-40岁年龄段拥有客户群的北美地区产生最大利润的产品的问题可以回答。回答这些问题可能需要来自多个OLTP系统的数据,并且还需要聚合大量数据。这是数据仓库被证明是最终数据源的地方。

在我们的业务场景中,AdventureWorksDW是AdventureWorks数据库的数据仓库版本。如果仔细查看解决方案,您会理解ETL需要将数据从OLTP系统或Staging区域填充到Data Warehouse中。考虑下载AdventureWorks SSIS示例 ,该 示例将AdventureWorks OLTP数据库中的数据加载到AdventureWorks数据仓库中。它以单个ETL包的形式,具有大量并行和顺序任务来加载数据仓库。这是SSIS包的一部分。

AdventureWorks SSIS包数据流示例
在下一章中,我们将看一下维度建模的细节,这将有助于我们理解为什么AdventureWorks DW模型以它的方式建模。我们还将讨论它如何解决我们的业务场景。

BI维度模型

概览

根据用户的分析要求开发尺寸模型,以与可用数据对齐。开发的模型有望轻松托管数据,并且应该足够灵活,以支持未来的变化。该模型的最终输出是一个关系数据库,它将充当数据集市/多维数据集的源系统。如果您从体系结构图中回忆起,则没有提取,转换和加载(ETL)来促进数据集市和数据仓库之间的数据传输。因此,这种维度模型还应该在很大程度上促进对数据的直接和快速访问。我们将研究如何为我们的业务场景开发维度模型。

说明

在我们继续讨论维模型之前,请仔细查看下图。我们对尺寸建模的整个讨论都集中在这个图上。

样本维度模型
1)在维度建模实践中,通常有两种理论来开发维数模型–Kimball方法和Inmon方法的数据建模。我们将专注于尺寸建模的Kimball方法。考虑阅读 本文以更多地了解这两种方法的相同点和不同点。

2)使用BUS架构开发尺寸模型的Kimball方法。在此体系结构中,为一组业务流程标识一组通用维度,这些业务流程将在一组数据集市中共享。在我们的业务场景中,我们专注于销售流程。如果我们查看OLTP数据库和我们建模的表格,我们会发现销售所需的一些常见维度是产品,客户和销售地理。

3)维度模型模式有两种类型 – 星型模式和雪花模式。对于简单的维度模型,星型模式应该足够了。但是,随着维度表在属性数量和数据方面开始增长,您可能希望规范化数据,然后采用雪花模式的形状。考虑从这里阅读更多关于雪花模式的内容 。

4)维度可以被视为主表,其包含与交易相关的定性属性。事实可以被视为交易表,其存储定量且有时很少存在交易的定性属性。Fact表的每个属性都称为度量。例如,FactInternetSales表中的SalesAmount是一种度量。密切观察事实表中的字段以及维度表,您将能够关联字段的定量和定性性质。

维度表封装与事实关联的属性,并将这些属性分成逻辑上不同的分组,例如时间,地理位置,产品,客户等。如果数据仓库包含多个事实表或向数据集市提供数据,则可以在多个位置使用维度表。例如,产品维度可以与数据仓库中的销售事实表和库存事实表一起使用,也可以与一个或多个部门数据集市一起使用。

维度表中的记录与事实表建立一对多关系。例如,单个客户可能有许多销售额,或单个产品的销售数量。维度表包含与维度条目关联的属性; 这些属性是丰富且面向用户的文本详细信息,例如产品名称或客户名称和地址。

5)观察事实表中的众多键。在维度中,您将找到作为表的主键的键。如果维度的关键属性发生更改,则将其称为代理键,用于维护维度中的数据。您可以从此处阅读有关代理键的更多信息 。

6)事实和维度表中的数据(也称为谷物)的粒度应该处于同一水平。例如,不建议在某些事实/维度表中以年度级别存储数据,而在其他事实/维度表中以月级级别存储某些数据。在通过ETL过程将数据输入仓库时,应确保整个维模型中的数据粒度保持同步。

7)日期是一个特殊维度,可以保证保持不变并且可以预先填充。我们可以根据我们打算在事实表中对数据进行切片和切块的属性,将属性添加到日期维度。AdventureWorks在DW数据库中具有标准且精细的日期维度。

8)考虑以下报表,并尝试通过研究上述数据模型将不同的度量与维度成员相关联。例如,我们可以将FactInternetSales表中的InternetSalesAmount字段与DimProductCategory表中的EnglishProductCategoryName连接起来。这将帮助您回答是否能够使用预期维度成员分析任何特定度量。

来自维度模型的样本报表

BI多维数据集

概览

SQL Server Analysis Services(SSAS)是MicrosoftBI堆栈的联机分析处理(OLAP)技术。开发维模型后的下一步是填充数据仓库是为了开发数据集市。在实现方面,我们将使用AdventureWorks DW中的数据开发一个多维数据集。

说明

使用SSAS开发一个立方体并理解它的概念需要精心的解释和动手练习。要开发多维数据集并为我们的业务场景创建报表,请遵循 SSAS教程。它具有与我们的业务场景相对应的精确练习,使用我们在前面章节中讨论过的同一组表。以下是开发任何多维数据集并生成报表所遵循的逻辑步骤:

  1. 确定包含要分析的数据的源表。在数据仓库数据库中创建可用于将数据公开给SSAS多维数据集的相应视图。
  2. 创建一个新的SSAS项目,并选择适当的事实和维度表作为多维数据集设计的数据源。
  3. 使用“维度向导”创建多维数据集维度,并进一步细化它们以创建适当的层次结构和关系。
  4. 使用多维数据集向导从事实表创建度量和度量组。
  5. 创建维度和事实后,需要部署和处理它们。处理这些对象会导致从数据仓库中获取数据,并使用在设计时定义的聚合级别将数据加载到多维数据集中。
  6. 现在,这些数据已准备好提供给任何报表解决方案。首先,您可以使用Excel从此多维数据集中读取数据并创建报表。理想情况下,您应该使用 SQL Server Reporting Services来开发报表解决方案。我们将在下一章中讨论报表。

在SSAS教程中按照分步练习后,您应该能够创建Sales多维数据集,如下所示。这个立方体是我们以不同方式分析数据的基础,然后必须以直观的方式报表相同的分析。

SQL Server Analysis Services Sales Cube

附加信息

  • SSAS是一个非常先进的主题,需要仔细研究该主题。有关SSAS的详细了解,请考虑阅读 SSAS开发技巧 和 SSAS教程

BI报表

概览

我们的解决方案架构中的最后一层是报表。报表主要是最终客户的分析解决方案。报表可以从在线事务处理(OLTP)系统以及在线分析处理(OLAP)系统中获取数据。报表可以在更高级别分为三类 – 运营,分析和战略。我们将研究这三类报表如何促进分析。

说明

运营报表通常用于报表来自交易系统的交易或短期数据。运营报表的示例包括每日状态报表,产品目录,Excel,如表格报表等。这些类别的报表通常是短暂的,用于帮助日常运营活动。通常,这些报表的用户是在销售主管,运营经理,商店服务员等领域工作的人员。

分析报表通常用于报表来自数据仓库或数据集市的分析或大量历史数据。分析报表的示例是生成报表,其中透视和汇总数据以矩阵表示,例如使用图表和图表表示数据的国家/地区级销售报表,在地理空间地图上表示的客户情绪分析数据等。此类报表通常用于问题分解,趋势分析,模式识别等。通常这些报表的用户是中级经理,数据分析师,统计员,数据管理者等。

战略报表通常用于根据组织目标,根据预定义的阈值报表混合运营和分析数据。战略报表的典型示例是由记分卡,仪表,趋势线,指标等组成的仪表板。这些报表也称为决策支持报表。此类报表通常用作决策支持和监测进度。通常,这些报表的用户是组织的高级管理人员或领导。

SQL Server Reporting Services(SSRS)是Microsoft Business Intelligence技术堆栈的报表技术。使用SSRS,可以在每个类别中创建专业质量报表。考虑阅读 SSRS教程以开始学习SSRS。下载 AdventureWorks SSRS样本并分析样本报表,该报表还包含与我们的业务场景相关的报表,如下所示。

SQL Server Reporting Services报表示例

BI可视化智能

概览

以最直观的方式报表数据是分析中最有趣的分支之一。在报表上显示数据等于在报表上转储数据。使用最合适的可视化对报表上的数据进行组织,以最吸引人的方式向人眼进行预期分析,这是视觉智能的实际本质。在本章中,我们将简要讨论可视化。

说明

条形图,柱形图,饼图等是日常报表中一些最常用的可视化的示例。随着更复杂系统的发展,数据的不同性质,数据量的增加以及当今可用的创新可视化数量的增加,报表过程变得非常复杂。可视化可以是不同类型,如图表,图形,地图,仪表,指标,趋势线,信息图表等。每种类型的可视化都针对特定类型的分析的特定数据性质。例如,散点图可用于在图上绘制大量数据点以进行相关性分析。但是,如果将相同的数据放在饼图上,则既不能容纳数据以使其保持可见,也不会对用户显而易见的预期分析。

下面提到的是使用可视化的一些目标:

  • 表示使用行和列无法以表格格式显示的大量数据
  • 在没有图形表示的情况下,直观地显示无法分析的趋势和模式
  • 以可视方式统计分析数据
  • 使用户能够直观地了解整个数据生态系统中数据点的值和位置

摘要

开发BI(BI)解决方案是一组并行和共同相关的任务。解决方案的每一层都需要准确工作并与相应的层集成。如果没有每个层的和谐功能,BI解决方案可能会导致错误的分析,这对使用此数据做出业务决策的用户极为有害。

我们从一个小型业务场景开始,并使用该示例来浏览BI解决方案流程的不同层。作为BI项目的新手,您可以从众多领域中进行选择,从而开发出深厚的技能并培养您的专业知识。您可以从任何BI练习区域开始学习。最终,随着您获得经验,您可能必须使用BI解决方案体系结构的不同区域。我希望本教程为那些希望使用Microsoft工具了解BI的整体情况并开始他们在BI世界中的旅程的爱好者提供了启动平台。

 442 total views

Author: Albert

Leave a Reply