The Fastest Cubes in the East

Around 2010, OLAP cubes were a common way to support analytics. They were the fastest way to do interactive analysis over a fixed set of "dimensions". They could be connected to Excel, Power BI, or custom dashboards.

One of the popular solutions was Microsoft's SQL Server Analysis Services, or SSAS. SSAS was well integrated with the Microsoft ecosystem (SQL Server, SSIS, etc.) and so was a popular default choice for companies using the SQL Server stack.

I was working at a company that used OLAP cubes for core marketing optimization tasks. One of our biggest challenges was ensuring that cubes updated early enough for analysts to update bids as soon as they came in the morning, while ensuring the cube was always available.

As the cube sizes grew towards 1 TB and beyond (Yahoo's 24 TB cube being a frequent nightmare scenario), we pulled the typical levers for improving cube processing time and query performance:

• Heavily leverage incremental updates
• Schedule expensive full updates on the weekend
• Use a SAN storage-based snapshot model to ensure that the cube was always available during the update process
• Use multiple frontend servers to serve the same processed cube (see snapshot)
• Split our backend processing across multiple SSAS servers
• Split cubes into multiple smaller cubes

Where else to optimize?

The other lever we had was to start our processing operations earlier. This was a challenge as updating a cube was downstream of a tangled processing graph responsible for creating all the cube dimensions, using those to create fact tables, and then finally kicking off processing. Manually resolving the optimum processing order was challenging, and we suspected that we were leaving processing time on the table. This ETL graph could span multiple databases.

We manually reviewed this dependency graph and tried to optimize it, but it was a time-consuming process, and we were never sure if we had the best solution.

The Solution

The solution was to remove any form of manual dependency analysis from the equation. We created a system that did the following:

• Parsed a cube XMLA on commit to extract all table dependencies
• Resolved each table dependency to a set of source tables (and partitions!)
• Created a directed acyclic graph (DAG) of all tables and their dependencies and every cube processing task (dimension update, partition incremental update)

We were bottlenecked by needing to run all the cube dimensions at once, so that became the first optimization criterion. After that, we'd want to unblock each partition update step as soon as the underlying SQL data was updated, and we'd run these jobs in parallel.

This system expanded to cover all dozen cubes we were running. We expanded this analysis upstream to the parent jobs that fed into cube creation, and we had an automatically optimized processing graph that we could run at any time. We had a nice long-pole analysis we could use to target partitions for specific optimization.

Implementation

Our parsing and orchestration system was built in Python and run as a service. We used a mix of shell calls to PowerShell and Python ADO.NET bindings through pythonnet to communicate with the cube servers. Python coordinated the other parts of the update process, including snapshots, data quality checks, upstream ETL, and notifications to end users.

All of this was implemented via an SSIS package scheduled through SQL Server Agent on a core processing server our team owned. When you have a hammer...

Optimization

The system reported the long-pole path for the overall cube update, which could be determined by just converting the DAG to a tree and weighting by runtime to get the longest path. This enabled teams to focus on the most important optimization paths.