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.
Found this very handy flowchart with Google and had to post it.
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.
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
Today, I’ve read a lot about PowerPivot…blogsitems, more blogitems and screencasts.
And made my first PowerPivot Dashboard by using the AdventureWorksDW InternetSales datamart.
Yesterday, I got a great tip from a colleague:
Stardock’s Fences – ‘Your tool for a clean desktop’
And just for an impression, I Googled some examples:
Want it? Get it here
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.