Behind the Scenes in PowerPivot for Excel
It has helped my clients to remember the Excel interface is primarily used to control and influence the presentation of data. Features for formatting values, as well as tasks to save, print, and share the solution, are included in the Excel interface. The existing Excel user community and the familiarity of these features to experienced spreadsheet users contribute to PowerPivot’s ease of adoption.
On the other hand, PowerPivot for Excel is a tool for integrating and manipulating large volumes of data. Nothing short of a revolution in database software would be required to create a user-friendly tool for organizing (sorting, filtering, and calculating) datasets that could contain millions of rows, using readily available, commodity, personal computer hardware. The key to solving this problem is the in-memory runtime for SQL Server Analysis Services. This database engine is known as SQL Server Analysis Services, Vertipaq mode. In essence, as you installed the PowerPivot add-in for Excel, you created a specific type of SQL Server Analysis Services instance on your personal computer. The PowerPivot user interface is your window into your local, in-memory version of SQL Analysis Services and the principal data engine for PowerPivot for Excel.
By opening a Windows Explorer window to the location of our Chapter2.xlsx example solution, you should see an ordinary Excel worksheet file as far as the operating system is concerned. However, PowerPivot for Excel has actually created both an Excel worksheet and a SQL Server Analysis Services (SSAS) database file, storing them together as an .xlsx file. In this section, we will do some minor hacking to pull back the curtain on the PowerPivot for Excel software.
As a foundation, recall that PowerPivot for Excel consists of two user interface windows, one for the Excel workbook and one for the PowerPivot data. Additionally, the .xlsx file created by PowerPivot for Excel contains structures to store the required worksheet and data. The xl directory of the PowerPivot for Excel file contains a number of folders. However, worksheets and customData tie directly to the two roles of PowerPivot for Excel: worksheets and SSAS data. Figure 2-10 shows a high-level depiction of the xl folder structures.
Figure 2-10. High-level PowerPivot for Excel file structure
To begin the exploration of the PowerPivot for Excel file structure, navigate to the folder in which the solution is saved. Copy the original file to a .zip archive, as the .xlsx format is really a compressed folder. In the case of our example, Chapter2.xlsx will become Chapter2.zip, and from within Windows Explorer, Chapter2.zip will be treated as a compressed folder. Opening the compressed folder and navigating to the xlcustomData folder should produce a list similar to the one shown Figure 2-11.
Figure 2-11. A SQL Analysis Services folder
The item1.DATA file is in actuality a SQL Server Analysis Services backup (.abf) file. However, because of the in-memory (Vertipaq) mode used by PowerPivot, this file can be restored only to an SSAS instance running in SharePoint integrated mode. Copying this file from the compressed folder into the backup folder for the local SSAS instance, renaming it to item1.abf, and attempting restoration will fail with an error message indicating the destination for the backup is inconsistent with the SSAS mode of the backup file.
These .DATA (also known as Analysis Services backup or .abf) files will be more useful as we progress into PowerPivot for SharePoint examples and establish the required development environment for working with SharePoint. For now, the goal is just to unwrap some of the packaging of the Excel Workbook and PowerPivot for Excel data that exists in the .xlsx files you will create.