What is new for BI in Excel 2016

It’s been awhile that we are waiting for a sensible improvements in Microsoft self-service BI. The good news is that finally there will be some cool new features added to the next version of Excel which is Excel 2016. By some, I mean, well, there not a lot new BI features, but, some. Something is better than nothing, not too bad though!

Integrating BI features with Excel:

Power View and Power Map:

As you know, Power Pivot was integrated as a built-it feature to Excel 2013. Now I’m really happy that the same thing happened to Power View and Power Map. So you don’t need to install them separately. You can now turn these features on from:

File–> Options–> Advanced-> (scroll down the page) Data-> Enable Data Analysis Add-ins: Power Pivot, Power View, and Power Map

image

OR you can still turn them on from “COM Add-ins”:

Continue reading “What is new for BI in Excel 2016”

Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA

Update September 2022:

I wrote a new blog post covering the same scenario in Excel 365.

In this post, I express an easy way to refresh a PowerPivot model dynamically based on SQL Server Stored Procedures. Let’s start with SQL Server Management Studio (SSMS) 2012 and use Adventure Works 2012 database. Run the following script to see the results in SSMS:

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

First parameter: Start Product ID

Second Parameter: Check Date

Results:

powerpivot01

Now we want to see the results for the following script:

exec [dbo].[uspGetBillOfMaterials] 762, ‘2009-01-02’

 Results:

powerpivot02

Now we want to do the same with PowerPivot.

Continue reading “Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA”