Our customer is a leading Nordic finance and general insurance company operating in Norway, Sweden, Denmark and the Baltic countries. It is the second-largest financial company in Norway with 3000+ employees working for them. The customer has been providing insurance services for nearly 200 years.
Customer’s departments are located in different countries, so they use different human resources management (payroll, time and attendance) software with poor integration between them.
The main objective was to create a powerful tool to provide analytical reports on absences and payroll transactions. The main challenge was to perform integration of source data so that the data in the reports would be comparable and presentable.
First, we had to get deeply into the business logic of four different source systems to merge their data. There were some issues, such as creation of managers' hierarchy based on a cross-referenced tree, dimensions uniqueness definition (a unique set of fields between source systems to map data), and company structure hierarchy which was changed over time.
Then, we designed architecture of the product: it consisted of two main parts, deployment and reporting. The deployment part is used for extracting the data from all of the data sources, transforming combined set of data and loading it to data warehouse. The reporting part of the product is OLAP-cubes upon the data warehouse. OLAP cubes are powerful and convenient tool that provides many cool features to analyze data like filtering facilities, aggregation by chosen dimension, etc.
There are two types of data sources for the project: SQL Server databases and flat files that source human management systems produce via their export routines.
Deployment starts every night by SQL Server Agent’s Jobs Scheduler. Nightly deployment guarantees a consistent and stable data set. Such frequency is enough for the customer’s needs. It takes about 45 minutes on production environment to run, so there has not been a need to implement an incremental load so far.
There are two main parts of deployment: ETL (Extract, Transform and Load) and OLAP-cubes processing. ETL part is built on SQL Server Integration Services as ‘Extract’ and ‘Load’ parts, SQL Server stored procedures as ‘Transform’ part mostly. Data Quality Control is implemented by means of SQL Server stored procedures also and produces log-files with lists of inconsistencies. Such a list is a rule for a responsible person to fix inconsistency in the source data.
We created two OLAP-cubes on SQL Server Analysis Services database separate for Payroll transactions reports and for Absences reports. Excel PivotTable was used as a client report tool.
MDX-queries were used to filter out report data in user-sensitive manner so that a user could see only the data that is supposed to be seen (for instance, a department manager sees only subordinates’ data).
The solution provides our customer with a convenient analytical reporting tool. It supplies the customer with the key to integrated reporting data.