Prologika Optimizes a Large 2.5TB Cube

case-study01

Customer: Fortune 50 organization

Industry: Software
Customer Profile

This is a multinational technology company that develops, manufactures, licenses, supports and sells computer software, consumer electronics and personal computers and services.

Value to Customer

Faster decisions from strategy reports.

Reduced query execution times from minutes to seconds.

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.