PivotTables 11: Key Performance Indicators, Actions, and Named Sets
数据透视表 11：关键性能指标，动作和名称集
今天，我将谈及Excel 12 数据透视表支持的Analysis Services的另外三个特点——关键性能指标，动作和名称集。
Today, I will cover three additional features of Analysis Services that Excel 12 PivotTables support – Key Performance Indicators, Actions, and Named Sets.[Point=2]
关键性能指标
SQL Server Analysis Services 2005引进了关键性能指标（KPIs）的概念。KPI是Analysis Services模型里定义的一套计算，代表可以在报告，入口，仪表板等上面显示的关键业务方法。关于KPIs，有很多著述，因此我不会花很多时间讨论KPIs这个概念，但是我确实想简单地讲讲KPI是如何在Analysis Services里定义的。KPI有四个主要成员：
Key Performance Indicators
SQL Server Analysis Services 2005 introduced the notion of key performance indicators (KPIs). A KPI is a set of calculations defined in an Analysis Services model that represent key business metrics which can be displayed in reports, portals, dashboards, etc. There is a lot of literature out there on KPIs, so I will not spend a lot of time on the value of KPIs as a concept, but I do want to briefly cover how a KPI is defined in Analysis Services. A KPI has four main components:
• 数值。业务方法的当前值——这可以是一个具体的衡量如销售，计算的衡量如利润，或者一个在KPI里特别的自定义计算。
• Value. The current value of the business metric – this could be a physical measure like Sales, a calculated measure like Profit, or a custom calculation defined specifically in the KPI.
• 目标。业务方法的目标——这通常是分解为一个数值的MDX表达式。
• Goal. The target for the business metric – this is usually an MDX expression that resolves to a value.
• 现状。一个定义数值当前状态的数字，标准化为范围-1（非常差）到1（非常好）——这也是个MDX表达式。
• Status. A number defining the current status of the Value, normalized in the range -1 (very bad) to +1 (very good) – this is also an MDX expression.
• 趋势。定义业务方法如何发展的预测——对目标变好或者变坏。趋势也标准化为-1到1，并且也是一个MDX表达式。
• Trend. An indication defining how the business metric is developing over time – getting better or worse relative to its goal. Trend is also normalized between -1 and 1, and also an MDX expression.
在Excel 12里，KPIs列在字段清单的一个特殊KPI文件夹里。这里有个Profit Margin的KPI示例。
In Excel 12, KPIs are listed in the field list in a special KPIs folder. Here is an example of a KPI for Profit Margin.
(Click to enlarge)
每个KPI成员可以通过勾选复选框添加到数据透视表数值区域，正如其它字段一样。我们来看一个例子，一个专门添加数值，目标，现状和趋势到我们产品和产品品类报告中的例子。当我添加这四个成员时，报告就是下面这个样子。
Each KPI component can be added to the PivotTable Values area by checking the checkbox just like any other field. Let’s take a look at an example, specifically, and example of adding Value, Goal, Status, and Trend to a report on our Products and Product Categories. Here is what the report looks like when I add those four components.
(Click to enlarge)
正如你所见，数值和目标用数字表示，另外，现状和趋势是一种美观的图形表示——它们可以给你的业务以非常快捷的视觉效果，非常容易抓住突出者等。正如我提过，现状和趋势通常值在-1到1之间。因为这些数字值显示在报告里不会有什么好奇的，我们和SQL Server Analysis Services 2005开发组合作，开发了一套图形来代表任何KPI的现状和趋势。这些即将使用的图形是在Analysis Services模型里定义的，因此任何从Excel里看到的现状和趋势是同样的图形。你们中有记得我发表的关于图标集的条件格式文章，可能已经领会到我们也使用那种技术在Excel 12里作为一部分KPI特点。
As you can see, Value and Goal are presented as numbers. Status and Trend, on the other hand, are nice graphical representations – they can be used to get a very quick visual overview of your business as it is easy to pick out outliers etc. As I mentioned, Status and Trend are normalized values between -1.0 and 1.0. Since these sorts of numerical values are not very interesting to show in a report, we have worked with the SQL Server Analysis Services 2005 team to develop a set of images to represent the Status and Trend for any KPI. The images to be used are defined in the Analysis Services model, so everyone that looks at the Status or Trend in Excel sees the same graphic. Those of you that remember the conditional formatting post I wrote on Icon Sets have probably already figured out we are using that capability in Excel 12 as part of this KPI feature.
更妙之处在于，因为这是数据透视表，当我展开、折叠数据透视表里的项目或者执行其它操作是，KPI成员讲会在新下上下文中自动重新计算。例如，如果我展开“Touring Bikes”，数据透视表会显示KPI成员的数值并且相应地更新现状和趋势图形。
Even better, since this is a PivotTable, as I expand/collapse items in the PivotTable or perform other operations, the KPI components will automatically be calculated in the new context. For example, if I expand “Touring Bikes”, the PivotTable will show the values of the KPI components and update the Status and Trend graphics accordingly.
(Click to enlarge)
动作
Analysis Services模型可以有一个随带“动作”。动作允许用户当他们在他们的数据里发现什么时相应在Analysis Services里浏览数据……执行动作会使用报告的上下文（用户正在查看的数据），并且使用该上下文作为输入运行一个不同的应用程序。例如，下面的数据透视表连接到Analysis Services模型，该模型有一个产品类别关联的动作。如果用户在该级上任意地方单击右键（例如我们选择Accessories），就会有个Server Actions菜单，带有一个启动Accessories报告的选项。
Actions
Analysis Services models can have “actions” associated with them. Actions allow users exploring data in Analysis Services to react to what they find in their data … executing an action will take the context of the report (the data the user is looking at) and run a different application with the context as input. For example, the PivotTable below is connected to an Analysis Services model that has an action associated with the product category level. If a user right-clicks any item of that level (here we have picked Accessories for example), there is a Server Actions menu with the option to launch a report for Accessories.
(Click to enlarge)
在本例中，该动作是使用SQL Server 2005 Reporting Services（报表服务）创建Accessories的销售比较报表。这是在我网页浏览器上的报表截屏。
In the example, the action is launching a sales comparison report for Accessories built using SQL Server 2005 Reporting Services. Here is a screenshot of that report in my web browser.
(Click to enlarge)
注意，该动作可以使用各种形式——运行ERP应用程序，浏览到网页，等等。这里有个连接到MSDN的指南。
Note that the action can take a wide variety of forms – running ERP applications, navigating to web pages, etc. Here is a link to a tutorial on MSDN.
名称集
Analysis Services（分析服务）提供一个功能，叫做“名称集”，它可以供Analysis Services模型创建者用来提供一个简单的方式给终端用户使用复杂但是经常使用的成员集。例如，模型创建者可以定义一个集合，列出当前非盈利产品，或者交付时间长的产品。名称集有很多好处。首先，因为它们可以在服务器上定义，所以每个看Analysis Services模型的人看到的都是相同的信息。再者，作为项目包括进入一个集合的标准可以是动态的，当模型中的数据随着时间添加或者更改了，项目清单是可以自动更新的。再次使用上面的名称集例子，列出当前非盈利的产品。你可以想象当时间迁移，该集合包含的产品会自动更改——无论是模型作者还是终端用户都不需要做任何事情。
Named Sets
Analysis Services provides a feature called “Named Sets” which can be used by Analysis Services model builders to provide a simple way for end users to work with complex but commonly used sets of members. For example, the model builder could define a set that lists the currently non-profitable products, or products that have a long lead time. Named sets offer a number of benefits. First, since they can be defined on the server, everyone that looks at an Analysis Services model sees the same information. Second, as the criteria for the items to include in a set can be dynamic, the list of items can automatically update over time as data is added or changed in the model. Again using the above example of a named set that lists the currently non-profitable products, you can imagine that as time passes, the products included in the set will change automatically – neither the model author nor the end user needs to do anything at all.
名称集显示在数据透视表的字段清单里，因此用户可以添加到数据透视表的行或列中。这里是我示例模型的产品维度定义的集合截屏。
Named sets are displayed in the PivotTable field list so that users can add them to a PivotTable on rows or columns. Here is a screenshot of sets defined for the product dimension of my example model.
(Click to enlarge)
一个用户创建显示2004年每个新产品模型销售报表仅需要做的是勾选该集合的复选框。
All a user has to do to build a report showing the sales for each of the new product models of 2004 is to check the checkbox for that set.
(Click to enlarge)
Published Tuesday, January 10, 2006 10:29 PM by David Gainer
注：本文翻译自http://blogs.msdn.com/excel，原文作者为David Gainer(a Microsoft employee)，Excel home授权转载。严禁任何人以任何形式转载，违者必究。
[/Point]
[此贴子已经被Kevin于2006-4-28 9:51:32编辑过] | 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-4-26 17:02
