Search
Twitter

Entries in Power Pivot (14)

Thursday
Nov072013

How Often are Thumbnail Images Refreshed in the Power Pivot Gallery?

Overview:  A brief discussion of how and when the thumbnail preview images in a Power Pivot Gallery in SharePoint get refreshed.

The Power Pivot Gallery is a specialized document library in SharePoint which utilizes Silverlight to render preview images of the report.  These previews are very helpful for users to quickly see if they have the correct report selected before executing it.

image

Ways to Refresh Thumbnail Images

Originally I had thought there would be a timer job which refreshes the thumbnails at a regular interval.  However, that is not the case.  There are 3 ways I’ve found to get thumbnails refreshed:

1.  Upload a new workbook.  The act of uploading a new workbook causes an event handler which will populate the thumbnail image for that workbook.

2.  Modify a workbook. The act of saving an existing workbook causes an event handler which will update the thumbnail image for that workbook.  Even if all you do is Edit Properties and then Save, that’s enough.

3.  Manually execute GallerySnapshot.exe.  This is a Windows service that runs on the app server where Excel Services is installed.  This exe gets called automatically when a file in the Power Pivot Gallery has been added or changed (an itemAdded or itemUpdated event, respectively, as mentioned in #1 and #2 above).  To run it manually, refer to this information:  http://blogs.msdn.com/b/mtn/archive/2010/10/15/how-to-manually-refresh-powerpivot-gallery-thumbnails.aspx.  Note that this service was called GetSnapshot.exe in SharePoint 2010, and has been renamed to GallerySnapshot.exe in SharePoint 2013.  The SharePoint 2013 GallerySnapshot.exe can be found at:  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN.

The takeaway:  Since the thumbnails only get regenerated in the above circumstances (and not at a regular interval), the preview images shouldn’t be thought of as something that is intended to coincide exactly with data updates.

Finding More Information

Technet – Refresh a Thumbnail Images

MSDN – Refreshing Power Pivot Gallery Thumbnails

Power Pivot Geek – General Problems with Gallery Snapshots Not Being Taken

 

Sunday
Jul212013

Why “Power BI” is a Big Deal

A preview of Power BI will be available later this summer.  Here’s a recap of what I’ve learned so far + why I think this is a very big deal for the world of Microsoft Business Intelligence.

Excel is Primary Tool for Self-Service BI

There are four components to Power BI, and all are delivered via Excel.  Microsoft is capitalizing on the fact that a huge percentage of business users are most comfortable in an Excel environment.  Historically a large portion of Microsoft BI was developed with SQL Server and SharePoint tools – i.e., software predominantly available to IT and BI professionals.

The newest Excel capabilities lower the entry point of BI to anyone who has Excel and an interest in working with data.  SQL Server and SharePoint will continue to be my professional-level tools of choice for creating and delivering Corporate BI solutions.  Meanwhile Excel has emerged as the primary mechanism for creation and delivery of Self-Service BI solutions.  This continues to build upon the Personal BI > Team BI > Corporate BI framework promoted by Microsoft using a combination of Microsoft Office that’s installed on the desktop, Office 365 in the cloud, SQL Server and SharePoint.

Four Components to Power BI

Two of the Power BI components are for data preparation:  Power Pivot and Power Query.  The other two are for data visualization:  Power View and Power Map. 

  • Power Pivot.  Imports and integrates data from various sources for the purpose of creating an in-memory data model.  This allows a functional user to combine various data sources which improves its value – classic examples here are the addition of weather data or demographics data to corporate sales data.  The Power Pivot data model also supports calculations, derived fields, aggregates, hierarchies and key performance indicators.  The ability to create a data model with a relatively low learning curve is a very big deal for data analysts, particularly for one-time analysis type of situations.  It can also be helpful to IT and BI folks for prototyping.
  • Power Query.  Formerly Codename Data Explorer, this tool will be able to search for data (just like a web search experience) online or within your corporate data sources.  The selected data set is immediately imported into an Excel table – this search feature alone is a game-changer.  Some of the native data connectivity it offers are truly distinctive and make data access far easier than it’s been in the past.  It also allows you to do things like merge data, rename columns, replace values and other data modifications in a step-by-step process.  A very big deal here is that the step-by-step data cleansing and shaping process is savable and therefore repeatable.  You can also view the state of the data before and after each step.  Optionally, you can bring the data into your Power Pivot model to continue working with the data even further.
  • Power View.  Power View is an interactive data visualization tool.  It does certain things like highlighting and cross-filtering really well with very little learning curve.  Working with data in Power View bears similarities to working with Excel Pivot Tables as well as to PowerPoint.  In addition to charts, graphs, and table data, Power View also supports maps which pan and zoom via integration with Bing Maps.  The naturally interactive behavior of Power View is what makes it a very big deal.
  • Power Map.  Formerly Codename GeoFlow, Power Map is a 3D mapping tool.  What makes it unique is the ability to save scenes and play them over time in a tour.

Cloud-Based Self-Service BI

Power BI will be delivered in Office 365 (which actually uses SharePoint Online).  Although the preview that starts later this summer will use Office 365, all 4 Power BI tools are available to use as Excel add-ins now (some functionality is not fully supported within on-premises SharePoint).  Considering Microsoft has a cloud-first strategy, it’s not a big surprise that the initial preview will be with Office 365.  It’s unclear currently if all Power BI functionality will be available to on-premises SharePoint sites following the initial cloud-based rollout.  I hope so.

This focus on the cloud is a big deal, especially for small and medium-sized businesses.  It can also be a good thing for business units that need self-service capabilities but don’t have IT support or resources to fire up the required infrastructure.  Even large corporate environments can find some benefits to augmenting their on-premises BI environment with the cloud – especially if they want to upgrade to newer versions in the cloud faster than they can upgrade on-premise versions (i.e., a company-wide upgrade to Office 2013 can be a big task).  Creating the SharePoint and SQL Server server infrastructure to support Excel Services, PowerPivot for SharePoint, Reporting Services, and Analysis Services is not an easy task.  Not having to invest so much time, effort, and cost into setting up this infrastructure will be welcomed by many organizations.  However, there are certainly some obstacles which prevent some companies from using Excel in Office 365 even if they wanted to.  It will be interesting to see to what extent companies embrace the cloud, or utilize a hybrid approach to on-premise and cloud BI.

Mobile Support

Quite obviously mobile support is a big deal … Microsoft has announced native mobile applications for Windows 8, Windows RT, and the iPad.  HTML5 will be used for browser-based mobile delivery on other types of devices.  The way I understand it, to see a report on a mobile device it’ll need to be published to a Power BI workspace in O365, and in the properties of the report you’d set it as a “Mobile Favorite.”  Translation:  companies with Excel and Power View in SharePoint now won’t get to take advantage of this mobile functionality right away.

     

     Source for Image:  Power BI Demo by Amir Netz available here:  https://www.youtube.com/watch?v=Jsa-5LGx_IY

My understanding is Silverlight won’t be immediately removed from the stand-alone Power View application in or the SharePoint PowerPivot Gallery, but I’m guessing that’ll come in time.

Data Search and Refresh

Office 365 will utilize collaborative BI workspaces optimized for BI functionality.  Users will be able to publish Excel workbooks to workspaces and securely share them with coworkers.  Reusable data connections can be published, and data refreshes can be handled on-demand or on a schedule.  Data refreshes can access corporate data on-premises as well as data online (with proper security credentials of course).  Being able to search and access your enterprise data is a very big deal (this sounds like something the IT folks will need to facilitate for self-service users to take advantage of).

Queries created from Power Query will be able to be searched and shared among users.  This sharing of queries is a big deal – anything that promotes reusability & consistency gets a big thumbs-up from me.

    image 

     Source for Both Images:  Power BI Demo by Amir Netz available here:  https://www.youtube.com/watch?v=Jsa-5LGx_IY

Tracking of Data Usage

A new feature I’m particularly excited about is the tracking of data usage.  Stats re: how often data sources & reports are accessed will be readily accessible to those who build models and reports.  This is currently a feature of Central Administration in SharePoint, or by querying the Power Pivot Management Data tabular model if you don’t want to grant Central Admin access to a non-SharePoint administrator.  I look forward to seeing how easily this information can be reviewed for one report, one data source, or for many reports and data sources.

Easy access to these stats is a big deal because if you see usage going way up, then there’s an awareness that this particular report or data source has become critical.  This might mean a data refresh failure is of greater urgency, or data quality is of even greater concern.  Let’s say it’s a Power Pivot model that’s become critical and it continues to grow larger and more complex – that might be a perfect scenario to upgrade the self-service Power Pivot model to a corporate Tabular model in Analysis Services.

Data Stewardship

Lastly, a big component of Power BI is the concept of data stewardship.  There’s not a lot of public information about this available yet.  This will include management of security, data refresh permissions, tracking data usage, as well as a few other things that we’ll learn more about when the preview is available.  I’ve typically thought of data stewardship with the mindset of data quality and/or master data management, so I’m extremely interested in seeing the direction Microsoft will take with this.  Regardless of how you define it, data stewardship is the kind of thing that can really make a self-service BI initiative more successful and/or less problematic – so I think this concept of data stewardship will end up being a very big deal.

Finding More Information

Register for the “Microsoft Power BI for Office 365” preview – Office Site

Chris Webb’s Blog – Some Thoughts About Power BI

Jen Underwood’s Blog – Microsoft Office 365 Cloud Power BI

Jason Thomas’ Blog – Power BI for Office 365

Jamie Thomson’s Blog – Thoughts on Power BI for Office 365

John White’s Blog – Power BI-What Is It?

 

Wednesday
Jun052013

Integrating Data from Data Explorer into PowerPivot

Overview:  A few thoughts about using Data Explorer with and without the Excel Data Model (i.e., PowerPivot).

My first reaction when I heard about Data Explorer being released in preview mode as an add-in to Excel was “Why isn’t it part of PowerPivot?” Now that I’ve thought about it further, it’s occurred to me that its independence means you can use Data Explorer with or without PowerPivot.

Before we illustrate that further, let’s pull a bit of data into Data Explorer. One nice piece of functionality is that when you hover on the items in the Online Search pane on the right, the left side populates with a preview of the data immediately – this is nice to give you an idea if the data will be useful before you bother downloading it. Using the Online Search button on the Data Explorer ribbon, I’ve done a search for “Charlotte North Carolina” (just for grins) which is shown as follows:

     clip_image002

Note that with this initial preview of Data Explorer, the only website being searched is Wikipedia. Options here will be enhanced over time.  There’s numerous other ways to get external data as well (some of which are just really super cool, but that’s for another time…).

Using Data Explorer to Import Data to an Excel Table

The settings we are concerned with are in the Query Settings pane, shown as follows:

     image

The default option, as shown above, is “Load to worksheet.” This is apparent because its slider is set to On.  Also, by observing the Table Tools > Design ribbon we can see that the data downloaded went to an Excel table named Table2.

Note that this isn’t a Pivot Table – it’s an Excel Table applied to a range of cells.

Using Data Explorer to Import Data to a PowerPivot Model

Conversely, if you wish to import your data into the Excel Data Model (i.e., PowerPivot), then you want to select the “Load to data model” option on the Query Settings pane. After the data has been uploaded to the PowerPivot model, it will change your Query Settings pane as follows:

     clip_image006

If you don’t see the Query Settings Pane, just click anywhere in the table & the pane should pop up. If it doesn’t, check the Table Tools > Query ribbon & select the “Show Query Settings” button.

Tip: if you need to modify the data and/or clean it up, you might want to handle this before you load it into the data model.

Just What is the Data Model Anyway?

If you are familiar with PowerPivot, then you are already familiar with the data model.  I’d imagine the Excel team is going for ease of use & understandability with the data model terminology.

You can import data into the data model by using the PowerPivot add-in.  Alternatively, a range of data can be added to the data model.  The following screen shot shows a small range of cells I have highlighted & then selected “Format as Table” from the Home ribbon:

     image

Next you are presented with a dialog box which confirms whether or not the first row is a header:

     image

Now we have some options on the Table Tools > Design ribbon.  If you choose “Summarize with PivotTable” you can interact with this range of data within the PivotTable field list. 

     image

Creating a PivotTable from a range of data is very cool, but not the reason I’m mentioning all this…note at the bottom of the dialog box there’s an option for you to “Add this data to the Data Model.”  If you choose to add the range of data to the data model, you can then launch your PowerPivot window and see it there represented as a table.

     image

This opens up a lot of possibilities considering you can bring data from a range of cells like this, and/or you can get external data from PowerPivot, and/or you can get external data from Data Explorer –> and then choose whether or not to integrate it with PowerPivot.  That really is a lot of flexibility.

Deciding Where to Store the Data

The way I see it, you could keep the data in an Excel table if a tabular result set (i.e., the output from Data Explorer) is really all you need.  You can even use a PivotTable with that Table range of data, which is very cool.

Here’s some reasons you might want to load the data obtained via Data Explorer into the data model (i.e., PowerPivot):

  • You have an existing PowerPivot data model, or other data sources, you wish to integrate this data with (although there is Merge functionality in Data Explorer that could work too).
  • You want to utilize other PowerPivot functionality such as Key Performance Indicators or Hierarchies.
  • You want to use the map feature of Power View (requires the geographic data to have a certain data category).
  • You want to change the default aggregation type from Sum to something else like Avg or Count.
  • You are more comfortable creating DAX calculations in PowerPivot than using the Data Explorer Formula Language (called “M”).
  • You wish to compress the data significantly.
  • You have a large set of data which will perform better using the xVelocity engine.

There’s times that Excel will implicitly create a data model for you, such as:

  • If you’re importing 2 or more tables at once
  • If you’ve inserted a Power View worksheet using the Table range of cells
  • If you've inserted a Power Map (its functionality requires a data model)

The implicit creation of the data model had me a bit stumped at first, but Excel is just being helpful.  Folks without the PowerPivot add-in enabled won't even know when the implicit conversion to the data model happens.

Happy hunting for data!

Finding More Information

Office – Create a Data Model in Excel

Office – Microsoft “Data Explorer” Preview for Excel Help

 

Sunday
Mar242013

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.”

      image

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. 

      image

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.

      image

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.

      image

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:

      image

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

      image

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:

      image

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

 

Sunday
Feb102013

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!

 

Sunday
Jan132013

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

     image

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:

     image