Phone:

+420 257 288 211

Pension Insurance Statistic for ČSSZ

Whenever several people gather in the Czech Republic, sooner or later the discussion turns to the tense situation of the state budget. Unpopular austerity measures are linked to the necessity of social expenditures, which comprise approximately 40% of the state’s total budgetary spending. Therefore, it is very important for the government and parliament to have at their disposal precise and detailed statistics on the development of social entitlements. KOMIX significantly contributed to fulfilling this requirement by building a system for processing these statistics for the Czech Social Security Administration.

Client:

Česká správa sociálního zabezpečení

Characteristics of the customer

The Czech Social Security Administration (hereinafter referred to by its Czech initials “ČSSZ”), which is subordinate to the Ministry of Labour and Social Affairs, is the biggest – and completely exceptional – financial-administration institution of the state administration of the Czech Republic. Its exceptionalism is due to both the size of its budget and especially its responsibility for disbursing a major part of social entitlements, which comprise a significant item in the state budget (specifically 38% of total expenditures). ČSSZ administers the affairs of more than 8.3 million clients, of whom more than 2.8 million are pensioners.

ČSSZ itself, or in cooperation with employers, provides sickness insurance, financial assistance during maternity leave, supplementary contributions during pregnancy and maternity leave and family medical leave for practically the entire working population of the Czech Republic. For the government of the Czech Republic and fiscal management of the state budget, it is very important to conduct statistical work that expresses trends in the area of social entitlements for pensioners, the unemployed and those who are unable to work.

The customer’s initial situation

Work on the initial phase of pension reform culminated in the mid-1990s. The manifestation of these systemic changes was the drafting of the Act on Pension Insurance, which came into force on 1 January 1996. One of the results of the reform was a requirement for the fundamental innovation of the statistical system in the area of pension entitlements. The result was the DSTAT project, the objective of which consisted in integration of data from a number of application modules and provision of the possibility to formulate outputs for operational, statistical and reporting purposes. The main objectives of the project included:

1) consolidation of the storage of data pertaining to pensions in a statistical database, which would serve as the basic source for the display and further processing of such data.

2) the possibility to create dynamic statistical outputs according to the users’ requirements. This involved not only strictly defined outputs (statistical yearbooks), but also the building of an available query system that would enable the realisation of special sets (ad hoc queries).

The importance and demanding nature of the project is documented by the fact that it was financed with funding from the European PHARE programme.

Our solution

Origins of the DSTAT system in 1995

The system’s architecture was standard three-layer, comprising a data warehouse and the logic and presentation layers of the application. Statistical indicators of 2.5 million pensioners and 3 million disbursed pensions were transferred into the data base within the initial operation.

Users could access aggregated data in two ways: either with an ordinary web browser for the simplified presentation layer (thin client), or via an installed application and fully-fledged presentation layer (thick client: Komix Warehouse proprietary application for creating multidimensional objects and presentation of data in the MS Excel environment).

At the beginning of 1996, when the new Act on Pension Insurance came into force, KOMIX put into operation the full DSTAT system, which automatically processed statistics on the number of pensioners and the amount of pensions according to the types of pensions and the regional, time and age structure.

Deployment of the QlikView technology for the presentation layer

The advantages of three-level architecture – with the possibility of relatively independent modification and development of individual layers – became fully apparent in 2009. The presentation layer of the DSTAT system was transferred to the QlikView environment, which on one hand enabled users to create their own analyses and reports without having to depend on programming capacities and, on the other hand, appreciably increased the speed of data processing and the system’s time of response to entered queries. The actual database foundation was unaffected, as it remained in the Informix environment.

Transition of the database environment to Oracle technology

In 2012 ČSSZ entered the next phase of unifying technology platforms, which involved the database environments. For this reason, the DSTAT system’s database environments were transitioned from Informix to Oracle technology. All data pumps were transferred from proprietary tools to the standard ETL tool, which had the result of better documentation and easier maintenance.

Evaluation – benefit for the customer

Thanks to the DSTAT system, the Czech Social Security Administration maintains and administers statistics from the area of selecting fees for social-security, pension and sickness insurance. The system regularly processes data into a Statistical Yearbook from the area of pension insurance – an important document by which ČSSZ presents itself to the public.