Vous êtes ici

Agile Business Intelligence & Data Warehousing in the Public Sector

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 number of OLAP cubes were defined, 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 temporarily continue to use Microsoft
Excel. In a later phase, the standard front end tool of the Flemish Government
will be used to unlock the data.

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.

Related Technologies