Data analysis and data warehouse design based on Pentaho data integration (kettle) to support the determination of student learning achievement

The successful implementation of various computerized information systems has led to high data growth. Automation works well, but some of the desired reports are not achieved. This is because the data scattered in various systems have not been integrated. This paper discusses the design of data warehouse which is the result of integration and data abstraction from the Academic Information System (AIS) and other systems at UIN Syarif Hidayatullah Jakarta campus that accommodates the need for data history and archiving to support reports in the academic field. These reports are used to fulfill the Higher education accreditation carried out by BAN-PT (Higher Education National Accreditation Agency) to assess the quality of university institutions and study programs in Indonesia. Therefore, the identification of information needs using the reference format of executive reports derived from the accreditation forms of study programs. The methodology in this study includes the project planning phase, defining needs, design, construction, deployment, and maintenance. In these stages, it starts from planning the data subject, designing the star schema, designing the data warehouse and then implementing it by means of Extract, Transform, Load (ETL) to form the cube using Pentaho Data Integration (PDI). The results of this study are academic fact tables that will be combined with fact tables from other standard forms as the basis for making an Academic Business Intelligent Application in University.

8 - Dimensions

Why is the snowflake schema a good data warehouse design?

<p>Education is very important for us, the country can go forward because the people are educated. The success of education is determined by the quality of education itself. To know the quality of education required a result or achievement. One of the factors affecting achievement is the facility. The purpose of this study is to determine whether there is influence of learning facilities on student achievement. Sampling using simple random sampling system as much as 58 people from the total population as much as 70 people at the level of formal education. The method used in data collection that is using quisioner, observation and documentation. Data analysis technique used is using simple linear regression analysis. Based on the results of data analysis and hypothesis testing it is known that the two variables of learning facilities (X) and student learning achievement (Y) have a positive influence, this is evidenced by r = 0.277 where the guidelines used to provide interpretation proposed by Sugiyono are at intervals 0.20 - 0.399 which fall into the low category[18]. Based on the results of research that has been put forward by the authors which there is influence between learning facilities on learning achievement with low categories, it is necessary to improve the effectiveness of the use of learning facilities and conducted other research on other factors that affect student achievement in addition to learning facilities.</p>

Not infrequently when students are dealing with mathematics problems where they do not know what they are supposed to do. Learning in class can make a habituation to students. This study aims to determine whether or not there are differences in achievement of students' Low Order Thinking (LOT) abilities between learning Probing Prompting (PP) and Realistic Mathematics (MR) on quadrilateral. This research was conducted with a total sample of 60 students, namely 30 students in class VIIA with PPL learning and 30 students in class VIIB with MR learning. The sample selection is done by cluster random sampling. The instruments used to collect data are based on three dimensions in the cognitive domain, namely remembering, understanding, and applying. The results of the descriptive statistical analysis of the data show that the average achievement of students in the PP learning group is equal to 75.36, while the average achievement of students in the MR group is equal to 68.53. Based on the results of data analysis, it was concluded that there were significant differences in the LOT achievement of students between PP and MR learning on Quadrilateral material.

A company, majorly company that active in commercial (profit orientation) need to analyze their sales performance. By analyzing sales performance, company can increase their sales performance. One of method to analyze sales performance is by collecting historical data that relates to sales and then process that data so that produce information that show company sales performance. A data warehouse is a set of data that has characteristic subject oriented, time variant, integrated, and nonvolatile that help company management in processing of decision making. Design of data warehouse is started from collecting data that relate to sales such as product, customer, sales area, sales transaction, etc. After collecting the data, next is data extraction and transformation. Data extraction is a process f or selecting data that will be loaded into data warehouse. Data transformation is making some change to the data afte r extracted to be more consistent. After transformation processing, data are loaded into data warehouse. Data in data warehouse is processed by OLAP (On Line Analytical Processing) to produce information. Information that are produced from data processing by OLAP are chart and query reporting. Chart reporting are sales chart based on cement type, sales chart based on sales area, sales chart based on plant, monthly and year ly sales chart, and chart based on customer feedback. Query reporting are sales based on cement type, sales area, plant and customer. Keywords: Data warehouse; OLAP; Sales performance analysis; Ready mix market

Index selection is one of the most important decisions to take in the physical design of relational data warehouses. Indices reduce significantly the cost of processing complex OLAP queries, but require storage cost and induce maintenance overhead. Two main types of indices are available: mono-attribute indices (e.g., B-tree, bitmap, hash, etc.) and multi-attribute indices (join indices, bitmap join indices). To optimize star join queries characterized by joins between a large fact table and multiple dimension tables and selections on dimension tables, bitmap join indices are well adapted. They require less storage cost due to their binary representation. However, selecting these indices is a difficult task due to the exponential number of candidate attributes to be indexed. Most of approaches for index selection follow two main steps: (1) pruning the search space (i.e., reducing the number of candidate attributes) and (2) selecting indices using the pruned search space. In this paper, we first propose a data mining driven approach to prune the search space of bitmap join index selection problem. As opposed to an existing our technique that only uses frequency of attributes in queries as a pruning metric, our technique uses not only frequencies, but also other parameters such as the size of dimension tables involved in the indexing process, size of each dimension tuple, and page size on disk. We then define a greedy algorithm to select bitmap join indices that minimize processing cost and verify storage constraint. Finally, in order to evaluate the efficiency of our approach, we compare it with some existing techniques.

The growth of the construction industry in Indonesia triggers an increasing need for an efficient project management system, especially in presenting project data accurately and in real-time. PT Dream Island Development (PT DID), a specialist MEP contractor company, faces challenges in presenting project reports to executives because the data is still presented in the form of Excel tabulations which require up to three days of processing time and are difficult to interpret quickly. This research aims to design an interactive dashboard-based project data visualization system using Google Data Studio (Looker Studio) to present project information intuitively and responsively. The method used includes a software engineering approach with five main stages: requirements analysis, data warehouse design, ETL process using Pentaho Data Integration, visualization using Google Data Studio, and testing using User Acceptance Test (UAT). Project data from 2022-2024 was modeled using a star schema and displayed in four main dashboards: project cost, project value, project progress, and details per project. The test results showed a high level of user satisfaction with a functionality score of 93.5%, reliability 91.33%, usability 96%, and efficiency 94.66%. These findings indicate that the developed system effectively supports PT DID's needs in project monitoring and data-based decision-making. The system also has the potential to be replicated in other construction companies as an efficient and scalable business intelligence solution. This research contributes to the growing body of construction informatics by integrating Kimball’s nine-step methodology with modern data visualization tools to enhance project transparency and decision-making.

The research method used is descriptive quantitative approach with the type of research used is descriptive correlational research. Determination of the sample of this study is population research because the sample is less than 100. The level of learning motivation of adolescents who experience a broken home is 96.93% in the high category, the level of student learning achievement is 83.57% in the high category, the level of the relationship between adolescent learning motivation which multiplies broken home with student learning achievement is 0.331> 0.284. This shows that there is a relationship between adolescent learning motivation who multiplies broken home and student learning achievement. Based on the discussion and data analysis, it can be concluded that the hypothesis which states "there is a positive and significant relationship between the relationship between adolescent learning motivation who multiplies broken home and student achievement” can be accepted. Where, the higher the relationship of adolescent learning motivation who multiplies broken home with student learning achievement. This shows that there is a relationship between adolescent learning motivation who multiplies broken home and student learning achievement. Based on the discussion and data analysis, it can be concluded that the hypothesis which states "there is a positive and significant relationship between the relationship between adolescent learning motivation who multiplies broken home and student achievement in students class VII SMP Negeri 1 Perbaungan” can be accepted. Where, the higher the relationship of adolescent learning motivation who multiplies broken home with student learning achievement.

Information systems allow companies to collect a large number of operational and transactional data. Data warehouses are increasingly used by organisations to extract concise information supporting decision processes. However, data warehouse design strategies as well as the structure and content of the original database largely influence the effectiveness of such tools. Our research is focused on data-driven approaches, and in this paper, we present a solution that, based on a set of metrics measuring different characteristics of the original data sources, effectively supports the creation of data warehouses. Moreover, combining the indexes computed by our metrics on selected dimensions and measures, it is also possible to derive quantitative information on the design quality of the final data warehouse. To demonstrate the effectiveness of our solution, we briefly present the results obtained from the analysis of a selling data warehouse derived from a real-word ERP system.

Decision support applications require complex queries, e.g., multi way joins defining on huge warehouses usually modelled using star schemas, i.e., a fact table and a set of data dimensions (Papadomanolakis & Ailamaki, 2004). Star schemas have an important property in terms of join operations between dimensions tables and the fact table (i.e., the fact table contains foreign keys for each dimension). None join operations between dimension tables. Joins in data warehouses (called star join queries) are particularly expensive because the fact table (the largest table in the warehouse by far) participates in every join and multiple dimensions are likely to participate in each join. To speed up star join queries, many optimization structures were proposed: redundant structures (materialized views and advanced index schemes) and non redundant structures (data partitioning and parallel processing). Recently, data partitioning is known as an important aspect of physical database design (Sanjay, Narasayya & Yang, 2004; Papadomanolakis & Ailamaki, 2004). Two types of data partitioning are available (Özsu & Valduriez, 1999): vertical and horizontal partitioning. Vertical partitioning allows tables to be decomposed into disjoint sets of columns. Horizontal partitioning allows tables, materialized views and indexes to be partitioned into disjoint sets of rows that are physically stored and usually accessed separately. Contrary to redundant structures, data partitioning does not replicate data, thereby reducing storage requirement and minimizing maintenance overhead. In this paper, we concentrate only on horizontal data partitioning (HP). HP may affect positively (1) query performance, by performing partition elimination: if a query includes a partition key as a predicate in the WHERE clause, the query optimizer will automatically route the query to only relevant partitions and (2) database manageability: for instance, by allocating partitions in different machines or by splitting any access paths: tables, materialized views, indexes, etc. Most of database systems allow three methods to perform the HP using PARTITION statement: RANGE, HASH and LIST (Sanjay, Narasayya & Yang, 2004). In the range partitioning, an access path (table, view, and index) is split according to a range of values of a given set of columns. The hash mode decomposes the data according to a hash function (provided by the system) applied to the values of the partitioning columns. The list partitioning splits a table according to the listed values of a column. These methods can be combined to generate composite partitioning. Oracle currently supports range-hash and range-list composite partitioning using PARTITION - SUBPARTITION statement. The following SQL statement shows an example of fragmenting a table Student using range partitioning.

A data warehouse is a data repository which collects and maintains a large amount of data from multiple distributed, autonomous and possibly heterogeneous data sources. Often the data is stored in the form of materialized views in order to provide fast access to the integrated data. However, to maintain the data in the warehouse consistent with the source data is a challenging task in a multiple remote source environment. Transactions containing multiple updates at one or more sources further complicate the consistency issue. In this paper we first consider improving the refresh time of select-project-join (SPJ) type materialized views in a data warehouse by presenting a frequency-partitioned based algorithm, which takes into account the source update frequencies and the total space for auxiliary data. We then propose a solution in the design of data warehouses which can handle a variety of materialized views with different refreshment requirements.

Multi-company companies have challenges in managing subsidiary data into performance information of all subsidiaries in one window. This is due to the variation of data dimensions according to the business processes of each subsidiary. CV. XYZ is a holding company engaged in the food & beverage business. CV. XYZ currently manages three companies consisting of restaurants, catering, and tent and decoration rental. The problem faced by the company owner is the limited access to company performance in one information window. Each company has working papers in Excel format to record expenditure and income transactions. This article proposes the development of a website-based business intelligence system to overcome the problems of CV. XYZ. The purpose of developing a business intelligence system in this article is to provide access to the performance of each subsidiary in one website media. The business intelligence system is developed through the stages of data collection and analysis, data warehouse design, ETL process, and data visualization with Microsoft Power BI. The data warehouse design uses Kimball's nine-step method which produces a data warehouse with a star scheme. The developed Business Intelligence system was tested using the UAT method. The UAT test results show that the system development is following the company's needs as indicated by the UAT score of 92%

Big data represents a new technology for managing data with high velocity, volume, variety and contributes to creating value for companies. As quoted in1, capturing all queries made on the company website or from customer support calls, emails or chat lines, regardless of their outcome, may have significant value in identifying emerging trends. The Big Data Era has largely contributed in accelerating the development strategic plans issued from governments and research organisms, coving the management, exploitation and analysis of these data by taking into account the different V's of Big Data. Among these plans, we can cite for instance the development of: (a) large-scale platforms (ex. data-clusters, distributed data clusters), (b) Software Defined Environments (SDE) (ex. IBM SDE), (c) advanced programming paradigms (ex. map-reduce, Spark, etc.), Data Analytics Tools (Rapid Miner, Google Fusion Tables, Solver), (d) Visualization tools (Google Chart, Tableau, Oracle Visual Analyzer), and (h) high quality and valuable Knowledge Bases (KB), constructed either by academicians (e.g., Cvc, DBpedia, Freebase, and YAGO) and industrials (e.g., Google Knowledge Graph, Facebook Knowledge Graph, Amazon Knowledge Graph, Credit Rating Agencies, Enterprise Knowledge Base, etc.). In this talk, we would like to foster the creation of a think tank dedicated to getting the best from Big Data V's and the efforts related to it to revisit our research activities without compromising them. In this talk, we would like to share the experience conducted with our Model and Data Engineering Team of the LIAS Laboratory at ISAE-ENSMA, which aims at the design of data warehousing applications. Based on the literature, this design is based on two main approaches: (i) a supply-driven approach (also called data-driven) that starts with an analysis of operational data sources in order to identify all the available data and (ii) a user-driven approach (also known as requirement-driven or goal-orientated) which stems from the determination of the information requirements of different business users. Several studies and experiments show that resorting to these two approaches entails a high risk for companies, since some functional requirements cannot be satisfied. This is due to the lack of relevant data in sources. In parallel, reference studies have identified the crucial role of knowledge bases (KB) for analytical tasks, by offering analysts more entities (people, places, products, etc.). The availability of a huge, high quality valuable KB is an asset for data warehousing designers and decision-makers to construct/exploit a valuable data warehouse. So, faced with this situation, we here present a value-driven approach that revisits the traditional life cycle of the design of data warehouses, by considering KB as an external resource. These different phases are illustrated via the YAGO KB.

This study is objected to analyze the current system to find the problems being faced by ABC Hospital and propose alternative solutions. Additionally, it aims to generate information needed by the hospital management to design a data warehouse according to the information needs. The research method used is the analysis and design of data warehouse by Kimball and Ross cited by Connolly and Begg, known as Nine-Step Methodology. The result obtained is a data warehouse application with a multidimensional historical data that can assist the hospital management in decision making. Designing data warehouse at ABC Hospital makes enterprise data into concise and can be viewed from several dimensions that help users analyze data for strategic decision quickly and accurately.