OLAP Performance Optimization

The OLAP performance optimization analysis focuses on the optimization of the processing and query performance within SunGard's risk analysis solution named Adaptiv Risk Cube. The document identifies and describes potential optimization methods and discusses the results for the most promising ones.

Weber Remo, 2012

Bachelor Thesis, SunGard (Switzerland) S.A.
Betreuende Dozierende: Hans Friedrich Witschel
Keywords: OLAP, Performance, Business Intelligence, Microsoft SQL Server Analysis Services, Multi-dimensional Cube
Views: 7 - Downloads: 1
The Adaptiv Risk Cube stores financial report data in a multi-dimensional cube structure and is an integrated part of SunGard's product suite. The goal of the analysis conducted was to identify and evaluate potential performance optimization methods and in collaboration with the project supervisors define the methods to apply in order to perform actual measurements in the cube.
The documentation on OLAP performance was reviewed and potential optimization methods were identified. From the identified optimization methods, the potential performance test cases were defined and evaluated by applying a scoring system corresponding to the expectation of any performance gain as well as the complexity of their application. Subsequently, four optimization methods were applied to the cube separately and the impact of the respective optimizations in the cube measured and documented in detail for both cube processing as well as query performance.
The most promising results obtained during the tests were achieved with cube partition merging as well as the modification of aggregation designs. The query performance over all test queries increased by up to 42% on an optimized aggregation design. Relative reductions of the cube processing time of 13% and query execution time of 15% were achieved with partition merging alone. As a result of this analysis, SunGard has a reference system with concrete measurements and evaluations on how the tested optimizations methods impact their Adaptiv Risk Cube solution in an actual client environment. Derived from the findings, the key points can be added to an optimization guide, which can be used to recommend certain optimization strategies for increasing the performance within the Adaptiv Risk Cube. Additional tests can be conducted employing further optimization methods identified, but not tested in the context of this paper.
Studiengang: Wirtschaftsinformatik (Bachelor)
Fachbereich der Arbeit: Wirtschaftsinformatik & IT-Management
Vertraulichkeit: öffentlich
Art der Arbeit
Bachelor Thesis
SunGard (Switzerland) S.A., Zürich
Autorinnen und Autoren
Weber Remo
Betreuende Dozierende
Hans Friedrich Witschel
Sprache der Arbeit
Wirtschaftsinformatik (Bachelor)
Standort Studiengang
OLAP, Performance, Business Intelligence, Microsoft SQL Server Analysis Services, Multi-dimensional Cube