Hi All,
Today i would like to talk about a booming technical feature by Microsoft i.e. “PowerPivot“.

What is PowerPivot?
PowerPivot (which is also known as Project “Gemini“) is Microsoft’s hottest business intelligence (BI) innovation. By integrating PowerPivot technology with Microsoft Excel, Excel has gradually becoming most wanted & preferred BI tool (client) in the world.

PowerPivot is actually of 2 types:
1. Client Add-In for Excel 2010 i.e. “PowerPivot for Excel” (Applicable to Excel 2010)
2. Server component i.e. “PowerPivot for SharePoint” (Applicable to SharePoint 2010 Onwards)

“PowerPivot for Excel” is an add-in for Excel 2010 that provides the capability to manipulate really vast amounts of data from multiple data sources for analysis.

“PowerPivot for SharePoint” is nothing but SQL Server 2008 R2 Analysis Services running in “Vertipaq” mode within SharePoint 2010. A Vertipaq is the in-memory technology that enables fast data manipulation in Excel Services and also enables efficient share & collaboration across your organization. And moreover, it allows IT administrators to monitor and control self-service BI through its Operation Dashboard.

Wait… What is the relation of “Managed Self-Service BI” with PowerPivot?
“PowerPivot for Excel” enhances self-service BI capabilities by providing user with greater flexibility of data manipulation using any size of data (believe me, forget to worry about Excel limitations but rather think about how much is your HDD) in structured and simple manner. And later, you can publish your excel analysis (Reports) in SharePoint 2010 as a web application and share it across your team members or department heads.

FYI, “Managed Self Service BI” means that IT also benefits greatly with PowerPivot. They can provision reports – thus providing a single version of truth and have end users work directly with data from those reports via Data Feeds. In addition, IT can track the usage of PowerPivot applications and discovering mission-critical Excel applications. (Said by Microsoft)

So, what are the data sources that “PowerPivot for Excel” can consume data from?
“PowerPivot for Excel” can use any data source. Some examples includes OLAP cubes, ATOM feeds with tabular data, Paste/Append & Paste/Replace any tabular data, Excel Pivot Tables, and structured relational databases.

How is referential integrity managed, especially when adding multiple data sets from various sources?
You can fix referential integrity issues using DAX expressions. “PowerPivot for Excel” also supports NULL/unknown members, similar to classic Analysis Services.

Can I Install “PowerPivot for Excel” without installing SQL Server 2008 R2?
Yes. The “PowerPivot for Excel” client does not require anything else besides a simple installer and does not required SQL Server 2008 R2. This client component is a standalone Excel 2010 add-in that can query any data source, including SQL Server. “PowerPivot for Excel” has only one dependency that is Excel 2010. (Note: It will not work with earlier versions of Excel)

Can I install “PowerPivot for SharePoint” without SQL Server 2008 R2 or SharePoint 2010?
No. The “PowerPivot for SharePoint” is a server component, it requires both SQL Server 2008 R2 and SharePoint 2010. And to enable sharing and collaboration on PowerPivot for Excel applications and models, you must install SQL Server 2008 R2 as part of the SharePoint farm.

Can I edit a “PowerPivot for Excel” within SharePoint 2010 if I don’t have Office 2010 installed on my machine?
Without the Excel add-in, you cannot edit PowerPivot models anywhere. Though you can view and use the models online through SharePoint.

Can I install Excel 2010 and Excel 2007 side by side on one machine?
Yes you can install them Side by side.

I hope that you will find this interesting & useful.
Thanks, Khilit