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 

http://www.microsoft.com/en-us/download/details.aspx?id=29074

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

Importing Geospatial data from MS Excel 2010 to SQL Server 2008 R2

Capturing geo-spatial data from web pages is something that Excel can do very well and processing of geo-spatial data is a new capability that became available in SQL Server 2008 R2. With Bing and Maps new applications are possible.
Getting data from MS Excel to SQL server 2008 R2 is very easy with the Export and Import wizard.
This article shows how you would bring in geo-spatial data from an MS Excel 2010 Spread Sheet file to a database table in SQL Server. As SQL Server 2008 R2 supports geo-spatial data it is very easy to process it on the SQL Server. SQL Server Import and Export Wizard is used.
 
The SSWUG article can be viewed free by registering as a guest and well sign up as well and get the full benefits.

While you are visiting SSWUG read this other article on Geo-Spatial data :

Does PowerPivot compress smaller files efficiently?

The Analysis Services engine shipped with SQL Server 2008 R2compresses and processes the data which is loaded by the Analysis Services into the worksheet. All this happens in the background using the Power Pivot [Microsoft.AnalysisServices.Modeler.FieldList.Addin.Integration] menu option. The storage mode of this service called the VertiPaq  does the magic of manipulating columnar data in memory.

Results from a simple test of compression:

I downloaded the Power Pivot sample file in Excel format from the CodePlex site. Review my recent post here for details:

http://hodentek.blogspot.com/2010/05/deliver-knock-out-punch-with-powerpivot.html

• The file is called ContosoStoreData with columns from A to S with 307 Rows as shown here. The file size is 88KB on disc.

• I copied the data and pasted into an excel spread sheet (not using the PowerPivot)and saved it as ContosoNormal. The file size of was 48KB on disc

• Next I imported ContosoNormal into an Excel Spreadsheet under PowerPivot control and named the new file, ContosoNormalReverted. It had the same number of columns as ContosoNormal and the same number of rows(307). The file size was 52KB on disc.

My computer was an Acer Notebook with the following specifications: Windows 7 Ultimate 32bit OS with 3.00 GB RAM. U2700@1.3 GHZ. Free space of 33 GB available on the 288 GB hard disc.