Tags: ETL, OLAP, SQL Server Integration Services
DB Best recently completed a complex development project for one of the largest banks in the Middle East. Faced with non-customizable and slow-to-generate reporting, the customer contacted DB Best to develop a new and improved solution. The customer’s original system was built on two SQL Server Analysis Services systems, each containing large amounts of various aggregated data types with Excel workbooks directly accessing the two SSAS cubes. Not only did this system take a considerable amount of time to generate reports, but the non-customizable structure proved limiting for the customer.
Check out the video below to see how DB Best used SQL Server Integration Services and a SQL Server database to offload connections to the SSAS cubes to dramatically improve performance of their Excel reports.
Technical solution
To speed up the generation of reports, we decided to make an intermediate SQL Server database which would contain data slices from the SSAS OLAP cubes, typically used for reporting.
To facilitate this, we created an ETL-script to extract data from SSAS systems using MDX-queries and load it into SQL Server database. In the next step, the data from this SQL Server database was transferred into Microsoft Excel reports using Visual Basic Application templates.
Now, the reports are built in accordance to the customer’s wishes, and their structure became easily customizable. Using the prepared data slices in the SQL Server database allowed for much faster report generation. Moreso, all tasks were solved using Microsoft SQL Server Integration Services, so the whole customer’s system continued to work under the Microsoft stack of applications.
For security reasons, another obstacle that we faced during this project was our lack of access to the customer’s servers. However, we still successfully completed the theoretical part of the development project, and made it ready for deployment.
