Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 Design and Evaluation of an Integrated BI Solution with Centralized Data Architecture Anastasia Lidya Maukar1.
Rayvaldi Hendra Wardhana2 Industrial Engineering Department.
President University.
Bekasi.
Indonesia almaukar@president.
wardhana@student.
id (*) Received: 2025-09-18.
Accepted: 2026-01-15.
Published: 2026-01-29 AbstractAi This study develops an integrated Business Intelligence (BI) dashboard to address inefficiencies in monitoring key performance indicators (KPI.
at a toy manufacturing company.
The previous system required monitoring 48 separate dashboards, used over 25 data sources, and suffered from inconsistent formats that caused frequent errors and delayed reporting.
Using a visualization methodology.
KPI data for OEE.
Quality Performance, and Scrap were standardized and integrated into a centralized SQL Server database via an automated ETL pipeline.
The resulting Power BI dashboard improved operational performance by reducing monitoring time from 107.
1 minutes by three workers to 78.
minutes by one staff member .
% reductio.
, decreasing data inconsistencies by eliminating redundant fields, and enabling near real-time These improvements strengthened decision-making accuracy and provided a scalable blueprint for continuous improvement.
KeywordsAi Business Intelligence.
Power BI.
OEE.
Centralized Database.
Data-Driven Decision Making.
Visualization Methodology.
aligns with user needs.
A brief review of related studies positions this work within existing BI applications and clarifies its contributions.
Previous studies have highlighted the importance of OEE monitoring and shown that modern BI tools improve interpretability, real-time analysis, and decision-making .
However, most existing research focuses on single-KPI dashboards and assumes structured, centralized databases, providing limited insight into decentralized manufacturing environments with heterogeneous data sources.
This research addresses a gap in existing BI studies by focusing on decentralized manufacturing environments with heterogeneous data sources, which have received limited attention in prior The novelty lies in integrating multiple KPIs into a single automated dashboard supported by an ETL pipeline.
This study addresses this gap by integrating multiple KPIs, including OEE.
Quality Performance, and Scrap, from 16 production areas into a single interactive dashboard supported by an automated ETL The findings contribute to the BI literature by demonstrating a scalable, practical approach to centralized BI adoption in complex manufacturing contexts.
The adoption of BI enables automation, interactive data exploration, and data-driven decision-making, allowing managers to respond quickly to production issues with accurate, real-time insights .
The ultimate goal is to improve monitoring efficiency, enhance decision quality, and support continuous improvement in OEE.
Quality, and Scrap performance across all production areas.
This study aims to centralize KPI data into a unified database, standardize data formats across all production areas, and design an integrated dashboard to reduce monitoring time and improve decision-making.
The contribution lies in demonstrating a practical BI adoption strategy for decentralized manufacturing systems, offering a visualization-driven design that enables real-time insights and continuous improvement.
INTRODUCTION
The toy manufacturing industry is highly competitive, with global demand requiring efficient production systems and effective monitoring tools.
As one of the worldAos largest toy producers.
TOY MANUFACTURER accounts for approximately 75% of global toy demand.
With weekly production ranging from 30 to 60 product variations, continuous process improvement is critical to meeting demand while maintaining quality.
The company applies Lean Manufacturing principles and monitors three key performance indicators (KPI.
: Overall Equipment Effectiveness (OEE).
Quality Performance, and Scrap.
Currently, monitoring relies on 48 separate dashboards across 16 production areas, resulting in a fragmented, time-consuming review process.
KPIs are widely recognized as essential tools for evaluating performance and supporting continuous improvement .
Yet the existing visualization system faces issues such as decentralized data storage, inconsistent formatting, and a heavy reliance on manual inspection .
Data is stored in multiple network folders without standardization, requiring manual file retrieval, accuracy verification, and repeated access requests .
This manual process takes approximately 107 minutes per day for three workers, reducing productivity and delaying decision-making.
To address these challenges, this research develops a centralized BI system using SQL Server and Power BI .
The solution integrates an Extract.
Transform, and Load (ETL) process to unify and standardize data across all production areas, ensuring consistency and minimizing manual intervention .
This study specifically aims to .
centralize KPI data into a unified database, .
standardize data formats across all areas, and .
design an integrated dashboard that reduces monitoring time and improves decision-making.
visualization-driven design approach ensures the dashboard DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 II.
RESEARCH METHODOLOGY
This study adopts a case study and system development research design to evaluate a Business Intelligence (BI) implementation in a real manufacturing environment.
A case study is appropriate because the BI system is embedded within a complex operational context involving heterogeneous data sources and organizational practices that cannot be isolated through experimental or survey-based methods.
The system development approach is selected to support the design, implementation, and evaluation of a functional BI artefact, enabling iterative refinement based on operational requirements and direct performance measurement.
The research framework, illustrated in Fig.
1, outlines the sequential steps of problem identification, analysis, data collection, development of a centralized database, dashboard design, system improvement, and evaluation.
Each stage builds on the previous one, ensuring that KPI data from 16 production areas are collected, standardized, and integrated in a reproducible workflow.
For reproducibility, each stage includes explicit artefacts: .
data dictionaries and source registries (Data Collectio.
, .
transformation mappings and ETL schedules (Centralized Databas.
, .
dashboard layout specifications and visual design rationale (Dashboard Desig.
, and .
evaluation protocols and statistical analysis scripts (Evaluatio.
The entire methodology is documented as a selfcontained workflow with versioned assets, covering data sources.
ETL scheduling, system architecture, and evaluation Data were collected from 48 sources .
production areas y 3 KPIs: OEE.
Quality Performance, and Scra.
in formats including Excel.
Access.
Parquet, and SQL tables, covering a one-month observation period with daily updates.
Preprocessing steps included removing extra headers, blank rows, and merged cells.
normalizing column names and units.
converting data types.
and handling missing values through rule-based imputation or exclusion, depending on KPI ETL jobs were scheduled to run daily at 06:00 AM using VBA scripts and Task Scheduler, with quality checks for completeness at Extract, type consistency at Transform, and load verification at Load, ensuring a consistent and automated pipeline without manual intervention.
The overall architecture follows a standardized flow: data sources Ie Power Query for ETL Ie a centralized SQL Server database Ie a Power BI dashboard operating in DirectQuery mode for near-real-time Tooling and versions were fixed for reproducibility:
SQL Server 2019.
Power BI Desktop .
and Excel 2016 for VBA automation.
All ETL scripts, queries, and transformation mappings were documented and stored in a version-controlled repository, enabling consistent daily execution and auditability.
Fig.
1 illustrates how each stage of the research was carried In the Identify the Problem stage, direct observations and discussions with the Lean team revealed inefficiencies in the existing monitoring system, including fragmented dashboards and scattered data.
During the analysis of the current system stage, dashboard accessibility, update frequency, and data formats were assessed to identify specific bottlenecks.
The Data Collection stage involved gathering KPI data from 16 production areas in various formats, which were then cleaned and standardized.
The Centralized Database stage consolidated these data into SQL Server through automated ETL processes.
In the Dashboard Design stage, user requirements were translated into interactive visualizations in Power BI.
Finally, the System Improvement stage focused on reducing monitoring time, minimizing errors, and improving data accessibility for decision-making.
Fig.
Research Framework To support the execution of these stages, several tools were selected based on their suitability and compatibility with existing workflows.
SQL Server served as the central database for its reliability, scalability, and automated ETL job DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 Power Query was used to clean, transform, and merge heterogeneous data from multiple sources, allowing seamless integration with Power BI.
VBA automation standardized file structures and automated daily imports and exports, leveraging the operational teamAos familiarity with Excel-based workflows and reducing the need for additional Power BI connected to SQL Server via DirectQuery for near-real-time updates and scheduled dataset refreshes synchronized with the ETL completion window to avoid stale .
OEE consists of three main components, among them:
availability is the ratio of the time equipment operates to the planned production time .
, accounting for downtime from failures, maintenance, or other causes.
High availability means machines are always ready when needed.
It is calculated as shown in Equation .
Performance measures how efficiently a machine works when available .
It compares actual output to the maximum possible output at optimal speed, as calculated in Equation .
Quality is the ratio of defectfree products to total output.
, indicating process efficiency and low waste .
It is calculated as shown in Equation .
Identify the Problem The first step involved observing the production line and current monitoring practices.
Discussions with the Lean team revealed that each of the 16 production areas was tracked using three separate dashboards for OEE.
Quality Performance, and Scrap, for a total of 48 dashboards per day.
KPI data were scattered across multiple folders and file types, including Excel.
Access.
Parquet, and SQL, with inconsistent naming and Scrap files often contained extra headers, blank rows, and merged cells, requiring manual cleaning.
This decentralized and inconsistent system increased monitoring time to about 107 minutes per day with three staff members, reduced data accessibility, and limited timely, data-driven decision-making.
Fig.
OEE Components ycCyaya = yaycycaycnycoycaycaycnycoycnycyc y ycEyceycyceycuycycoycaycuycayce y ycEycycaycoycnycyc .
ycOycyycycnycoyce % yaycycaycnycoycaycaycnycoycnycyc = ycIycycuycycnycoyce Analyze the Current Visualization System ycIycycc ycaycycaycoyce ycycnycoyce % ycEyceycyceycuycycoycaycuycayce = yaycayc ycaycycaycoyce ycNycnycoyce A detailed assessment of the existing dashboards was conducted, focusing on accessibility, data formats, update frequency, and user interaction.
The analysis revealed that KPI data were scattered across multiple folders and file types, including Excel.
Access.
Parquet, and SQL tables, with inconsistent naming conventions and formatting.
Scrap and quality files often contained extra headers, blank rows, or merged cells, which complicated automated processing.
This decentralized structure made it difficult for team members to locate, verify, and use data efficiently.
Additionally, monitoring 48 separate dashboards manually increased the risk of errors and slowed decision-making.
These findings emphasized the need for a centralized, standardized, and automated system to improve data accessibility, accuracy, and operational efficiency .
yaycuycuycc ycuycycycyycyc %ycEycycaycoycnycyc = ycNycuycycayco ycuycycycyycyc Quality Performance: Evaluates how effectively a manufacturing system maintains product quality .
considers defect rate, rework, scrap, and customer complaints, with defect rate often expressed in Parts Per Million (PPM).
, using Equation .
to calculate the defect rate PPM.
ycAyaya yayceyceyceycayc ycIycaycyce ycEycEycA = ycIycaycoycyycoyce ycIycnycyce ycu1,000,000 .
Scrap: Scrap refers to materials removed from production that do not meet product specifications butan be reprocessed or reused .
Measured in PPM, scrap data allows comparison across products or plants.
Collect the Key Performance Indicator (KPI) Data .
Measurement: To evaluate the effectiveness of the improved monitoring system, several measurement indicators were used.
Monitoring time reduction was assessed as the total time spent reviewing dashboards per day, with the target being a reduction from 107 minutes to under 80 minutes.
Error reduction was measured by the decrease in inconsistencies and incorrect data entries following the centralization and automation of data.
Data accessibility and reliability were evaluated based on the completeness and availability of KPI data in the centralized system, as well as the successful daily execution of ETL processes.
The evaluation followed a reproducible protocol: sample = daily sessions over one month .
efore vs.
ShapiroAeWilk normality check, paired t-test at = 0.
05 (Wilcoxon if non-norma.
, reporting meanASD.
Data Sources: KPI data originated from 48 distinct sources .
areas y 3 KPI.
, with daily files collected over the onemonth File xlsx, .
accdb, .
parquet, and SQL tables.
Access was governed by role-based permissions to ensure data integrity.
Overall Equipment Effectiveness (OEE): OEE (Overall Equipment Effectivenes.
is a key metric for assessing manufacturing efficiency .
It evaluates performance through three main components: Availability.
Performance, and Quality, which help identify improvement opportunities and increase productivity .
Fig.
2 shows the OEE components .
The OEE can be calculated using Equation DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 xlsx, .
accdb, .
SQL) Ie .
ETL in Power Query with scheduled execution via VBA and Task Scheduler Ie .
centralized SQL Server 2019 with staging, curated, and mart schemas Ie .
Power BI DirectQuery using a star schema (Fact_KPI with Dim_Date.
Dim_Area.
Dim_Tea.
Ie .
dashboards with role-based access and row-level security (RLS).
Each release follows a deployment checklist for synchronization, and RLS verification to guarantee consistent t-statistic, p-value, 95%.
scripts and parameters were versioncontrolled with fixed tool versions.
Develop a Centralized Database Following the system design phase, a centralized database was developed using the ETL methodology .
Data from multiple sources, primarily Excel files across 16 production areas, were first extracted and examined for inconsistencies, such as extra headers, blank rows, merged cells, and varying field names.
Using Power Query, the data were cleaned, standardized, and transformed into a consistent structure suitable for integration .
Key transformations included unifying column names, removing unnecessary rows, converting data types, and aligning formats across all sources, as shown in Fig.
Design the Visualization and Build a BI Dashboard The dashboard was created to meet the needs of the Lean team, production staff, and management.
Key KPIs such as OEE.
Quality Performance, and Scrap were selected with defined targets.
Charts were chosen according to data type and purpose, including line charts for trends, gauge charts for OEE.
KPI cards for quick metrics, and bar charts for categorical data.
Data from the centralized SQL Server database was cleaned and transformed in Power Query, with Date and Team Dimensions added for filtering and drill-down.
The dashboard includes multiple pages, including a Homepage.
KPI trends, and detailed area-specific pages, all utilizing interactive visuals and conditional formatting to support efficient monitoring and data-driven decision-making.
Fig.
ETL Data Pipeline System Improvement Raw data from various production sources is extracted, transformed, and loaded into the centralized SQL Server Each stage is clearly defined: the Extract step retrieves raw files daily, the Transform step cleans and standardizes data for consistency, and the Load step integrates the cleaned data into the database.
This automated workflow ensures that KPI data, including OEE.
Quality Performance, and Scrap, are reliably consolidated and ready for visualization in Power BI.
To ensure that ETL processes are executed consistently and meet quality standards.
Table I presents the ETL schedule, automation tools, and the corresponding quality checks applied at each stage.
A step in the ETL workflow is automated and monitored to ensure data reliability and accuracy.
By following this schedule, updates to the centralized database are performed regularly without manual intervention, thereby minimizing errors introduced by decentralis sources .
This structured ETL process enables seamless integration of KPI data, including OEE.
Quality Performance, and Scrap, into a centralized SQL Server database and prepares the data for visualization and analysis in Power BI.
The monitoring system was enhanced by developing a centralized database, standardizing datasets, designing an entity-relationship diagram, and creating a BI dashboard .
Data from multiple sources were extracted, cleaned, and transformed using Power Query, and then loaded into a centralized SQL Server database via VBA automation, supported by an automated ETL schedule and quality checks to ensure accuracy and reliability.
The Power BI dashboard integrates all KPIs, including OEE.
Quality Performance, and Scrap, with interactive visualizations to enable faster decisionmaking .
The system was evaluated over a one-month horizon, with daily KPI monitoring as the unit of observation, using documented equations and fully reproducible workflows.
Observations confirmed improved monitoring efficiency, reduced manual workload, and a decrease in average monitoring time to 78.
7 minutes per day, while providing centralized, reliable, and actionable insights for real-time, datadriven decision-making.
Comparison and Evaluation To evaluate the effectiveness of the improved monitoring system, the new centralized BI dashboard was compared with the previous fragmented approach.
Key performance indicators, including monitoring time, ease of access, and user interaction, were assessed.
Observations over multiple days revealed that a single staff member could efficiently monitor all production areas using the integrated dashboard, with its interactive features enabling the faster identification of anomalies and performance trends.
The consolidated visualizations reduced repetitive tasks, minimized errors, and enabled more timely TABLE I ETL Step
Extract
Transform
Load
BI Refresh
ETL SCHEDULE AND SLA
Frequency Tool Quality Check Daily Power Query Completeness check Daily Power Query Type consistency Daily VBA Script and Load verification to Scheduler Daily Power BI SQL Server To ensure reproducibility, the system architecture is defined as a standardized workflow: .
heterogeneous data sources DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 decision-making.
These improvements demonstrate that the centralized system significantly enhances operational efficiency, provides clearer insights, and improves overall productivity compared to the prior decentralized monitoring For quantitative validation, the time spent before and after the BI system implementation was analyzed using a paired ttest, as the same operational unit was observed under both This test was selected to determine whether the observed reduction in monitoring time was statistically The analysis was performed at a 5% significance level, and 95% confidence intervals were used to assess the reliability and magnitude of the improvement.
visualization tools, and is refined iteratively through user In this study, however, the methodology was not adopted in its entirety.
instead, it was applied specifically duringhe requirements analysis stage to better capture and interpret user needs for the dashboard design .
The adapted process is illustrated in Fig.
Fig.
Visualization Methodology As shown in Fig.
5, the visualization methodology helped translate user requirements into meaningful system features, ensuring the final dashboard addressed stakeholders' decisionmaking needs.
By focusing on requirement gathering rather than the entire visualization cycle, the method served as a practical tool for aligning system design with user expectations.
Business Intelligence Business Intelligence (BI) refers to the methods, tools, and processes for collecting, integrating, analyzing, and visualizing data to support informed decision-making .
Its core components include data warehousing, data mining, reporting and visualization, predictive analytics, and data quality management .
The main stages are data collection, data cleaning and transformation (ETL), data analysis, data visualization, and reporting for decision-making .
, as illustrated in Fig.
Methodological Limitations Despite the structured approach, this methodology has several limitations.
The study focused on a single organizational setting, so the findings may not generalize to other manufacturing environments.
ETL processes depend on pre-cleaned, structured source files, so exceptions require manual intervention.
Additionally, dashboard updates are subject to data refresh schedules, which may result in short delays when accessing real-time KPI information.
These limitations should be considered when interpreting the results and assessing the system's scalability.
RESULT AND DISCUSSION
After implementing the centralized BI system, all KPI datasets (OEE.
Scrap, and Quality Performanc.
were consolidated into a single SQL Server database, and the dashboards were consolidated into a single interactive Power BI file.
This reduced the monitoring effort from three staff members checking 48 separate dashboards to a single staff member overseeing all KPIs.
Daily monitoring time decreased 1 minutes to 78.
7 minutes, while data consistency, reliability, and accessibility improved significantly.
Outlier correction and standardized data formats further enhanced KPI reporting accuracy, enabling faster, more informed decisionmaking.
Analytically, the observed gains stem from reduced fragmentation and standardized schemas, which lower ETL failure rates, reduce task-switching overhead, and shorten decision latency by enabling earlier anomaly detection.
Fig.
Business Intelligence Framework To deliver insights from the consolidated data.
Microsoft Power BI was selected as the primary BI tool.
Power BI enables the integration of multiple data sources, supports entitylationship modelling, and provides interactive dashboards that facilitate real-time monitoring and decision-making .
Prior studies highlight its effectiveness in combining accessibility, advanced visualization, and user familiarity, making it particularly suitable for production environments .
Current System Weaknesses The initial monitoring system implemented by the Lean Team relied on multiple fragmented dashboards and nonstandardized datasets, which significantly hindered efficiency.
Several weaknesses were identified:
Visualization Methodology Visualization methodology is a structured, user-centred approach that emphasizes clear, purposeful communication of data to support informed decision-making .
The process typically begins with understanding the audience and their needs, continues with selecting appropriate chart types and .
Inconsistent Data Format: Each production area reported KPI data (OEE.
Scrap, and Quality Performanc.
in different formats.
Scrap files often contained unnecessary DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 header rows, merged cells, or blank rows, while others applied inconsistent naming conventions.
These irregularities required manual cleaning before integration and increased the risk of The example of the current data formatting is shown in Fig.
KPI data was distributed across multiple sources, including Excel files.
Access databases.
Parquet files, and SQL tables, all stored in separate shared folders.
This decentralized structure made it challenging to ensure data integrity and delayed the retrieval of relevant information.
The lack of a centralized repository also hindered collaboration, as different users often accessed outdated or incomplete data, further weakening the reliability of the monitoring process.
These issues underscore the need to transition to a centralized, standardized database.
Inefficient Dashboard Monitoring: The Lean Team needed to monitor KPI performance across 48 separate dashboards daily.
This process required a substantial amount of time each day and involved three staff members.
The monitoring activity was repetitive, time-consuming, and prone to human error.
The trend in the monitoring time required by the current system is shown in Fig.
Fig.
Current Data Formatting The structure illustrated in Fig.
6 highlights the lack of standardization in scrap data reporting.
Even a minor change, such as renaming a header, adding or removing a row, or shifting columns, could cause errors in Power Query.
This issue frequently caused refresh failures in Power BI, leaving the corresponding dashboard visualizations incomplete or blank.
Such instability underscored the need to design a standardized and simplified data format to ensure consistency and reliability in the monitoring system.
Limitations of the current system include format drift .
, header changes, merged cell.
that disrupt ETL processes.
These scattered repositories lead to version mismatches and high manual effort, reducing the auditability and reproducibility of daily KPI reports.
Fig.
Monitoring Time .
Decentralized Data Storage: The current system created accessibility issues, especially for team members who needed to use or analyze the data.
Each user was required to know the exact file location, folder structure, and file naming conventions, which were often inconsistent.
This not only complicated the data search process but also increased the likelihood of duplication, inconsistency, and errors during data The scattered state of the database is illustrated in Fig.
The total daily monitoring time was high, exceeding 100 This workload was shared among three workers, reflecting not only the inefficiency of the system but also the significant human resources required for routine monitoring.
The excessive time demand highlighted the lack of integration and the need for a more streamlined process.
To illustrate how this monitoring process was carried out.
Fig.
9 shows the fragmented structure of the current BI files, which had to be checked individually by multiple staff members.
Fig.
Current Monitoring System Fig.
Scattered Database DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 The decentralized monitoring practice in which 48 dashboards were managed separately across different files.
Each user was responsible for opening and reviewing multiple BI files daily, resulting in unnecessary duplication of work and an increased risk of oversight.
This approach not only took longer but also delayed the detection of performance issues, underscoring the urgency of an integrated, centralized dashboard solution.
Analytically, the three weaknesses share a common root:
Heterogeneous formats increase transformation entropy and the risk of refresh failures.
decentralized storage inflates retrieval and verification time.
and multi-file dashboards magnify task-switching overhead and the probability of errors.
Collectively, these factors delay anomaly detection and degrade decision timeliness, underscoring the need for a single source of truth, standardized schemas, and consolidated visualization.
Limitations of the current system are structural: format drift .
, header renames, merged cell.
that breaks ETL pipelines.
location ambiguity and version mismatches due to scattered repositories.
and high manual effort that reduces the auditability and reproducibility of daily KPI reports.
These constraints explain the > 100-minute daily monitoring burden and the inconsistent data confidence.
The final dataset was reduced to three standardized columns:
Weekending.
Area, and Scrap PPM.
This clean, structured format not only facilitates automated ETL processes but also ensures seamless integration with SQL Server and Power BI.
Standardising the scrap dataset reduces the likelihood of ETL errors, ensures consistent dashboard refreshes, and minimizes manual interventions, ultimately improving the reliability and speed of KPI monitoring.
By removing redundant fields and maintaining a consistent schema, the new format minimized errors, improved refresh reliability, and laid the foundation for a stable, scalable data visualization system.
From an analytical perspective, standardizing to three columns (Weekending.
Area.
Scrap PPM) reduces schema variability and the error surface area, lowering the probability of ETL failures and stabilizing Power BI refreshes.
This directly improves data reliability and reduces decision latency.
Data Validation: Before visualization, an exploratory analysis was conducted to examine the OEE dataset for accuracy and consistency.
A boxplot (Fig.
was created to display the distribution of OEE values, including the minimum.
Q1, median (Q.
Q3, and maximum, and to identify any extreme outliers that could affect the analysis.
Improvement System .
Data Format Standardization: The first improvement focused on standardizing the scrap summary dataset.
In its initial form, the data contained multiple headers, blank rows, and inconsistent field names, which often disrupted automated After transforming the Power Query dataset, it was loaded into a centralized SQL Server database using a VBA The result of this transformation was a simplified structure consisting of only three essential columns, as shown in Fig.
Fig.
Boxplot Outliers Detection Several OEE values exceeded the expected range (OEE > .
, indicating potential data entry errors or abnormal operating To improve data reliability, these extreme values were replaced with the median, which represents the dataset's central tendency.
Using the median avoids distortion that could occur if the mean were applied, as extreme values can skew the This approach prevents skewed visualizations, enables managers to make more accurate comparisons across production areas, and ensures that decision-making is based on reliable, normalized data.
The updated dataset now exhibits a more balanced distribution, reducing the impact of anomalies and providing a clearer representation of machine performance for subsequent visualization and analysis.
Median replacement for extreme outliers preserves the central tendency without inflating the mean, improving the interpretability of OEE distributions across areas.
By flagging corrected records in a data-quality field, the workflow maintains transparency and reproducibility while preventing skewed visuals that could mislead operational decisions.
Fig.
Scrap Data Format DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 .
Centralized Database: All KPI datasets, including OEE.
Scrap, and Quality Performance, were integrated into a centralized SQL Server database via an automated ETL Fig.
12 illustrates this ETL flow, showing how data from multiple sources, such as Excel.
Access.
Parquet files, and SQL tables, are extracted, transformed, and loaded into a unified database.
During the Extract stage, raw data files from 16 production areas were retrieved daily.
The Transform stage, performed in Power Query, standardized schemas by removing unnecessary rows, aligning field names, and unifying data Finally, the Load stage used a VBA script to import the cleaned data into SQL Server, creating a repeatable and consistent workflow with minimal manual intervention.
Integrating all KPIs into a centralized SQL Server eliminated errors caused by decentralized file systems and created a single source of truth, ensuring consistency and reliability.
The automated ETL pipeline minimized manual cleanup, standardized data structures, and enabled dashboards to refresh Consequently, managers no longer needed to compile multiple sources, freeing staff from manual monitoring to focus on more analytical, decision-making tasks.
This centralization accelerated issue detection, supported faster corrective actions, and improved data-driven decision-making.
The centralized database, shown in Fig.
13, provides structured tables and dynamic views for seamless integration with visualization platforms, enabling users to focus on insights rather than raw data.
Integrated Monitoring Dashboard: Development began with gathering user requirements from the Lean Team to ensure the design addressed their daily challenges and needs.
These requirements focused on functionality, usability, and performance, particularly on reducing time wasted in monitoring and improving the accessibility of KPIs across all production areas.
The summarized user requirements are presented in Table II.
UR-07
Fig.
ETL Flow
UR-08
UR-09
UR-10
UR-11
TABLE II
USER REQUIREMENT VISUALIZATION
Requirement Description Develop a dashboard for KPI Quality Performance and scrap for data-driven decision-making.
I want to see all KPIs from every area in just one dashboard.
The dashboard should let me filter by area or by date.
Checking the data every day should be faster, not as slow as it is now.
It is a waste of time to open 48 files every day.
Type Functional Functional Functional Performance Functional After identifying these requirements, the dashboard design emphasized consolidating all KPIs into a single interface, supported by interactive filters for area and date selection.
This approach ensured that the dashboard was not only functional but also aligned with user expectations of efficiency, accuracy, and ease of use.
The next step in dashboard development was building the underlying data model in Power BI.
To make the dashboard dynamic, relationships were established between the fact tables (OEE.
Scrap, and Quality Performanc.
and the dimension tables (Area.
Date, and Machin.
This relational design allowed the system to support filtering, slicing, and drilldown analysis across all KPIs.
The overall structure of the model is shown in Fig.
Connecting the fact and dimension tables ensured that the dashboard displayed consistent data while enabling users to filter results by area or date.
This approach enhanced usability, reduced redundancy, and improved monitoring efficiency by facilitating real-time interaction with KPI data.
After designing the data model, the next stage was developing the dashboard visuals.
The first page created was the Homepage Dashboard, which functions as the main navigation hub.
Instead of opening multiple files, users can now access the desired visualization by clicking its logo.
The layout of this homepage is shown in Fig.
The homepage Fig.
Centralized SQL Database DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 serves as an intuitive entry point that streamlines navigation.
By consolidating links to all KPI dashboards into a single interface, it reduces complexity, saves time, and enhances user This approach ensures that users can quickly access the specific analysis they need without having to browse through numerous folders or dashboards.
performing periods, detect anomalies, and make timely, data-driven decisions to maintain operational The next visualization in the integrated dashboard is the Detail KPI Visualization page.
This page is widely used by Lean staff as it provides an in-depth analysis of KPI performance and the underlying root causes.
Unlike the overview or trend pages, this page dives deeper into the operational details, making it an essential tool for daily problem-solving and improvement activities.
A preview of the layout is shown in Fig.
Fig.
KPI Trend Fig.
Entity Relationship Diagram Fig.
Detail KPI Visualization As shown in Fig.
17, the page displays detailed breakdowns of downtime duration, machine performance, and defect information across all 16 production areas via a dynamic filtering option.
By providing this level of granularity.
Lean staff can immediately identify which machines or processes are causing inefficiencies, analyze the duration and frequency of downtime events, and review defect categories.
These insights directly supported data-driven decision making, enabling staff to take targeted corrective actions and improve overall production performance.
Providing root-cause context .
owntime reasons, performance ratios, defect categorie.
at the same granularity as KPI trends bridges the gap between signal and action, translating anomalies into targeted interventions with measurable impact on OEE and quality.
The final visualization illustrates the overall improvement achieved after implementing the integrated monitoring system.
As shown in Fig.
18, the Lean Team, which previously required three Fig.
15 Homepage Dashboard Another key component of the integrated dashboard is the KPI Trend page.
This page is significant for managers, especially Lean Managers, as it enables them to track KPI performance over time.
visualizing KPI trends, managers can quickly identify deviations, track progress, and evaluate the impact of improvement initiatives.
The layout of this page is illustrated in Fig.
The KPI Trend page provides time-series visualizations that highlight patterns and changes in OEE.
Scrap, and Quality Performance.
It enables users to identify low- DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 workers to monitor 48 separate dashboards daily, now only needs one person to oversee all KPI performance.
containing multiple headers, blank rows, and inconsistent field This inconsistency led to frequent errors during Power Query refreshes and forced the Lean Team to perform repetitive manual cleanup before the data could be analyzed.
As a result, the process was both time-consuming and prone to human error.
To address these issues, the improved system introduced a standardized, simplified format with only three essential columns: Weekending.
Area, and Scrap PPM.
This format was designed to be directly compatible with SQL Server and Power BI, thereby reducing the number of transformation steps required and ensuring a reliable automated workflow.
The details of the improved data format are summarized in Table i.
The new data format establishes clear rules for each field, enabling seamless integration with SQL Server without requiring additional manual adjustments.
This structured approach not only improves system reliability but also provides a foundation for consistent KPI monitoring across all 16 production areas.
Column
[WE]
[Are.
[ScrapM] TABLE i SCRAP D ATA TYPE IMPROVEMENT Data Type Description DATE Stores only the date (MM-DD.
VARCHAR.
Text up to 50 characters for machine FLOAT Decimal for Scrap Part Per Million.
Database: The second aspect of comparison focuses on the database setup.
In the previous system.
KPI data was scattered across multiple file types and storage locations, including Excel.
Access.
Parquet, and SQL tables.
Each production area maintained its own files, which complicated integration and slowed down access.
Users were required to know the exact folder structure and file names, which limited data sharing and often led to duplication or inconsistencies.
The database's decentralized nature also made system maintenance and troubleshooting difficult.
Table IV summarizes the database type used for each of the three KPIs (OEE.
Scrap, and Quality Performanc.
across all 16 production areas.
The heterogeneity of storage formats across the 16 production areas created inefficiencies and limited scalability.
Fig.
18 Monitoring System Improvement This significant reduction in monitoring effort was made possible by consolidating all 48 dashboards into a single, integrated Power BI file.
With this integration, users no longer need to spend time searching, opening, and cross-checking multiple dashboards.
Instead, the entire monitoring process can be carried out more efficiently and consistently within one unified system.
This not only reduces time and resource requirements but also minimizes human error, enabling faster, more accurate decision-making.
Database Name East_AS East_Decoration East_Molding East_Hair East_Casting East_MoldingMC1 East_MoldingMC2 East_Assembly East_DecorationMC East_Spray West_Molding Comparison This section compares the existing system with the improved version across three key aspects: data format, database setup, and monitoring system.
The comparison highlights how the new system resolved inefficiencies and enhanced monitoring performance.
West_Casting West_Decoration .
Data Format: The first aspect of comparison focuses on the data format used in the monitoring system.
In the previous
setup, the scrap summary files were not standardized, often West_Plating TABLE IV
CURRENT DATABASE TYPE
Storage File
Quality OEE/ DSA
Performance SQL
Excel
Parquet
Excel
SQL
Excel
Parquet
Excel
SQL
Excel
Excel
Excel
Excel
Excel
SQL
Excel
SQL
Excel
Excel
Excel
Excel
Microsoft
Access
Excel
Excel
SQL
Microsoft
Access
SQL
Microsoft Access Scrap Excel Excel Excel Excel Excel Excel Excel Excel Excel Excel Excel Excel Excel Excel DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367
Database Name
West_Tire Bar
OEE/ DSA
SQL
West_Assembly SQL Storage File Quality Performance Microsoft Access Microsoft Access minutes per day to monitor 48 separate dashboards.
After integrating all KPI dashboards into a single Power BI platform, the process required only one staff member and 78.
7 minutes, representing a 26% reduction in monitoring time and a 67% reduction in labour.
Fig.
21 illustrates the comparison between the previous fragmented system and the improved integrated dashboard, highlighting the significant efficiency gains.
Before integration, the Lean Team needed to monitor 48 separate Power BI dashboards daily, which required three staff members and was time-consuming.
After the integration, all dashboards were consolidated into one interactive dashboard.
This change enables a single staff member to efficiently monitor all KPIs across production areas, significantly reducing both monitoring time and labour requirements while enhancing the overall reliability of reporting.
Scrap Excel Excel In the improved system, all KPI datasets, including OEE, scrap, and quality performance metrics, were consolidated into a centralized SQL Server database.
The migration process involved automated ETL pipelines, with Power Query handling data transformation, and VBA scripts loading the cleaned datasets into SQL Server, as shown in Fig.
Structured tables and dynamic views were also implemented to ensure data consistency and readiness for visualization.
The centralized SQL Server now serves as a single source of truth, integrating data from all production areas.
This setup enables seamless connectivity with Power BI, reduces redundancy, and ensures that decision-makers can access reliable, validated data at any Fig.
Time Comparison to Monitor KPI .
Fig.
Comparison Monitoring System .
Comparison Summary: The key differences between the current and improved systems across three aspects: data format, database setup, and monitoring system.
This overview highlights how the improvements addressed inefficiencies and established a more reliable, integrated, and efficient monitoring As shown in Table V, the improved system introduced major enhancements.
The data format was standardized, eliminating errors caused by inconsistent file formats.
The database was centralized in SQL Server, making data cleaner, more validated, and easier to access.
Finally, the monitoring system was streamlined into a single integrated Power BI dashboard, reducing both time and human resources required Fig.
The Improved Database .
Monitoring System: The monitoring system before and after integration, highlighting the difference in time required to complete daily KPI monitoring.
As shown in Fig.
20, the previous system required multiple staff members and over 100 DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 for daily KPI monitoring.
These combined improvements provided a strong foundation for data-driven decision-making.
Aspect Data Format Database Setup Monitoring System significant improvement consistent with the expected benefits of centralization.
TABLE V
COMPARISON SUMMARY
Current System Improved System Scrap summary files lack a Simplified, standardized standardized structure, contain format with three clear extra rows/columns, and have columns: Weekending, inconsistent headers.
Manual Area.
Scrap PPM.
Easier cleaning is required, prone to to transform, reliable for human error, and causes Power Power BI.
Query failures.
Decentralized files are scattered Centralized SQL Server across multiple folders with database with structured inconsistent names and tables and views.
Clean.
Users need to locate validated, and easy to and understand each file, which is a high risk of duplication and The 48 separate dashboards Integrated Power BI .
ne per KPI and are.
were dashboard consolidating manually checked by three all KPIs and areas into workers and required one platform.
Checked significant daily monitoring by one worker, taking time, making the process prone 7 minutes daily.
to delays and errors.
Fig.
95% Confidence Interval Mean Reduction Compared to prior studies .
that primarily focused on single KPI dashboards such as OEE and relied on structured datasets, this integrated dashboard handles multiple KPIs (OEE.
Quality Performance.
Scra.
, manages decentralized and unstructured data, and incorporates an automated ETL pipeline, offering a more scalable, time-efficient, and reliable solution for operational monitoring.
A paired t-test was applied because the same operational unit was observed before and after the BI system implementation, making the data dependent.
This test is appropriate for beforeAe andAeafter comparisons, and the distribution of paired differences showed no severe deviation from normality.
Although the sample size is relatively small .
= .
, paired ttests are robust to small sample sizes when extreme nonnormality is absent.
The descriptive statistics for monitoring time before and after system implementation are summarised in Table VI.
The descriptive comparison presented in Table VI, along with the inferential statistical analysis, further validates the improved system's effectiveness.
A paired t-test confirmed that the reduction in monitoring time was statistically significant .
< 0.
, with a 95% confidence interval of 22.
4 minutes.
Data Driven Decision Making The improved system enabled data-driven decisionmaking by consolidating multiple datasets into a centralized SQL Server and presenting them in an integrated Power BI Decision makers now have access to validated, realtime KPIs, eliminating manual compilation and reducing delays in obtaining insights.
The system allows managers and staff to monitor performance across 16 production areas, apply filters for specific areas or dates, and drill down into detailed KPI This capability accelerated root-cause analysis, allowing rapid corrective actions and improving overall operational responsiveness.
For example, when the OEE in one area suddenly declines.
TABLE V dashboard enables the Lean Team to investigate the INFERENTIAL STATISTICAL TEST underlying cause immediately.
By reviewing related charts.
Before After Metric Implementation Implementation such as Downtime Reason Breakdown.
Machine Performance, and Total Defect Reason, staff can determine whether Sample Size .
excessive machine stoppages, reduced cycle efficiency, or an Mean A SD .
10 A 7.
70 A 4.
increase in product defects contributed to the drop.
With this Median .
clarity, corrective measures can be prioritized in the exact area that most affects OEE, ensuring a faster recovery in production The large effect size indicates that the improvement is not only performance.
statistically significant but also practically substantial.
The User feedback was collected via direct interviews and task confidence interval chart in Fig.
22 visualizes the magnitude observation to evaluate the usability and efficiency of the and reliability of this improvement.
The 95% confidence integrated dashboard, as shown in Table VII.
Brief interviews interval for the mean reduction in monitoring time, shows that with Lean Team members, the end-users of the dashboard, the improvement is highly reliable.
The mean difference of highlighted the advantages of the new system, such as reduced 41 minutes, with a 95% CI of 22.
80 to 33.
40 minutes, monitoring time and streamlined access to multiple KPIs, while confirms that the streamlined monitoring system significantly also identifying potential areas for future improvement, reduced time requirements compared to the previous including automated alerts and additional visual cues for rapid fragmented process.
The mean reduction of 28.
41 minutes .
% decision-making.
The Lean Users summarizes the key CI 22.
80Ae33.
indicates a statistically and practically responses.
Overall, users confirmed that the integrated DOI : https://doi.
org/10.
25139/inform.
Inform : Jurnal Ilmiah Bidang Teknologi Informasi dan Komunikasi Vol.
11 No.
January 2026.
P-ISSN : 2502-3470.
E-ISSN : 2581-0367 dashboard significantly reduced the effort required for daily monitoring and minimized the need to open multiple files, shortening the process from nearly 2 hours to less than 1.
OEE visualization was perceived as the most valuable feature, providing clear insights into the causes of downtime and performance issues and enabling data-driven corrective and enable staff to focus on problem-solving and continuous performance improvement.
IV.
CONCLUSION
This study demonstrates that developing an integrated BI system effectively resolved the inefficiencies of the previous fragmented monitoring process at Toy Manufacturing Company.
By implementing a centralized SQL Server database, standardizing data formats, and integrating key performance indicators (OEE.
Quality Performance, and Scra.
into a single Power BI dashboard, the company improved data accessibility, consistency, and reliability.
Evidence from Table VI and Fig.
shows that the average daily monitoring time decreased by 26%, 1 minutes with three staff members to 78.
7 minutes with one staff member.
User feedback (Table VII) confirms that the new system reduced manual workload, minimized errors, and enabled faster, data-driven decision-making.
The centralized BI architecture establishes a single source of truth, strengthening data governance, reducing reporting inconsistencies, and shortening decision latency by enabling faster access to validated KPIs.
These improvements support organizational learning by providing continuous visibility into performance trends and a strong basis for ongoing operational Integrate the Dashboard into the SGA System Web Platform.
Managers and employees can monitor machine performance in one place without switching between tools.
Implement Real-Time Alerts for Downtime.
Notifications for down machines enable faster problem identification and Broaden KPI Integration.
Extend the dashboard beyond OEE.
Quality Performance, and Scrap to include metrics such as energy usage, maintenance schedules, and employee productivity for a more comprehensive operational TABLE VII
SHORT I NTERVIEW WITH THE LEAN USERS
Question Response How has the new dashboard AuIt is much easier now because all helped your daily monitoring three KPIs are in one place.
We donAot need to open many files anymore.
Ay Has the dashboard affected the time needed for your monitoring AuYes, the time is clearly shorter.
Before, it could take almost two hours, it takes less than one and a half.
Ay Are there any difficulties in using the dashboard? AuAt the beginning, yes.
It took some time to adapt, but now we are more familiar, and it feels easier.
Ay Are there any limitations or features you would like to add? AuIt would be better if the system could auto-assess when OEE goes below the target, so we donAot always have to interpret manually.
Ay Limitations and Future Improvements .
Efficiency Improvement: While the centralized BI system significantly reduced daily monitoring time from over 100 minutes to under 80 minutes and minimized manual reconciliation, some efficiency constraints remain.
The ETL process relies on pre-cleaned, structured source files, so exceptions still require manual intervention.
Data Reliability: Integrating multiple KPIs into a centralized SQL Server database improved consistency and reduced errors caused by decentralized file systems.
However, reliability depends on the ongoing accuracy of source data.
Extreme outliers or incorrectly formatted inputs can still disrupt automated processes, necessitate periodic validation and .
monitor to maintain data quality.
User Experience: The integrated dashboard enhanced usability and accessibility, allowing staff to focus on analysis rather than data handling.
Initial adaptation challenges were .
reported but diminished over time.
Future enhancements could include automated alerts for declining KPI performance.
Scalability and Applicability: Currently, the system has been implemented in a single manufacturing environment.
While it can handle additional KPIs and larger datasets, further optimization may be needed as data complexity increases.
Moreover, expanding this centralized BI approach to other .
production lines or industries would require careful alignment with each environmentAos data structures and operational .
Future Improvements: Planned enhancements include real-time data updates, automated notifications for KPI thresholds, integration with additional data sources, and extension to other plants or industries.
These improvements .
aim to reduce manual effort further, accelerate decision-making.
REFERENCES