Our customer is a provider of online surveys platform with thousands of clients in most European countries.
The product is a SaaS platform consisting of three main parts: survey creation, survey participation and reporting. The platform has been growing a lot for a few years and now it operates terabytes of data.
Reporting part offers many features: from simple browsing answers to powerful filtering, cross tabulating and combining data. The reporting was initially created in a simple way and it works with transactional database. As data volume grows, the reporting has started to perform slower and slower. Additionally, running a number of complex reports affects survey platform.
In some scenarios, users have to see answers data in reports quite immediately after participants submit answers.
Data warehouse and OLAP cube built on SQL Server Analysis Services becomes the best approach to improve reporting performance and keep survey platform stability.
Changes in live database are tracked with the Change Tracking (CT) feature of the SQL Server database engine. The data is pulled every 15 minutes by an incremental ETL (Extract, Transform and Load) process built on SQL Server Integration Services. The ETL gets changed data, clears and transforms it if necessary, and puts the data into the warehouse. To minimize cube processing time, every ETL execution is followed only by Add and Update processing. Full process occurs only during off-hours. The system utilizes Proactive Caching feature of SQL Server Analysis Services to make the cube available to users during processing.
Reporting application was reengineered to support multidimensional data queries.
The solution suggested and implemented by Arcadia team improves scalability of the reporting and entire survey platform. It helps the Customer to continue growth.