Microsoft Business Intelligence Tools for Excel Analysts

Chapter 11: Calling Views and Stored Procedures from Excel

In This Chapter

• Importing Views from SQL Server databases
• Running SQL Server stored procedures from Excel
• Creating dynamic connections with VBA
• Creating a data model with multiple SQL data objects
• Loading SQL Server stored procedures directly into Power Pivot

In Chapter 10, we introduced you to SQL Server databases and discovered how to pull data from SQL Server by creating views and stored procedures.

As you start working with SQL Server views and stored procedures, you’ll quickly find the need to bring that data into your reporting models. Luckily, Excel 2013 vastly improves your ability to connect to SQL Server data by providing several methods for integrating external data into your Excel BI solutions.

Importing Data from SQL Server

The option to pull data from SQL Server has been available in Excel for many versions; it was just buried several layers deep in somewhat cryptic menus. This made getting SQL Server data into Excel seem like a mysterious and tenuous proposition for many Excel analysts. With the introduction of the Ribbon in Excel 2007, Microsoft put the Get External Data group of commands right on the Ribbon on the Data tab, making it easier to import data from SQL tables and views.

Using the Get External Data group in Excel allows you to establish an updatable data connection between Excel and SQL. Follow these steps:

1. On the Data tab, select From SQL Server from the From Other Sources drop-down list. ...