PowerPivot 2013 to SQL Server connection

Data model in PowerPivot is a collection of data tables along with their relations as real world entities. PowerPivot data model allow users to integrate data from multiple data tables building relations between them in an excel workbook. A number of sources ranging from SQL server to Text file are available as external data sources in PowerPivot. Data models allows users to work on millions of records in excel but there are some over kill of resources using this technique to manipulate data because of the in-memory analytics engine. Using a large data set in excel workbook consume memory which could affect other application sharing same resources but still data models in excel compresses up to 7 times of the original size. The data compression depends upon the unique values in each column, more unique values means less compression. Another limitation of large data is the size limit of 10mb while working in SharePoint

PowerPivot Tab is available in MS Excel as shown below

if not then user have to enable the tab performing the following steps.

Þ      Go to File à Options à Add-Ins.
Þ      In the Manage box, click COM Add-ins à Go.
Þ      Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then click OK

To connect to Database, click on the "Manage" icon and select "From Database".

Connecting to Database

Database connection can be linked to DBs in SQL Server, Access and SQL Server Analysis services. All the data connections are re-usable and exists in “Existing Connections”. Connection to a database can be created from the “From Database” link as shown below.

Friendly data connection is a name given to the connection, it could be anything readable and can be used to identify from other connections when return to some same sort of work. Server name is the name of the machine where SQL server is installed, for local SQL server instances a period (.) can be used. User can select from windows authentication or SQL server authentication to connect to database. Finally put in the database name and test connection, if it’s a success Press next.

User can select from tables directly or filter data according to requirement using the query builder. In case of large records it is efficient to use Query builder and the best way is to exclude all the un-related columns. The best practice is to use the Query builder, because large data can be filtered out here and enhance performance by using less memory. Larger data import requires more memory and can be slow.

Selecting tables allows users to also get the related tables which allows data connections.

The last step to import data using the SQL Data Source will show user a summary of selected tables as well as the connected tables (if selected). Un-related columns can also be excluded using the Review and Filter button. The filter are applied according to the data type of the selected column.

Close the dialog box. A tab for each table selected above will be added to the data management window of PowerPivot. User can view all connected tables in “Diagram View” as shown below

Now the selected data has been imported in to MS Excel, user can work on it using different powerful tools available like Charts, Pivot tables for data presentation.