使用 Secure Store (SharePoint Server 2013) 來設定 Power Pivot 的排定的資料重新整理Configure scheduled data refresh for Power Pivot by using Secure Store (SharePoint Server 2013)

摘要:了解如何使用 Secure Store 在 Power Pivot 中設定排定的資料重新整理 sharepoint。Summary: Learn to configure scheduled data refresh in Power Pivot for SharePoint by using Secure Store.

重要

此案例僅適用於 SharePoint Server 2013。This scenario applies only to SharePoint Server 2013.

在本文中,我們將看看一下使用 Secure Store Service 在 SQL Server 2012 Power Pivot for SharePoint 2013 中設定排定的資料重新整理。In this article, we'll take a look at configuring scheduled data refresh in SQL Server 2012 Power Pivot for SharePoint 2013 by using the Secure Store Service.

使用 Secure Store,您可以在加密資料庫儲存您的資料存取認證並 Power Pivot for SharePoint 可以使用這些認證重新整理排程您設定您的報告中的資料。By using Secure Store, you can store your data access credentials in an encrypted database and Power Pivot for SharePoint can use these credentials to refresh the data in your reports on a schedule that you set up.

開始之前Before you begin

開始之前,您需要有:Before starting, you will need:

  • Active Directory 帳戶可用來存取您的報表中所使用之資料來源。我們將參照此為資料存取帳戶。我們將看看如何針對本文中設定的帳戶來存取資料來源,以便您只需要本身開始的帳戶。An Active Directory account that you can use to access the data sources used in your report. We'll refer to this as the data access account. We'll look at how to configure the account for access to your data sources in this article, so you just need the account itself to get started.

  • Active Directory 群組包含所有使用者藉由設定資料重新整理排程或手動啟動資料重新整理外的一般的排程工作流程會觸發重新整理資料。我們將這個群組參照做為資料重新整理使用者群組。An Active Directory group that contains all users who will trigger data refresh, either by setting the data refresh schedule or by manually starting a data refresh outside the normal schedule. We'll refer to this group as the data refresh users group.

  • 對將使用之 SharePoint 文件庫的參與存取權。Contribute access to the SharePoint document library that you will be using.

此外,請確定您的 SharePoint Server 2013 伺服器陣列中所設定Excel ServicesSecure StoreAdditionally, be sure that Excel Services and Secure Store are configured in your SharePoint Server 2013 farm.

影片示範Video demonstration

這段影片會顯示設定排定的資料重新整理在 SQL Server 2012 Power Pivot for SharePoint 2013 中使用 Secure Store Service 本文所述的步驟。This video shows the steps involved in configuring scheduled data refresh in SQL Server 2012 Power Pivot for SharePoint 2013 by using the Secure Store Service, as described in this article.

影片: 使用 Secure Store 來設定 Power Pivot 的排定的資料重新整理Video: Configure scheduled data refresh for Power Pivot by using Secure Store

使用 Secure Store 設定排定的資料重新整理的第一步是確認資料存取帳戶具有適當的存取權報表中所用的資料來源。我們將看看一下 SQL Server 和 Analysis Services 資料來源。The first step in setting up scheduled data refresh by using Secure Store is to ensure that the data access account has the proper access to the data source used in your report. We'll take a look at SQL Server and Analysis Services data sources.

使用 SQL Server 資料來源Use a SQL Server data source

如果您使用 SQL Server 資料來源,您需要確定您的資料存取帳戶具有讀取 SQL Server 資料庫的權限您資料所在。If you're using SQL Server for your data source, you'll need to make sure that your data access account has read permissions to the SQL Server database where your data resides.

若要設定的讀取權限的 SQL Server 資料庫To set read permission on a SQL Server database

  1. 在 SQL Server Management Studio 中,連線至資料庫引擎。In SQL Server Management Studio, connect to the database engine.

  2. 展開 [安全性]。Expand Security.

  3. 登入,以滑鼠右鍵按一下,然後選擇 [新增登入Right-click Logins, and then choose New Login.

  4. 在 [登入名稱] 方塊中輸入您要授與資料庫存取權之帳戶的網域和使用者名稱。In the Login name box, type the domain and user name of the account to which you want to grant database access.

  5. 在 [使用者對應] 頁面上,選取您要授與存取權之資料庫的 [對應] 核取方塊。On the User Mapping page, select the Map check box for the database to which you want to grant access.

  6. 選取 [ db_datareader]核取方塊。Select the db_datareader check box.

  7. 選擇 [確定]。Choose OK.

如果您也使用 Analysis Services,請參閱 [下一步] 區段中的如何設定 Analysis Services 資料來源權限的相關資訊。如果您不使用 Analysis Services,跳至儲存在 Secure Store 中的資料存取帳戶If you're also using Analysis Services, see the next section for information about how to set up access to Analysis Services data sources. If you're not using Analysis Services, skip ahead to Store your data access account in Secure Store.

使用 Analysis Services 資料來源Use an Analysis Services data source

如果您使用的 Analysis Services,您需要確定您的資料存取帳戶是適當的 Analysis Services 角色的成員與該角色具有 Analysis Services cube 的讀取權限。If you're using Analysis Services, you'll need to make sure that your data access account is a member of the proper Analysis Services role and that the role has read access to the Analysis Services cube.

若要設定的讀取權限 Analysis Services cubeTo set read permission on an Analysis Services cube

  1. 在 SQL Server Management Studio,連線至 Analysis Services。In SQL Server Management Studio, connect to Analysis Services.

  2. 依序展開 [資料庫],並展開您要授與存取權的資料庫。Expand Databases, and expand the database to which you want to grant access.

  3. 以滑鼠右鍵按一下 [角色],然後選擇 [新增角色Right-click Roles, and then choose New Role.

  4. 輸入角色的名稱。Type a name for the role.

  5. 在 [成員資格] 頁面上:On the Membership page:

  6. 按一下 [新增]。Click Add.

  7. 輸入您的資料存取帳戶,然後選擇[確定]Type your data access account, and then choose OK.

  8. 在 [ Cube ] 頁面上,選取您要授與存取權之 cube 的讀取權限。On the Cubes page, select Read access for the cubes to which you want to grant access.

  9. 選擇 [確定]。Choose OK.

在 Secure Store 中儲存資料存取帳戶Store your data access account in Secure Store

已授與資料存取帳戶資料來源的存取權下, 一步是 Secure Store 中儲存這個帳戶。首先,我們將建立資料存取帳戶的安全認證儲存目標應用程式。目標應用程式基本上是一種定義與帳戶相關的一些事項、 like 何種帳戶並誰具有可以使用它。Once the data access account has been granted access to your data source, the next step is to store this account in Secure Store. First, we'll create a Secure Store target application for the data access account. A target application is basically a way of defining some things about the account, like what kind of account it is and who's allowed to use it.

若要建立的安全認證儲存目標應用程式To create a Secure Store target application

  1. 在管理中心的 [應用程式管理] 下選擇 [管理服務應用程式In Central Administration, under Application Management, choose Manage service applications.

  2. 按一下 [Secure Store Service 應用程式]。Click the Secure Store service application.

  3. 在功能區的 [編輯] 索引標籤上選擇 [新增]。On the ribbon, on the Edit tab, choose New.

  4. 輸入目標應用程式識別碼Type a Target Application ID.

    注意

    當您設定 Power Pivot 活頁簿的重新整理排程時需要此值。You'll need this value when you configure the refresh schedule for your Power Pivot workbook.

  5. 輸入顯示名稱] 和 [連絡人電子郵件Type a Display Name and Contact E-mail.

  6. 作為目標應用程式類型] 下,選擇 [群組]。For Target Application Type, choose Group.

  7. 選擇 [下一步]。Choose Next.

  8. 保留預設認證設定,然後選擇 [下一步Keep the default credential settings, and choose Next.

  9. 指定要管理目標應用程式,例如您的伺服器陣列管理員,在 [目標應用程式管理員] 方塊中的使用者。Specify a user to administer the target application, such as your farm administrator, in the Target Application Administrators box.

  10. 在 [成員] 方塊中,包括:In the Members box, include:

    • 執行 Power Pivot for SharePoint 服務應用程式之應用程式集區帳戶。The account that runs the application pool for the Power Pivot for SharePoint service application.

    • 資料重新整理包含將排程或啟動 Power Pivot 資料重新整理 sharepoint 使用者群組。The data refresh group that contains the users who will schedule or start a data refresh in Power Pivot for SharePoint.

  11. 選擇 [確定]。Choose OK.

建立目標應用程式之後,下一步是建立您的資料存取帳戶與目標應用程式的關聯。Once the target application has been created, the next step is to associate your data access account with it.

若要建立資料存取帳戶與目標應用程式的關聯To associate the data access account with the target application

  1. 在 [安全認證儲存管理] 頁面上選取您要設定認證的目標應用程式] 核取方塊。On the Secure Store management page, select the check box for the target application for which you want to set the credentials.

  2. 在功能區] 的 [認證] 區段中選擇設定On the ribbon, in the Credentials section, choose Set.

  3. 輸入存取帳戶的使用者名稱與密碼。Type the user name and password of your access account.

  4. 選擇 [確定]。Choose OK.

安全認證儲存安裝程式已完成。下一步是設定 SharePoint 的 Power Pivot 資料重新整理排程。Secure Store setup is now complete. The next step is to set up a data refresh schedule in Power Pivot for SharePoint.

設定資料重新整理排程在 Power Pivot for SharePointSet up a data refresh schedule in Power Pivot for SharePoint

每個項目設定之後,我們可以設定重新整理排程和中 Power Pivot for SharePoint 的其他設定。我們將啟動建置含有在 Excel 中的資料模型的測試活頁簿並將其發佈至其中啟用 Power Pivot for SharePoint 網站集合中的文件庫。然後,我們可以設定重新整理設定。Now that everything is configured, we can set up the refresh schedule and other settings in Power Pivot for SharePoint. We'll start by building a test workbook with a data model in Excel and publishing it to a document library in a site collection where Power Pivot for SharePoint is enabled. Then, we can configure the refresh settings.

建立測試活頁簿To create a test workbook

  1. 在 Excel 中,在 [資料] 索引標籤上選擇 [從其他來源],然後按從 SQL ServerIn Excel, on the Data tab, choose From Other Sources, and then choose From SQL Server.

  2. 輸入您資料所在的 SQL Server 執行個體的名稱。Type the name of the instance of SQL Server where your data resides.

  3. 遵循精靈的指示,連線至包含您資料的表格。Follow the wizard through to connect to the table that contains your data.

  4. 精靈完成時,您應該會看到 [匯入資料] 對話方塊。選擇 [只建立連線] 選項,然後選取 [新增此資料至資料模型] 核取方塊。When the wizard completes, you should see the Import Data dialog box. Choose the Only Create Connection option, and then select the Add this data to the Data Model check box.

  5. 選擇 [確定]。Choose OK.

  6. 在 [ Power Pivot ] 索引標籤上選擇 [管理]。On the Power Pivot tab, choose Manage.

  7. Power Pivot ] 功能區中,選擇 [樞紐分析表On the Power Pivot ribbon, choose PivotTable.

  8. 在 [插入樞紐] 對話方塊中,選擇 [現有工作表] 選項,然後選擇[確定]On the Insert Pivot dialog box, choose the Existing Worksheet option, and then choose OK.

  9. 選取樞紐分析表報表要有的欄位。Select the fields that you want in the PivotTable report.

  10. 儲存至文件庫的活頁簿上已啟用 Power Pivot 的網站集合。Save the workbook to a document library on the site collection where you enabled Power Pivot.

活頁簿有已儲存至 SharePoint 文件庫,現在讓我們設定重新整理設定。Now that the workbook has been saved to a SharePoint document library, let's configure the refresh settings.

若要設定的活頁簿的重新整理設定To configure refresh settings for a workbook

  1. 在 Excel 活頁簿儲存所在的文件庫,選擇省略符號 (...) 控制項兩次,,然後選擇 [管理 Power Pivot 資料重新整理In the document library where your Excel workbook is stored, choose the ellipsis (...) control twice, and then choose Manage Power Pivot Data Refresh.

    文件庫控制項的螢幕擷取畫面。

  2. 在 [管理資料重新整理] 頁面上選取 [啟用] 核取方塊。On the Manage Data Refresh page, select the Enable check box.

  3. 在 [排程詳細資料] 區段中,選擇想用於重新整理此活頁簿中的資料的排程選項。In the Schedule Details section, choose the schedule options that you want for refreshing the data in this workbook.

  4. (選用) 若要立即重新整理活頁簿,請選取 [盡也重新整理] 核取方塊。Optionally, if you want the workbook to refresh right away, select the Also refresh as soon as possible check box.

  5. 在 [認證] 區段中選擇 [使用儲存在 Secure Store Service 的連線] 選項。In the Credentials section, choose the Connect using the credentials saved in Secure Store Service option.

  6. 輸入您在 [識別碼] 方塊中建立的安全認證儲存目標應用程式的識別碼。Type the ID of the Secure Store target application that you created in the ID box.

  7. 選擇 [確定]。Choose OK.

您可以測試如果資料重新整理可以透過一些變更您的資料,並再使用也重新整理盡選項立即重新整理活頁簿運作正常。You can test if data refresh is working properly by making some changes to your data, and then setting the workbook to refresh right away by using the Also refresh as soon as possible option.

另請參閱See also

概念Concepts

設定 Power Pivot for SharePoint 2013Configure Power Pivot for SharePoint 2013

使用指定的帳戶 (SharePoint Server 2013) 來設定 Power Pivot 的排定的資料重新整理Configure scheduled data refresh for Power Pivot by using a specified account (SharePoint Server 2013)

使用自動的資料重新整理帳戶 (SharePoint Server 2013) 來設定 Power Pivot 的排定的資料重新整理Configure scheduled data refresh for Power Pivot by using the unattended data refresh account (SharePoint Server 2013)