A BUSINESS INTELLIGENCE SYSTEM FOR SCHOOL MANAGEMENT ON LOCAL GOVERNMENT LEVEL

Original scientific paper This paper presents one approach to logical modelling of business intelligence system which should help school management at local level. The use of automated tools for decision makers becomes essential due to the exponential increase of data from several and dispersed sources. As the basis for the creation of a model, Balanced Scorecard method is used, whose dimensions are slightly modified to be suitable for the needs of school management. Created is a data warehouse with defined fact tables and dimension tables, and proposed is a technical solution, adequate to the needs and size of local government. For the application of the model, any founder (local or regional administration) should enter master data, realized values and the scope of activities for its school system, and as a result, through the OLAP cubes, it is possible to analyse main indicators in financing, efficiency and excellence.


Introduction
Since 2001, based on several legislative acts in Croatia, the regional government took over from the state financing of schools, current and investment school maintenance, reconstruction, expansion and construction of school facilities, according to the defined standards [1].The regional and local governments also carry out other programs in the school system, in the area of broad public needs, which by their scope, available financial resources and the importance, are strategic projects (public-private partnership, the cash pool, energy efficiency, free transportation for high school students, scholarships and loans, EU projects, centres of excellence, the students' entrepreneurship and green office programs).It introduces a new dimension in the organization and management of education at the local level [2].
The implementation of the above activities and programs is monitored using various computer programs, in different places and with different participants.Local government, after almost 15 years of management of elementary and secondary schools in its area, has a certain amount of data, and daily creates new data, both in the own information system, as well as in schools founded by it.A certain amount of this data will never be used.In a situation where the education does not have enough money, the question is, can the data containing information on financing be used to make business decisions, in order to ensure equity in funding.Also, can the data about other programs and activities, carried out by regional and local governments and schools, provide information on the effectiveness, or how to encourage excellence and provide specific funding for stimulating and project financing of schools.Decision makers can take action and follow paths from intuition; however, the use of automated tools becomes essential due to the exponential increase of data from several and dispersed sources.To organize and display this data to meet the perspective of executives is the purpose of Business Intelligence (BI) solutions [3].Business-intelligence has proven to be an effective means of improving the decision-making within an organization.While traditionally applied to profit making private companies [4], BI has recently been applied to public institutions as well.
Based on the above, setting up a model of business intelligence is a new dimension in monitoring, management and operation of education at the local level.After having an insight into available references, it may be noted that there are no articles with research in the area of modelling business intelligence systems to support decision-making in the school system at the local level.
There are just a few examples of the business intelligence application in higher education [5,6] or application of some commercial BI tools [7].The aim of this work is to develop a model of business intelligence system to support decision-making in the school system at the regional and local level, which will provide the right information to the user, at the right time, in order to manage schools in an efficient manner.The introduction of business intelligence is a strategic management support, as well as the implementation of adequate activities for improvement and progress, and initiates the process of learning.The process of analysis and developing the capital budget is one of the most important activities of management and the management's decisions regarding the capital budget may have impact on organization for many years [8].
Business intelligence (BI) can be defined as a set of mathematical models and analysis methods to systematically exploit the available data to recover useful information and knowledge to support complex decisionmaking processes [9].BI refers to various concepts, methods, processes and software applications designed to improve business decisions by analyzing raw data, querying, making aggregations, forecasting and correlations, data mining, online analytical processing, and producing reports [10].
The proposed model for Business Intelligence should enable users to create their own business analytics, require minimal intervention and maintenance from the IT and minimal period of learning.It is important to note that the business intelligence systems that need and use large systems with large amounts of data, differ from business intelligence systems that need and use small and medium systems with smaller amounts of data.To use the strategic potential of business intelligence methodologies, organizations should return to active forms of support for decision making, based on systematic adoption of mathematical models capable of converting data into knowledge, not only information [11].

Methodology
The research comprises an empirical study on school management on local level, based on which the BI model is proposed.The purpose of this empirical research is to reduce the effort and duration of budget implementation, as well as more equitable distribution of the budget, in accordance with national and regional strategy in education.In this empirical study a combination of quantitative and qualitative research was used for analyzing all aspects of school management.Research methods used are statistics analyses and case study methods based on archives.The case study was used explicitly for understanding the causes and effects of different objectives and measures.The results consist of a set of requirements for a business intelligence model dedicated to school management on local level, and an analysis of significant factors underlying performance differences between projected and actual achievements, to define the structure and the methods compatible in developing the solution.The methodology for BI modelling used in this paper is based on three phase approach, as described in [12].
The first phase is defining key performance indicators (KPI), value and level of business intelligence for related area, as well as collecting, classifying and organizing the documentation regarding the application field and identification of the project needs as a list of items.Phase two is creating a design and navigation prototype, and phase three is implementation and improving of the model.For KPI monitoring, the Balanced Scorecard method (BSC) is used, which in non-profit organizations connects more goals to measure the overall success of the business system in a balanced way [13].The study was based on a sample of 33 primary and 14 secondary schools established by the Varaždin County in Croatia.In total, these schools count 19.000 students and 2800 employees that are teachers and staff.
Analysing the vision, goals and strategy in the area of primary and secondary school system, identified are the key indicators or measures that can achieve established goals, and they are grouped into logical groupsperspectives.Used perspectives in this model are slightly modified, compared to the traditional, and they are finance, equity, efficiency and excellence [14].
Financing is a key activity in the strategic management of primary and secondary education, and therefore the focus is on the perspective of finance.It should answer the question: How to provide financial resources to enable the core mission?
Equality in primary and secondary education is viewed through financial, educational and generational equality, and the perspective of equality should indicate to which areas, programs and activities attention should be paid to enable the educational system of equal opportunities?The effectiveness of primary and secondary education can be seen through the increase of results without increasing funding.Such approach is based on improving existing systems through measures and activities in the field of equality and through programs of excellence, and perspective efficiency should indicate those activities that should be implemented in order to achieve the intended objectives.Excellence is defined through seven areas of work in schools that are extremely important for the functioning of education: lifelong learning, attitude towards business on the principles of good management, encouraging learning for competence in the real sector, promoting sustainable development, informatization, introduction of project financing and working with talented students.The perspective of excellence shows which areas, programs and activities should be used to ensure strategic advantage.Objectives, measures, limits and activities are defined for each perspective, and each perspective is shown at the level of strategic decision-making, management and operational performance.In the BSC method it is common to use about twenty measures (in this study 22 measures are used), which at first glance does not seem complex and they are considered to be traced manually, or using simple office tools.However, when these measures are distributed through different areas, and when it is necessary to collect data, create reports and distribute them throughout the organization, then the problems become more complex and the organization then uses software tools and develops computer models.Finally, the proposed BI model for decision support in school management, on the highest level, is shown in Fig. 1.The entrances to the BSC model are based on analysis and mathematical models.BSC model is checked and improved using the tools of business intelligence (BI) on the data warehouse.

Business intelligence tools perform advanced analytics over the data warehouses (DW).
A data warehouse is a subject-oriented, integrated, time-defined and unchangeable set of data, intended to help management in decision making.It is a copy of transactional data specifically structured for analysis, and includes all processes that are associated with the construction, use and maintenance of data warehouses.
This paper analyses the data warehouse used by Department of Education, Culture and Sports of the Varazdin County in the management of primary and secondary school system.Analysis of the data obtained from the data warehouse will serve as the feedback correction for BSC model of strategic management.
To this end, in the data warehouse are collected and analysed data in the field of finance, insurance of equity, efficiency and excellence in organization of education, to be able to answer questions such as: • How much energy is spent by a school in a certain time period?
• How many funds certain school has spent for electricity up to a certain period?
• What are the planned funds for materials and energy per year?
• How many funds has the individual school spent for the services of phone and mail per year?
• How much are the secondary schools spending on phone and mail services by a certain date?

Building a data warehouse
DW consists of a set of related tables populated with subject-oriented, integrated, denormalized, time-variant and non-volatile data from more sources, which can be viewed and analysed using a variety of tools.
The tables can be of two types: dimensions (i.e.employees and products, they answer questions like who, what, when, where holds values that describe facts), and facts (refer to the dimensions, hold numerical measures to quantify and answer the question how much).Both require a surrogate key, a new code usually, used instead of any composite key as primary key [15].
Data warehouses typically use a design called OLAP (On-Line Analytical Processing) which implies simpler structures, easier and faster to work with.The number of tables and connections is reduced, and data are denormalized [16].The first step in building a data warehouse is the definition of requirements which the data warehouse must meet and the development of the logic model that will enable the required functionality.The data model that is used to create a logical model of a warehouse data is a dimensional model.In dimensional model, the central table is the table of facts that stores data to be monitored and measured.Proper selection of fact table details is essential for the success of a data warehouse project.The fact tables are the place where numeric business indicators are stored.They also have the key, but it is complex and consists of dimensional tables primary keys.Each table of facts corresponds to a single business process that is monitored.In this model, the processes of financial management, ensuring equality in primary and secondary education, maintaining the efficiency of the school system and business management excellence are monitored, and the appropriate fact tables are defined for them.
Table of facts FINANCE contains information about the planned resources for decentralized functions, the wider public needs and realized expenditure by subgroups of the budget, as well as the foreign keys (FK) for connecting to the dimension tables (budget, time, schools, territory, type of school and sectors) in the "snowflake" structure, as shown in Fig. 2.

Figure 2 The tables for model Finance
The data in this table refer to the plan (planned funds from decentralized functions, planned funds from the county for programs of wider public needs, the amount of financial resources provided for non-financial assets and equipment for primary and secondary schools, the total amount of operating expenses, index of increase in funds compared to the previous year, the share of operating costs in the budget for decentralized functions), source of funding (source revenue of budget, decentralized funds, EU funds), execution (amount of used funds from the decentralized functions, for programs of wider public needs from the County sources of funds, for non-financial assets and equipment for primary and secondary schools and the total amount of operating expenses) and date of entry.
Table of facts EQUALITY (Fig. 3) contains data about the number of students, classes, employees and the number of enrolled students per year, the number of inhabitants of a particular village or school area, population density.It contains all the necessary data for the observation of financial, educational and generational equality, as well as the keys to connect with the dimension of tables of schools, budget, territory, time, type of school and school sector, and dimensions typical for this process: demographic indicators and a network of schools and programs.The data in this table are the facts related to the amount of minimum funding for business schools that are calculated based on the number of classes, the number of teachers, working in shifts, owning a sports hall, number of students, graduates and so on.
Table of facts EFFICIENCY (Fig. 4) contains data on paid financial obligations for the decentralized functions and the wider public needs, the number of approved projects for structural and social funds, the number of mobility projects, the number of energy reconstruction projects, the number of registered projects, the amount of funds to individual projects, etc.In addition to the previously mentioned common dimension tables that are found in this model too, tables of specific dimensions are: energy efficiency table and table of EU projects, which contain data that will be used to set criteria related to the success of project implementation (Fig. 4).
The process of managing business excellence is defined and shown through the seven areas of work of the school: the excellence of management, the excellence of operating expenditures, the excellence of student entrepreneurship, the excellence of green programs, the excellence of e-programs, the excellence of EU programs and excellence of gifted students.All this data are contained in the table of facts EXCELLENCE (Fig. 5).
It is associated with several tables of dimensions, common and special, such as the dimension of tables to determine the boundaries of excellence of talented students, for e-business, for students' entrepreneurship and cooperatives, operating expenditures excellence, excellence of EU projects, excellence of green programs and excellence of management stuff, as shown in Fig. 5.
The dimension tables hold data related to each dimension.Dimensions provide context to the facts.Dimension tables have a number of textual attributes that are later used to set restrictions on queries.

Analytics
For a computer model of business intelligence in school management, symbolic OLAP cubes are developed, based on previously defined logical dimensional model.
Symbolic models for cube Finance and cube Equality are shown in Fig. 6 and Fig. 7, and they differ in the fact tables.A more complex problem is to present the symbolic OLAP model of efficiency, because it has five dimensions, and therefore it is displayed in the form of a five pointed star, (Fig. 8).In order to rapidly adapt users to this system, instead of complex SQL Server Analysis Services (SSAS), which is a specialized analytical system for managing databases and for OLAP analysis, as a part of the SQL Server 2012 Standard, for small and medium-sized organizations is recommended the use of Microsoft Power Pivot 2013 for Microsoft Excel 2013, on the client side.Using business intelligence on the client side has its limitations, but the flexibility offered to end-users surpasses limitations.Business intelligence solutions on the client side are intended for small and medium systems that have not more than 5 million rows of data.
The cube that is created is available on the server and by launching PowerPivot table it is possible to connect to the server and access the data.Also, data can be exported to an off-line OLAP cube and processed offline.
An example of pivoting tables based on OLAP cube finance model is shown in Fig. 10.

Application example and results
Implemented model of business intelligence provides the possibility of analysing customer data, in this case, primary and secondary schools at the local (regional) level.First of all, these are the data relating to the financing: • Expenses (why the school has an expense, how often it spends, how the school makes a decision on certain expenditure, how the school spends, etc?).
• What are the characteristics of spending during the lifetime of school?
• What happens outside the school, and has an impact on the behaviour of school expenditures?
• What are the expectations of school after spending?
In addition, the model allows the so-called "competitive intelligence": • monitoring of the actual, and predicting the future state of primary and secondary education in the region The principle of "learning from the best" is used, and the main information is obtained from publicly available materials and personal contacts.These data are analysed by comparing to the "best", according to the criterion of the budget, the salary of employees, equipment in schools and pedagogical standards.Based on this analysis, the plan of changes, or improvements, is developed.
Finally, the proposed model performs analysis of the success of business process management.Increasing efficiency has a direct impact on the financial performance of the business.One example is the area of energy efficiency, where investments in the energy efficiency of schools directly reduce costs of energy, which are one third of total material costs in education, in the given region.The analysis showed in which schools the curve "financing criteria" is different from the curve "energy consumption", as shown in Fig. 11, for high schools in the given region.
Activities undertaken on the basis of previous analysis, caused changes in the structure of expenditures for energy compared to the previous period, reduced costs for heating.Cost reduction in primary and secondary education leads to business excellence.

Conclusion
Original scientific contribution of this paper is reflected in the fact that shows how the well-known managerial (BSC) and IT tools (DW) can be utilized in so far unused way in the field of education, in order to construct a model for assistance in strategic and operational school management.
During the preparation of this model, a comprehensive analysis of the school system at the local level is conducted, in particular analysis of the structure of the school budget.Mathematical model for the financing of education at the local level is created, which gives a clear picture of the relationships within the scope of activities of the school and the relationships between schools.Based on this, key performance indicators are defined, which will be traced and analysed.
A model of business intelligence systems in education at the local level is proposed, which helps the founders to create their own business analytics with minimal background maintenance and with a brief period of learning.The model follows the process of financial management, the processes of ensuring equality in education and efficiency of the school system, as well as business excellence management process.The presented model is universally applicable.It is intended for founders -Local and Regional Administration.For the application of the model any founder should enter realized values, master data and the scope of activities for its school system.
The possibility of further development of this system exists in the area of data mining, when enough historical data is collected, and in the possibility of connecting with databases that are not owned by the local administration.Development of the system in these areas will enable obtaining new information and knowledge that will be used to support decision-making.

Figure 1
Figure 1 BI model in school management

Figure 3 Figure 4
Figure 3 The tables for model Equality

Figure 5
Figure 5 The tables for model Excellence

Figure 6
Figure 6 OLAP cube Finance

Figure 7
Figure 7 OLAP cube Equality

Figure 8
Figure 8 OLAP cube EfficiencyThe symbolic model of excellence cube is shown in Fig.9.Because this model has 9 dimensions, it is presented as a 9-spoke star.

Figure 9
Figure 9 OLAP cube Excellence

Figure 10
Figure 10 An example of Pivot table

•
reviewing of assumptions for such predictions • proactive detection of deficiencies in future conditions in primary and secondary education • adaptation and timely modification of the strategy of primary and secondary education.