Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

Excel 服务第十部分：数据连接库，更简单，更易管理，更安全的数据连接

In the past few posts I have talked about all the work we have done to make managing, sharing, and securing your Excel workbooks better using SharePoint and Excel Services. Today, I am going to cover a new feature that provides management, sharing, and security of data connections – the Data Connection Library (DCL).

过去几篇文章里我们已经讨论过所有使用SharePoint和Excel服务以实现对Excel工作簿更好的管理、共享以及安全的方法。今天，我将要介绍一个可以对数据连接实现管理、共享和安全性的新功能－－数据连接库DCL（Data Connection Library）。

What is a Data Connection Library?

A Data Connection Library (or DCL) is a new type of SharePoint library (much like a document library) that provides a place to store, share, and manage connection files. By connection files, I mean Office Data Connection (ODC) files which contain all the information and parameters needed to form a data connection, such as server name, OLAP cube or table name, and query (note – ODC files are not a new feature – they were introduced in Excel 2002). Since the DCL is a library in SharePoint, it comes with all the great SharePoint features you would expect – such as workflow support, file approval, library level/item level security, and sorting filtering based on metadata. You can create a DCL the same way you create any library, and DCLs can be created almost anywhere in SharePoint e.g. on a portal, team site, etc. Here is what a DCL looks like in SharePoint 12.

什么是数据连接库？

数据连接库（DCL）是一种新的SharePoint库，很像一个文档库，它提供了一个存储、共享以及管理连接文件的空间。连接文件，这里是指一个包含了用于构建一个完整数据连接的信息和字段（例如：服务器名，OLAP CUBE,表和查询等）的ODC（Office Data Connection）连接文件（注：ODC文件是在Excel 2002中被引入的）。鉴于DCL是一个SharePoint的库，正如你所期望的一样，它也带来了SharePoint的所有强大功能，像工作流的支持，文档的签入签出，库或项目级的安全性，基于元数据的归类和过滤。你可以像创建任何库一样的创建一个DCL文件，此外，DCL可以创建在SharePoint中几乎任何地方，例如：一个门户，工作组站点等等。下面是一幅SharePoint 12中DCL的样子：

Even though the DCL reuses the library concept in SharePoint, it is much more valuable than just a document library full of connection files – this is because of how Excel interacts with the DCL. Let’s take a look at that and see how the DCL and Excel 12 solve a few problems.

虽然，DCL再次使用了SharePoint库的概念，但它比一个单纯的连接文件的文件库有价值的多，这是Excel和DCL的互动机制决定的。让我们看看DCL和Excel 12如何解决一系列的问题。

Connecting to databases made easy …

Setting up a connection to a database in Excel is a task that many users struggle with – for example, if you want to connect to an ODBC datasource or SQL Server Analysis Services cube, users must know server names, cube names, table names, what type of connection to create, user credentials, etc. Lots of clicks and knowledge are required.

更简单的数据库连接…

在Excel中建立数据库连接，是很多用户的努力要完成的任务－－例如：如果你想连接一个ODBC数据源或者一个SQL Server Analysis Services cube，用户必须知道服务器名称，cube名称，表名称，想要创建的连接类型，用户凭证等等。这需要太多的步骤和相关的知识。

Excel 12 and DCLs make connecting to databases a much simpler, faster activity. Users will simply need to know what data they want to work with, and that’s pretty much it. Let’s take a look at an example of getting started with a PivotTable connected to a database in Excel 12.

Excel 12和DCL让数据库连接变得更加简单和快速。用户需要知道的全部内容就仅仅是他要处理什么样的数据。让我们来看一个在Excel 12中以数据透视表连接到数据库的例子。

To connect to a database (or other external data source like a web query) in Excel 12, users will use the Data tab. Here is a shot of the Data tab in the beta build (note, this is not the final UI we will ship).

要在Excel 12中连接到一个数据库（或者其它像web查询一样的外部数据源），用户要使用Data标签。下面是一个beta版中数据（Data）标签的截图（注：这并不是最终的界面）。

One of the buttons on the Data tab is the “Existing Connections” button. To connect to the database they want to use for their PivotTable, the user will start by pressing this button. When they do, they see a new dialog (“Existing Connections”) which lists the connections that are stored in the DCL.

数据标签中有一个已有连接（Existing Connections）的按钮。如果要连接到用于该透视表的数据库，点击该按钮。之后，就可以看见一个新的对话框（Existing Connections）显示了DCL中存储的连接列表。

One point to note is that these connections all have friendly names and non-technical descriptions, so it is easy for users to choose the connection they want. Those names and descriptions are provided by the person(s) who set up the DCL and populate it with ODC files – more on this below.

有一点需要要说明的是，所有的这些连接都有一个友好的名字和一些非技术性的描述，所以你可以很容易的选择你想要的连接。那些名称和描述都是由设置DCL和存储ODC文件的人提供的，关于这一点，下面将有详细的阐述。

Next, the user simply needs to highlight the connection they want to use and press open. At this point, they see another dialog which allows the user to tell Excel 12 what to do with the data.

接着，用户只要简单的选中要使用的连接，然后单击打开。这时，就可以看见另一个对话框询问将要如何使用该数据。

Another point to note is that Excel 12 looks at the connection the user has selected and only offers options that are possible for a particular database (for example, in this case, the datasource was a SQL Server Analysis Server database, which cannot be represented in Excel 12 as a table, so that option is disabled). At this point, the user needs to simply press OK and they have a PivotTable connected to data in Excel 12. That’s a total of 3-4 clicks.

另外一点要说的是，Excel 12会查看用户选定数据库并且仅提供特定数据库的可用选项（例如：数据源是一个SQL Server Analysis Server数据库，不能在Excel 12中像表一样显示出来，这种情况下，这个选项就不可用了）。这样，用户只需要轻松的点一下OK就可以在Excel 12中得到一个连接到数据源的透视表了。所有的操作只需要点击3到4次鼠标。

Some of you are probably asking how the connections ended up in the DCL in the first place. In general, we anticipate that either departmental “connection-savvy” power users or IT will author data connection files and put them in DCLs where the connections will be reused by many people in the organization.

也许有些人会问，在第一个地方的DCL中的连接是如何结束的。通常来说，我们预计的是，部门的高级用户或者IT人员将会验证数据连接文件，并将组织中用户需要重复使用的连接存放在DCL中。

You may also be asking how Excel knows about the existence of DCLs. SharePoint has a new feature that allows the administrator to “advertise” the location of the DCL to Office 12 clients, allowing connections from a DCL to show up in Excel 12. Of course, the DCL only shows up if the user has permissions to access those connection files.

也许你依然会问，Excel是怎么知道那些已有的DCL的。在SharePoint中有一个允许administrator将DCL位置"广告"（advertise）给所有Office 12客户端的新功能，它可以让Excel 12显示DCL中的连接。当然，DCL只会显示用户拥有访问权限的连接文件。

Solving connection management problems …

In addition to improving discoverability of connections, DCLs will help customers manage connections. Information about data sources can change, such as server name, OLAP cube name, table name, etc. – a typical example is a database moving from a test server to a production server. For organizations that have many authors, it may be difficult to communicate these changes to all the right people. Worse yet, there may be hundreds of existing workbooks that need to have their data connections updated. The DCL helps solve these problems because customers will only need to update a single connection file in the DCL with new information. After they have done so, workbook authors will get the right connection information the next time they use that connection file, and any existing workbooks that were created using the connection file will now have their connections updated automatically the next time that workbook’s data is refreshed.

解决连接管理的问题…

除了提高查找连接的能力，DCL还会帮助客户管理连接。数据源的信息是可以修改的，例如服务器名称，OLAP CUBE名称，表名称等等，一个典型的例子就是当数据库从一个测试服务器转移到发布服务器上的时候。一个组织中当然会有很多个作者，要把这样的改变告诉所有的用户太困难了。更糟的可能是，数以百计的已有工作簿需要更新它们的数据连接。DCL解决了这个问题，现在用户仅需要更新DCL中的一个连接文件就可以了。之后，工作簿的作者们在下一次使用连接文件的时候就可以得到正确的连接信息了，所有使用这些连接文件创建的已有工作簿都会更新它们的连接和工作簿中的数据。

How exactly does this work? By default, workbooks will refresh their connection information from a DCL only when they fail to connect to the data source (you might think of this as a “failover” mechanism). But we have also added the ability to force workbooks to always get the latest connection information before attempting to connect. An example of when this might be useful is when you want workbook authors to start using a new database for business reports, but you still want to keep the old database around and functioning for auditing or test reasons. Connections to the old database still work, but you want current and future workbooks to start using the new database. The “always use this file to refresh data” setting is designed for exactly that kind of scenario. The setting is a property in the ODC file itself – it can be set when the ODC file is created (pictured below).

这究竟是如何工作的呢？默认情况下，工作簿仅会当它们连接数据源失败的时候才会从DCL中刷新它们的连接信息（你也许认为这是一种失效转移“failover”机制）。但是我们也增加了强制工作簿永远在尝试连接前更新连接信息的功能。一个可以体现这种机制好处的例子是：当你希望那些工作簿的作者将新的数据库用于工作报告的时候，可是你仍然想保持原本的数据库用于审计或者测试的环境和功能。旧的数据连接依然可以使用，但是你希望当前以后的工作簿都使用新的数据库。始终使用此文件更新数据（always use this file to refresh data）的设定就是为这种情况设计的。这个设定是ODC文件本身的一项属性，它可以在创建ODC文件的时候设定（如下图）。

Making data connectivity more secure …

Now that we have talked about discoverability and manageability, let’s conclude by looking at how the DCL can be used to make connecting to data more secure.

更安全的数据连接…

到此，我们已经讨论了DCL连接和管理的方便性，最后让我们看看DCL是如何实现更安全的数据连接的。

One common security concern is knowing which data connections are safe to run – for example, data connections can contain malicious queries, or they could contain connection parameters that can slow an app down or compromise the integrity of the data. By creating a DCL, and by only allowing most knowledgeable and trusted “connection authors” to save connections to the DCL, you add an extra layer of security that helps ensure that connections coming from a DCL safe to run.

一个通常会遇到的安全问题是，如何判断数据连接是安全的。举例来说，一个数据连接可能包含恶意查询，或者它们的连接字段会拖慢程序的运行速度，也有可能会损害数据完整性。通过使用DCL，再加上仅允许最值得信任的权威连接作者（connection authors）在DCL中保存连接，就相当于又增加了额外的安全层以确保DCL中的连接是可以安全运行的。

In a previous entry, I talked about Trusted Locations on Excel Services as a means to ensure that malicious workbooks were prevented from running on the server. Much like Trusted Locations, Excel Services has “Trusted Connection Libraries” for data connections. Excel Services has a mode where it will only process data connections from DCLs that the administrator has explicitly marked as “trusted” by the server. As mentioned above, data connections have many security threats associated with them – in many ways processing a data connection can be like running code. By providing Trusted Connection Libraries, Excel Services gives the administrator the ability to allow only specific data connections to be run on the server.

上面的内容阐述了Excel服务中的信任位置（Trusted Locations），它确保了恶意工作簿不能在服务器上运行。和信任位置非常相似，Excel服务对与数据连接还有一个信任连接库（Trusted Connection Libraries）的概念。Excel服务有一个仅处理被administrator明确标记为信任（Trusted）的DCL中数据连接的运行模式。通过信任连接库机制，Excel服务让administrator可以仅允许特定的数据连接在服务器上运行。

That’s it for DCLs. Next time we will take a look at how Excel Services integrates with SharePoint dashboards.

这就是DCL了。下次我们会讨论Excel服务是如何与SharePoint仪表盘整合的。