使用 Excel Services 共用 SQL Server 資料連線 (SharePoint Server 2013)Share a SQL Server data connection using Excel Services (SharePoint Server 2013)

摘要:了解如何使用 Excel 來建立及共用人員可用來建立資料模型、 報表、 計分卡和儀表板的 SQL Server 資料的連線。Summary: Learn how to use Excel to create and share connections to SQL Server data that people can use to create data models, reports, scorecards, and dashboards.

您可以使用 Excel 建立等 SQL Server 資料庫的連線,然後與其他人共用這些連線。當您可以將資料連線上傳至 SharePoint Server 2013 中的 Excel Services 信任的資料連線庫時,資料連線是可供您與其他用來建立資料模型、 報表、 計分卡和儀表板。根據使用的特定資料來源、 人員可以輕鬆地重新整理 Excel Services 活頁簿中的資料使顯示最新資訊。You can use Excel to create connections to databases such as SQL Server, and then share those connections with others. When you can upload a data connection to an Excel Services trusted data connection library in SharePoint Server 2013, the data connection is available for you and others to use to create data models, reports, scorecards, and dashboards. Depending on the particular data source that is used, people can easily refresh data in Excel Services workbooks so that the most current information is displayed.

開始之前Before you begin

在開始進行此工作之前,請先檢閱下列先決條件的相關資訊:Before you begin this task, review the following information about prerequisites:

  • 您必須使用 Excel 2016 和 SharePoint Server 2013。You must be using Excel 2016 and SharePoint Server 2013.

  • Excel Services 必須設定成包含信任的資料連線庫與受信任的文件庫。理想狀況下,您必須商務智慧中心網站設定您可以使用資料連線及活頁簿。如需詳細資訊,請參閱 < Configure SharePoint Server 2013 的商務智慧中心Excel Services must be configured to include a trusted data connections library and a trusted documents library. Ideally, you'll have a Business Intelligence Center site configured that you can use for your data connections and workbooks. For more information, see Configure a Business Intelligence Center in SharePoint Server 2013.

  • 您必須至少參與權限指派在 SharePoint Server 2013。You must have at least Contribute permissions assigned in SharePoint Server 2013.

  • 您將會需要 SharePoint 管理員提供您有關如何為組織使用的資料庫設定資料驗證的資訊。這會影響您連接至不同資料來源的方式。You will need information from a SharePoint administrator about how data authentication is configured for the databases your organization uses. This can affect how you connect to different data sources.

  • 如果您打算要發佈活頁簿包含資料模型至 SharePoint Server 2013、 Excel Services 必須設定為支援資料模型。如需詳細資訊,請參閱 < Configure Excel Services in SharePoint Server 2013 PreviewIf you plan to publish workbooks that contain data models to SharePoint Server 2013, Excel Services must be configured to support data models. For more information, see Configure Excel Services in SharePoint Server 2013 Preview.

步驟 1:使用 Excel 建立 SQL Server 資料的連線Step 1: Use Excel to create connections to SQL Server data

您可以使用 Excel 建立的連線會儲存在 SQL Server 中的一或多個資料表。這種連線是可用來建立報表、 計分卡和儀表板使用應用程式,例如 Excel Office 資料連線 (ODC) 檔案。You can use Excel to create a connection one or more tables that are stored in SQL Server. This kind of connection is an Office Data Connection (ODC) file that can be used to create reports, scorecards, and dashboards by using applications such as Excel.

使用 Excel 建立 SQL Server 表格式資料連線To create a SQL Server tabular data connection by using Excel

  1. 在 Excel 中,[資料] 索引標籤的 [取得外部資料]群組中,按一下 [從其他來源],並再選取 [從 SQL ServerIn Excel, on the Data tab, in the Get External Data group, click From Other Sources, and then select From SQL Server.

    [資料連線精靈] 隨即開啟。The Data Connection Wizard opens.

  2. 在 [連接至資料庫伺服器] 頁面上 [伺服器名稱] 方塊中,指定您想要使用的 SQL Server 資料所在的伺服器名稱。請勿按下一個尚未。On the Connect to Database Server page, in the Server name box, specify the name of the server where the SQL Server data that you want to use resides. Do not click Next yet.

  3. 在 [登入認證] 區段中,執行下列任一下列步驟:In the Log on credentials section, take one of the following steps:

    • 如果您的組織會使用 Windows 驗證,請選擇 [使用 Windows 驗證],然後選擇 [下一步] 按鈕。If your organization is using Windows Authentication, choose Use Windows Authentication, and then choose the Next button.

    • 如果您的組織使用特定使用者認證,選擇 [使用下列的使用者名稱和密碼、 指定適當的使用者名稱和密碼,,然後選擇 [下一步] 按鈕。If your organization is using specific user credentials, choose Use the following User Name and Password, specify an appropriate user name and password, and then choose the Next button.

  4. 在 [選取資料庫及資料表] 頁面中選取包含您想要將資料的資料庫] 清單中,選取您想要使用的資料庫。請勿按下一個尚未。On the Select Database and Table page, in the Select the database that contains the data that you want list, select the database that you want to use. Do not click Next yet.

  5. 在 [選取資料庫及資料表] 頁面上執行下列任一步驟:On the Select Database and Table page, take one of the following steps:

    • 若要建立使用一個表格的連線,請選取 [連接至特定的表格。選取您要使用的表格,然後按一下 [下一步To create a connection that uses a single table, select Connect to a specific table. Select the table that you want to use, and then click Next.

    • 若要建立使用多個資料表的連線,請選取 [連接至特定的表格啟用的多個資料表的選取範圍。選取您要使用、 按一下 [選取相關的資料表,然後按 [下一步的表格。To create a connection that uses more than one table, select both Connect to a specific table and Enable selection of multiple tables. Select the tables that you want to use, click Select Related Tables, and then click Next.

  6. 在 [儲存資料連線檔案和完成] 頁面上執行下列步驟:On the Save Data Connection File and Finish page, take the following steps:

  7. 在 [檔案名稱] 方塊中,保留或變更預設檔案名稱。In the File Name box, keep or change the default file name.

  8. 在 [描述] 方塊中輸入資料連線的簡短描述。In the Description box, type a brief description for the data connection.

  9. 在 [易記名稱] 方塊中,保留預設名稱或輸入檔案的新名稱。In the Friendly Name box, keep the default name or type a new name for file.

  10. 在 [搜尋關鍵字] 方塊中輸入一些單字或片語可協助使用者尋找資料連線發佈至 SharePoint Server 2013 時。In the Search Keywords box, type some words or phrases that will help users find the data connection when it is published to SharePoint Server 2013.

  11. Excel Services] 旁按一下 [驗證設定...] 並再執行下列任一步驟:Next to Excel Services, click Authentication Settings…, and then take one of the following steps:

    • 若要讓此連線設為使用 Windows 驗證] 或 [有效的使用者名稱 」 功能,請選取 [使用驗證的使用者帳戶,並再按一下 [確定]If you want this connection to use Windows Authentication or the Effective User Name feature, select Use the authenticated user's account, and then click OK.

    • 如果您想要使用 Secure Store Service 此連線,請選取 [使用儲存的帳戶。在 [應用程式識別碼] 方塊中指定安全認證儲存目標應用程式識別碼,並再按一下 [確定]If you want this connection to use Secure Store Service, select Use a stored account. In the Application ID box, specify the Secure Store target application ID, and then click OK.

    • 若要使用自動的服務帳戶設定 Excel Services、 選取None、 然後再按一下 [確定]If Excel Services is configured to use the unattended service account, select None, and then click OK.

      重要

      如果您不知道要選擇哪個選項,請連絡 SharePoint 管理員。If you do not know which option to choose, contact a SharePoint administrator.

  12. 按一下 [完成] 以關閉 [儲存資料連線檔案和完成] 頁面。Click Finish to close the Save Data Connection File and Finish page.

  13. 在 [匯入資料] 頁面上按一下 [只建立連線,並再按一下 [確定]On the Import Data page, click Only Create Connection, and then click OK.

  14. 重複步驟 1-7,直到建立完您想要的所有資料連線。Repeat steps 1-7 until you have created all the data connections that you want.

  15. 關閉 Excel。Close Excel.

根據預設,資料連線會儲存在文件庫中的 [我的資料來源] 資料夾中您的電腦上。By default, the data connection is saved in the My Data Sources folder in the Documents library on your computer.

步驟 2:將資料連線上傳至 SharePoint ServerStep 2: Upload data connections to SharePoint Server

建立資料連線之後,下個步驟就是將它上傳到資料連線庫。建議您使用商務智慧中心網站來儲存及管理商務智慧內容,例如資料連線。After data connections are created, the next step is to upload it to a data connection library. We recommend that you use a Business Intelligence Center site to store and manage business intelligence content, such as data connections.

注意

如果您不使用商務智慧中心網站,請確定您使用指定為信任位置的 Excel services 資料連線庫。如需詳細資訊,請參閱 < Manage Excel Services 信任的資料連線庫 (SharePoint Server 2013)If you are not using a Business Intelligence Center site, make sure that you use a data connection library that is specified as a trusted location in Excel Services. For more information, see Manage Excel Services trusted data connection libraries (SharePoint Server 2013).

資料連線上傳至 SharePoint ServerTo upload a data connection to SharePoint Server

  1. 開啟網頁瀏覽器,並瀏覽至包含您要使用之資料連線庫的 SharePoint 網站。Open a web browser and navigate to the SharePoint site that contains the data connection library that you want to use.

    如果您使用商務智慧中心,網址 (URL) 的格式類似於http://servername/sites/bicenter。If you are using a Business Intelligence Center, the website address (URL) resembles http://servername/sites/bicenter.

  2. 按一下 [網站內容] 以檢視清單和文件庫可供該網站。Click Site Contents to view the lists and libraries that are available for that site.

  3. 按一下 [資料連線] 以開啟該文件庫。Click Data Connections to open that library.

  4. 按一下 [新增項目以開啟 [新增文件] 對話方塊。Click New Item to open the Add a document dialog box.

  5. 按一下 [瀏覽]以開啟 [選擇要上傳檔案] 對話方塊。Click Browse to open the Choose File to Upload dialog box.

  6. 假設資料連線會儲存在其預設位置中,按一下 [文件庫、 按一下 [文件並連按兩下 [我的資料來源Assuming the data connection is saved in its default location, click Libraries, click Documents, and then double-click My Data Sources.

  7. 選取您要上傳的 ODC 檔案,然後按一下 [開啟Select the ODC file that you want to upload, and then click Open.

  8. 在 [新增文件] 對話方塊中,按一下 [確定]In the Add a document dialog box, click OK.

    資料連線] 表單隨即開啟。A Data Connections form opens.

  9. 在 [資料連線] 表單中,指定下列設定:In the Data Connections form, specify the following settings:

    • 在 [內容類型] 區段中,確認已選取 [ Office 資料連線檔案In the Content Type section, confirm that Office Data Connection File is selected.

    • 在 [名稱] 方塊中,保留或變更 ODC 檔案的檔案名稱。In the Name box, keep or change the file name of the ODC file.

    • 在 [標題] 方塊中,保留或變更 ODC 檔案的標題。In the Title box, keep or change the title of the ODC file.

    • 在 [描述] 方塊中輸入資料連線的說明。In the Description box, type a description of the data connection.

    • 在 [關鍵字] 方塊中輸入一個或多個字或詞。此資訊使用搜尋查詢探索資料連線。In the Keywords box, type one or more words or phrases. This information is used by search queries to discover the data connection.

      然後按一下 [儲存]。Then click Save.

      此資料連線隨即新增至文件庫。請針對您要共用的每個資料連線重複此程序。The data connection is added to the library. Repeat this procedure for each data connection that you want to share.

另請參閱See also

概念Concepts

使用 Excel 與 Excel Services (SharePoint Server 2013) 共用資料連線Share data connections by using Excel and Excel Services (SharePoint Server 2013)

支援的 Excel Services (SharePoint Server 2013) 資料來源Data sources supported in Excel Services (SharePoint Server 2013)