Configure and Use Stored Credentials for PowerPivot Data Refresh. PowerPivot data refresh jobs can run under any Windows user account as long as you create a target application in Secure Store Service to store the credentials you want to use. Similarly, if you want to provide a database login that varies from the one used to originally import the data in PowerPivot for Excel, you can map those credentials to a Secure Store Service target application, and then specify that target application in a data refresh schedule. After you follow the instructions in this topic, you will be able to use the following credentials option in the PowerPivot data refresh schedule page: This topic explains how to set up the user names and passwords that are used for PowerPivot data refresh in a SharePoint 2010 farm. Before you can use these steps, you must have enabled Secure Store Service and generated a master key.
For more information, see Enable and Configure PowerPivot Data Refresh (Analysis Services) This topic contains the following sections: Plan a PowerPivot Deployment in a SharePoint Farm. SharePoint farm topologies range from standalone servers to large multi-server installations. Once you understand the basics of adding PowerPivot to smaller deployments, you can apply the same concepts and techniques to more complex topologies. Adding PowerPivot to a Standalone Server To take advantage of this approach, you must have SharePoint 2010 installation media, with SharePoint installed but not yet configured on the computer. "Installed but not configured" refers to installing SharePoint, but stopping before configuration by not running the SharePoint Configuration Wizard that starts at the end of the installation process. Your choice of server for this type of deployment is important. Because there is only one server for all processing and hosting, you should use a higher end server.
By definition, you are installing only one PowerPivot for SharePoint instance, so the entire load will be on the one server. Adding PowerPivot to a Server Farm PowerPivot Scale-out Deployment. Install PowerPivot for SharePoint on an Existing SharePoint Server. Installation (PowerPivot for SharePoint) PowerPivot for SharePoint is a collection of server components that provide query processing and management control over PowerPivot workbooks that you publish to SharePoint.
Services include the Analysis Services engine and PowerPivot System Service. Analysis Services provides server-side processing for Excel workbooks that contain PowerPivot data. PowerPivot System Service works alongside Analysis Services, adding SharePoint integration, load balancing and connection management. PowerPivot for SharePoint extends Excel Services by pairing its large scale data processing capability with the data rendering services that Excel provides. To install PowerPivot for SharePoint, use the SQL Server 2012installation media. For instructions on advanced deployment scenarios, see Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint and Deployment Checklist: Scale-out by adding PowerPivot Servers to a farm. PowerPivot Walkthrough Using SharePoint Lists | David Lozzi's Blog. Before we can just walk through using PowerPivot and SharePoint, we need to make sure things are in order.
Some prerequisites PowerPivot has been installed and properly configured on your farm.Download PowerPivot for Excel 2010, SQL Server 2008 R2 version (SQL 2012 version WILL NOT WORK!) For free at I am using the Northwind sample data provided by Chris Beckett, you can download it here. If you don’t want to load up the Northwind sample, target a list or two of data you want to work with. First thing, open Excel and create a new workbook. You’ll get a nice empty window. If PowerPivot is installed on your farm correctly, you can simply go to your SharePoint list, click the List tab in the ribbon, then click the Export to Data Feed button.
This will prompt you to open or download a file. That will then send you to the Table Import Wizard in PowerPivot. You should get an empty window with a button Create. Like this: Management Features of PowerPivot for SharePoint. Thanks to John Hancock from the PowerPivot team for putting together this post. Previous articles have covered some of the features of PowerPivot for Excel, and shown how PowerPivot helps users build really powerful BI applications right in Excel. We have described some of the PowerPivot for SharePoint features on this blog as well as the PowerPivot blog, so for this article I will be focusing on the features that we provide to help IT to manage and understand the self-service BI activity. Self-Service Business Intelligence and IT The goal of PowerPivot for SharePoint is to allow people to share their PowerPivot workbooks in a secure, scalable and managed environment. The management experience for PowerPivot for SharePoint is integrated into SharePoint Central Administration, so it can be managed from the same place as other services like Excel Services.
Finding the Mission Critical PowerPivot Workbooks Understanding How Data Sources are Used. How to: Use Data Feeds (PowerPivot for SharePoint) Data feeds are one or more data streams that are generated from an online data source and streamed to a destination document or application. If you are using PowerPivot for Excel, data feeds can help you get existing corporate or business data from arbitrary data sources into the PowerPivot window in your Excel 2010 workbook.
After you import a data feed to a workbook, you can reference it later in any data refresh operations that you schedule on a SharePoint server. How you use a data feed depends on whether you are using built-in export features in applications that support Atom data feeds, or creating and using custom data services. Applications that are able to publish and read Atom XML data provide seamless data transfer that hides the mechanics of data feeds and data services from users. To a user, he or she is simply moving data from one application to another. This topic contains the following sections: Prerequisites Create a Data Feed from a SharePoint List. Plan for PowerPivot Integration with Reporting Services. Reporting Services can run on a broader range of SharePoint products and technologies than PowerPivot for SharePoint. In order to use the server products together in the same deployment, your SharePoint server or farm must be SharePoint Server 2010, with Excel Services, Secure Store Service, and Claims to Windows Token Service enabled.
PowerPivot data access on a server is supported only through SharePoint 2010 servers. This is the only server deployment model that is supported. The report server must be SQL Server 2008 R2 Reporting Services. This version includes the report data feed export feature that allows you to use report data in a PowerPivot workbook. The report server can be operating in native mode or in SharePoint integrated mode, but you will benefit from a more seamless integration if the report server is configured for SharePoint integrated mode. Reporting Services includes two applications for authoring reports.
Publishing to PowerPivot Gallery. How to: Share Data Feeds Using a Data Feed Library. A data feed is an XML data stream that is generated from a service or application that exposes data in the Atom wire format. Increasingly, it is used to transport data between applications and to client-side viewers. In a PowerPivot for SharePoint deployment, data feeds are used to populate a PowerPivot data source with data from an Atom-aware application or service. If you already use a combination of Atom-aware applications, you might never need to know how feeds are generated and consumed because the data transfer is seamless between the applications.
However, organizations that use custom solutions to publish Atom feeds often need a way to make feeds available to information workers. One way to do that is to create and share data service document (.atomsvc) files that provide connections to the online sources that produce the feeds. A special-purpose library, called a data feed library, supports creating and sharing data service documents in a SharePoint web application.
Activate PowerPivot Feature Integration for Site Collections. Activating PowerPivot feature integration for specific site collections is required if you used the Existing Farm installation option to install SQL Server PowerPivot for SharePoint. If you installed PowerPivot for SharePoint using the New Server option, you can skip this task because SQL Server Setup already activated PowerPivot feature integration for the root site collection when it configured your deployment. Feature activation at the site collection level is necessary to make application pages and templates available to your sites, including configuration pages for scheduled data refresh and application pages for PowerPivot Gallery and Data Feed libraries.
You must activate PowerPivot integration for each site collection that supports PowerPivot query processing. You must be a site collection administrator. On a SharePoint site, click Site Actions. By default, SharePoint web applications are accessed through port 80.