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.

 You can also find the post on Microsoft Technet

SharePoint 2013: Installing and configuring PowerPivot

PowerPivot 2013 Installation

Microsoft SQL Server 2013 PowerPivot for SharePoint 2013 extends SharePoint to use PowerPivot features. To enable PowerPivot 2013 in SharePoint, spPowerPivot.msi needs no be installed. The package can be downloaded from Microsoft download center at the location; http://www.microsoft.com/en-us/download/details.aspx?id=35577

Setup is a simple click and proceed affair. Configuration starts after the installation.

For systems requirements please refer to the following article on TechNet

http://technet.microsoft.com/en-us/library/jj218792.aspx

Double click the spPowerPivot.msi after downloading, the screen shown below appears.

Click next to proceed and accept the license agreement to install.

All the features below are pre-selected. PowerPivot for SharePoint 2013 includes features of PowerPivot for SharePoint 2013. OLE DB for Analysis service provides an interface for PowerPivot 2013 to interact with analysis service in SQL Server. ADOMD.net is a .NET framework data provider to interact with Analysis services in SQL Server. Analysis management object (AMO) is used by application to manage instance of SQL Server analysis services.

A screen to confirm the selection on the previous screen is shown, if you want to change just click back. Click next to proceed for installation to begin.

Installation in progress...

Installation completes.

PowerPivot Configuration for SharePoint 2013

Once installation is complete, configuration can be started by going to

Start Menu à All Programs à PowerPivot for SharePoint 2013 configuration

For configuration the logged in account must be an administrator on local machine and a farm admin in SharePoint 2013 Farm. The same account must have rights of DB Owner on SharePoint 2013 Farm configuration data base.

Configuration wizard start by examining the systems settings. This may take couple of minutes.

After examining the system settings, Select “Configure or Repair PowerPivot for SharePoint” option. For a fresh installation this is the only option available as shown below.

Configuration wizard will then run a validation check.

In case of failed validation for different components in wizard, an alert mark is displayed in front of all failed components. I the screen below the alert icon is displayed for different components like “Create Default Web Application”.

For PowerPivot configuration following information is required;

1. Default Account Username, Domain account for web application administrator
2. Default Account Password,  A password for the above mentioned account
3. Database Server, used by SharePoint farm
4. Passphrase, the same passphrase you have used in the configuration of SharePoint Products configuration wizard.
5. PowerPivot server for excel services, an instance will be created in the form of servername\PowerPivot for excel services where servername is the server where SharePoint is installed.

PowerPivot requires a default web application in SharePoint farm for its settings, if you have a new farm and it does not contains any default web application like “SharePoint 80”, the PowerPivot configuration wizard will do this for you but this will require some information.

 

For setting up a default web application information required are;

1. Web application name, any name of your choice
2. URL, A URL for web application in format of http://<sharepointservername>/
3. Application pool name
4. Application pool account, a domain account, preferably the same as you will use for the collection administrator as discussed below.
5. Application pool account password
6. Database server
7. Database name

After the web application settings, fill in the information for site collection for the same web application.

1. Site Administrator, domain account
2. Contact email
3. Site URL
4. Site Title

Add information for Unattended Account for Data refresh. All information except the Account Username and password are pre-filled. You can go with them of change the values as you require.

After you fill in all the information, click on the validate button again, the setup will again run the validation. If validation succeeded you will see the “Run” button is enabled. Click the Run button and this will start configuration.

After successful run, a message will be displayed confirming the completion of all task selected.




You can confirm the setting by going in to;

Central Administration à Web Application management à Manage service Applications.

Here you can find the following service with status as “started”

1. Default PowerPivot Service application
2. Excel Services
3. Secure Store service

You can also find the post on Microsoft Technet