Set the Data Source for Excel PowerPivot Files

When we develop sample excel spreadsheets we use sample data on our development machines. For these spreadsheets to be useful to you, the data connection needs to be changed to point to your database. The steps below outline how to adjust this connection.

1. Open the sample file in Excel.
2. Click the PowerPivot Ribbon and click Manage (See Notes below for info on PowerPivot availability and activation).
   
   

   

3. In the Data Model editor, click existing connections.
   
   

   

4. On the Existing Connections dialog, click edit.
   
   

   

5. On the Edit Connection dialog, use the Server Name drop down to select your Microsoft SQL Server Instance. If you do not know your server name, find this in Comsense Advantage by clicking Help > About Comsense Advantage listed as SQL Server Instance.
   
   

   

6. When you change the Server Name the Database name will clear out. From the Database Name drop down select ComsenseDataWarehouse.
7. Click Save.
8. Click Close on the Existing Connections dialog.
9. Click the drop down under Refresh and click Refresh All.
   
   

   

10. The Data Refresh dialog will appear and show you progress. Click close when complete.
    
    

    

11. Close the PowerPivot for Excel editor to return to Excel, using either File > Close or click X.
12. Save the Excel spreadsheet.
13. To refresh the data in the spreadsheet in the future, simply click Refresh All on the Data Ribbon in Excel.
    
    

    

Notes:

• Only certain editions of Excel include the PowerPivot feature see this article on PowerPivotPro for more info.
• To enable the Excel PowerPivot feature see Microsoft's instructions here.
• If you receive an error attempting to refresh connect or refresh the data, see the article Installing SQL Client Libraries for help.