Look inside the Power Pivot data model hidden in an Excel workbook — with nothing but what already ships with Windows. No Python, no pip, no Cython, no NuGet.
Install-Script -Name Show-PowerPivotModel
Show-PowerPivotModel -Path .\Book.xlsx -ListFilesFile : C:\models\Book.xlsx
Member : xl/model/item.data (4913152 bytes)
Format : uncompressed ABF (winapi xpress8 path applies)
== Backup log ==
Object : Microsoft_SQLServer_AnalysisServices
SyncVersion : 1
ApplyCompression : True
ErrorCode trailer : True
Languages : 1033
File groups : 2
Embedded files : 214 (header says 214)
== Embedded files (by size) ==
Size Offset Name
---- ------ ----
892,384 4096 Sales_bfd1.Order Date.0.idf
611,208 896480 Sales_bfd1.Product Key.0.idf
...
An .xlsx workbook that contains a Power Pivot model is not just a spreadsheet.
Inside the ZIP, the member xl/model/item.data holds a complete Analysis
Services backup file (ABF) — the same structure a .pbix keeps in its
DataModel member. Power Pivot has always been a local SSAS Tabular instance in
a trench coat; msmdsrv.exe shows up in Process Explorer when you use it.
Getting at that model normally means opening Excel. This script reads it straight off disk in under 200 lines of PowerShell — and the part that actually does the decompression is about thirty of them.
The files inside the backup are compressed with what the VertiPaq world calls xpress8. That name suggests something proprietary, but the framing is simple:
repeated: [uint16 uncompressed_size][uint16 compressed_size][body]
…and when the two sizes are equal, the body is stored verbatim. Each compressed body is a raw MS-XCA Xpress buffer — plain LZ77, no Huffman layer.
Windows can already decode that. ntdll!RtlDecompressBuffer with
COMPRESSION_FORMAT_XPRESS (3) takes a raw MS-XCA buffer and hands back the
plaintext. That is a P/Invoke away from PowerShell:
[DllImport("ntdll.dll")]
public static extern int RtlDecompressBuffer(
ushort fmt, byte[] dst, int dstLen, byte[] src, int srcLen, out int finalSize);The detour that cost me time: the obvious candidate is cabinet.dll's
Compression API (CreateDecompressor / Decompress). In its default buffer
mode it will not read these chunks — it expects data wrapped in its own
container header. You can opt into raw with the COMPRESS_RAW flag, but that
puts you in block mode, where you must supply the exact original uncompressed
size and manage blocks yourself.
RtlDecompressBuffer skips all of it: bare input buffer, output buffer, output
size, done. It is documented as a driver DDI, but ntdll exports it and user
mode can call it — which is the whole reason this fits in a P/Invoke.
.xlsx (ZIP)
└── xl/model/item.data ← Analysis Services backup (ABF)
├── bytes 0..204 STREAM_STORAGE_SIGNATURE (UTF-16)
├── bytes 72..4096 backup-log header, null-padded UTF-16 XML
│ ApplyCompression, ErrorCode, Files, DataSize,
│ m_cbOffsetHeader → where the directory lives
├── virtual directory storage path → offset + size, per file
├── backup log manifest storage path → friendly path (last dir entry)
└── N embedded files xpress8-chunked, at the offsets above
Two XML documents have to be joined to get anything useful. The virtual
directory knows where each file physically sits but names them by opaque
storage path. The backup log knows the friendly names (Sales_bfd1.Order Date.0.idf) but not the offsets. Join them on the storage path and you have a
browsable file table.
The ErrorCode flag in the header means every embedded file carries a 4-byte
trailer that is not part of its content — miss that and every decode is off by
four bytes at the tail.
# Summary + preview of the smallest XML metadata file
Show-PowerPivotModel -Path .\Book.xlsx
# Every embedded file, largest first
Show-PowerPivotModel -Path .\Book.xlsx -ListFiles
# Decompress a specific file and print all of it
Show-PowerPivotModel -Path .\Book.xlsx -Dump 'cub.xml' -Full
# Decompress and save the raw decoded bytes
Show-PowerPivotModel -Path .\Book.xlsx -Dump 'Sales' -OutFile .\sales.idf| Parameter | Meaning |
|---|---|
-Path |
.xlsx with a Power Pivot model, or a legacy .pbix with an uncompressed DataModel |
-ListFiles |
Print the whole embedded file table, sorted by size |
-Dump <substring> |
Pick the file to decode by friendly-path substring (smallest match wins) |
-Full |
Print the entire decoded file instead of the first 1400 characters |
-OutFile <path> |
Write the full decoded bytes to disk |
- Windows. The script P/Invokes
ntdll. It refuses to run elsewhere. - Windows PowerShell 5.1 or PowerShell 7+.
- Nothing else.
This is deliberately a glimpse, not a parser.
- XPress9 models are not supported. Newer and larger models wrap the whole backup in XPress9, which the Windows compression APIs cannot decode. The script detects this and tells you. Use pbixray, which carries a Cython XPress9 port.
- No VertiPaq decoding. You get the
.idfcolumn segments as bytes. Turning those back into rows means dictionaries, hash indexes and RLE runs — again, that is what pbixray is for. - It prints, it does not pipe. The script writes a human-readable report via
Write-Host. APowerPivotPeekmodule exposing proper objects is the planned next step. (PSAvoidUsingWriteHostis a deliberate, documented exception here: the entire purpose of the script is console display.)
If you want tables, relationships, DAX measures and reconstructed row data as DataFrames, you want pbixray. This script is just a small window into the same container.
It is one self-contained file. Downloading it and running it works fine:
irm https://raw.githubusercontent.com/Hugoberry/PowerPivotPeek/main/Show-PowerPivotModel.ps1 -OutFile Show-PowerPivotModel.ps1
.\Show-PowerPivotModel.ps1 -Path .\Book.xlsx-
PowerPivotPeekmodule exportingShow-PowerPivotModel,Get-PowerPivotFile,Export-PowerPivotFile - Pester tests against a committed sample workbook
- Emit objects to the pipeline instead of formatted text
- [MS-XCA]: Xpress Compression Algorithm
- [MS-XLDM]: Spreadsheet Data Model File Format
RtlDecompressBuffer- pbixray — the full Python parser
- Parsing Power Pivot Data Models from Excel XLSX Files
MIT — see LICENSE.