Optimizing SSAS (SQL Server Analysis Services) OLAP cubes

Business intelligence solutions do not work forever. They degrade over time, grow in data, are introduced small changes that in many cases deteriorate response times. The company begins to demand more. Many more reports are generated. In short, what used to work before makes it worse and worse.


Customer: Industrial company


Need: Data from the company's multidimensional cubes must be available at all times, with reasonable response times for users.


Previous Situation: The client applications for generating reports and dashboards worked very slowly and the OLAP server was unavailable many times.


Implementation: A system performance study was carried out by activating and generating traces of the SSAS server to detect where delays and blockages were occurring. The multidimensional model was studied to relate the delays with the traces and to identify time consuming mdx queries. When conclusions were drawn, changes were introduced that did not entail major disruptions to the service in order to improve it as much as possible. In a pre-production environment, changes were finally introduced in the design of the cubes that were deemed necessary to improve performance (new grouping of dimensions, new partitions, new added measures, ...) and the relevant tests and changes were made to meet the needs of the company. The process of updating the cubes was optimized and automated with Integration Services (SSIS).