I was recently having a debate with an individual about whether you can use a SQL Server stored procedure as a source of data in PowerPivot, or in a Business Intelligence Semantic Model. The answer, is “yes, you can”. However, you may encounter an error along the way. Let’s try this example, using the stored procedure below:

Now try to reload your data in PowerPivot with a refresh, and the following error will present itself: We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server. Okay, so now we have an error…not very intuitive as to how to fix it. The stored procedure used to run fine, and if you run it in SSMS it runs fine. The problem here is that we need to modify the stored procedure to SET NOCOUNT ON. So let’s try it again.

Reload the data in PowerPivot again using refresh – SUCCESS! While this may not be presented with an error that points you towards a resolution, using a stored procedure to load data in to PowerPivot does work when you have complex logic.

I just installed Office 2013 – where did PowerPivot go? Well, it isn’t a download that you need to find and install now. Rather, it is an Add-In that you need to enable.

Open Excel

File -> Options -> Add-Ins

Under “Inactive Application Add-ins” you will notice that PowerPivot is listed. Hence, it is “Inactive” post install.

Since it is listed as a “COM Add-in”, Manage COM Add-ins at the bottom of this screen, and click “Go…”

Here you can modify the state of the Add-in. Check the box and click “OK”. While you are at it, I would also suggest enabling “Power View” for visualizations.

Why you cannot just toggle the Add-ins from inactive to active without the “manage” step is a bit odd, but it works. Perhaps in the future they will let you double click, right click, check a box, or just drag the add-in to the other area without this extra step.