PowerPivot: make the end users a BI Specialist

Intro

In most of the organizations Business Intelligence (BI) is a hot subject. Most of the management information is created by the IT-departments (OLAP cubes, reports, dashboard, etc). But the ad hoc information will be created by the end user, and most of the time in Excel (familiar application). This information in these sheets is decentrally managed and stored. The problem with these decentrally managed sheets is the fact that people can make critical decisions based on this information and the organization doesn’t know that it exists.

PowerPivot Client

Microsoft resolves this by giving end users a perfect alternative option using the PowerPivot Client and PowerPivot for SharePoint. Users are familiar with Excel and can make use of Excel and the extended PowerPivot tools by downloading the free add-in from http://www.powerpivot.com.

In Excel you get new options added like slicers, with slicers you can slice and dice through your data in Excel, which Is really powerfull in combination with PowerPivot.

Thanks to PowerPivot pivot tables in Excel are no longer based on cells but on tables like in a real database (datasources that can be easily connected are: SQL, Azure, Oracle, Excel and Atom). The information will be physically transferred to PowerPivot.

PowerPivot makes use of an in-memory engine (called VertiPaqengine) in Analysis Services, thanks to this engine it is possible to analyse and transform the data on the workstation of the end user. To use million rows of data PowerPivot uses column compression. Under the hood the engine is a simple version of Analysis Services. With column compression duplicate values will only be saved once, all the duplicate columns will be saved as a shortcut.

PowerPivotLanuage

PowerPivot has got his own function language called Data Analysis Expressions (DAX). With DAX aggregations can be made dynamically based on relational database concepts and an Excel look-a-like syntax. In your PowerPivotresultset (Excelsheet) you can easily add columns based on the columns of your resultset. The formulas will be entered in the familiar Excel formula box.

PowerPivot Server

Users can easily share their PowerPivot files to publish them to SharePoint 2010 in the PowerPivot gallery. In this Silverlight based gallery all the PowerPivot workbooks will be saved and a thumbnail will be shown. The workbooks will be opened (when installed) via Excel Services. So to view PowerPivot files in SharePoint no client installation of PowerPivot and Excel is necessary.

PowerPivot management

The IT department can see the use of the PowerPivot applications by using the PowerPivot Management Dashboard. Thanks to the dashboard the IT department can see if an application is used ad hoc or permanently. When the PowerPivot application will be heavily used , the IT department can decide to create a “static” dashboard for it.