Saturday, January 4, 2014

Connecting to SQL Server Express 2012 from Power Pivot - 2

Before you start using Power Pivot you need to install this add-in.

Determine which version (x32 bit or x64bit) Excel you have on your computer. You can find this in File | Help as shown.


You then download the appropriate version of Power Pivot add-in. Also review the following thread here:
http://social.technet.microsoft.com/Search/en-US/Technet?query=Power%20BI%20and%20Jayaram%20krishnaswamy&beta=0&ac=5

Download the add-in from here:
Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 

This is the power of this add-in in Microsoft's own language:
"Microsoft PowerPivot for Microsoft Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the software users already know and love — Microsoft Excel."
 For the 32bit Office, the PowerPivot_for_Excel_x86.msi (98.5MB) was downloaded and installed.

The following are some of the screen shots for connecting to SQL Server 2012 Express:

Launch PowerPivot window from MS Excel as shown by clicking on PowerPivot
 Window (extreme left)


Click from drop-downs From DataSources followed by From SQL Server in the PowerPivot for Excel window.

Table Connect Wizard gets displayed requesting SQL Server  details as shown.


Provide the database you want to connect to as shown. You better have permissions to do so.

I

If want you can test (and troubleshoot) connectivity from this screen.

Click Next. You can import a set of tables or design data using query as shown in the screen that gets displayed.


The following windows is displayed for the first option and it shows few tables selected. The second part (right-side) of the image shows the Preview and if needed some filtering that can be made.


The import was successful as shown here after some processing.

 
Click Close and the data gets into the PowerPivot window as shown.


You can see relationships that exists as shown.

Now begins the hard work of things that you want to do with this data.

Good luck