Customer
The European Social Fund (ESF) stimulates Flemish enterprises to reinforce the labor market and increase the employment rate. The fund helps people to transition to new job opportunities or helps them to look for their first job. Enterprises receive assistance in matching the work-home balance for their employees and the structure of the employment organization. Socially vulnerable groups, such as low-skilled employees, immigrants or people with a work disability are involved in this process. In addition, the ESF Agency encourages organizations to be innovative and work internationally.
The European Union is facing a whole new set of challenges ranging from globalization over new technologies to an ageing population. To deal with these potential hazards for employment, the EU calls upon the European Social Fund. This fund helps people to adjust to a changing labor market. Everyone should be able and willing to work: man or woman, young and old, poor or highly educated and independent of culture and heritage.
To aid the Flemish workers, job applicants and organizations to arm themselves against these changes, the ESF receives funds from the European Union and the Flemish Government. The European Social Fund Flanders distributes these resources in the form of allowances amongst organizations that stimulate the Flemish labor market to encourage job applicants to grab new opportunities and to keep employees motivated at work.
Challenges
The employment policies of the EU Member States are coordinated by the European Union. Europe carefully oversees progress to ensure that the Member States achieve their Europe 2020 objectives. Member States that fall behind get recommendations and have to take action upon these recommendations. The EU also grants funds to the Member States to help them achieve the Europe 2020 goals and targets. These funds come from the European Structural and Investment Fund (ESIF). The European Social Fund is one of those ESIF-funds and for Flanders by far the most important.
With the Europe 2020 strategy and framework, the EU has defined a number of thematic objectives and investment priorities, a kind of drop-down menu that forms the basis for negotiations between the European Commission and the member states on the priorities and scope of the different operational ESIF programs. During these negotiations, an agreement is found between what Europe deems mandatory in the program and the top priority for the member state. The priorities for Flanders are reflected in various policy documents such as the Flemish coalition agreement, the policy statements of the ministers, Pact 2020, the Flemish reform program.
Following these negotiations, based on the European and Flemish policy objectives, the ESF program is established. This program will invest nearly a billion euro in the Flemish labor market between 2014 and 2020: 378 million of that amount is ESF-funded.
The ESF application allows all calls and associated projects in its various stages (from concept to closure) to be monitored in detail. It is a custom written dossier management system. In this application, there are already a number of standard reports. The most important report is the boordtabel that is used as the most important instrument for follow-up. During the life cycle of the application a number of standard operational reports and several hundreds of ad hoc queries were written directly on the database. The amount of queries and accompanying long execution times have had consequences for the application performance and availability. In addition, it was not possible to have business users perform ad-hoc analysis.
There was also a need for integrated reporting using additional data sources. On a monthly basis the VDAB (Vlaamse Dienst voor Arbeidsbemiddeling) is delivering data on training that was conducted on ongoing ESF projects (MLP). This MLP data can be linked to a call and a project. The data is used for monthly reporting and to report interim or final figures at any time. In addition, these reports combine the MLP and application data into a single report. The combining of the data and compiling of the reports were a time-consuming, manual process that was mainly conducted in Microsoft Access. In addition, to be able to answer questions posed by Members of Parliament it was necessary that additional data was integrated and compiled into the reports. This processing was also done manually in Microsoft Access and Microsoft Excel.
As a result of the new Operational Program 2014-2020, the reporting requested by Europe has become much more elaborate than before. These Annex reports contain detailed operational and financial indicators, compiled from multiple data sources. Additionally, ESF customers should receive detailed reporting on their projects.
Solution
In 2015, the management made the decision to do an exercise to review how reporting efficiency could be increased. ESF Agency Flanders at that time did not have a BI architecture. The reporting needs were covered by means of the Microsoft Office suite, especially Excel and Access. This solution had its limits in terms of maintainability and the processing of large data volumes. In addition, each report meant a considerable manual time investment was necessary, which could be avoided with an automated solution. The endless combining of information in Access and Excel-sheets also had consequences for the reliability of the information. Each manual step increased the risk of errors.
Following a request for proposal, the element61 proposition was chosen. It featured an agile approach, meaning that priorities and requirements off course within the budgetary framework could be modified to accommodate changing requirements. The project management overhead was limited as much as possible so that the project budget could be maximally used on analysis, design and development tasks. The model of coaching and co-development methodology applied, meaning that employees of ESF Agency Flanders were closely involved at all stages of the project and also played an active role in the development of the solution.
A data warehouse architecture was chosen in which all data sources could be optimally combined to provide extensive, detailed reporting and ad-hoc analysis. A data warehouse has the added advantage of removing the burden of queries performed on the live database which had a significant impact on the application performance. The data warehouse layer was built using the Kimball methodology and a number of star schemas were devised. These schemas can be easily navigated by business users. Star schemas are made up of measured values and dimensions that provide context on the measurement value. They are built specifically for reporting which means that previously time consuming queries can be executed much more quickly. On top of the data warehouse a Microsoft SSAS Tabular model was created, making it easy to do ad-hoc analysis questions.
Technology used
This data warehouse was constructed with TimeXtender on a Microsoft SQL Server 2014. TimeXtender is a tool based on Microsoft SQL Server and greatly simplifies the implementation of ETL. TimeXtender supports the agile methodology and the Kimball data warehouse methodology. As all metadata that is used to generate the ETL code is stored in a central repository database, it is very easy to make changes, even by users who do not have extensive SQL knowledge. By using a drag and drop interface users can easily build star schemes. All the code to create the table objects and fill these with data is generated by TimeXtender. Out of the box, TimeXtender delivers a project repository, full documentation, version control and a graphical drag and drop user interface.
As a front end tool, the customer has chosen to swith to MS PowerBI after temporarily continue to use Microsoft Excel.
element61 responsibilities
element61 was involved in the ESF project for the following roles and responsibilities, in close collaboration with ICT and business:
- Introduction of Business Intelligence terminology and concepts
- Guidance on making the right choices with respect to the BI architecture and solutions
- Installation and support of the various tools (e.g. Microsoft SQL Server 2014, TimeXtender)
- SQL Server expertise
- Design and development of the data warehouse
- Support on the usage of TimeXtender
- Creating provisional reporting in Excel
Conclusion
The data warehouse has made reporting over multiple data sources really easy. The manual workload that was needed for every report has been virtually eliminated as the data is loaded and combined in an automated manner. As an added advantage, the application performance is no longer burdened by queries that were run on the live database. By using the data warehouse, the amount of errors made in the reporting has been greatly reduced as all business users use the same integrated data from a central data warehouse. In a next phase the focus will be on presenting the integrated and easy to report on data in appealing reports and dashboards.
