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

摘要:了解如何設定 Power Pivot for SharePoint Server 2013。Summary: Learn how to configure Power Pivot for SharePoint Server 2013.

重要

此案例僅適用於 SharePoint Server 2013 和 SQL Server 2012 Service Pack 1 (SP1)。This scenario applies only to SharePoint Server 2013 and SQL Server 2012 with Service Pack 1 (SP1).

在此案例中,我們將看看一下如何設定 Power Pivot for SharePoint 2013。這包括安裝 Power Pivot for SharePoint 伺服器陣列,設定 Power Pivot 服務應用程式,並執行一些測試以確定一切正常運作中。In this scenario, we'll take a look at how to set up Power Pivot for SharePoint 2013. This includes installing Power Pivot for SharePoint on the servers in your farm, configuring a Power Pivot service application, and running some tests to make sure everything is working properly.

開始之前Before you begin

開始之前,請確定您已經:Before getting started, make sure you have:

您可能會注意到我們將使用 Power Pivot 設定工具已部署 Excel Services 與 Secure Store 的選項。不過,Power Pivot 設定工具不是能夠設定使用建議的帳戶,讓我們建議您設定這些服務手動使用 [上方連結這些服務。You may notice that the Power Pivot configuration tool that we'll be using has options for deploying Excel Services and Secure Store. However, the Power Pivot configuration tool is not able to configure these services using the recommended accounts, so we recommend that you configure these services manually using the links above.

影片示範Video demonstration

這段影片顯示步驟設定 Power Pivot for SharePoint 2013 在本文所述。This video shows the steps involved in setting up Power Pivot for SharePoint 2013, as described in this article.

影片: 設定 Power Pivot for SharePoint 2013Video: Configure Power Pivot for SharePoint 2013

在本節,我們會準備 Power Pivot 安裝在伺服器陣列。這包括註冊受管理的帳戶和設定一些 Secure Store 設定在管理中心內,以及在 SQL Server 中設定一些安全性設定。必須是伺服器陣列管理員和 SQL Server 系統管理員可以執行下列步驟,為命令介面管理員以執行 PowerShell 步驟。In this section, we will prepare the farm for Power Pivot installation. This includes registering a managed account and configuring some Secure Store settings in Central Administration, as well as configuring some security settings in SQL Server. You'll need to be a farm administrator and a SQL Server administrator to do these steps, as well as a Shell Administrator to do the PowerShell steps.

將網域帳戶登錄為 SharePoint Server 2013 中受管理帳戶Register a domain account as a managed account in SharePoint Server 2013

第一個步驟是註冊您想要用來執行 Power Pivot 應用程式集區為 SharePoint Server 2013 中受管理帳戶的網域帳戶。這會將帳戶提供給我們我們建立 Power Pivot 服務應用程式稍後時使用。The first step is to register the domain account that you want to use to run the Power Pivot application pool as a managed account in SharePoint Server 2013. This will make the account available for us to use when we create the Power Pivot service application later on.

註冊受管理帳戶To register a managed account

  1. 在管理中心按一下 [安全性]。In Central Administration, click Security.

  2. 在 [一般安全性] 下按一下 [設定受管理帳戶]。Under General Security, click Configure managed accounts.

  3. 按一下 [註冊受管理帳戶]。Click Register Managed Account.

  4. 為您執行 Power Pivot 應用程式集區所建立的帳戶輸入使用者名稱與密碼。Type the username and password of the account that you created to run the Power Pivot application pool.

  5. 若要為您處理帳戶密碼變更 SharePoint Server 2013,請選取 [啟用密碼變更] 核取方塊,並再填入的詳細資料時要變更密碼。If you want SharePoint Server 2013 to handle account password changes for you, select the Enable password change check box, and then fill in the details on when you want the password changed.

  6. 按一下 [ OK ]。Click OK.

    授與受管理的帳戶存取內容資料庫Grant the managed account access to the content database

您也必須授與此帳戶的存取權的 SharePoint 內容資料庫。使用下列程序會與 Power Pivot 相關聯的每個 web 應用程式。You must also grant access to the SharePoint content database for this account. Use the following procedure for each web application that will be associated with Power Pivot.

若要授與內容資料庫的存取權的受管理帳戶To grant content database access to the managed account

  1. 按一下 [開始]、 [所有程式]、 [ Microsoft SharePoint 2013 產品SharePoint 2013 管理命令介面中,以滑鼠右鍵按一下,然後按一下 [以系統管理員身分執行Click Start, click All Programs, click Microsoft SharePoint 2013 Products, right-click SharePoint 2013 Management Shell, and then click Run as Administrator.

  2. 在 Microsoft PowerShell 命令提示字元處輸入下列語法 (在每一行之後按 ENTER):At the Microsoft PowerShell Command Prompt, type the following syntax (press ENTER after each line):

    $w = Get-SPWebApplication -identity http://<WebApplication>
    $w.GrantAccessToProcessIdentity("<Domain>\<PowerPivotApplicationPoolAccount>")
    

重要

如果在未來新增多個內容資料庫,您需要重新執行這些 cmdlet 以確保 Power Pivot 可存取新的資料庫。If in the future you add more content databases, you'll need to rerun these cmdlets to ensure that Power Pivot has access to the new databases.

建立安全認證儲存目標應用程式Create a target application in Secure Store

下一步是建立 Secure Store 目標應用程式 Power Pivot 的自動的資料重新整理帳戶。此目標應用程式將用來重新整理 Power Pivot 報告中的資料。The next step is to create a target application in Secure Store for the Power Pivot unattended data refresh account. This target application will be used to refresh the data in your Power Pivot reports.

建立目標應用程式To create a target application

  1. 在管理中心的應用程式管理] 下按一下 [管理服務應用程式]。In Central Administration, under Application Management, click Manage service applications.

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

  3. 在功能區中,按一下 [新增]。In the ribbon, click New.

  4. 在 [目標應用程式識別碼] 方塊中輸入 PowerPivotUnattendedAccount。In the Target Application ID box, type PowerPivotUnattendedAccount.

    注意

    請務必使用這個值以便 Power Pivot for SharePoint 2013 設定工具識別它。Be sure to use this value so that the Power Pivot for SharePoint 2013 Configuration tool will recognize it.

  5. 輸入顯示名稱和電子郵件地址。Type a display name and email address.

  6. 針對目標應用程式類型,請確定已選取 [個別],並再按 [下一步For Target Application Type, make sure Individual is selected, and then click Next.

  7. 確定 [欄位類型設為 [ Windows 使用者名稱Windows 密碼,然後按 [下一步Ensure Field Type is set to Windows User Name and Windows Password, and click Next.

  8. 輸入的名稱或群組的目標應用程式管理員,並再按一下 [確定]Type a name or group for the Target Application Administrators, and then click OK.

  9. 選取您剛才建立之目標應用程式,然後按一下 [在功能區的 [認證] 區段的 [設定Select the target application that you just created, and then in the Credentials section of the ribbon, click Set.

  10. 針對認證擁有者、 輸入您建立 Power Pivot 應用程式集區 (您剛設定為受管理帳戶的一個) 的帳戶。For Credential Owner, type the account that you created for the Power Pivot application pool (the one that you just configured as a managed account).

  11. 輸入可存取資料來源的認證使用者名稱與密碼。Type the user name and password of the credentials that have access to your data sources.

  12. 按一下 [ OK ]。Click OK.

    授與存取權的 SharePoint Server 管理內容資料庫Grant access to the SharePoint Server admin content database

為了讓 Power Pivot 管理儀表板報告運作,執行 Excel Services 應用程式集區的帳戶必須具備 SharePoint Server 2013 管理內容資料庫的讀取權限。您的設定 SQL Server 中。In order for the Power Pivot Management Dashboard reports to work, the account that runs the Excel Services application pool must have read access to the SharePoint Server 2013 admin content database. You configure this in SQL Server.

管理內容資料庫存取權授與To grant admin content database access

  1. 開啟 SQL Server Management Studio 並連線至資料庫引擎。Open SQL Server Management Studio and connect to the database engine.

  2. 展開 [安全性],並按兩下用於 Excel Services 應用程式集區帳戶。Expand Security and double-click the account that is used for the Excel Services application pool.

  3. 在 [使用者對應] 頁面上,按一下 [SharePoint 管理內容資料庫 (通常稱為 SharePoint_AdminContent_)。On the User Mapping page, click the SharePoint Admin content database (usually named SharePoint_AdminContent_).

  4. 選取 [ SPDataAccess資料庫角色] 核取方塊。Select the SPDataAccess database role check box.

  5. 按一下 [ OK ]。Click OK.

基本伺服器陣列安裝程式只有在接下來来做的是安裝和設定 Power Pivot for SharePoint 已完成。The basic farm setup is now complete, so the next thing to do is to install and configure Power Pivot for SharePoint.

設定 Power Pivot for SharePointConfigure Power Pivot for SharePoint

在本節,我們將設定 Power Pivot 本身,包括安裝 Power Pivot 軟體和設定服務應用程式。In this section, we'll configure Power Pivot itself, including installing the Power Pivot software and configuring a service application.

在伺服器陣列中每部伺服器上安裝 Power PivotInstall Power Pivot on each server in the farm

在 SharePoint Server 2013 伺服器陣列中設定 Power Pivot 的第一個步驟是在伺服器陣列中每部伺服器上安裝 Microsoft sharepoint (sppowerpivot.msi) 的 SQL Server 2012 SP1 PowerPivot。The first step in configuring Power Pivot in a SharePoint Server 2013 farm is to install SQL Server 2012 SP1 PowerPivot for Microsoft SharePoint (sppowerpivot.msi) on each server in the farm.

使用下列程序在伺服器陣列的每部伺服器上執行 sppowerpivot.msi。Use the following procedure to run sppowerpivot.msi on each server in your farm.

若要安裝 Power Pivot for SharePoint 2013To install Power Pivot for SharePoint 2013

  1. 按兩下 sppowerpivot.msi。Double-click sppowerpivot.msi.

  2. 依據精靈的指示安裝 Power Pivot for SharePoint 2013。看見 [功能選擇] 頁面時,保持選取所有的項目 (這是預設值),然後完成精靈。Follow the wizard to install Power Pivot for SharePoint 2013. When you see the Feature Selection page, leave all of the items selected (this is the default), and then complete the wizard.

    執行 Power Pivot for SharePoint 設定工具Run the Power Pivot for SharePoint configuration tool

您已在伺服器陣列中每部伺服器上安裝 sppowerpivot.msi 下, 一步是執行 [PowerPivot for SharePoint 2013 設定工具。執行 SharePoint 管理中心網站的伺服器上執行此程式。Once you have installed sppowerpivot.msi on each server in the farm, the next step is to run the PowerPivot for SharePoint 2013 Configuration tool. Run this program on the server that is running the SharePoint Central Administration website.

若要執行 Power Pivot for SharePoint 2013 設定工具To run the Power Pivot for SharePoint 2013 Configuration tool

  1. 按一下 [開始 > 所有程式] > Microsoft SQL Server 2012 > PowerPivot for SharePoint 2013 設定Click Start > All Programs > Microsoft SQL Server 2012 > PowerPivot for SharePoint 2013 Configuration.

  2. 選擇 [設定或修復 Power Pivot for SharePoint ] 選項,並再按一下 [確定]Choose the Configure or Repair Power Pivot for SharePoint option, and then click OK.

  3. 在 Power Pivot 設定工具的左窗格中,選取 [建立 PowerPivot 服務應用程式。In the Power Pivot Configuration Tool, in the left pane, select Create PowerPivot Service application.

  4. 在 [參數] 索引標籤中,清除包含此巨集指令中的工作清單] 核取方塊。這會防止工具建立 Power Pivot 服務應用程式。我們將建立 Power Pivot 服務應用程式之後以手動方式。On the Parameters tab, clear the Include this action in the task list check box. This will prevent the tool from creating a service application for Power Pivot. We will create the Power Pivot service application manually later.

    未核取核取方塊時的螢幕擷取畫面。

    這將也使得設定步驟無法完成,但是我們稍後仍然更新版本,以及進行這些。This will also prevent further configuration steps from being completed, but we'll come back and do those later as well.

  5. 按一下 [執行]。Click Run.

  6. 按一下 [是]確認。Click Yes to confirm.

  7. 動作完成時,請按一下 [確定] 並再按一下 [結束]。When the action completes, click OK, and then click Exit.

如果您有其他伺服器陣列中,我們將最後執行 Power Pivot for SharePoint 2013 設定工具在這些,但先有幾個執行動作的詳細步驟。If you have additional servers in your farm, we'll eventually run the Power Pivot for SharePoint 2013 Configuration tool on those as well, but first there are a few more steps to do.

建立 Power Pivot 服務應用程式Create a Power Pivot service application

我們可以禁止 Power Pivot 設定工具建立 Power Pivot 服務應用程式中,我們可以手動建立 web 應用程式和使用稍早建立的應用程式集區帳戶。現在,我們需要移回並建立服務應用程式。We prevented the Power Pivot configuration tool from creating a Power Pivot service application, so that we could create one manually and use the application pool account that we created earlier. Now, we need to go back and create the service application.

若要建立 Power Pivot 服務應用程式To create a Power Pivot service application

  1. 在管理中心 [應用程式管理] 下按一下 [管理服務應用程式In Central Administration, under Application Management, click Manage service applications.

  2. 在功能區中,按一下 [新增] 和 [ SQL Server Power Pivot 服務應用程式In the ribbon, click New, and then click SQL Server Power Pivot Service Application.

  3. 在 [名稱] 文字方塊中輸入服務應用程式的名稱。Type a name for the service application in the Name text box.

  4. 在 [應用程式集區名稱] 文字方塊中輸入新的應用程式集區的名稱。Type a name for the new application pool in the Application pool name text box.

  5. 可設定]下拉式清單中,選擇登錄為 Power Pivot 應用程式集區的受管理帳戶的帳戶。From the Configurable dropdown list, choose the account that you registered as a managed account for the Power Pivot application pool.

  6. 按一下 [ OK ]。Click OK.

現在已建立 Power Pivot 服務應用程式,您可能需要重新整理頁面才能看見。The Power Pivot service application has now been created, though you may need to refresh the page to see it.

設定 Power Pivot 自動的資料重新整理帳戶Configure the Power Pivot unattended data refresh account

下一步是設定 Power Pivot 自動的資料重新整理帳戶。我們執行這項作業在 Power Pivot 服務應用程式設定所指定的自動的資料重新整理我們在 Secure Store 中建立的帳戶。The next step is to configure the Power Pivot unattended data refresh account. We do this in the Power Pivot service application settings by specifying the unattended data refresh account that we created in Secure Store.

若要設定自動的資料重新整理帳戶To configure the unattended data refresh account

  1. 在管理中心的應用程式管理] 下按一下 [管理服務應用程式]。In Central Administration, under Application Management, click Manage service applications.

  2. 按一下 Power Pivot 服務應用程式。Click the Power Pivot service application.

  3. 按一下 [動作] 底下的 [設定服務應用程式設定]。Under Actions, click Configure service application settings.

  4. [重新整理資料(約三分之一個向下的方式),輸入您在 [ PowerPivot 自動資料重新整理帳戶] 方塊中建立 (PowerPivotUnattendedAccount) 的目標應用程式的目標應用程式識別碼。Under Data Refresh (about a third of the way down), type the target application ID of the target application that you created (PowerPivotUnattendedAccount) in the PowerPivot Unattended Data Refresh Account box.

  5. 按一下 [ OK ]。Click OK.

    重新執行 Power Pivot for SharePoint 2013 設定工具Rerun the Power Pivot for SharePoint 2013 Configuration tool

已建立服務應用程式並我們已設定自動的資料重新整理帳戶,我們需要執行 Power Pivot for SharePoint 2013 設定工具一次以完成步驟的其餘部分。Now that the service application has been created and we've configured the unattended data refresh account, we need to run the Power Pivot for SharePoint 2013 Configuration tool once again to complete the remainder of the steps.

若要執行 Power Pivot for SharePoint 2013 設定工具To run the Power Pivot for SharePoint 2013 Configuration tool

  1. 按一下 [開始 > 所有程式] > Microsoft SQL Server 2012 > Power Pivot for SharePoint 2013 設定Click Start > All Programs > Microsoft SQL Server 2012 > Power Pivot for SharePoint 2013 Configuration.

  2. 選擇 [設定或修復 Power Pivot for SharePoint ] 選項,並再按一下 [確定]Choose the Configure or Repair Power Pivot for SharePoint option, and then click OK.

  3. 在 Power Pivot 設定工具的左窗格中,選取 [網站集合中啟動 Power Pivot 功能]。In the Power Pivot Configuration Tool, in the left pane, select Activate Power Pivot Feature in a Site Collection.

  4. 在 [參數] 索引標籤上選擇您要啟動 Power Pivot 從網站 URL ] 下拉式清單的網站集合。On the Parameters tab, choose the site collection where you want to active Power Pivot from the Site URL dropdown list.

    注意

    如果您想要在多個網站集合中啟動 Power Pivot、 選擇一個開始使用以下。很容易稍後加入更多。If you want to activate Power Pivot in more than one site collection, choose one to start with here. It's easy to add more later.

  5. 如果您變更預設的網站集合設定,按一下 [驗證]。If you changed the default site collection setting, click Validate.

  6. 按一下 [執行] 並按一下 [是]確認。Click Run, and then click Yes to confirm.

  7. 動作完成時,請按一下 [確定] 並再按一下 [結束]。When the action completes, click OK, and then click Exit.

Power Pivot 安裝程式已完成與 Power Pivot 可供使用。Power Pivot setup is now complete and Power Pivot is ready to use.

在伺服器陣列中的其他伺服器上執行 Power Pivot for SharePoint 設定工具Run the Power Pivot for SharePoint Configuration tool on other servers in the farm

因為我們只已有一部伺服器上執行 Power Pivot for SharePoint 2013 設定工具,只會使用此伺服器上執行 SQL Server Power Pivot 系統服務。可使用 Power Pivot 如此一來,但如果您有多個伺服器陣列中,我們建議您在所有的這些上安裝服務。這可讓您變更哪些伺服器您上執行服務 (例如,如果您想要平衡出在伺服器陣列上服務) 或更大容量的多部伺服器上執行服務。Because we've only run the Power Pivot for SharePoint 2013 Configuration tool on one server, the SQL Server Power Pivot System Service will only be available to run on this server. It's fine to use Power Pivot this way, but if you have multiple servers in your farm, we recommend that you install the service on all of them. This will allow you to change which server you run the service on (for example, if you want to balance out the services on your farm) or to run the service on multiple servers for greater capacity.

您可以在伺服器陣列的其他伺服器上執行 Power Pivot for SharePoint 2013 設定工具,安裝 SQL Server Power Pivot 系統服務。You install the SQL Server Power Pivot System Service by running the Power Pivot for SharePoint 2013 Configuration tool on the other servers in your farm.

在伺服器陣列的每部伺服器上使用下列程序。Use the following procedure on each server in your farm.

若要執行 Power Pivot for SharePoint 2013 設定工具To run the Power Pivot for SharePoint 2013 Configuration tool

  1. 按一下 [開始 > 所有程式] > Microsoft SQL Server 2012 > PowerPivot for SharePoint 2013 設定Click Start > All Programs > Microsoft SQL Server 2012 > PowerPivot for SharePoint 2013 Configuration.

  2. 選擇 [設定或修復 Power Pivot for SharePoint ] 選項,並再按一下 [確定]Choose the Configure or Repair Power Pivot for SharePoint option, and then click OK.

  3. 如果您看到啟動網站集合中的 PowerPivot 功能的左窗格中,在其上按一下,然後選取 [您要啟動 Power Pivot 或如果您不想包含此巨集指令中的工作清單] 核取方塊取消核取網站集合啟動 Power Pivot 另一個網站集合中。If you see Activate PowerPivot Feature in a Site Collection in the left pane, click on it, and then select the site collection where you want to activate Power Pivot or uncheck the Include this action in the task list check box if you don't want to activate Power Pivot in another site collection.

  4. 如果您進行任何變更,請按一下 [驗證]。If you made any changes, click Validate.

  5. 按一下 [執行] 並按一下 [是]確認。Click Run, and then click Yes to confirm.

  6. 當工作完成後時,按一下 [確定] 並再按一下 [結束]。When the tasks complete, click OK, and then click Exit.

當您執行 Power Pivot for SharePoint 2013 設定工具時,它會安裝並開啟 SQL Server Power Pivot 系統服務的每部伺服器上執行所在。您可能不想在每一部伺服器上執行此服務,因此請務必移至管理中心的 [管理伺服器上的服務並停止您不想執行的伺服器上的服務。When you run the Power Pivot for SharePoint 2013 Configuration tool, it installs and turns on the SQL Server Power Pivot System Service on each server where you run it. You probably don't want this service running on every server, so be sure to go to Manage services on server in Central Administration and stop the service on the servers where you don't want to run it.

測試 Power Pivot for SharePoint 安裝Test the Power Pivot for SharePoint installation

每個項目設定之後,我們可以執行簡單的測試可行。我們將建立資料模型中的 Excel 以及將它發佈至已啟用 Power Pivot 的網站集合的文件庫。Now that everything is configured, we can do a simple test to see if it's working. We'll build a data model in Excel and publish it to a document library in the site collection where we enabled Power Pivot.

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

  1. 在 Excel 中,在 [資料] 索引標籤上選擇 [從其他來源],然後選擇 [從 SQL ServerIn Excel, on the Data tab, choose From Other Sources, 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 select the Add this data to the Data Model check box.

  5. 按一下 [ OK ]。Click OK.

  6. 在 [ Power Pivot ] 索引標籤上按一下 [管理]。On the Power Pivot tab, click Manage.

  7. Power Pivot ] 功能區中,按一下 [樞紐分析表]。In the Power Pivot ribbon, click PivotTable.

  8. 插入樞紐] 對話方塊中,選擇 [現有工作表] 選項,然後再按一下 [確定]On the Insert Pivot dialog box, choose the Existing Worksheet option, and then click 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.

一旦儲存該活頁簿的文件庫,即可存取 Power Pivot 設定值依序按一下 [省略符號 (...) 控制項兩次,然後選擇 [管理 Power Pivot 資料重新整理Once you've saved the workbook to the document library, you can access the Power Pivot settings by clicking the ellipsis (...) control twice, and then choosing Manage Power Pivot Data Refresh.

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

在 [管理資料重新整理] 頁面上選取 [啟用也重新整理盡] 核取方塊。此外,確定已選取 [使用系統管理員所設定的資料重新整理帳戶] 選項。(這是預設值)。On the Manage Data Refresh page, select the Enable and Also refresh as soon as possible check boxes. Also, ensure that the Use the data refresh account configured by the administrator option is selected. (This is the default.)

Power Pivot 資料重新整理設定的螢幕擷取畫面。

重複此好幾次時對您的資料進行一些變更。若所做的變更顯示在報告中瀏覽器中轉譯之後,則資料重新整理運作正常。Repeat this several times while making some changes to your data. If the changes show up in the report when it's rendered in the browser, then data refresh is working properly.