Monday, April 6, 2015

Create a PowerPivot Workbook for SharePoint

Create a PowerPivot Workbook for SharePoint

If you plan to publish a PowerPivot workbook to SharePoint, be sure to consider how it might be used in a collaborative environment. The workbook might become the basis for Reporting Services reports created by your colleagues, repurposed in other PowerPivot workbooks, or linked to from different sites, possibly in different farms. Knowing in advance that your published PowerPivot workbook might be used in new and different ways can help you make decisions about how you add, modify and present the data that you create in a PowerPivot workbook.
This topic contains requirements, design tips, and data sharing and security issues to consider when designing workbooks that are intended for publication to SharePoint.


If you are familiar with Excel, you already have the foundational skills for creating and using PowerPivot data. The best way to build on those skills is to learn about the expression language and the Excel data visualization features that allow you to present PowerPivot data in the most effective way.
Visualizing data   Although the PowerPivot workspace is tabular and has no data presentation features of its own, you can create sophisticated presentations reports by using data visualization objects in Excel such as PivotTables, PivotCharts, filters, and Slicers. If you are not familiar with the use of PivotTables or PivotCharts, the new capabilities in PowerPivot will give you a reason to learn. For more information about working with PivotTables, see Overview of Reports, Charts, and PivotTables
Adding and updating formulas    In PowerPivot, formulas are more than a way to calculate numbers; formulas created by using the Data Analysis Expressions (DAX) language let you build relational queries, look up related values, and develop custom calculations over time. DAX is an extension of the formula language in Excel, but is far more powerful. For more information about DAX, including samples, see Data Analysis Expressions (DAX) Overview.
Sharing your work   Saving a PowerPivot workbook to SharePoint 2010 gives you a way to share your work with others in your organization. When you publish your workbook to SharePoint, you can take advantage of the presentation features in PowerPivot Gallery. For more information about PowerPivot Gallery, see Use PowerPivot Workbooks on SharePoint.

Create Refreshable Data Connections

Data that you load into a workbook is static by default, but once published, you can arrange for automatic refresh of the data. Data refresh that runs on SharePoint uses the connection information in your workbook. While you can replace user name and passwords in a data refresh schedule, you cannot change the connection information in a workbook that is published to a server. If you want to change a data source connection in a workbook that you saved to SharePoint, you must download it to your workstation to the make that change in PowerPivot for Excel. For this reason, before you save a file to SharePoint, check the data source connection information to verify that the connection will work once data refresh is initiated from the server. Imported data that is kept on file shares or corporate servers that are generally available to a large group of users are most likely to have connection information that is specified in a way that will resolve equally well on SharePoint. In contrast, data that you import from a local file on your computer might not work if the server tries to use the same connection string to refresh data.
The credentials and connection strings that are used in data refresh are stored inside the PowerPivot workbook, one connection for each data source. This includes the file location or file name that you specify during data import, which is stored verbatim in the workbook and can be viewed by other users of the workbook. The following is a summary of how you can change different parts of the connection string:
  • Data source. Connection strings can be edited after you create the workbook, but the change must be made in PowerPivot for Excel, not on SharePoint. This change requires that the data in the workbook be refreshed and all formulas recalculated. Depending on the complexity of your workbook, this might take a long time. For more information, see Different Ways to Update Data in PowerPivot.
  • User name and password. You can override the credentials that are stored inside the workbook by specifying different ones when you set up data refresh on SharePoint, but doing so requires extra steps by your SharePoint administrator.

Best practices for setting up connection information

Although PowerPivot data is static by default after you import it into the PowerPivot window, you can enable regular updates from connected data sources by ensuring that the connections you define are reusable after the workbook is published to SharePoint.
For best results, follow these recommendations:
  • Use connected data sources wherever possible. Do not use data sources that are on private computers that might be shut off, or on servers that have restricted access, unless all the potential users also have access.
  • Specify data or file locations in a format that is accessible over a network connection. An HTTP URL or a shared folder (such as \\AWSRV-01\public\MarketData) are examples of locations that can be accessed from either a client workstation or a SharePoint server.

Securing PowerPivot Workbooks on SharePoint

Before you publish a PowerPivot workbook to SharePoint, review the data so that you fully understand what you are publishing. Whenever you publish a workbook, the columns and tables in the workbook will be available to all users, even if you create a different presentation for reports.
In other words, all data that you add to the PowerPivot window is accessible even if it is not visible. For example, filters that you use to exclude information in a PowerPivot table or PivotTable do not apply when you use that same PowerPivot data to create a Reporting Services report or to import data into a second workbook.
Remember that SharePoint permissions are set on documents, and not on information within a document. If you have a workbook that contains sensitive data, be sure that you publish the workbook to a library that is only accessible to authorized users.
For more information, see Secure a PowerPivot Workbook on SharePoint.

Source :

Wednesday, April 1, 2015

What is PowerPivot for SharePoint? Part 2.

What is PowerPivot for SharePoint? Part 2.

Now that we have a PowerPivot workbook in the Excel Rich Client, we are ready to publish that workbook to SharePoint.

In "What is Power Pivot for SharePoint? Part 1" I discussed the Data Model and how it is a database inside of Excel.  When you publish/upload a regular Excel workbook to SharePoint, that file gets chunked up into blobs and stored in the Content Database.  With a PowerPivot workbook the Excel workbook also gets stored in the Content Database, BUT the Data Model gets created and stored (after the initial (successful) refresh in browser) on the server running POWERPIVOT.

Note: In SharePoint 2010, the Analysis Service POWERPIVOT instance is going to be on a SharePoint server in the farm.  In SharePoint 2013, we recommend using a SQL Server off the farm.  Step by step install instructions here.

I am now going to touch on the three refreshes of PowerPivot 2013 and how they work.

1. Browser Refresh

When you open workbook and click a slicer, you are merely accessing the Data Model.  In other words, (in this example) the PowerPivot workbook stored in a SharePoint Report Library is going our to the SQL Server running PowerPivot > Databases > PowerPivot20Test20Workbook and is pulling whatever data (fresh or stale) that is currently in that cube.

If you login to SQL Management Studio > Analysis Services > Servername\POWERPIVOT > Databases you will see the Data Model (in this 

Troubleshooting:  I have seen the refresh in browser fail periodically.  This was caused by not having the correct ASOLEDB and ADOMD.Net drivers (located here: for SQL 2008 R2 SP1 & SQL 2012 SP1) on the SharePoint Servers running Excel Services and/or the Claims to Windows Token Service not running on these machines/lacking proper permissions.

2. Scheduled Data Refresh

In SharePoint 2010, running the Scheduled Data Refresh is the only way to get fresh data into the PowerPivot workbook (this is designed to run once daily during "after business hours").  A timer job runs, the Data Model is updated with fresh data and workbook is republished back to SharePoint.  When you open the workbook and click on a slicer, the Browser Refresh occurs a you will see data from the time the Scheduled Data Refresh ran (data from the time the Scheduled Data Refresh ran that night).  In 2013, you are not 100% dependent on the "Scheduled Data Refresh" (if your backend data source accepts Windows Credentials; SQL or Analysis Services) to see fresh data.  In 2013, you can use the "Interactive Data Refresh" which I will touch on next.  If you want to refresh from a data source that does not accept Windows Credentials (Oracle, IBM (in some cases) SQL, etc.)  you will need to use the Scheduled Data Refresh in both versions of SharePoint (2010 & 2013).


For this functionality to be present is SharePoint 2013, you need to install the PowerPivot.msi on a SharePoint server.
Troubleshooting: For detailed instructions on how to set this up please follow this article. For help pulling from a data source that does not accept Windows Credentials, please see our blog.

 3. Interactive Data Refresh

The "Interactive Data Refresh" is only available in SharePoint 2013.  The "Interactive Data Refresh" passes the SharePoint user's credentials all the way to the backend and pulls real-time data into a PowerPivot workbook.  To do this, you need to choose Data > Refresh All Connections


What is PowerPivot for SharePoint? Part 1.

What is PowerPivot for SharePoint? Part 1.

Part 1: PowerPivot for Excel

PowerPivot for SharePoint is nothing without the Excel Rich Client and the PowerPivot Add-in.  Pre-Excel 2013, the PowerPivot Add-in was a separate COM Add-in that you had to download.  In Excel 2013, the PowerPivot Add-in ships with the product.
Before you can do anything in SharePoint, you need to create a PowerPivot workbook in Excel.  To do this, open thePowerPivot tab choose Manage > From Database (choose your datasource, servername and database name).


You (potentially) will be pulling in a large amount of data into Excel.  I have seen people with Excel (PowerPivot) workbooks 1GB in size (which is huge since .xlsx (Open Office XML) files are zipped).  This large amount of data is essentially a database and you can see this in the workbook.  To view the Data Model, rename workbook file extension (.xlsx to .zip), open the workbook > xl > model >

After you have pulled this data (essentially a database) into the PowerPivot environment, Excel can point to this database and build a PivotTable off it.  In the PowerPivot environment choose PivotTable > PivotTable

Excel is now pointing at the database (Data Model) embedded inside itself.  You can see for yourself via Excel > Data Connections > Properties.  In SharePoint (when we get there), we will call this the "Refresh in Browser".

Keep in mind there is still the PowerPivot Connection that can also refresh.  In SharePoint, we will call this the "Scheduled Data Refresh".  You can view this connection in Excel > PowerPivot > Manage > Existing Connections (choose the PowerPivot Data Connection) > Edit > Advanced.

There is still one more refresh (in SharePoint 2013 & SQL 2012 SP1) called the "Interactive Data Refresh".  I will touch on that later in the "What is PowerPivot for SharePoint?  Part 2".
At this point, we should have a beautiful Excel (PowerPivot) workbook that has a Pivot Table and Slicers and looks something like the below sample.

Now that we have a beautiful Excel (PowerPivot) workbook that functions wonderfully in the Excel Rich Client, we are ready to publish this to SharePoint!

Optimising bulk inserts with Entity Framework 6

Optimising bulk inserts with Entity Framework 6 In Entity Framework 6+ the database context implements the  unit of work  pattern so th...