Where is the data stored that PowerPivot for Excel collects and uses?

PowerPivot for Excel 2010 internally builds a SQL Server Analysis Services (SSAS) database. The data is highly compressed and is stored within the .xlsx file itself in the SSAS database format.
OK…so where is it exactly stored then?

Create a sample PowerPivot application in Excel 2010 and save the resulting file to your local file system. Next, rename the .xlsx file extension to .zip (use command prompt in Win 7).

Now open the resulting .zip file and navigate to the xl\customData directory.

The .data file is where the PowerPivot data (the actual SSAS database) is stored.

Guess what….you can deploy that SSAS database in SSAS if you like!
More info on IMDB or VertiPaq? Click here.

Microsoft Contoso BI Demo Dataset for Retail Industry

A nice addition to your database collection is Microsoft’s Contoso (BI):

The Contoso BI Demo dataset is used to demonstrate DW/BI functionalities across the entire Microsoft Office product family. This dataset includes C-level, sales/marketing, IT, and common finance scenarios for the retail industry and support map integration. In addition, this dataset offers large volumes of transactions from OLTP and well-structured aggregations from OLAP, along with reference and dimension data.

Download the Microsoft Contoso BI Demo Dataset for Retail Industry here.

PowerPivot for SharePoint – Single Server Installation

The SQL Server Customer Advisory Team (SQLCAT) published a whitepaper about the installation of PowerPivot for SharePoint on a single server SharePoint farm.

Installing PowerPivot for Microsoft SharePoint on a new single server SharePoint farm is the simplest way to setup your PowerPivot server. The step-by-step procedure presented in this paper should ensure that you are up and running in less than an hour.

The whitepaper describes step-by-step how to create a new environment with the following setup:

Windows Server 2008 with Service Pack 2 (SP2)
SQL Server 2008 R2
SharePoint Server 2010
PowerPivot for SharePoint

Click here for the whitepaper

Gartner’s opinion about PowerPivot

Gartner recently published two articles about PowerPivot:

β€œIn all organizations that use Excel, PowerPivot is coming, whether the IT department likes it or not. Once an organization upgrades to Office 2010, there will be no stopping it. Better to embrace its use …”

What Is SQL Server PowerPivot?

Q&A: What Are Customer Experiences With SQL Server PowerPivot

Summary of Strengths:
Fast analysis of large data sets.
Easy to use for business users; “slicers” are an excellent interactive analysis feature.
End-user data mashup and modeling capabilities do not require programming.

Summary of Cautions/Areas for Improvement:
Handling of many-to-many relationships in the modeling environment is a challenge.
Business users must learn SQL Server PowerPivot’s new expression language, DAX, for advanced time series and other analysis.
Limited facilities for data quality during the data load process.