一、PowerPivot初步认识
PowerPivot是全球领先的自助BI开发软件之一,是Microsoft Excel和SQL Server的一个附加组件,可以让用户在Excel中创建数据模型,包括从多个源导入、数据转换和关系建立,以此建立可扩展的数据模型。PowerPivot能让用户使用大量数据,更加高效、原生地表达数据,创建最深刻的见解, PowerPivot的基础知识包括如何建立数据模型、如何为您的模型准备数据和如何创建可视化。
//PowerPivot的简单示例代码
=SUMX (
FILTER (
'FactInternetSales',
'FactInternetSales'[SalesTerritoryKey] = 2
&& 'FactInternetSales'[OrderDateKey] >= 20100101
&& 'FactInternetSales'[OrderDateKey] <=
20101231
),
[SalesAmount]
)
理解PowerPivot需要两个方面 - 数据建模和数据分析。作为一名编码人员,您的工作始于模型设计。 这意味着PowerPivot不仅仅是一个数据分析工具,它是围绕重点任务管理过程建立的程序。数据建模不仅是建立数据模型,而且还涉及确定数据源、目标受众、数据样本、数据结构和数据关系。
二、PowerPivot中的数据建模
在PowerPivot中,您需要将数据建模分为以下四个方面。数据源定义,数据结构,数据导入和数据关系。如果您能够将这些方面组合起来整合,您的数据模型将会展现出它对它所分析的信息的所有重要性。
数据源定义:这是流程中的第一步,需要确定您的模型数据将来自于哪里。可以选择的数据源包括:SQL Server数据库、Excel工作表、Text文件、Access数据库、Oracle、OLE DB、ODBC连接等。确保数据源被正确地定义和配置,以确保PowerPivot可以访问源数据。
数据结构:这个步骤是PowerPivot模型中最重要的步骤之一,这将确定模型如何组织源数据。PowerPivot支持的不仅仅是简单的行和列。利用PowerPivot固有的多维关系支持,您的模型能够发展成高维度的视图。确定合适的结构对于提供支持给预先定义视图时非常重要。
数据导入:数据源和结构定义完成后,模型需要获取数据。PowerPivot允许插入多个表和查询,并对数据进行各种不同的导入方式。其中包括个别导入、批量导入和同步导入。PowerPivot保留所有数据历史版本、数据结构变更和关系变更的记录。
数据关系:构建在PowerPivot数据结构之上的数据关系,决定了在模型中可以生成的视图的类型和方式。可以在表之间建立多种数据关系,如一对一、一对多、多对多等。 数据关系的定义依赖于合适的命名规则、适当的关键字和正式基础的ID设计。
三、PowerPivot的数据分析
一旦您建立了可靠且充分的数据模型,就可以进入数据分析阶段。数据分析通常包括两个截然不同的部分 - 计算和可视化。计算主要集中在对数据的查询和监控。此外,还应使用Excel Avanced Formulas、DAX、MDX或SQL语言的技能和知识。可视化部分则依赖于PowerPivot视图和Excel图表的使用,如果必要可以通过Power View和Power Map进行扩展。
计算是PowerPivot分析的基础。它可以在模型中引用一个或多个数据表,进行多个较小轻量级操作,以快速创建新的单元格或分析结果。PowerPivot提供了强大的计算功能,包括SUM、SUMX、COUNT、COUNTX、MIN等多个函数,可以这些函数可以通过公式在Excel中进行计算,生成高级视图。
除了计算之外,还需要使用Data Analysis Expressions(DAX)编写一些复杂的公式,以便更好地管理和整理数据。DAX具有类似于Excel公式的语法和可读性,它可以使用多个操作符和函数,计算行、列和平面值。在PowerPivot中,DAX十分重要,同时也是深入专业知识的重要组成部分。
// DAX公式示例代码
=CALCULATE (
SUM (
Sales[TotalSales]
),
USERELATIONSHIP ( Sales[CreatedOn], Calendar[Date] ),
LASTNONBLANK (
Sales[SalesTerritoryKey],
MAX ( Sales[CreatedOn] )
)
)
最后,Power Pivot支持高级可视化,可以创建更加观察力的报表和演示。PowerPivot允许使用Excel的图表来分析大型数据集。如果需要更加高级的可视化方式,可以使用Power View、PowerMap甚至BI工具和Web Dashboards。
四、总结
以上是PowerPivot教程的基础介绍,PowerPivot作为一款强大的数据分析软件,拥有丰富的功能和强大的计算能力、可视化效果。全面的数据建模和数据分析流程使其成为全球领先的自助BI开发软件之一。如果您想成为PowerPivot专家,我们建议您深入学习数据建模和数据分析流程,并掌握DAX函数、Excel高级公式和可视化技巧。