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

Art der Arbeit Bachelor Thesis
Auftraggebende SunGard (Switzerland) S.A.
Betreuende Dozierende Witschel, Hans Friedrich
Keywords OLAP, Performance, Business Intelligence, Microsoft SQL Server Analysis Services, Multi-dimensional Cube
Views: 14 - Downloads: 2
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)
Vertraulichkeit: öffentlich
Art der Arbeit
Bachelor Thesis
Auftraggebende
SunGard (Switzerland) S.A., Zürich
Autorinnen und Autoren
Weber, Remo
Betreuende Dozierende
Witschel, Hans Friedrich
Publikationsjahr
2012
Sprache der Arbeit
Deutsch
Vertraulichkeit
öffentlich
Studiengang
Wirtschaftsinformatik (Bachelor)
Standort Studiengang
Olten
Keywords
OLAP, Performance, Business Intelligence, Microsoft SQL Server Analysis Services, Multi-dimensional Cube