PBIXRay
Go back

Windows Already Ships the Decompressor Power Pivot Needs

Most of what I have written on this site describes work that took months. Reconstructing column data from .idf and .idfmeta files, working out how dictionaries and hash indexes relate to each other, unpicking the RLE runs. That kind of thing does not compress into a short article.

This one does, because it is about something I found late and should have found early.

The short version is that for one of the two compression schemes involved, Windows has had the decoder all along, sitting in ntdll.

The String That Told Me Where I Was

Before any of the compression work there was a more basic question. I had a blob of bytes out of xl/model/item.data and no particular reason to believe it was anything I would recognise.

The answer sits in the first few dozen bytes, stored as UTF-16, and it is hard to miss:

STREAM_STORAGE_SIGNATURE_)!@#$%^&*(

Somebody at Microsoft finished the sensible half of that name and then held down shift and rolled across the number row. A signature that specific cannot be an accident, and the moment it turned up I knew the workbook was carrying an Analysis Services backup file. Suddenly the whole ABF vocabulary applied. Backup log, virtual directory, file groups, storage paths. All of it had names I could go and look up.

It also explains a number that otherwise looks arbitrary. The signature is 35 characters, stored UTF-16 behind a two-byte byte-order mark, which comes to 72 bytes. That is exactly where the backup-log header starts.

The reach is wider than Excel, too. This is the same container Analysis Services writes when you back up a Tabular database, so an .abf off an SSAS instance has the same header, the same virtual directory and the same backup log. Compressed chunks are the common case there as well, since Microsoft’s own documentation lists compressing the backup as the default. Power Pivot workbooks, older .pbix files and SSAS Tabular backups are three wrappers around one format, which is why work on any one of them keeps paying off on the other two.

What “xpress8” Actually Is

Inside a Power Pivot workbook, or inside a .pbix, the embedded Analysis Services backup stores its files compressed with something the VertiPaq world calls xpress8. For a long time I read that name as a proprietary variant, some Microsoft-internal codec I was going to have to reverse byte by byte.

The reality is duller. There are two length fields:

repeated until the slice is consumed:
  [uint16 uncompressed_size][uint16 compressed_size][body]

When the two sizes match, the body was not worth compressing and sits there verbatim.

That is the whole container. Take those four bytes off and what remains is a raw MS-XCA Xpress buffer, plain LZ77 with no Huffman layer. [MS-XCA] covers two variants, one with Huffman coding and one without. VertiPaq uses the one without.

So xpress8 is not a codec at all. It is a chunk framing wrapped around a codec that Microsoft has published a specification for.

Why Windows Has the Decoder

Once you know it is MS-XCA Xpress, the useful question is who else uses it. The answer is the operating system. Xpress turns up in the hibernation file, in parts of the SMB stack, and in the Windows Overlay Filter behind Compact OS, where system binaries sit on disk compressed and get expanded on every read.

Something that expands binaries on every read needs a fast Xpress decoder, and Windows has one:

NTSTATUS RtlDecompressBuffer(
  USHORT CompressionFormat,
  PUCHAR UncompressedBuffer,
  ULONG  UncompressedBufferSize,
  PUCHAR CompressedBuffer,
  ULONG  CompressedBufferSize,
  PULONG FinalUncompressedSize
);

Set CompressionFormat to COMPRESSION_FORMAT_XPRESS (3) and it takes a bare MS-XCA buffer and hands back the plaintext, which is exactly what an xpress8 chunk body is once you drop the four header bytes.

RtlDecompressBuffer is documented in the Windows Driver Kit, under ntifs.h, and that is what kept me away from it for so long. It reads like kernel territory. But ntdll exports it and user mode can call it without any trouble. The WDK framing says more about who the API was written for than about who is allowed to call it.

The Detour Through cabinet.dll

Before I got there I spent a while on the wrong API, which is worth writing down because it is the obvious first guess.

Windows has a user-mode Compression API in cabinet.dll, with CreateDecompressor and Decompress, and it advertises support for an XPRESS algorithm. That looks like the right tool and it is not.

In its default buffer mode, Decompress wants data wrapped in the API’s own container, with a header describing the block. Give it a bare VertiPaq chunk body and it has nothing to work with.

There is a way around that. The COMPRESS_RAW flag does accept raw buffers, but it puts you into block mode, where you have to supply the exact original uncompressed size and handle block boundaries yourself. By the time you have done that bookkeeping you could have called the other function.

RtlDecompressBuffer wants none of it. Input buffer, output buffer, output size, status code back. That is why the working code is a P/Invoke declaration rather than a wrapper class.

Thirty Lines of PowerShell

The binding is short enough to read in one go:

Add-Type -TypeDefinition @'
using System;
using System.Runtime.InteropServices;
namespace PP {
  public static class Nt {
    // COMPRESSION_FORMAT_XPRESS = 3 (raw MS-XCA LZ77 -- what xpress8 chunks are)
    [DllImport("ntdll.dll")]
    public static extern int RtlDecompressBuffer(
        ushort fmt, byte[] dst, int dstLen, byte[] src, int srcLen, out int finalSize);
  }
}
'@

And the loop over the chunks:

$out = New-Object System.IO.MemoryStream
$pos = 0
while ($pos -lt $data.Length) {
    $u = [BitConverter]::ToUInt16($data, $pos)      # uncompressed size
    $c = [BitConverter]::ToUInt16($data, $pos + 2)  # compressed size
    $pos += 4
    if ($u -eq $c) {
        $out.Write($data, $pos, $c)                 # stored verbatim
    } else {
        $body = Get-ByteRange $data $pos $c
        $dst = New-Object byte[] $u
        $final = 0
        $st = [PP.Nt]::RtlDecompressBuffer(3, $dst, $u, $body, $c, [ref]$final)
        if ($st -ne 0) { throw ("RtlDecompressBuffer NTSTATUS 0x{0:x8}" -f $st) }
        $out.Write($dst, 0, $final)
    }
    $pos += $c
}

That is the decompressor. Nothing to install, nothing to compile, and it runs under Windows PowerShell 5.1 on a machine that will not let you install anything.

The $u -eq $c branch matters more than it looks like it should. Incompressible chunks show up regularly in column data, and if you hand one to the decompressor anyway you get back a status code that is easy to read as a parsing bug in your own code.

Decompression Was Never the Hard Part

This is the part I want to be straight about, and the reason I called it a small realization rather than a breakthrough.

Decompressing the bytes came to about thirty lines. Working out which bytes to hand over is most of the script, and that is where the months went.

The backup stream splits its bookkeeping across two XML documents, and neither one is enough on its own:

You join the two on the storage path. Before that you have a pile of byte ranges with no names, and a list of names with no byte ranges.

Around that sit the details that corrupt quietly rather than failing loudly. The backup-log header runs from byte 72, immediately after the signature, up to byte 4096, as null-padded UTF-16 XML. The ErrorCode flag in that header means every embedded file carries a four-byte trailer that is not part of its content, so if you miss it, every file you pull out is wrong at the tail and still decompresses without complaint. ApplyCompression decides whether the chunk loop above applies at all.

Written down like that it looks straightforward. Finding it was not.

Where This Stops

The script handles the uncompressed ABF case, which covers Power Pivot workbooks and older .pbix files. It does not handle XPress9, which newer and larger models use to wrap the whole backup stream.

XPress9 is a different algorithm and the Windows compression APIs cannot decode it, so that is where the free ride ends. pbixray carries a Cython port for that path, and the port exists precisely because there was no shortcut to find.

So the split is:

The script recognises the second case and says so instead of producing garbage.

The Script

It had been sitting in pbixray/utils for a while, so I gave it a home of its own:

Install-Script -Name Show-PowerPivotModel
Show-PowerPivotModel -Path .\Book.xlsx -ListFiles
File   : C:\models\Book.xlsx
Member : xl/model/item.data  (4913152 bytes)
Format : uncompressed ABF  (winapi xpress8 path applies)

== Backup log ==
  Object            : Microsoft_SQLServer_AnalysisServices
  ApplyCompression  : True
  ErrorCode trailer : True
  Embedded files    : 214 (header says 214)

Source is at Hugoberry/PowerPivotPeek. One file, MIT licensed, and deliberately unambitious. It lists what is inside the model and decompresses one file at a time so you can look at it.

pbixray is still where the real parsing lives, and it is what you want for tables, relationships, measures and reconstructed rows. This is for the step before that, when you have a workbook in front of you, you think there is a model inside it, and you would like to see the shape of the thing without installing a Python toolchain on a laptop that will not allow one.

Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
Next Post
PBIXRay for macOS: Open and Inspect PBIX Files on a Mac