Prologika Optimizes a Large 2.5TB Cube

Industry: Software

This organization had a large 2.5 TB cube to analyze website traffic. Prologika optimized the cube and reduced the report query execution times from minutes to seconds.

Business Needs

This organization has implemented a large multidimensional cube to analyze website traffic, search results, and ad campaigns. This cube a strategic role in the company. Senior management based their decisions on the results from the cube.

The cube had grown to 2.5 TB, with some 25 billion rows in the fact table. The customer was complaining about long-running queries that would take minutes to execute. They had doubts if Analysis Services can scale.

Solution

Referred to by the Microsoft Analysis Services team, Prologika assessed the cube architecture with a focus on performance and provided remediation steps to optimize the cube performance.

We discovered issues with the way the MDX queries were written. Specifically, the query WHERE clause used arbitrary-shaped sets. They caused performance issue causing the Analysis Services server to scan all partitions. The cube was partitioned by hour and data was kept for 40 days, resulting in 960 partitions. Although a query might need much less data, the server would scan all the 960 partitions, resulting in enormous amount of data being read and processed. As it turns out, the WHERE clause is not optimized to project the filter on partitions. The solution was to replace the WHERE clause with SUBSELECT to bring the query execution time from minutes to seconds.

Benefits

Thanks to the findings and remediation steps from Prologika, the query execution times were greatly reduced and the company’s faith in Microsoft organizational BI was restored.