EXCEL: What you can do with PowerPivot?

PowerPivot for Excel is an add-on for Microsoft Excel. It lets users conduct powerful business intelligence (BI) in an environment that is familiar.

PowerPivot is a free download from Microsoft and allows users to work with extremely large data sets. Before PowerPivot, this kind of analysis was limited to enterprise BI tools such as SAS and Business Objects.

PowerPivot uses an in-memory engine called VertiPaq. This SSAS engine takes advantage of the increased RAM available in most personal computers today.

Most IT shops are challenged with the resources needed to build out an enterprise BI environment. PowerPivot moves some of this work closer to the business user. While there are many features in PowerPivot for Excel, we have chosen five that we consider to be the coolest.

1. Work With Very Large Data Sets

In Microsoft Excel, if you move to the very bottom of a worksheet, you will see that the maximum number of rows is 1,048,576. This represents around a million rows of data.

With PowerPivot for Excel, there is no limit on the number of rows of data. While this is a true statement, the actual limitation is based on the version of Microsoft Excel you are running and whether you are going to publish your spreadsheet to SharePoint 2010.

If you're running the 64-bit version of Excel, PowerPivot can reportedly handle about 2 GB of data, but you also must have enough RAM to make this work smoothly. If you plan to publish your PowerPivot based Excel spreadsheet to SharePoint 2010, the maximize file size is also 2 GB. 

The bottom line is that PowerPivot for Excel can handle millions of records. If you hit the maximum, you'll receive a memory error.

2. Combine Data From Different Sources

This has to be one of the most important features in PowerPivot for Excel. Excel has always been able to handle different data sources such as SQL Server, XML, Microsoft Access and even web based data. The problem comes when you need to created relationships between different data sources.

There are 3rd party products available to help with this, and you can use Excel functions like VLOOKUP to "join" data, these methods are impractical for large data sets. PowerPivot for Excel is built to accomplish this task.

Within PowerPivot, you can import data from virtually any data source. I have found that one of the most useful data sources is a SharePoint List. I've used PowerPivot for Excel to combine data from SQL Server and a list from SharePoint.

Note: You need SharePoint 2010 to make this work, along with the ADO.Net runtime installed on the SharePoint environment.

When you connect PowerPivot to a SharePoint list, you are actually connecting to a Data Feed. To create a Data Feed from a SharePoint list, open the list and click on the List ribbon. Then click on Export as Data Feed and save it.

3. Create Visually Appealing Analytical Models

PowerPivot for Excel lets you output a variety of visual data to your Excel worksheet. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts, and a Flattened PivotTable.

The power comes when you create a worksheet that includes multiple outputs. This provides a dashboard view of the data that makes analysis really easy. Even your executives should be able to interact with your worksheet if you build it correctly. 

Slicers, which shipped with Excel 2010, makes it simple to visually filtered data.

4. Use DAX to Create Calculated Fields for Slicing and Dicing Data

DAX (Data Analysis Expressions) is the formula language used in PowerPivot tables, primarily in creating calculated columns.

I typically use DAX date functions to make date fields more useful. In a regular Pivot Table in Excel that included a properly formatted date field, you can use grouping to include the ability to filter or group by year, quarter, month and day.

In PowerPivot, you need to create these as calculated columns to accomplish the same thing. Add a column for each way you need to filter or group data in your Pivot Table. Many of the date functions in DAX are the same as Excel formulas, which makes this a snap.

For example, use =YEAR([date column]) in a new calculated column to add the year to your data set in PowerPivot. You can then use this new YEAR field as a slicer or group in your Pivot Table.

5. Publish Dashboards to SharePoint 2010

PowerPivot, when combined with SharePoint 2010, puts the power of dashboards into the hands of your users.

One of the prerequisites of publishing PowerPivot driven charts and tables to SharePoint 2010 is the implementation of PowerPivot for SharePoint on your SharePoint 2010 farm.

Source: https://www.lifewire.com/