Big Data Reporting and Analysis

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. The reporting part offers many features: from simple browsing answers to powerful filtering, cross tabulation and combining data.

The reporting was initially created in a simple way and worked with a transactional database. As data volume grew, the reporting became slower and slower. Besides, running a number of complex reports affected the survey platform performance.

In some scenarios, users needed to get the reports on the collected data immediately after getting the answers from the participants.

The best approach to improve reporting performance and keep survey platform stability was using a data warehouse and an OLAP cube built on SQL Server Analysis Services.

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 minimise cube processing time, every ETL execution is followed only by Add and Update processing. The full process occurs only during off-hours. The system utilises Proactive Caching feature of SQL Server Analysis Services to make the cube available to users during processing.

Reporting application was re-engineered to support multidimensional data queries.

The solution suggested and implemented by Software Country's team improved the scalability of the reporting and entire survey platform. It helps the Customer to continue growth.