Using a Trace File to Troubleshoot Errors in PowerPivot

Overview:  How to produce a trace file for the IT or BI Support team to review when troubleshooting an error within PowerPivot running on an individual user machine.

Let’s say you’ve been working with PowerPivot for a while and suddenly an error starts to occur.  Perhaps this error has popped up when you try to refresh the data.  In the following screen shot, the error message states “We couldn’t refresh the connection.  Please go to existing connections and verify they connect to the file or server.”

     

In the above screen shot, the Work Item of “AutoSales” is the friendly name given in the PowerPivot model for the first table it’s trying to process – it’s not the actual name of the data connection.  So, the friendly table name may or may not be descriptive enough to infer what the actual data source is.  In this situation, you will probably be able to resolve this issue by going to Existing Connections on the Home tab > select the appropriate PowerPivot Data Connection and choose Edit > then Test Connection.  The test will almost certainly fail if the data refresh failed. 

     

The screen shot above which displays the Server and Database name is excellent information to provide to the IT/BI team for troubleshooting connectivity.

But…what if it’s not so simple?  What if you need more information to figure out what the workbook is actually doing?  Enter the trace file…

Creating a PowerPivot Trace File

PowerPivot has an option to create a trace (*.trc) file which will describe, in excruciating detail, each step that is happening behind the scenes.  In certain situations, having all of this detail this can be tremendously helpful.

To generate a trace file, go to the PowerPivot menu in the Excel window (not the PowerPivot window) > select the “Client tracing is enabled” checkbox.

     

This will enable tracing for this one workbook, while it remains open - i.e., for this session only.  This behavior is nice because you don’t have to remember to turn the tracing off; it terminates when the workbook is closed.

Finding Information in the Trace File

In our situation, we can quickly scroll down and spot the error in the trace file.  The descriptive message appears at the bottom of the window for each line.

     

The full error is a mouthful, but the description from the trace does give more info than the initial PowerPivot screen did:   OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 08001. A connection could not be made to the data source with the DataSourceID of 'b3c4d1db-0d27-477c-bff6-8a020d0841c4', Name of 'SqlServer SQLIMAGE2012 AutoSalesSourceDW'. An error occurred while processing table 'AutoSales'. The current operation was cancelled because another operation in the transaction failed.

If you scroll up to earlier entries in the trace file, we can find the actual query being executed against the source:

     

It also captures information such as how calculated measures are being created:

     

There’s can be a lot of volume to scroll through, but there is lots of excellent information captured by a trace file.  In some cases, sending the trace file to the IT/BI Support Team will provide enough details to figure out what’s gone wrong.

Reading a Trace File

An application which is able to read a *.trc file is required.  Common applications like Notepad or Excel cannot read all the contents of a trace file:

     

To read *.trc files on your machine, you’ll need the SQL Server Profiler which comes as part of a SQL Server installation.  If you are a business user who uses PowerPivot but not SQL Server, installing the Developer Edition of SQL Server is probably overkill, but it is an option if you don’t have IT support to help.

Finding More Information

PowerPivot-info-.com – How can I see what internal commands PowerPivot executes in its engine?

Denny Lee’s blog – Reading Your PowerPivot Profiler Trace

TechNet – PowerPivot Options & Diagnostics Box

Checklist for Knowing Your PowerPivot Model is Complete

Overview: This post includes a suggested list of items to be addressed by the data modeler before publishing a PowerPivot model for other users to consume.

If you are creating a PowerPivot model for others to use for reporting, then I firmly believe you have a responsibility. (Think Spiderman here just a wee bit.) This responsibility is to make sure the data model is tidy, easy to use, and useful for its intended purpose. Thus the inspiration for the following checklist of things to address before considering a PowerPivot model complete.

Tables and Relationships

	To Do	Where	Why
1	Import Minimum # of Fields	Design Ribbon > Table Properties	Importing only the fields that are needed for the data model not only simplifies the model, but it reduces the size which is important considering it’s all in memory. Sometimes you don’t always know all the specifics when the data is imported initially, so you may have to revisit this while finalizing the model to make sure you’re satisfied with what is and isn’t being imported.
2	Friendly Name for Tables	Design Ribbon > Table Properties or Right-Click Table (Worksheet) Name	A table name like “Students” is nicer to look at within a field list than something like “TblStdt.” It goes without saying that this name should represent the fields contained within it. If you know there’s a granularity issue (like it’s a snapshot set of numeric fields rather than at the detail level), that’s a great piece of information to make part of the table name.
3	Date Table	Design Ribbon > Mark As Date Table	By marking a date table, additional date-oriented filters and calculations are available. Most models will have a date table.
4	Relationships	Diagram View	Depending on the source of data, relationships may or may not be created when the model is initially set up. One of the most important things the data modeler needs to do is verify that all required relationships have been created and are accurate.

Fields

	To Do	Where	Why
5	Hide Fields	Data View > Right-click column > Hide from Client Tools or Diagram View > Right-click field > Hide from Client Tools	Hiding fields, such as ID fields needed to join tables but not useful for reporting, helps simplify the data model because there’s less fields shown in the field list. The consumers of your data model will appreciate the lack of what they’d view as clutter. One caveat: If a field is not needed for something – a relationship, basis for a calculation, something – then don’t import it at all (see #1 above). Second caveat: Hiding of fields isn’t a security mechanism; rather, it’s meant for ease of use and simplification. If you have conditions when you want to hide fields, consider using a Perspective instead.
6	Friendly Name for Fields	Data View > Right-click column > Rename or Diagram View > Right-click field > Rename	A field such as “Student Name” is nicer to look at for reporting than something like “Stdt_nm” which may be how it’s stored in the source database. Source systems often have abbreviated naming conventions which is not necessarily the ideal name for display on reports. Since field names are used by default as column titles, friendly names are well worth a bit of time investment. You can also use them for self-documenting the model.
7	Formatting of Fields	Home Ribbon > Data Type Home Ribbon > Format (for stored & calculated columns) Data View > Calculation Pane (for calculated measures)	It’s no fun to add a field onto a report that needs to be reformatted every single time. Defining units as whole numbers, or amounts as currency, is a helpful timesaver on the reporting end.
8	Sorting of Fields	Home Ribbon > Sort Home Ribbon > Sort By Column	Creating a default sort order is a common need for certain types of fields, such as dates. If needed, you have the flexibility to sort one column by another column if you need to - for example, you could sort the Month Name field by the Month Number field.
9	Aggregation Behavior	Advanced Ribbon > Summarize By	The aggregation default is sum, but this doesn’t suit all fields. Some fields are more appropriate to be set to choices such as count, min, or max. For example, high temperature per day would never be added together for a meaningful number; rather, its average is likely a better choice for aggregation. Setting this properly allows subtotals and totals to be presented properly.
10	Calculated Columns	Data View > Add Column	Creation of calculated columns (aka derived fields) is useful to enrich the data model when the stored fields aren’t sufficient. A very simple example of this is names – perhaps the underlying data source keeps First Name and Last Name in separate fields; you may wish to derive a Full Name field for reporting purposes which concatenates them together. Neither Power View nor Excel permits calculated columns on-the-fly, so having these set up in the data model helps usability significantly.
11	Calculated Measures	Data View > Calculation Area or Excel window > PowerPivot Ribbon > New Measure	Creation of calculated measures (aka explicit measures) is extremely useful to augment reporting and analysis. Calculated measures rely upon “context” so they may be calculated on aggregated data sliced a certain way versus a calculated column (from #10 above) that is calculated for every row regardless of context. A reporting tool like Power View doesn’t permit calculations on-the-fly, so having a good set of calculations available helps the usability factor tremendously. Try not go overboard with every possible calculation anyone could need, but start out with the most logical & useful calculations.

Power View Optimization

Within the PowerPivot model, there are several things which can significantly enhance the reporting within Power View. These settings don’t have an effect within Excel.

	To Do	Where	Why
12	Default Field Set	Advanced Ribbon > Default Field Set	Selection of the default fields, as well as the order they will be displayed initially, defines which fields will be automatically added to a Power View report when a table is added to a view.
13	Table Behavior	Advanced Ribbon > Table Behavior	Grouping behavior within Power View is affected by the unique values specified. Also, default labels and images used in a Power View (such as in a Card) may also be included.
14	Images	Advanced Ribbon > Table Behavior Advanced Ribbon > Image URL	For images to be displayed in Power View (whether they use a URL pointing to a SharePoint document library or are embedded within the PowerPivot model), several settings need to be specified.
15	Field Descriptions	Data View > Right-Click column > Description	A description can help users understand the contents of a field. When a description has been defined, it is shown in the Power View field list as a tooltip when the mouse hovers on the field name.

Other

	To Do	Where	Why
16	Hierarchies	Diagram View > Right-Click Field > Create Hierarchy Diagram View > Right-Click Field > Add to Hierarchy	Date fields (such as Year>Quarter>Month) and geography fields (such as Country>State>City) are great candidates for hierarchies. After a field has been added to a hierarchy, then it’s up to you whether the individual fields are still visible and available for reporting (sometimes users find having the individual fields & the hierarchy fields listed as confusing, so you may want to opt for showing one or the other for simplicity).
17	Key Performance Indicators	Data View > Calculation Area > Right-click calculated measure > Create KPI	Creation of KPIs can be a great enhancement to reporting because they facilitate visual indicators for how a value compares to its target (often seen as red/yellow/green). KPIs is often a really popular enhancement to a data model.
18	Perspectives	Advanced Ribbon > Perspectives	If multiple subject areas are contained within a PowerPivot model, creation of a Perspective may enhance the usability of the model because each Perspective contains a subset of the entire model. This solves the problem of wanting to hide Fields A, B and D when doing a certain type of reporting, yet show them in another situation.
19	File Name	File > Save As	When saving the file, you may wish to include the word “Model” in the name. For example, “Sales Forecast Model” lets users know that within this xlsx file is a PowerPivot model. This helps when viewing the PowerPivot Gallery SharePoint, which contains both models and reports.
20	Documentation	Excel worksheet or SharePoint metadata fields	Depending on the complexity level of the data model, and how well versed users are with the data, you might consider using an Excel worksheet as a place for documentation about the underlying model.

Testing and Validation

• Excel Data Visualization. Even if users will be developing their own independent Excel & Power View reports from your data model, they may find an Excel worksheet of data visualization to be very helpful to get started reporting on the underlying data model.
• Excel Testing. Test the data model using Excel as the reporting tool. Because the data model and Excel data visualization are linked together via the Excel file, it’s very easy to iteratively go back and forth between windows to finalize the data model.
• Power View Testing. Test the data model using Power View as the reporting tool (if you have SharePoint 2010/2013 and Power View available in your environment, or a business edition of Excel 2013).

The above list might look like a lot, but once you’re in a rhythm it’s really not bad. Many of the items above are cases where a little work upfront pays off over and over on the reporting end. And, you’ll be a hero among your coworkers if you make a little extra effort in your data models.

See anything I missed? Leave me a comment & I’ll be sure to add it!

Resolving the Error During Attempt to Connect to External Data Source for PowerPivot

When you initially launch an Excel file stored in the PowerPivot Gallery, you see the last saved state.  Upon interacting with a slicer, filter, or otherwise trying to refresh the data being presented, Excel will return an error if it cannot query the PowerPivot data source.  Specifically, the error I’m referring to is:

An error occurred during an attempt to establish a connection to the external data source.  The following connections failed to refresh:  PowerPivot Data

    

There’s a number of possibilities for this error, as your favorite search engine will tell you.  One common resolution:  start the SQL Server Browser service.  The ULS Log (usually found at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS) confirms this:

01/13/2013 15:01:06.03     w3wp.exe (0x1940)                           0x1B7C    PowerPivot Service                Background Jobs                   99    High        EXCEPTION: Microsoft.AnalysisServices.ConnectionException: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it

Services pane in Control Panel:

    

Making Sense of the “Power” and “Point” and “Pivot” Apps

I know I’m not the only one who stumbles over my words sometimes and says Power View when I meant to say PowerPivot, or maybe PowerPoint.  And, especially for those folks who don’t focus on BI full-time, the jargon can get a bit confusing.  Here’s a quick summary of the applications with “Power” or “Point” or “Pivot” in the name:

PowerPivot

PowerPivot is an in-memory data modeling tool which allows data analysts to combine (or mashup) different sources of data.  For example, perhaps the corporate BI environment has sales data, but the data analyst needs to perform an analysis of sales fluctuations based on weather – a tool like PowerPivot makes it pretty straightforward to extract the sales data and relate it to the weather data needed for the analysis.  This is, of course, assuming the data can be logically related.

PowerPivot is an add-in to Excel which is able to handle more volume of data than Excel normally does.  This is because it’s based on xVelocity column-store indexes which compress the data based on uniqueness of values in each column. 

The data in PowerPivot is embedded within the Excel workbook (i.e., the xlsx file).  This is convenient for the business user, but the reality is it does create multiple copies of the data.  These copies of the data have the potential for creating many versions of the truth – good processes & procedures for managing Self-Service BI tools such as PowerPivot mitigate the chaos that can ensue if not managed well.

More Info on PowerPivot:  PowerPivot

---

PowerPivot Gallery

The PowerPivot Gallery is a specialized document library in SharePoint which facilitates Self-Service BI.  It primarily stores PowerPivot models and Power View reports.  Because it’s Silverlight-based, this library displays thumbnail previews of what’s contained inside the file – this is really helpful for making sure it’s what you want before you open it.

When a PowerPivot model has been published to SharePoint, additional features are available such as scheduled data refreshes.  The SharePoint environment also facilitates other applications, besides Excel, using the PowerPivot model as a data source – tools like Power View, Report Builder, and PerformancePoint all support the creation of reports using data in PowerPivot. 

The PowerPivot Gallery can store whatever document types you allow it to, but it’ll only display the thumbnail for PowerPivot models and Power View reports. 

More Info on the PowerPivot Gallery:  TechNet

---

Power View

Yup, this one has a space in its name and the rest don’t.  Sigh.  Now that I got that off my chest…

Power View is a visual reporting tool which strives for interactivity and data exploration.  It’s a Self-Service BI tool, so the primary users will be business people who develop reports, dashboards, presentations, and data analysis projects.  This tool introduces some new features not previously available in the Microsoft BI stack such as cross-filtering, highlighting, and a playable time axis.  It’s considered a complementary tool to Report Builder.

As of now (late Nov. 2012), the data source for a Power View report must be an Analysis Services Tabular Model, or a PowerPivot Model.  Support for Analysis Services Multi-Dimensional Models is currently in CTP; an actual release data isn’t known to the public at this time.

With the combination of SQL Server 2012, Office 2010, and SharePoint 2010, Power View has a dependency on SharePoint as it’s a shared service running under the Reporting Services umbrella.  However, with SQL Server 2012 SP1, Office 2013, and SharePoint 2013, you get an additional choice & that’s to use Power View as an add-in to Excel.  This removes the absolute dependency on SharePoint, and allows you to choose if you want to initially generate the Power View report inside of Excel (in which case the Power View report is embedded as part of the .xlsx file), or generate the Power View report from within SharePoint (in which case it’s an independent .rdlx file).

More Info on Power View:  Power View

---

PerformancePoint

PerformancePoint is the Microsoft BI tool typically known for developing dashboards and scorecards.  This tool isn’t always called by its formal name; if you hear SharePoint Insights, it’s very possible that it’s PerformancePoint being referred to.

Although the Dashboard Designer tool can be given to a sophisticated business user, PerformancePoint is not considered a Self-Service BI tool.  One of the PerformancePoint strengths is its ability to define Key Performance Indicators (KPIs) within the Dashboard Designer – that leaves a big decision to the BI team as to whether KPIs should be defined within PerformancePoint, or within the Analysis Services cube where other reporting tools can also take advantage of the common definitions.

Although it can use relational and tabular data sources, PerformancePoint is really meant to use an Analysis Services database as its data source, so it can deliver all of the right-click interactivity and drill-down features.  Because it’s a service in SharePoint (as of the 2010 version), there is a SharePoint dependency to use PerformancePoint.

More Info on PerformancePoint:  SharePoint

---

PivotViewer

PivotViewer is a very visually-oriented feature of Silverlight. Its strength is the display of large sets of data known as collections – which could easily include thousands of images – and allowing the user of the report to interactively zoom in, zoom out, filter, regroup, reorganize and so on to interpret and find patterns in the data.  It associates the data itself to the images (ex: clickthrough data for advertising programs could display an image of the actual advertisement, or athletic statistics could display images of each player).

There is a PivotViewer Extension for Reporting Services available, although it’s not clear to me if it ever made it out of CTP2. 

More Info on PivotViewer: Microsoft PivotViewer

---

Pivot

Pivot is extremely similar to PivotViewer (above).  However, Pivot is not dependent upon Silverlight; rather, it’s built upon Internet Explorer.

Unlike PivotViewer, the Pivot tool is an unsupported tool still in the experimentation phase.  It’s currently released as a CTP from Microsoft Research; it was formerly released from LiveLabs.  The functionality is the same from what I’ve been able to tell, just without the Silverlight component.

More Info on Pivot:  Microsoft Research

---

Power Tools

Visual Studio has Power Tools available for download, which are intended to improve developer productivity.  Power Tools includes all kinds of utilities and goodies.

More Info on Power Tools:  Productivity Power Tools  |  Team Foundation Server Power Tools

---

PowerPoint

Good old PowerPoint.  It, of course, is the tool we all know and love for developing slides to accompany presentations. It’s part of the Office suite of products, and has been around over 20 years now.  And it’s here in this list because sometimes this comes out of my mouth when I actually meant to say PowerPivot!

Power View (above) actually has a design environment a bit similar to PowerPoint – both are considered “presentation ready” because you don’t have to worry about setting page sizes, margins, and so on. Also, Power View is able to export its “views” to PowerPoint “slides.” These exported views even retain their interactivity if you’re still connected to the company server.

More Info on PowerPoint: Office PowerPoint 2010

PowerPivot Data Refresh Options

Overview:  Discussion of the Data Refresh options available in SharePoint for updating data contained in PowerPivot.  If row-level security needs to be applied to the source data being extracted out of a source system into PowerPivot, then Option 1 won’t work.

Level:  101

Where Data Refresh Options Are Located

In the world of Self-Service BI, the producer of the data model (aka power user, author, workbook owner, data modeler) is who typically sets up and schedules the timing of data refreshes based on the business need for updated data. However, that doesn’t mean that the administrator of the system is completely uninvolved!

Data Refreshes are managed within the PowerPivot Gallery (a Silverlight-enabled SharePoint document library).  When a PowerPivot model has been uploaded to SharePoint, there are 3 icons visible in the PowerPivot Gallery.  The icon on the far right is used to “Manage Data Refresh.”  Utilizing SharePoint is the way to automate scheduled data refreshes for PowerPivot models.  This does require a special “PowerPivot for SharePoint” environment to be set up – a normal SharePoint document library won’t do the trick.

    

Data Refresh Options

Following are all of the options available in the “Manage Data Refresh” screen.  The middle screen shot – Credentials – is the focus of the following Option 1, 2, 3 sections.

    

    

    

Credentials Used to Refresh Data

For the refresh Credentials, there are 3 options to choose from.  Each option influences:

• What account is executing the process on the server?
• What account is being used to query the underlying data source? <—this affects how row level security is handled when the data is extracted

---

Option 1: Use the data refresh account configured by the administrator

    

Process executed by: 

• The PowerPivot Unattended Account (predefined for the system by the administrator). 

Data queried by: 

• Retains the credentials in place when the model was uploaded to SharePoint, as follows:
• If Windows Authentication was used to initially populate the PowerPivot model:  It will continue to use Windows Authentication, in which case the PowerPivot Unattended Account is considered the “current user.”
• If a specific User ID and Password (SQL Server authentication, for example) was used to initially populate the PowerPivot model:  It will continue to use that User ID and Password.

Pros: 

• Simple for workbook owner to complete. 
• No “special” User ID and Password needs to be shared with the workbook owner.
• Should be few concerns with password changes, terminations & transfers, which simplifies some maintainability.

Cons: 

• Not suitable when row level security is implemented on the source system being queried.  Since there can be only one PowerPivot Unattended Account per service application, to satisfy all possible data refresh scenarios for all PowerPivot workbooks, the PowerPivot Unattended Account may have broad or unrestricted permissions to the source data.  This may be far more liberal security than the original workbook author had & inadvertently result in the data refresh populating the workbook with too much data.

Prerequisites:

• The PowerPivot Unattended Account needs read rights to all possible data sources used in all PowerPivot data refresh operations.
• This user is also required to have read/write (Contribute) permissions to the PowerPivot workbook itself (preferably via permissions at the library level).

Disabling this option:

• Option #1 can be disabled by the system administrator, in which case it would be greyed out for the workbook owner when defining the data refresh schedule. The option to disable it looks like this in Central Administration (PowerPivot Service Application Settings):

    

---

Option 2: Connect using the following Windows user credentials

    

Process executed by: 

• The Windows user specified in the User Name field. 

Data queried by: 

• The Windows user specified in the User Name field. This will override the credentials that were used when the model was uploaded to SharePoint.

Pros: 

• If the workbook owner puts in their own credentials here, the likelihood of complications with source systems is minimal - i.e., the workbook owner already has read permissions to the source system.
• Row level security on the source data can be retained (limited to the Windows ID specified – the assumption is made that the workbook is only being shared with users who have the same permissions to see the data).
• The system administrator could provide workbook authors with a set of credentials to use that do not expire & does not have password changes.

Cons:

• It can be challenging to manage terminations, transfers, and password changes when individual user accounts are used.
• Each time the User Password is required to be changed (for example, every 90 days), the workbook owner must go into the Data Refresh Schedule for each PowerPivot model they own and re-key the new password.

Prerequisites:

• The Windows user specified needs read rights to the data source.
• The Windows user specified is required to have read/write (Contribute) permissions to the PowerPivot workbook itself.

Disabling this option: 

• Option #2 can be disabled by the system administrator, in which case it would be greyed out for the workbook owner when defining the data refresh schedule.  The option to disable it looks like this in Central Administration (PowerPivot Service Application Settings):

    

---

Option 3: Connect using the credentials saved in Secure Store Service

    

Process executed by: 

• The user defined by the system administrator that corresponds to the ID provided in the field.  This is similar to Option 1 above, except that Option 3 is not limited to just one account per PowerPivot service application.

Data queried by: 

• The user defined by the system administrator that corresponds to the ID provided in the field.  This will override the credentials that were used when the model was uploaded to SharePoint.

Pros:

• Should be very few or no concerns with password changes, terminations & transfers, which simplifies some maintainability.
• All that is shared with the workbook owner is a Secure Store Service ID (such as 4672) rather than an actual User Name and Password.
• Row level security on the source data can be retained (limited to the ID specified – the assumption is made that the workbook is only being shared with users who have the same permissions to see the data).

Cons:

• Which ID that’s shared with which set of users needs to be managed very carefully (i.e., if different IDs have varying levels of source system permissions).
• The permissions assigned to the Secure Store Service ID may be far more liberal than the original workbook author had & inadvertently result in the data refresh populating the workbook with too much data.  This can be mitigated by creating and sharing IDs appropriately.

Prerequisites:

• This user needs read rights to the data source.
• This user is also required to have read/write (Contribute) permissions to the PowerPivot workbook itself.
• The ID used cannot relate to the PowerPivot Unattended Account (i.e., the account used in Option 1).

---

Managing Data Refresh Failures

The lower section of the PowerPivot Management Dashboard (available only to administrators in SharePoint) displays recent data refresh failures.  This data is also available to be queried in the Management Data Sandbox.  Possible reasons for failure might be:

• Read permissions to the data source specified do not exist for the user specified in the Data Refresh options
• Contribute permissions to the PowerPivot workbook are not defined for the user specified (because the data refresh operation updates the PowerPivot file)
• Source system is offline at the time the data refresh attempted to execute
• Data source must exist on a server which can be accessed by the data refresh process (i.e., not on a user machine)
• An individual account was used and the employee’s password expired or was changed
• An individual account was used and the individual terminated employment upon which time their source system permissions were revoked
• An individual account was used and the employee transferred to another department upon which time their source system permissions were revoked
• If data refresh would force the workbook to exceed 2GB in size
• If a custom driver or data provider is required to query a particular source system which hasn’t been installed on the PowerPivot server
• If a 32-bit data provider was used when the workbook was set up, but the server has the 64-bit version
• The workbook is checked out or is being actively edited
• The workbook uploaded may be from an older version of PowerPivot than what is running on the server
• Workbook author chose “After business hours” to refresh their workbook, but the system has no valid business hours defined (technically you can set the same start & end time to achieve no business hours, but that causes failures if users select “After business hours” in their refresh schedule – so setting a very small window is better)
• An individual account is from another domain that isn’t trusted

When Data Refreshes are Executed

Although the Data Refresh options are set up by the workbook owner, the time the data refresh is executed is not entirely within their control.  The time a data refresh runs depends upon these things:

• Earliest Start Time defined for the individual PowerPivot model. This is set up by the workbook owner after the PowerPivot model is uploaded to SharePoint.
• Business Hours defined for the PowerPivot application.  This is set up by the administrator within the PowerPivot options in Central Administration.  These hours represent when priority is given to querying versus refreshing data. If you don’t really want to define business hours (perhaps you have an international business), then set this window to be extremely small.
• How often the PowerPivot Refresh Timer Job is scheduled to run. This is managed by the administrator within the Timer Job Definitions in Central Administration.
• How many refreshes are permitted to run concurrently.  This is managed by the administrator within the Analysis Services (PowerPivot) Service Settings in Central Administration.
• Sufficient system resources are available to execute the job (for example, if the system is being taxed with a lot of queries, the data refresh process may need to wait).

Termination of a Data Refresh Schedule

By default, SharePoint will stop trying to execute Data Refreshes when one of the two happens:

• Consecutive Failures.  Ten data refreshes in a row have failed, or
• Inactive Workbooks.  No one has queried the PowerPivot model for ten data refresh cycles

The administrator may change these settings in the PowerPivot options in Central Administration.  You may want to consider changing the Inactive Workbooks setting – there are some workbooks that are only used minimally, but it’s still important for them to have current data when they are used.

    

Finding More Information

TechNet – Different Ways to Update Data in PowerPivot

TechNet – PowerPivot Data Refresh

MSDN – Everything You Always Wanted to Know About PowerPivot Data Refresh but Were Afraid to Ask

Analysis Services & PowerPivot Blog – PowerPivot Data Refresh

Kasper de Jonge’s Blog – PowerPivot Data Refresh, what settings to use and why

TechNet – Troubleshooting PowerPivot Data Refresh

Comparison of Direct URL, BISM, and RSDS Data Connections for a Power View Report

Overview:  Discussion of the 3 possibilities for connecting a Power View report to a PowerPivot model (aka a “Workbook as a Data Source”).  This entry lists the differences in URL formatting, authentication types, and reusability.

Data Sources Supported by Power View

In the initial SQL Server 2012 release, a Power View data source must be “tabular.” This qualifies as either:

• A PowerPivot Workbook         <—the focus of this blog entry
• A SQL Server Analysis Services Tabular Model

Power View passes DAX (Data Analysis Expressions) to the data source, so a traditional SSAS Multidimensional (OLAP) database is not currently supported.

The following 3 methods may be used to connect Power View to a PowerPivot model:

1. Direct URL
2. BISM connection
3. RSDS connection

The PowerPivot workbook must be uploaded to SharePoint in order to create a Power View report on it. A workbook stored outside of SharePoint (known as PowerPivot for Excel rather than PowerPivot for SharePoint) cannot support other Microsoft BI tools connecting to it (although some 3rd party tools can handle this scenario).

1. Direct URL Embedded in Power View Report

Within a PowerPivot Gallery, a shared data connection is not required for Power View to connect to a PowerPivot model. This is a very common type of connection, as it’s what is created when using the “Create Power View Report” icon.

Connection string:

Data Source=”http://<SharePointSite>/<Library Name>/<PowerPivotModelName>.xlsx”;

How to create:

From within the PowerPivot Gallery, find the PowerPivot model you wish to use as a data source. Click the icon at the top right called “Create Power View Report.” This creates a blank Power View report with the data connection set to the PowerPivot model you started from:

    

When finished, save the Power View report. By default, SharePoint will save it in the same PowerPivot Gallery as the workbook data source.

Viewing the connection properties:

To see the data connection properties of your Power View report, change the view to the “All Documents” view:

    

Then, locate your Power View report & select Manage Data Sources from its drop-down menu:

    

Click EntityDataSource in the next window:

    

Notice the connection string is just a URL to the xlsx file (Excel 2010 / PowerPivot workbook).

    

Type of Connection:

Custom (non-shared). This technique creates a “Custom data source” which is a report-specific data source; this means if the name or path of the PowerPivot model changes, each individual report which references the PowerPivot model will need to be updated. This additional potential maintenance is in exchange for the ease in which a Self-Service BI user can create a new Power View report from a PowerPivot model.

Authentication Types:

You can use stored credentials or prompt for credentials, in addition to the default of Windows authentication. You’d have to go to Manage Data Sources after it’s set up in order to change it from the default.

Availability of Data Connection to Other BI Tools:

Excel can use a workbook URL to connect (Data > From Other Sources > From Analysis Services > enter the URL of the XLSX workbook as the Server Name).

PerformancePoint can use a workbook URL to connect (as an Analysis Services data source).

2. BI Semantic Model (BISM) Connection to a PowerPivot Workbook

Connection string:

http://<SharePointSite>/<Library Name>/<PowerPivotModelName>.xlsx

How to create:

Go to the library where you wish to create the connection (this might be your PowerPivot Gallery, or it might be a separate Data Connections Library). Select New Document > BI Semantic Model Connection:

    

After giving it a name, enter the URL of the PowerPivot workbook. In the example below, the workbook URL is http://intranet.contoso.com/SelfService BI/Book Sales PowerPivot Model.xlsx.

    

This URL is the same one as used in option 1 above; however, it doesn’t have the quotes around it, nor the semicolon at the end. Also, the database name is unnecessary (we’d use that if we were connecting to an SSAS Tabular model instead).

Type of Connection:

Shared. (Centralized maintenance should the original file move or be renamed.)

Authentication Types:

With a BISM connection you cannot use stored credentials or prompt for credentials. Only Windows authentication is supported with a BISM data connection.

Using the Connection:

To create a new Power View report from the BISM connection, simply locate the connection & select “Create Power View Report” from the drop-down menu:

    

Availability of Data Connection to Other BI Tools:

Excel can use a BISM data connection (Data > From Other Sources > From Analysis Services > enter the URL of the BISM data connection as the Server Name).

3. Report Data Source (RSDS) Connection to a PowerPivot Workbook

Connection string:

Data Source=”http://<SharePointSite>/<Library Name>/<PowerPivotModelName>.xlsx”;

How to create:

Go to the library where you wish to create the connection (this might be your PowerPivot Gallery, or it might be a separate Data Connections Library). Select New Document > Report Data Source:

    

After giving it a name, under Data Source Type choose “Microsoft BI Sematic Model for Power View” from the drop-down list:

    

In the connection string, use the URL. Contrary to the BISM connection, an RSDS connection is formatted with the double quotes and semicolon at the end.

    

Type of Connection:

Shared. (Centralized maintenance should the original file move or be renamed.)

Authentication Types:

With a Report Data Connection you can indeed use stored credentials or prompt for credentials, in addition to the default of Windows authentication. This gives the RSDS connection type an advantage over the BISM connection type.

Using the Connection:

To create a new Power View report from the RSDS connection, simply locate the connection & select “Create Power View Report” from the drop-down menu:

    

Availability of Data Connection to Other BI Tools:

Report Builder can use a Report Data Source (RSDS) as its data connection.

Excel can use a BISM data connection (Data > From Other Sources > From Analysis Services > enter the URL of the BISM data connection as the Server Name).

A Few Thoughts About Where to Store Shared Data Sources

Self-Service BI has gotten me to rethink the way some things are done, one of which is where shared data sources should be stored. Traditionally I would have created a separate Data Connections library. However, if a Self-Service BI user is working in a PowerPivot gallery, it would be more user-friendly to have the shared data sources available right there. I’m still not sure how much I like combining reports & data sources in the same library – although it’s convenient, it can get a bit messy. Good naming conventions can certainly help manage users knowing what is what (albeit difficult to enforce in a self-service environment).  There’s also a good possibility that you’ll have multiple PowerPivot galleries, organized & secured by subject area – which means you’d have duplicate shared data connections in each PowerPivot Gallery.  Where to store your shared data sources is a decision point when you are planning out your SharePoint document libraries for reporting.

A Few Words About Content Types

Not all data connection types are set up by default when a new library is created. Chances are you’ll have to manually add the following content types:

• BI Semantic Model Connection (BISM)
• Report Data Source (RSDS)
• Data Service Document (for data feeds, if desired to house them here instead of a separate Data Feeds library)

    

Finding More Information

MSDN – Create a Shared Data Source for a Data Model (SSRS)