# Design and Build OLAP Business Intelligence for Village Sustainable Development Planning

Irya Wisnubhadra, Stephanie Pamela Adithama

► To cite this version:

Irya Wisnubhadra, Stephanie Pamela Adithama. Design and Build OLAP Business Intelligence for Village Sustainable Development Planning. 14th International Conference on Social Implications of Computers in Developing Countries (ICT4D), May 2017, Yogyakarta, Indonesia. pp.559-569, 10.1007/978-3-319-59111-7_46. hal-01650114

HAL Id: hal-01650114

https://inria.hal.science/hal-01650114

Submitted on 28 Nov 2017

HAL is a multi-disciplinary open access
archive for the deposit and dissemination of sci-
entific research documents, whether they are pub-
lished or not. The documents may come from
teaching and research institutions in France or
abroad, or from public or private research centers.

L'archive ouverte pluridisciplinaire HAL, est
destinée au dépôt et à la diffusion de documents
scientifiques de niveau recherche, publiés ou non,
émanant des établissements d'enseignement et de
recherche français ou étrangers, des laboratoires
publics ou privés.

<figure><img src="image_1.png" /></figure>

Distributed under a Creative Commons Attribution 4.0 International License
# Design and Build OLAP Business Intelligence for Village Sustainable Development Planning

Irya Wisnubhadra¹, and Stephanie Pamela Adithama¹

¹ Informatics Engineering Department, Atma Jaya Yogyakarta University, Yogyakarta, Indonesia
{ irya, stephanie_pamela}@staff.uajy.ac.id

**Abstract.** The Indonesian government-year period 2014-2019, endorsed the National Medium Term Development Plan (RPJMN) with the concept of building Indonesia from the rural area. One of the RPJMN focus is the development of rural and border areas brings a new paradigm for the village development. This village development paradigm change requires the support of village information system. Village Information System and Rural Areas (SIDeKa) has developed and implemented in dozens of villages in the some district as a pilot project. The transaction data and information in the village have captured accurately using SDeKa. These data and information becoming crucial sources for development planning in the next phase. This paper describe design and build OLAP Business Intelligence for development planning at village and supra village (district) that integrates SDeKa's data from all villages and aligns the development planning with the vision, mission, and objectives of rural development. This paper presents model/architecture for ETL, star schema, and new measures that capture spatial and temporal dimensions. OLAP Business Intelligence will be useful for Village Sustainable Development Planning.

**Keywords:** OLAP Business Intelligence, Sustainable Development Planning, Village Information System and Rural Areas (SIDeKa)

## 1. Introduction

The Indonesian government year period 2014-2019, endorsed the National Medium Term Development Plan (RPJMN) with the concept of building Indonesia from the rural area. The government develops all sectors in order to get a better Indonesia, with the several objectives focus: (a) improve competitiveness, (b) improve the quality of human being, including mental development, (c) utilize and restore the lost potential in maritime and marine sectors, (d) improve the quality of economic growth, (e) reduce the inequality between regions, (f) restore environmental damage, and (g) advance social life. In the fifth focus that is the reduction of inequality between regions, government provides a large focus on the development of rural and border areas [1].

Village authority in the philosophy of "Village Builds" in the government of President Joko Widodo, change the village development paradigm from object becomes the subject of development. The development initially overlapping as an institution, financial and planning is now becoming more consolidated.

Changes in the development paradigm and village authority is the authority on village government administration, the implementation of rural development, rural community development, and community empowerment requires support from village information system. Village information system explicitly has appeared in the Village Law, in article 86 concerning Rural Development and Development of Rural Areas Information System, which contain (1) Village has right to access information through the village information system developed by the local district/city government, (2) The government and regional governments must develop a rural development and development of rural areas information system, (3) Village information system
referred in paragraph 2 includes hardware and software facilities, network, and human resources, (4) Village information system referred in paragraph 2 includes the villages data, rural development data, rural areas data, and other information related to rural development and the development of rural areas, (5) village information system referred in paragraph 2 is managed by village government and can be accessed by village communities and all stakeholders, (6) The local district/city government provide district/city information planning for the village. Village information system then becomes very important.

Rural development should also be aligned with the level of development in the above village level (Supra Desa), ie district/city and sub-district. Development plans at village level should be aligned with the policy and strategy of the district/city, Regional Development Plan (RPJMD), Strategic Plan Working Unit (SKPD), Spatial Planning and Regional Plan (RTRW) District/City, and the planned development of rural areas [3].

In the previous research, the authors have designed and built integrated Village Information System and Region (SIDeKa). The information system developed through strategic planning based on Enterprise Architecture, and priority-based information system development planning [6]. SDeKa has implemented in several villages in districts in Indonesia, ie in Meranti, East Belitung, Boalemo Gorontalo, Pangkajene Islands, Raja Ampat, Gianyar, Pemalang, Tasikmalaya, and Kulon Progo. The transaction data and information in the village have captured accurately using SDeKa. These data and information becoming crucial sources for development planning in the next phase.

The volume of data generated by the SDeKa for daily operations of the different kinds of businesses has experienced an explosive growth. Data warehouses play an important role in helping decision makers obtain the maximum benefits of these large amounts of data. Data are extracted from several sources, cleansed, customized and inserted into the data warehouse. A data warehouse is defined as a subject oriented, integrated, time-variant and non-volatile collection of data in support of management decision making process. [10]. The most popular analysis mean is the Online Analytical Processing (OLAP) which enables users to examine, retrieve and summarize data within a multidimensional model.

This paper describe a design and implementation of OLAP Business Intelligence for development planning at village and supra village (district) that integrates SDeKa's data from all villages and aligns the development planning with the vision, mission, and objectives of rural development in the Village Law No. 6 of 2014. OLAP Business Intelligence will be useful for Village Sustainable Development Planning for support the strategic and tactical decisions [7] as well as being a tool to monitor development progress in every villages which are connected as human neural network. In the end OLAP Business Intelligence is expected to improve organizational performance in sustainable development [8].

## 2. Related Work

Nowadays we are witnessing of explosion of transactional data transferred in the internet. 2 billion people from developing countries using the internet. The huge data need to be efficiently gathered, stored and analyze to support decision making not only for business institution but also for the government. One of the solution is Data warehouse and Online Analytical Processing (OLAP) Business Intelligence. Data warehouse is a collection of data that is subject oriented, integrated, time variant, and non-volatile designed for complex queries [14] [15].

Data warehouse is used to strengthen action ability of Health Information System, in Tajikistan [16]. This research proposed process of standardization using meta-data dictionary that would support data for action. Business Intelligence also useful for developing countries. Purkayastha et.al proposed Big Data Analytics for developing countries, using cloud computing for operational Business Intelligence in health [17]. This big data is resulting from integration of health data from multiple sources. Mohamed et.al show that KM is critical for innovation, prioritization and efficient use of resources. A significant linear association between IICTs and KM across time and geography is detected. IICTs improve the quality of shared decision making in inter- and intra-organizational settings [18].
Inspired by the usefulness of data warehouse and business intelligence as a tool for decision making in the government or in organization, authors design and build an OLAP Business Intelligence for sustainable development.

**3. Research Methodology**

This research comprehensively done by doing some related activities, can be viewed on a fishbone diagram in Figure 1 below:

<figure><img src="image_2.png" /><figcaption>Figure 1. SIDeKa research activity</figcaption></figure>

Activity 1, Villages and Rural Areas information system strategic planning implemented in phases: (1) Literature Study. A literature study by gathering information from books, articles, and scientific journals that discuss information system/information technology strategic planning related to Village Information System and Rural Areas. (2) Data Collection. Data collection related to information system strategic planning is done for several techniques, including: (a) direct observation, surveying the location, (b) interview, interviewing stakeholders related to village government organization, and business processes related to information system strategic planning, (c) survey, if interview with stakeholders not enough yield important information. (d) Business processes analysis in organization. The business processes are analyzed with analysis tools, i.e.: Porter's Value Chain [9] and Business Process Analysis [10]. (e) Enterprise Architecture modeling, using phases in TOGAF ADM methodology [11]. This approach is a complete and comprehensive approach that is suitable for strategic information system planning [12].

Business process identified for village government services shown in Figure 2 below:

<figure><img src="image_3.png" /><figcaption>Figure 2. Village Value Chain</figcaption></figure>
Activity 2 and 3 is Design and Build Village Information System and Rural Area (SIDeKa) and its implementation. This system has been implemented in several pilot areas. The results are shown in Figure 3 below:

<figure><img src="image_4.png" /><figcaption>Figure 3. Front End Side Screenshot SDeKa</figcaption></figure>

Activity 4 is Design and Build OLAP Business Intelligence for sustainable development planning conducted in this study. The design process of OLAP Business Intelligence performed with the following steps: 1) Identify the sustainable development planning regulation and procedures, 2) Presenting a star schema based on dimensional model that captures information needs for sustainable development planning, 3) Introduction new measures that captures both the temporal and spatial dimensions, 4) Design model/architecture for Extract, Transform and Loading, and 5) Implementing the OLAP Business Intelligence System.

## 4. OLAP Business Intelligence for Village and Rural Areas for Sustainable Development Planning

Indonesia is a big country that have at least 70 thousand villages. The new development paradigm turn village into new role as become the subject of the development instead of as an object. The new role implies the need for management information system of village development. Right now villages already have an open source information system that could support for village development planning, actuate, and monitoring that called SDeKa.
The information system has been developed in the early stages (SIDeKa) is an application which is still in transactional scale and handle daily operations. The information needs in aggregation and reporting form are important for sustainable development planning at the village and supra village (district) level. The application is then called OLAP Business Intelligence. OLAP Business Intelligence is a decision making support applications that could satisfy organization needs to meet the efficiency of decision making using technology to rapidly extract useful information from very large data so that accelerate the decision-making [13].

The development of OLAP Business Intelligence Application develop in the following steps: (1) Identify the sustainable development planning regulation and procedures. This step is an OLAP Business Intelligence Requirements Analysis. Requirements analysis was conducted through Focus Group Discussion (FGD) with stakeholders at the district level such as Bappeda and SKPD and business processes analysis related to development planning. Several government regulations related to development planning are: (a) Law No. 25 of 2004 concerning National Development Planning, (b) Law No. 32 of 2004 concerning Regional Government, (c) PP 08 of 2008 concerning Stages, Preparation Procedures, Control and Implementation Evaluation, Regional Planning, (d) Regulation 54/2010 concerning the implementation of PP 08/2008, and (e) Law No. 11 of 2008 on Information and Electronic Transactions. One of the main business processes related to development planning is shown in block diagram in Figure 4 below. The first block from the block diagram shows that the data processing and information is the first main issue. This first block is then filled by the Online Analytical Reporting (OLAP) application, which is one of the basic functionality of business intelligence application. Some important questions related to OLAP, which need to be answered for sustainable development planning such as: (a) How large and which land area, and the potential revenues that can be achieved for the entire area in the district, including all of the potential of agricultural, livestock, tourism? (b) How is the development of agricultural, livestock, tourism, etc. during the period of last five years? (c) How large the disaster-prone district area? (d) How many people that was born, died, migration in a certain district?

<figure><img src="image_5.png" /><figcaption>Figure 4. Preliminary Drafting RKPD Regency/City Diagram [3]</figcaption></figure>

(2) OLAP Business Intelligence Design. This design phase comprises of: (a) presenting a star schema based on dimensional model that captures information needs for sustainable development planning. Create dimensionality model in information package and star schema design is conducted in this design phase. Information package explain the facts/measure, and dimension along with the attributes and hierarchies that will compose data warehouse. Data warehouse is a collection of data that is subject oriented, integrated, time variant, and non-volatile designed for complex queries [14] [15]. (b) Introduction new measures that captures both the temporal and spatial dimensions, and (c) Design model/architecture for Extract, Transform and Loading

OLAP Business Intelligence application architecture is shown in Figure 5 below:
<figure><img src="image_6.png" /><figcaption>Figure 5. Supra Village OLAP Business Intelligence Application Architecture</figcaption></figure>

Transactional Data for village development planning from SIDeKa integrated through a process of extraction, transformation, and loading into data warehouse. Furthermore, the data is accessed through an application in OLAP Business Intelligence web application.

Village conditions involving location and region characteristic information obtained from external data such as BMKG and other institutions. Extract, Transform, and Load process using two databases comprises Staging Area and Data Warehouse. Staging area is used for preliminary ETL and the star schema

Data warehouse/star schema design for district condition indicator shown in figure 6 below:

<figure><img src="image_7.png" /><figcaption>Figure 6. Data Warehouse Design for District Condition</figcaption></figure>
This star schema represents district condition fact, and dimensions that could be analyzed from our OLAP Business Intelligence application. The continuous dimension also include in this design to accommodate continuous query. Besides district condition, this fact could be answered economics and finance condition.

Economics and finance condition fact also could be derived from Fact Demography in figure 7 below:

<figure><img src="image_8.png" /><figcaption>Figure 7. Data Warehouse Design for Demography</figcaption></figure>

This Demography fact could answer economic and financial condition in the district/supra village level. These birth rate, death rate, and migration rate could be used to predict district population and district income.

District performance indicator star schema depicted in figure 8 below:

<figure><img src="image_9.png" /><figcaption>Figure 8. Data Warehouse Design for District Performance Indicator</figcaption></figure>

This District Indicator fact could answer how success the district development performance in term of planning and realization. This information could be drilled down to detail villages / sub villages, time, activity planned, and development account.
## 5. The Result

The result of the development of OLAP Business Intelligence application shown in Figure 9 below:

<figure><img src="image_10.png" /><figcaption>Figure 9. Data Warehouse Design Screenshot for Demography Fact</figcaption></figure>

This screenshot depicts the dashboard of the OLAP Business Intelligence that could display the previously defined report. Report is based on OLAP Business Intelligence standard capabilities that could choose measures and dimension for reporting and display it in variety of reports like map, graph, and table. The OLAP Business Intelligence is an open source tool that embed with the SIDeKa (Village Information System and Rural Areas).

## 5. Conclusion and Future Works

The OLAP Business Intelligence Application and Information System at district / supra village have been built by integrating transactional data from SIDeKa that have been developed previously. This system be able to answer the important questions used for development planning at the district / supra village includes the district condition, economic and financial potency, and the timely development performance by district.
The application is open source in nature and could be used as tool for gathered, stored, and presented information of aggregate data to integrated district development planning. Reports are created custom and could be presented on map, table, or graph.

In the future, the challenge is how to extend OLAP Business Intelligence in the province and national level. There is so many data integration issues because of the variability of Village Information System that has already deployed. Analytical Reporting presented in this study is only limited to development planning, which is very important for developing country like Indonesia. The research have opportunity to extend to covers the food fulfillment decision making for the community.

REFERENSI

[1] Kementerian Perencanaan Pembangunan Nasional/ Badan Perencanaan Pembangunan Nasional. 2014. *Rencana OpenPembangunan Jangka Menengah Nasional (RPJMN) 2015 – 2019*.

[2] Undang Undang Desa Nomor 6 Tahun 2014 tentang Desa.

[3] Dirjen Bina Pembangunan Daerah. 2010. *Peraturan Menteri Dalam Negeri Nomor 54 Tahun 2010 Tentang Pelaksanaan Peraturan Pemerintah Nomor 8 Tahun 2008 Tentang Tahapan, Tatacara Penyusunan, Pengendalian, Dan Evaluasi Pelaksanaan Rencana Pembangunan Daerah*.

[4] Turban, E. & Volonino, L. 2012. *Information Technology for Management*. 8 ed. Hoboken: John Wiley & Sons.

[5] Anonim. 2015. *Membangun desa dengan data: Belajar dari Pengalaman Desa Terong dan Desa Nglegi dalam Membangun Sistem Informasi Desa (SID)*, http://kombinasi.net/bahan-bacaan-SID.pdf, diakses tanggal 3 Januari 2015.

[6] Ward, J. & Peppard, J. 2005. *Strategic Planning for Information Systems*. 3rd ed. West Sussex England: John Wiley & Sons.

[7] Ranjan, J. 2009. *Business Intelligence: Concepts, Components, Techniques And Benefits*. *Journal of Theoretical and Applied Information Technology*, Vol. 9. No 1, pp 060 – 070.

[8] Nazier, M.M., Khedr, A., & Haggag, M. 2013. *Business Intelligence And Its Role To Enhance Corporate Performance Management*. *International Journal of Management & Information Technology*, Volume 3 No.3, pp. 8-15.

[9] Porter, M. E., 1985. *Competitive Advantage Creating and Sustaining Superior Performance*. New York: The Free Press.

[10] Al-Aboud, F. N., 2011. *Strategic Information Systems Planning : A Brief Review*. *IJCSNS International Journal of Computer Science and Network*, VOL. 11(May 2011), pp. 179-183.

[11] Open Group, 1999-2011. www.opengroup.org. [Online] Available at: http://pubs.opengroup.org/architecture/togaf9-doc/arch/

[12] Alonso, I. A., Verdun, J. C. & Caro, E. T. 2010. *The IT Implicated Within The Enterprise Architecture Model: Analysis of Architecture Models and Focus IT Architecture Domain*. Perth, WA, Service-Oriented Computing & Application (SOCA), 2010 IEEE International Conference.

[13] Farrokhi, V. & Pokorádi, L. 2012. *The Necessities for Building a Model to Evaluate Business Intelligence Projects*. *International Journal of Computer Science & Engineering Survey (IJCSES)*, Volume 3 Issue 2, pp. 1-10.

[14] Inmon, W.H. 2005. *Building the Data Warehouse*, Fourth Edition. New York: John Wiley & Sons, Inc.

[15] Ponniah, P. 2010. *Data Warehousing Fundamentals*, 2nd edition. New York: John Wiley & Sons, Inc.

[16] Latifov, M.A., Sahay, S. 2012. *Data Warehouse Approach to Strengthen Actionability of Health Information Systems: Experience from Tajikistan*, *The Electronic Journal on Information System in Developing Countries*.

[17] Purkayastha, S., Braa, J. 2013. Big Data Analytics for Developing Countries – Using the Cloud for Operational BI in Health, *The Electronic Journal on Information System in Developing Countries*.

[18] Mirghani Mohamed Michael Stankosky Mona Mohamed, (2009), "An empirical assessment of knowledge management criticality for sustainable development", Journal of Knowledge Management, Vol. 13 Iss 5 pp. 271 - 286