The purpose of this project is to demonstrate the access of PowerPivot measures (calculated fields) in Excel 2010 xlsx and xlsm files without opening the files within Excel. The application is inspired from a manual process described
by PowerPivot guru Rob Collie
on his PowerPivotPro website.
Benefits from the this project include (1) facilitating the learning of DAX (Data Analysis Expressions) which is used to formulate measures not only in Excel data models, but also in the Tabular Model of SQL Server Analysis Services and (2) debugging existing
measures in Excel 2010 data models.
The differentiating factor offered by the current project (in contrast to other efforts to extract data model measures from Excel 2010 files) is summed up in that this project is not limited to accessing a single Excel 2010 file's DAX measures per request,
but can concurrently extract DAX measures from files located in multiple directories or across multiple disks.
Special features include the use of DotNetZip
to extract data from files without creating copies on disk and without changing their file extensions--the processing is done in your machine's memory.
The measure information is extracted as a single block of text for each Excel file and the results are displayed in a Visual Studio 2010 WinForm application, which also allows the results to be saved as a text file to disk. An option to order the output by
table name, measure name and expression is a topic available for comment under the Discussions tab of this project.
Special features include:
- Each Excel 2010 file is opened as a memory stream inside of the measure reader application (no need to alter file name on disk or create a special directory for Excel file copies)
- A custom Excel class is really a wrapper around a DotNetZip ZipFile class (the code "lets" Excel extract its own items)
- Multiple Excel 2010 files can have their measures extracted with a single request
- Files located in different directories, even on different disks, can be processed concurrently
Applies only to Excel 2010 files with PowerPivot data models (not to Excel 2013).