Calling Views and Stored Procedures from Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 226

Excel connections versus Power Pivot connections
In this chapter, you created a connection via the Excel Ribbon interface and the Power Pivot interface.
Although they both seem to be doing the same thing, it's important to note that Excel connections and
Power Pivot connections are two different things.
Excel connections are used to bring data into the Excel workbook. These connections can exist without
ever touching the Power Pivot Data Model. You can edit, update, and fully configure Excel connections.
Power Pivot connections are designed to be used in the internal Data Model and not in the Excel work-
book. However, Power Pivot connections must be linked to an Excel connection. This means that when
you create a connection using Power Pivot, an Excel connection is automatically generated even though
the connection was created for the internal Data Model. Because of this dependence on an Excel connec-
tion link, Power Pivot connections cannot be edited or configured. So, for example, if you create a custom
query to run a stored procedure in Power Pivot, you cannot change the SQL syntax as you would with an
Excel connection. You have to delete the connection and rebuild it.
For this reason, it's often best to create an Excel connection and simply add the connection to the Data
Model (versus creating the connection directly in Power Pivot), especially if you're using SQL statements or
VBA to dynamically change the connection string. Using an Excel connection enables you to edit and con-
figure as needed.
To add an existing Excel connection to the Data Model, click the Connections button on the Data tab.
Choose your Excel connection in the Workbook Connections dialog box, click the drop-down arrow next
to the Add button, and select Add to the Data Model.