IX 对SQL服务器分析服务的更强大支持

hxhgxy

PivotTables 9: Great support for SQL Server Analysis Services
数据透视表 9：SQL服务器分析服务的强大支持
今天，我将开始我们在数据透视表和OLAP（OnLine Analytical Processing联机分析处理）数据源，特别是Microsoft SQL Server Analysis Services模式（除了它有关的数据库产品之外，SQL Server还包括了一个名叫Analysis Services的功能，它提供业务情报和数据发掘能力）连接方面所作改进的系列文章。Excel已经有好几个版本能和SQL Server Analysis Services协作了，但是，为了使其作为SQL Server Analysis Services的前端能更好的工作，我们仍花了很多时间和精力在Excel 12上，特别是与Microsoft SQL Server 2005 Analysis Services （Microsoft SQL Server 2005 Analysis Services在前不久，作为Microsoft SQL Server 2005的一部分发布了，并且增加了很多新的，强大的数据分析功能……有关Analysis Services的更多详细信息，请看看这里）的协作上。[Point=2]
Today, I’ll start a series of articles on the improvements we’ve made to PivotTables connected to OLAP (OnLine Analytical Processing) data sources, specifically Microsoft SQL Server Analysis Services models (in addition to its relational database product, SQL Server includes a feature named Analysis Services which provides business intelligence and data mining capabilities). Excel has worked with SQL Server Analysis Services for several versions now, but we have put a lot of time and effort into Excel 12 in order to make it a great front end to SQL Server Analysis Services, especially Microsoft SQL Server 2005 Analysis Services (Microsoft SQL Server 2005 Analysis Services was recently released as part of Microsoft SQL Server 2005 and introduced many new, powerful features for analyzing data … for more information on Analysis Services, please take a look here and here).
在我进入讨论Excel 12如何和SQL Server Analysis Services协作之前，我想总结一下我所认为的使用Analysis Services作为分析业务数据的注意好处。
Before I launch into discussing how Excel 12 works with SQL Server Analysis Services, I wanted to summarize what I see as several key benefits to using Analysis Services as a tool for working with business data.
• 友好：业务数据通常储存于相关的数据库里，这些数据库适于数据输入或者存储，但是不适于数据分析。诸如列名之类的元素对于终端用户不是很直观，字段之间没有清楚的关系，等等。Analysis Services提供了一个用户友好的模型，你可以提供易于理解的业务名称，明确字段（Product Category – Product Subcategory – Product）之间的关系，这样一来，业务用户就可能设置他们自己的报告，不必求助于IT部门了。

• Friendliness. Business data is typically stored in relational databases optimized for data input or storage and not analysis of that data. Names of columns etc. are typically not intuitive to end users, there are no clear relationships between fields, etc. Analysis Services provides a user-friendly model where you can provide understandable business names, specify relationships between fields (Product Category – Product Subcategory – Product) so that it is possible for business users to design their own reports without help from IT.

• 人性化：Analysis Services提供工具，通过仅显示他们关心的数据以及有权限查看的数据来个性化具体用户的汇报；此外，Analysis Services可以将数据转换为用户首选的语言。

• Personalization. Analysis Services offers tools for personalizing individual users’ reporting experience by only showing them the data that they care about and have permissions to see; in addition, Analysis Services can translate data into users’ preferred languages.

• 分析能力：关键性能指标，计算，条件格式，以及动作是一些在Analysis Services里可以定义的业务逻辑的例子，之后自动出现在Excel数据透视表里面。其美妙之处在于，所有用户在他们的数据透视表里看到的是同样的东西，因为，例如格式，是在一个地方计算的——服务器上。

• Analytical capabilities. Key Performance Indicators, calculations, conditional formatting, and actions are just a few examples of business logic that you can define once in Analysis Services and then expose automatically in Excel PivotTables. Part of the beauty of this is that all users see the same thing in their PivotTables because the formatting, for example, is calculated in one place – on the server.

• 快速分析：Analysis Services 会累计数据，因此当使用常规相关数据库执行的分析查询可能会花几分钟，而Analysis Services则不需要一秒。

• Fast analysis. Analysis Services aggregates data so that analytical queries that might take minutes when executed against a relational database are typically executed in less than a second with Analysis Services.

• 一个合并的分析模型：Analysis Services允许你将不同的业务系统里的数据合并到单个的分析模型。例如，你可能在Oracle数据库里有一些销售数据，在SQL服务器数据库里有一些消费者数据，但是你想在同一个报告里看到该分析。通过一个Analysis Services模型，你可以仅做此而已，而根本不必更改源系统。

• One consolidated analytical model. Analysis Services allows you to consolidate data from different business systems into a single analytical model. For example, you might have some sales data in an Oracle database and some customer data in a SQL Server database but for analysis that you would like to see in the same report. With an Analysis Services model, you can do just that without needing to change the source system at all.

• 同一个真理：在Analysis Services分析数据时，所有的业务逻辑都在一个分析模型中集中管理，因此每个用户会看到使用相同业务逻辑计算出来的相同的数字。对该模型做出的任何更改，在用户刷新报告时，都立即会为Excel数据透视表用户可用。再也不用担心不同的用户使用不同的电子表格会有不同的财务结果。

• One version of the truth. When analyzing data in Analysis Services, all the business logic is centrally managed in one analytical model so that every user will see the same numbers, calculated using the same business logic. Any changes made to the model will immediately be available to all Excel PivotTable users when they update their report. No more worrying that different users with different copies of the spreadsheet have different financial results.

[/Point]

[此贴子已经被Kevin于2006-4-27 10:16:49编辑过]

Kevin

[Point=2]

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


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


数据透视表里的KPI是很有趣的——我将在即将发表的文章中更详细地叙述数据透视表的KPI支持。
KPIs in PivotTables are quite interesting - I’ll cover PivotTable KPI support in more detail in an upcoming post.
维度
最后，Analysis Services模型的维度列在数据透视表的字段清单里。（维度是一些不同的属性，你可以用来将你的数据分成片断，例如时间，地区，消费者，产品，等等。）在下面的截屏中，Customer维度文件夹是开启的，你可以看到Analysis Services模型中与消费者相关的字段。
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.

组织字段清单
在衡量组合文件夹里，KPIs文件夹和维度文件夹，Analysis Services模型的作者可以创建子文件夹，以一种更直接的方式来组织数据，让业务用户更容易浏览字段清单。在上面的截屏中，Contacts和Location文件夹是这方面的例子。这些文件夹是在Analysis Services上定义好的；Excel在初始化数据透视表字段清单的时候选上它们的。
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.
对于那些熟悉SQL Server 2005 Analysis Services的人来说，字段清单既会显示用户层次（象示例中的Customer Geography），也会显示属性层次（象示例中的Email Address）。如果你在服务器上不指定任何属性层次的话，那么我们就会在维度文件夹那里显示一个特殊的“More Fields”（译者：更多字段）文件夹。我们这样做是因为通常的属性层次太多了（在源数据库里，经常是每列一个），如果将它们列在上面，就会使字段清单的浏览太困难了。
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时，在数据透视表上面的字段列表里，有一个下拉列表，用户可以选择想要使用哪个衡量组合。很多情况下，你的报告中只需要一个衡量组合里的衡量，该下拉列表允许你筛选掉所有其它的衡量组合，以及和你所选衡量组合不相关的KPIs和维度。效果是可以减少字段清单上的可见字段数目，使你更容易去创建你的分析。
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.
为了示范，我将选择Financial Reporting衡量组合。
To illustrate this with an example, I’ll pick the Financial Reporting measure group.

这里是字段清单结果，筛选为只显示与财务报告相关的信息。现在，只有一个衡量组合文件夹是可见的，并且维度也明显少了，这样我就更容易找到我需要的字段了。
And here is the resultant field list, filtered to only show information related to Financial Reporting. Now there is only one measure group folder visible and significantly fewer dimensions, it is much simpler for me to find the fields I need.


数据透视表的透视法
SQL Server 2005 Analysis Services中有个功能，就是“透视法”。从Analysis Services网页上直接抄过来，庞大的Analysis Services模型会在用户前面呈现庞大的维度，衡量组合，衡量和KPIs，这可能会使浏览更困难，即使使用前面讨论过的基于衡量组合筛选字段。透视法，在Analysis Services模型里定义，会创建一些“视图”子集——从本质上说，模型设计者可以创建只包含既定目标所需信息的透视法。
Perspectives in PivotTables
One feature available in SQL Server 2005 Analysis Services is the idea of a “perspective”. To crib from the Analysis Services website, a large Analysis Services model can present to the user a large number of dimensions, measure groups, measures, and KPIs and may be challenging to navigate, even with the ability to filter the field list based on a measure group discussed previously. A perspective, which is defined in the Analysis Services model, creates a subset "view" of a cube – essentially, model designers can create perspectives that only contain the information needed for a given purpose.
Excel 12支持透视法；用户一旦连接到透视法（对于Excel 12来说，这就象其它任意的数据源），数据透视表字段清单在“Show fields related to:”下拉框里只会显示透视法里包含的衡量组合，并且下拉清单里的选择只会显示透视法里包括的字段给用户。
Excel 12 supports perspectives; once a user has connected to a perspective (which to Excel 12 looks just like any other data source), the PivotTable Field List will only show the measure groups included in the perspective inside the “Show fields related to:” drop down, and selecting (All) in the drop-down will only show the user the fields included in the perspective.
层次让浏览容易
今天我最后要讲的Analysis Services特点是层次。基于Analysis Services的数据透视表的优点之一是你可以在每个维度内设置层次。层次帮助用户直观正确地浏览数据。层次为用户定义字段之间的关系……我们来看看一个例子吧。在下面的截屏中，我展开了Customer Geography层次，里面包含具体的字段（或者级别）。
Hierarchies make exploration easy
The last Analysis Services feature I will cover today are hierarchies. One of the advantages of PivotTables based on Analysis Services models is that you can set up hierarchies within each dimension. Hierarchies help users navigate the data intuitively and correctly. To users, a hierarchy defines relations between fields … let’s look at an example. In the screenshot below, I’ve expanded the Customer Geography hierarchy to show the individual fields (or levels) it contains.


在本例中，有五个级别，因此当我通过点击勾选框添加Customer Geography到数据透视表时，我实际上在一次性添加五个字段（对于非Analysis Services的数据源，你得安装正确的顺序添加多个字段到同一个报告，并且需要选择哪个字段不会总是那么明显）。这让我有机会展开国家来查看州等，而不必要添加其它四个字段到数据透视表。在我添加Customer Geography到数据透视表后，我就可以通过点击展开标志（“+”）在浏览数据透视表里的Australia层次，它给我展示下一级的详细信息（“州－省”）。
In this example there are five levels, so when I add Customer Geography to the PivotTable by clicking the checkbox for it, I’m actually adding five fields at once (for non-Analysis Services data sources, you have to add multiple fields in the right order to get the same report, and it might not always be obvious which fields to pick.). This gives me the opportunity to expand countries to see states etc. without having to also add the four other fields to the PivotTable. After I’ve added Customer Geography to the PivotTable, I can explore the hierarchy by clicking the expand indicator (“+”) for Australia in the PivotTable, which shows me the next level of detail (“State-Province”).


新的Excel 12展开、折叠标志（在前面的文章中讨论）自动出现在层次前，非常容易知道是否要展开或者折叠详细信息。例如，我可以使用展开标志进一步展开并查看“City”，“Postal Code”，等等。
The new Excel 12 expand/collapse indicators (discussed in a previous post) appear automatically for hierarchies to make it very easy to determine when there are details to expand or collapse. For example, I could use the expand indicators to further expand to see “City”, “Postal Code”, etc.

Published Thursday, December 29, 2005 3:12 PM by David Gainer

注：本文翻译自http://blogs.msdn.com/excel，原文作者为David Gainer(a Microsoft employee)，Excel home授权转载。严禁任何人以任何形式转载，违者必究。

[/Point]

[此贴子已经被作者于2006-4-27 10:15:24编辑过]

海儿无量

真想好好学学这方面内容，有谁可以说说需要哪些物质条件做准备吗？