Using a SQL Server Stored Procedure as a PowerPivot Data Source

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:

create procedure dbo.testproc

as
select 1 as data
  1. In PowerPivot, choose “Get External Data”
  2. Choose SQL Server and enter your connection information
  3. Choose to “write a query that will specify the data to import”
  4. In the SQL Statement, execute the stored procedure – exec dbo.testproc

Success! However, what if you have some complicated logic, such as using a temporary table for some reason in your sproc. Shouldn’t the results be the same? Let’s change the stored procedure and see.

alter procedure dbo.testproc

as

declare @t table(data int)
insert into @t(data)
values(1)

select data
from @t

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.

alter procedure dbo.testproc

as

set nocount on

declare @t table(data int)
insert into @t(data)

values(1)

select data
from @t

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.

Advertisements

Excel 2013 – Where is PowerPivot?

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.

  1. Open Excel
  2. File -> Options -> Add-Ins
  3. Under “Inactive Application Add-ins” you will notice that PowerPivot is listed. Hence, it is “Inactive” post install.

  1. Since it is listed as a “COM Add-in”, Manage COM Add-ins at the bottom of this screen, and click “Go…”
  2. 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.