PivotTables 9: Great support for SQL Server Analysis Services

数据透视表 9：SQL服务器分析服务的强大支持

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).

今天，我将开始我们在数据透视表和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的更多详细信息，请看看这里）的协作上。

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.

在我进入讨论Excel 12如何和SQL Server Analysis Services协作之前，我想总结一下我所认为的使用Analysis Services作为分析业务数据的注意好处。

• 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提供了一个用户友好的模型，你可以提供易于理解的业务名称，明确字段（Product Category – Product Subcategory – Product）之间的关系，这样一来，业务用户就可能设置他们自己的报告，不必求助于IT部门了。

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

• 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里可以定义的业务逻辑的例子，之后自动出现在Excel数据透视表里面。其美妙之处在于，所有用户在他们的数据透视表里看到的是同样的东西，因为，例如格式，是在一个地方计算的——服务器上。

• 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 会累计数据，因此当使用常规相关数据库执行的分析查询可能会花几分钟，而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允许你将不同的业务系统里的数据合并到单个的分析模型。例如，你可能在Oracle数据库里有一些销售数据，在SQL服务器数据库里有一些消费者数据，但是你想在同一个报告里看到该分析。通过一个Analysis Services模型，你可以仅做此而已，而根本不必更改源系统。

• 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.

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