滁州会计继续教育网：Excel2007新知-IX 对SQL服务器分析服务的更强大支持

高顿网校友情提示，*7滁州会计继续教育网上总结信息Excel2007新知-IX 对SQL服务器分析服务的更强大支持等内容公布如下：
　　All that said, let’s return to Excel 12, and take a look at what the PivotTable Field List looks like when connected to an Analysis Services 2005 model.

　　我们说了那么多，让我们回到Excel 12，看看当连接到Analysis Services 2005模型时数据透视表的字段清单长什么样。

　　Measure groups

　　When connected to Analysis Services, a PivotTable exposes three types of fields – “measures”, or the numbers (like “sales” and “profit”) that appear on your PivotTables, as well as “KPIs” and “dimensions” (both discussed below). Measures can be grouped together in Analysis Services (by the person that designs the model) into something called “measure groups”. In the Excel 12 field list, each measure group has a “sigma” icon to communicate to the user that the fields in the group are numerical and that they belong in the Values area of the PivotTable. Measure groups essentially represent different sets of business metrics available for analysis; typically a measure group contains related measures from the same business application. In the image below, the Exchange Rates measure group folder is open and there are two measures listed which can be added to the PivotTable – Average Rate and End of Day Rate.

　　衡量组合

　　当连接到Analysis Services时，数据透视表会显示三类字段——“衡量”，或者数字（如“销售”和“利润”），还有“KPIs”和“维度”（下面都会讨论）。衡量可以在Analysis Services里组合（由设计该模型的人）为名叫“衡量组合”的东西。在Excel 12字段清单里，每个衡量都有一个“西格马”图标，告诉用户该组合里的字段是数字型的，并且它们都属于数据透视表中的数值区域。衡量组合本质上代表不同的分析可用的业务方法（译者：作者经常提到Business Metrics，不明所以，暂且译为业务方法）；衡量组合通常包含来自相同业务软件的相关衡量。在下面的图像上，Exchange Rates衡量组合是开启的，有两个衡量，它们可以添加到数据透视表——Average Rate和End of Day Rate。

　　Key Performance Indicators (KPIs)

　　Below the measure group folders are is a KPI folder (assuming KPIs have been defined in an Analysis Services model). This folder contains Key Performance Indicators defined on the Analysis Services server. (Key Performance Indicators are a big subject unto themselves – for the sake of this article, suffice to say that they track key business metrics and that they are defined in Analysis Services). The different components of a KPI (Value, Goal, Status and Trend) can be added to the Values area of the PivotTable so you can track the latest values of your key business metrics. Here is a screenshot of the KPIs folder ... in the image, the Product Gross Margins KPI is open and all you have to do to add the Value, Goal, Status or Trend of the KPI to the PivotTable is to check the checkbox next to it.

　　关键性能指标（KPIs）

　　在衡量组合文件夹下面是一个KPI文件夹（假设KPIs已经在Analysis Services模型里定义了）。该文件夹包含在Analysis Services服务器上定义的关键性能指标。（关键性能指标对它们自己来说是个大主题——为了这篇文章，足可以说它们追踪关键业务方法，它们在Analysis Services上已定义好）。KPI的不同成员（数值，目标，现状和趋势）可以添加到数据透视表的数值区域，这样你就可以在你的关键业务方法中追踪*7的数值。这是KPI文件夹的截屏……在该图片里，Product Gross Margins KPI是开启的，所有你需要做的只是勾选KPI里的Value，Goal，Status或者Trend旁边的勾选框，将它们添加到数据透视表里。

　　KPIs in PivotTables are quite interesting - I’ll cover PivotTable KPI support in more detail in an upcoming post.

　　数据透视表里的KPI是很有趣的——我将在即将发表的文章中更详细地叙述数据透视表的KPI支持。

　　Dimensions

　　Finally, the dimensions of the Analysis Services model are listed in the PivotTable field list. (Dimensions are the different attributes that you can use to slice and dice your data, like time, geography, customer, product, etc.) In the screenshot below, the Customer dimension folder is open and you can see the customer-related fields available in the Analysis Services model.

　　维度

　　最后，Analysis Services模型的维度列在数据透视表的字段清单里。（维度是一些不同的属性，你可以用来将你的数据分成片断，例如时间，地区，消费者，产品，等等。）在下面的截屏中，Customer维度文件夹是开启的，你可以看到Analysis Services模型中与消费者相关的字段。

　　Organizing the field list

　　Within the measure group folders, the KPIs folder and the dimension folders, the person that authors the Analysis Services model can set up subfolders to organize the data in an intuitive way, making it much easier for business users to navigate the field list. In the screen shot above, an example would be the Contacts and Location folders. These folders are defined on the Analysis Services; Excel picks them up when initializing the PivotTable Field List.

　　组织字段清单

　　在衡量组合文件夹里，KPIs文件夹和维度文件夹，Analysis Services模型的作者可以创建子文件夹，以一种更直接的方式来组织数据，让业务用户更容易浏览字段清单。在上面的截屏中，Contacts和Location文件夹是这方面的例子。这些文件夹是在Analysis Services上定义好的；Excel在初始化数据透视表字段清单的时候选上它们的。

　　For those of you that are familiar with SQL Server 2005 Analysis Services, the field list will show both user hierarchies (like Customer Geography in the example) and attribute hierarchies (like Email Address in the example). If you do not specify any folder for an attribute hierarchy on the server, we will display it in a special “More Fields” folder under the dimension where it belongs. We do this since there are typically many attribute hierarchies (often one per column of each table in the source database), and listing them at the top level makes it hard to navigate the field list.

　　对于那些熟悉SQL Server 2005 Analysis Services的人来说，字段清单既会显示用户层次（象示例中的Customer Geography），也会显示属性层次（象示例中的Email Address）。如果你在服务器上不指定任何属性层次的话，那么我们就会在维度文件夹那里显示一个特殊的“More Fields”（译者：更多字段）文件夹。我们这样做是因为通常的属性层次太多了（在源数据库里，经常是每列一个），如果将它们列在上面，就会使字段清单的浏览太困难了。

　　Focusing the information in the field list

　　When a PivotTable is connected to SQL Server 2005 Analysis Services, at the top of the PivotTable Field List, there is a drop down where the user can select which measure group you want to work with. In many cases, you only need the measures from one measure group for a report, and this drop down allows you to filter out all the other measure groups as well as KPIs and dimensions that are not related to the measure group you select. This can have the effect of reducing the number of fields visible in the field list making it much easier to build your analysis.

　　聚集字段清单中的信息

　　当一个数据透视表被连接到SQL Server 2005 Analysis Services时，在数据透视表上面的字段列表里，有一个下拉列表，用户可以选择想要使用哪个衡量组合。很多情况下，你的报告中只需要一个衡量组合里的衡量，该下拉列表允许你筛选掉所有其它的衡量组合，以及和你所选衡量组合不相关的KPIs和维度。效果是可以减少字段清单上的可见字段数目，使你更容易去创建你的分析。

　　To illustrate this with an example, I’ll pick the Financial Reporting measure group.

　　为了示范，我将选择Financial Reporting衡量组合。

 　　　　

　　扫一扫微信，学习实务技巧

　　高顿网校特别提醒：已经报名2014年财会考试的考生可按照复习计划有效进行！另外，高顿网校2014年财会考试高清课程已经开通，通过针对性地讲解、训练、答疑、模考，对学习过程进行全程跟踪、分析、指导，可以帮助考生全面提升备考效果。