This case study will discuss the implementation of a Business Intelligence environment and corporate data warehouse at OCMW Brugge, the Public Welfare Centre of the city of Bruges.
In our rapidly evolving society, the Public Welfare Centre (CPAS-OCMW) of Bruges has a mission to help people to live a life in compliance with human dignity and to have access to social security and assure help. As a public center for social welfare, OCMW Brugge in the first place tries to be an open door organization, where everyone should feel welcome. Easy accessibility and a range of services make the Public Welfare Centre (CPAS-OCMW) an approachable organization for everyone in the community, where the employees are ready to find a suitable solution and strive to give everyone the right to a dignified existence.
The range of services that the Public Welfare Centre (CPAS-OCMW) Bruges offers is vast. Here is a small overview:
- Housing and (Health) Care
o Social housing
o Services flats
o Day care centers for adults
o Residential guidance
o Elderly care (woonzorg centra)
- Financial and Social
o Debt mediation
- Home care
o Meals at home
o Domestic help (cleaning and ironing)
o Shopping services
- Child and youth
o Day care for children
o Care for sick children
o Youth sports
and many others.
At the start of the project, Public Welfare Centre (CPAS-OCMW) Bruges was facing several problems related to Management Information and Business Insights. They had a vast amount of information residing in several operational systems. A few of these systems had a datawarehouse-like reporting solution from the application vendor, but this silo approach was not sufficient to provide organisation-wide analytics. Next to being limited to the generic solution of an application vendor, a public welfare organization as Public Welfare Centre Bruges also needs to provide social statistical information residing in different of these systems. To create these statistics, a lot of time was spend in manually gathering the information, transforming that information in Microsoft Excel and producing final pivot reports and graphs on top of that information. This approach was very time consuming and error sensitive.
The creation of the new solution should solve all of the current problems:
- Foresee a DWH/BI solution for all applications relevant. At the project start, only a very limited set of applications had some kind of reporting solution. For most applications there is only the administrative, operational application where people can find data & information. For those applications that do have a reporting solution we can divide them in 2 group. The first group were silo reporting solutions delivered by a software vendor. These are generic solutions that cover a lot of the needs, but certainly not all of them. The second group are the reporting applications created by the business users. These solutions retrieve information in a semi-automated way, unstructured and not standardized way and only serve a very limited set of users.
- The Business Intelligence solution needs to be cross source platform. Make sure that information from different source applications can be joined together when needed to create cross-application, cross-process management information. For some of the reports, like the gauge on work pressure in the retirement homes it is necessary to combine the information of personnel, time registration and retirement home residents and their "Katz scoring. The combination of all this information will give an idea on the available workforce vs. the necessary workforce. This is of course only one of the examples in a long list.
- Automation of statistical information production. A lot of time is spent in the different parts of the organization to export the information of the administrative applications to then centralize the information in one place and put it all together. The project goal was to automate this process as much as possible.
The approach to this data warehouse project was slightly different than in most customer assignments.
In most projects, we try to solve a problem/information request by extracting the necessary information from one or several sources. For Public Welfare Centre (CPAS-OCMW) Bruges we choose to have a "by application" approach. This means that -when adding a source system to the data warehouse- the team look at all the information from an application and decided what was interesting/relevant to be added to the data warehouse. Keeping this approach in mind, in combination with the large number of applications that need to be integrated in the data warehouse, a phased approach is desired.
These are the phases that have currently been defined & mainly completed :
- Phase 1: Personnel information from the InfoHos application
- Phase 2: Retirement home and other adult care information from the RBS application together with the hour registration information from SDP
- Phase 3: Social services information concerning debt mediation from the New Horizon application, together with information on the process starting from the first appointment till the final decision in a social dossier
- Phase 4: accounting information from the New Horizon application
- Phase 5: remaining information from the New Horizon application
In the next section we will describe more in detail the phases that were already executed, or are currently being executed.
Phase 1 - HR & Personnel information
During the first phase we loaded the information from the InfoHos application. This gave us the necessary information on the personnel of Public Welfare Centre (CPAS-OCMW) Bruges. Next to the personal information of personnel, the InfoHos application also had information on the contracts of personnel and the working places within one contract, all quantified in number of hours per week. Next to that information also illness information of the personnel and -after the wages calculation- the salary and worked number of days could be loaded from InfoHos. Since all this information had to be compared to the budgets in both FTE and monetary value -determined by different department- within the Public Welfare Centre (CPAS-OCMW) Bruges, also this information was added to the data warehouse.
As a result of all that information, several types of Business Intelligence reports could be created:
- Statistical reports on the personnel demographics. (Count by gender, citizenship, age groups )
- Reports on staffing specifically for department with a lot of staff turnover due to contract changes, contract termination, illness and pregnancy leave.
- Budget control per department: actual number of FTEs/salary vs budget
Phase 2 - RBS application and time registration
The RBS application of Cevi supports the administration of retirement homes like RVT (Rust- en Verzorgingstehuis), ROB (Rustoord voor Bejaarden) and DVC (Dag VerzorgingsCentrum).
This application contains important information on:
- the residents and their admittance
- their Katz scoring
- room occupation and room changes
- invoicing to the residents for their admittance and care
- invoicing to the residents of non-care products like drinks, cable TV
- invoicing to RIZIV
- leave from retirement home because of hospital admittance or holiday
With this information a whole range of reports and analysis can be created - that can help both the people on the floor and management on all different levels- in the Public Welfare Centre (CPAS-OCMW) of Bruges organisation. Some examples of these reports:
- an overview of residents in house for the next weeks
- Error reporting that will facilitate the quarterly RIZIV invoicing like
o Missing Katz scoring
o Incorrect input of information like admittance
o Leave from retirement home larger than x number of days
- Missed Riziv invoice days because of the start or end of admittance
- Vacancy of rooms based on:
o Resident is in hospital/on leave
o Clearing the room after departure
o Remodeling and works in the room
o Normal vacancy, room is available
- Waiting list based on the Katz scoring
- Statistical information on demographical and geographical information of the residents
Special about this phase was not that this information mas made available to the employees of Public Welfare Centre (CPAS-OCMW) Bruges, because the vendor Cevi also has a silo reporting solution on top of the RBS application. Moving this information to the corporate data warehouse was thus no high priority, since a solution was in place. But combining this information with the personnel information and the planning/time registration -major benefits of a data warehouse- would make it possible to also create the "werkdruk meter" (work load gauge).
This is a very important KPI within the retirement homes to get a good planning on the work floor. The "work load gauge" makes it possible to calculate the number of nurses and care workers that are needed within a retirement home, based on the number of residents and their care needs. To be able to make the calculation information from several systems needs be brought together. We need to have information from the personnel application to know what employees work in which department of a retirement home, and also what their qualification is. Then we need information from the RBS application to know how many residents are actually present in the different departments of the retirement homes and what their Katz score of care level is. Thirdly we need to know how many nurses and care workers are planned for a specific day, and how many are/were actually present at that day. The combination of all that information will give a percentage that is called the "work load gauge". If this percentage is too high, there are not enough employees on the work floor. If the percentage is too low, there are sufficient employees available.
So for this second phase, we also included the information coming for the time registration system of SDP.
Phase 3 - Social services information
For the third phase, we looked at another application of Cevi : New Horizon. Since New Horizon is a rather large application that consists of several sub applications the choice was made to not go for a large iteration, but only take one of the sub applications namely the one for the "Social Services". The application of the social services department registers all requests from people applying for social or financial support. These requests are noted and investigated by the social services and a proposal is made by the employees of the social services. Finally the request is reviewed by the board of Public Welfare Centre (CPAS-OCMW) Bruges and a final decision in the dossier is taken.
Technology used by Public Welfare Centre (CPAS-OCMW) Bruges is the complete Microsoft BI toolset of SQL Server 2008
- Database : SQL Server 2008 DB
- ETL : SQL Server 2008 Integration Services
- OLAP : SQL Server 2008 Analysis Services
- Reporting : SQL Server 2008 Reporting Services
- Ad Hoc reporting: Excel 2007/2010
Most of the sources that we load information from are SQL Server-based. The Time registration application however is an exception. There we load information from object oriented database from InterSystems.
Next to the standard logging from all above tools, a specific setup for report logging was made. In this setup we extract information from the standard reporting services management tables and put that information in our environment. Main reason for working like this is because Reporting Services is only keeping logging information for a limited time.
For element61, this has been primarily a coaching assignment. Public Welfare Centre (CPAS-OCMW) Bruges wanted to become self-sufficient as soon as possible. The request thus came to help them in both conceptual & technical training of principles of setting up a "state-of-the-art" data warehouse architecture & associated Business Intelligence layer. The coaching assignments -lasting several quarters- included:
These aspects are:
- Project management
- Definition of the architecture
- Source analysis & data profiling
- Requirements analysis
- Dimensional modeling
- ETL design & implementation
- Training of the customer staff on dimensional modeling & data warehousing concepts and overall coaching on the chosen BI-methodology
- Training of the customer staff on SQL Server Integration Services, Reporting Services and Analysis Services
- Report & cube development
As a result of the assignment, OCMW Brugge today has considerably automated and improved management information production and decision making, whilst being able to independently continue to grow the information covered by the organisation wide data warehouse environment.