Dimension Tables Research Articles

Development of Star-Schema Model for Lecturer Performance in Research Activities

In this study, the researchers developed a multidimensional data model to investigate the activities of lecturers in universities in carrying out research activities as part of the Three Pillars of Higher Education. Information about lecturers' research activities has been managed using spreadsheet (excel) documents. Thus, access and analysis of the information were limited. Data warehouse development was carried out through several stages, namely requirement analysis, data source analysis, multidimensional modeling, ETL process, and reporting. The information generated in this data warehouse (DW) can be used as one of the business intelligence (BI) models in universities. In this study, the star-schema model was used in designing dimension tables and fact tables to facilitate and speed up the query process. The information generated in this study can be used by management in universities to make decisions and strategic planning. The results of this study can also be used as one of the important information in the preparation of institutional accreditation data and study program accreditation.

Mechanism for securing cloud based data warehouse schema

Several studies have recommended encryption of data for securing Data Warehouse (DW) stored in hostile cloud networks. Most of the published work on encrypted DWs encrypts the DW data while DW Schema, i.e., Fact and Dimension table’s name and their attributes, remain unprotected. Unencrypted DW schema attributes may reveal critical information about the data contents to the cloud administrators or attackers. In this paper, we propose a novel mechanism for encrypting column names of DW’s fact and dimension tables, which in turn protect the revelation of details of interesting columns to the cloud network. For this, we have proposed a customized method for encryption of column names of facts and dimension tables and implemented it in MySQL’s column name restrictions to test its validity. This paper also introduces an Enhanced Encryption Model for Data Warehouse, which provides a complete solution for securing data warehouse. Here, the column name gets encrypted with the help of keys from a secure host without the revelation of security details to the cloud network. It reduces the attacker’s ability to target strategically important columns such as sales figures, cost, etc. As a result, it increases attacker effort by (n – k − W)/(n − k) in case of a weighted column scenario. Here, ‘n’ is the total number of columns, ‘k’, and ‘W’ is the number of unencrypted and encrypted columns, respectively. We have also conducted a performance analysis of the proposed mechanism on the standard TPC-H database for both encryption and decryption cycles.

Genetics of Lactose Intolerance: An Updated Review and Online Interactive World Maps of Phenotype and Genotype Frequencies.

In humans the ability to digest milk lactose is conferred by a β-galactosidase enzyme called lactase-phlorizin hydrolase (LPH). While in some humans (approximately two-thirds of humankind) the levels of this enzyme decline drastically after the weaning phase (a trait known as lactase non-persistence (LNP)), some other individuals are capable of maintaining high levels of LPH lifelong (lactase persistence (LP)), thus being able to digest milk during adulthood. Both lactase phenotypes in humans present a complex genetic basis and have been widely investigated during the last decades. The distribution of lactase phenotypes and their associated single nucleotide polymorphisms (SNPs) across human populations has also been extensively studied, though not recently reviewed. All available information has always been presented in the form of static world maps or large dimension tables, so that it would benefit from the newly available visualization tools, such as interactive world maps. Taking all this into consideration, the aims of the present review were: (1) to gather and summarize all available information on LNP and LP genetic mechanisms and evolutionary adaptation theories, and (2) to create online interactive world maps, including all LP phenotype and genotype frequency data reported to date. As a result, we have created two online interactive resources, which constitute an upgrade over previously published static world maps, and allow users a personalized data exploration, while at the same time accessing complete reports by population or ethnicity.

Perancangan Data Warehouse Perguruan Tinggi untuk Kinerja Penelitian dan Pengabdian kepada Masyarakat

Accreditation is an evaluation to ensure the quality of a study program at higher education by completing two main documents for qualitative data and self-evaluation, namely Study Program Performance Report (LKPS) and Self Evaluation Report (LED) respectively. Data used for this process must be consistent and accurate, therefore a central repository is proposed. A data warehouse for the domain of Research and Community Service is built using the snowflake schema and a five-step approach methodology proposed for the educational data warehouse. The schema consisted of one fact table, ten dimension tables, and four bridge tables. Using a business intelligence tool, data from the data warehouse is queried and provided in a dashboard. The data warehouse successfully returned the qualitative data needed for LKPS. As for the evaluation data for LED, the data warehouse is able to fulfill seven out of ten requirements. The data for the remaining three are provided with a slightly different orientation to the requirement, this was due to the data source.

ATrie Group Join: A Parallel Star Group Join and Aggregation for In-Memory Column-Stores

This article presents a new holistic and efficient approach to big data analysis. We introduce a new parallel algorithm, known as <italic xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">ATrie Group Join (ATGJ)</i> , that integrates join, grouping and aggregation operations to accelerate big data analytical workloads in in-memory column-stores. ATGJ performs a single scan of the fact columns and uses a mixture of data and task parallelism for the optimal use of computing resources. It uses a novel technique to perform group-by and aggregation realising the grouping attributes as a tree shaped deterministic finite automation known as <italic xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">Aggregate Trie</i> or <italic xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">ATrie</i> . ATrie facilitates grouping of attributes and processing of data in tight loops that significantly improves the performance on modern hardware. Unlike other competing algorithms, use of ATrie avoids the creation of multiple data structures with the increasing number of dimension tables and grouping attributes. Also, we demonstrate that ATGJ performs efficiently even when the ATrie becomes bushy. We evaluated the algorithm using Star Schema Benchmark (SSBM) to show that it is significantly faster and scales better than other algorithms for the number of concurrent threads, the number of group-by attributes, the data set size and the query complexity.

Anthropometry and Size Groups in the Clothing Industry

Abstract It appears that from generation to generation the anthropometric dimensions of the human population are changing. The aim of this paper was to examine the extent of these changes and the need for generating updated measurements for the clothing industry. The clothing industry uses mannequins and avatars to represent the modal group of the population. The industry tends to use three different categories for the human body shape (endomorphic, mesomorphic, and ectomorphic). The clothing industry should focus on specific measurements of the body rather than general categories and create more body shapes to satisfy customer needs. The paper also aimed at showing the problems faced by clothing designers. The traditional way of measuring takes into account only selected dimensions of the human body; this does not reflect the “true” overall body shape. The dimension tables used by the apparel industry are based on the fourth anthropometric photograph taken between 1987 and 1989. These tables are still in the use currently; however, after 30 years they are outdated and should be revised for the young contemporary generation. This study can be used for the development of new dimension tables as well as defining methods aimed at improving the quality of measurements for clothing engineering purposes. This is an important issue, because the National Institute of Anthropometry does not deal with such problems (the measurements are conducted mainly for understanding the human body shape rather than any other application), which means that anthropometric measurements are not ideally suited to applications of clothes fitting.

Lossless Data Deduplication: Alternatif Solusi untuk Mengatasi Duplicated Record

The implications of poor data quality bring negative effects for organisation through: increased operational costs, inefficient decision-making processes, lower performance and decreased both employee and customer satisfaction. Generally duplicated records can be handled by elimination or merge, but when duplicated records are occur in master table and used in a transaction the handling becomes not easy. This paper seeks to provide data deduplication solutions without losing the historical value of the transaction. To save all duplicated records which related to the transactions we use mapping table between Dimension table and facs table. Using this approach the quality of Dimension table increased since for this handling duplicated records process include enrichment process and delete dirty records and all dupplicated records which related to the transactions can be access completely in data warehouse, no transaction data loss.

A MODEL AND IMPLEMENTATION OF ACADEMIC DATA INTEGRATION IN NEAR-REAL TIME USING MESSAGE-ORIENTED MIDDLEWARE TO SUPPORT ANALYSIS OF STUDENT PERFORMANCE IN THE INFORMATION TECHNOLOGY DEPARTMENT OF POLITEKNIK CALTEX RIAU

Data utilization has effectively contributed for institutions growth by providing insights for managerial purposes. In Information Technology (IT) Department of Politeknik Caltex Riau, information systems were built separately, makes it hard for the head of study program to analyze academic performance. For analytical purposes, there’s a business intelligence developed to equip each head of study programs in IT Department with knowledge about their department. Unfortunately, the business intelligence hasn’t considered with data integration. To solve this problem, this research proposes 2 different academic near-real time data integration model that are documented using Enterprise Integration Pattern and benchmarkes the implementation to obtain best data integration model. The models use Message-Oriented Middleware, a technology that enables asynchronous communication between diverse applications. This research uses WSO2 ESB as the MOM tools in Service-Oriented Architecture (SOA) that use NuSOAP library for helping generating web service WSDL and will use Enterprise Application Integration approach. The testing is conducted based on ISO 9126 aspects: functionality, efficiency, and reliability. Based on the testing results, it can be concluded that both integration models fulfill the functionality and reliability aspects, but the 2nd pattern is more efficient because it distincts message channel and store for each dimension and fact table.

Extract transform load (ETL) process in distributed database academic data warehouse

While a data warehouse is designed to support the decision-making function, the most time-consuming partis the Extract Transform Load (ETL) process. Case in Academic Data Warehouse, when data source came from thefaculty’s distributed database, although having a typical database but become not easier to integrate. This paperpresents how to an ETL process in distributed database academic data warehouse. Following Data Flow Threadprocess in the data staging area, a deep analysis performed for identifying all tables in each data sources, includingcontent profiling. Then the cleaning, confirming, and data delivery steps pour the different data source into the datawarehouse (DW). Since DW development using bottom-up Kimball’s multidimensional approach, we found the threetypes of extraction activities from data source table: merge, merge-union, and union. Result for cleaning andconforming step set by creating conform dimension on data source analysis, refinement, and hierarchy structure. Thefinal of the ETL step is loading it into integrating dimension and fact tables by a generation of a surrogate key. Thoseprocesses are running gradually from each distributed database data sources until it incorporated. This technicalactivity in distributed database ETL process generally can be adopted widely in other industries which designer musthave advance knowledge to structure and content of data source.

Data warehouse model for population registration in Kerambitan village Tabanan Regency

Kerambitan village is one of the 15 villages in Kerambitan sub-district, Tabanan regency, Kerambitan village has 7 service hamlets. Kerambitan village population has more than 3,338 inhabitants consisting of 1,700 men and 1,638 women with a sex ratio of 103.79. . To realize good governance, it needs to be recorded well so that the information can be utilized by related agencies which can be used for receiving assistance.&#x0D; During this time the process of recording population events such as recording the receipt of assistance is done manually where the information is done in tabular form so it does not clearly describe the respective information groups.&#x0D; Data warehouse (DW or DWH), also known as enterprise data warehouse (EDW), is a system used for reporting and analyzing data, and is considered a core component of business intelligence, The purpose of building a data warehouse is to provide a system that allows the right data to reach the right end user at the right time. Thus, the main purpose of implementing this data warehouse system is to provide relevant and timely information in an easily understood format so that service decisions to the public can be made more efficiently and effectively.&#x0D; the purpose of this research is to form a data warehouse model scheme of population records owned by kerambitan village government and through modeling this data warehouse also provides reliable information from a collection of data, in addition this research also retrieves some information from the data warehouse in build based on kerambitan village population records&#x0D; in this study I chose to use the fact constellation scheme because there are fact tables that are interconnected with dimension tables and also fact tables that are related to other fact tables.&#x0D; The design model of the data warehouse is designed based on 3 excel table files, each of which has 1 table from the Government of Kerambitan Villages. In this case, 3 reports are proposed to be made based on the query results from the data warehouse.&#x0D; Through the application of a data warehouse that was formed, the executive or government can conduct analysis of the reports generated based on various dimensions that exist.

PEMBUATAN DASHBOARD PENJUALAN DAN PEMBELIAN HEVINDO SPORT

Dashboards are used to present quality information on sales and purchases made available to managers or owners of a business. The purpose of this thesis is to assist shop owners in knowing the state of the sale and purchase of Hevindo Sport. Hevindo Sport is a shopping center engaged in sports, especially badminton. Dashboard uses the data warehouse Extract, Transform, and Load (ETL) method for the importing process. Importing ETL with the process of making dimension tables and fact tables. The dimensions of the table consist of the importing master table in SQL Server 2018. The fact table will be created after all dimension tables are finished and then create a dashboard. The results will be displayed in graphical form with the Tableau tools. The results of the dashboard are used to make decisions on the sale of goods reached in the safe zone and monitor the entry and exit of existing goods.

Distributed ATrie Group Join: Towards Zero Network Cost

The combination of powerful parallel frameworks and on-demand commodity hardware in distributed computing has made both analytics and decision support systems canonical to enterprises of all sizes. The unprecedented volumes of data stacked by companies present challenges to process analytical queries efficiently. This data is often organised as star schema, in which star join and group-by are ubiquitous and expensive operations. Although parallel frameworks such as Apache Spark facilitate join and group-by, the implementation can only process two tables at a time and fail to handle the excessive network communication, disk spills and multiple scans of data. In this paper, we present Distributed ATrie Group Join (DATGJ), a fast distributed star join and group-by algorithm for column-stores. DATGJ uses divide and broadcast-based joining technique where the fact table columns are partitioned equally and fast hash table (FHT) for each dimension table are broadcasted. This technique helps it avoid cross communication between workers and disk spills. DATGJ performs a single scan of partitioned fact table columns and use FHT to join data. FHT uses Robin Hood hashing with the upper limit on number of probes and achieve significant speed up during join. DATGJ performs group-by and aggregation leveraging progressive materialisation and realising grouping attributes as a tree shaped deterministic finite automation known as Aggregate Trie or ATrie. We evaluated our algorithm using Star Schema Benchmark (SSBM) to show that it is 1.5X to 6X faster than the most prominent approaches while having zero data shuffle and consistently perform well with addition of resources and in memory-constrained scenarios.

OLAP Approach in Searching Manufacturing Industries in West Sumatera

The manufacturing industry is a business that uses machines, tools and workers in the process of converting raw materials into finished goods that can be sold. The manufacturing industry has an important role in the Indonesian economy. The industry contributed a large income to the country. In West Sumatra, there are many manufacturing industries that are growing and developing both on a medium scale and large scale which are spread throughout the cities and districts. So that it makes it difficult for the Department of Industry of West Sumatra Province to collect data on the existence of manufacturing industries. To solve this problem, approach of Online Analitycal Processing (OLAP) is used to search data in a hierarchical manner. OLAP is designed using fact tables and several dimension tables. OLAP will provide the number of industries as a measure . This research can help the people of West Sumatra to find out about the local manufacturing industry and what products the industry produces. In the end, local industrial products will be favored by many local people and increase the income of the province of West Sumatra.

The Development of Data Warehouse for Contractor System

In the middle of the crowded infrastructure of facility and infrastructure while the data of project budget has been increasing every day, PT XYZ requires new technology of data warehouse in order to respond the needs of recording and extracting project budget data quickly and accurately because Ms Excel, recent software used by managing-data staff to store data manually, is fairly time-consuming to record and extract daily realization of project budget. Therefore, in order to help the company to learn the result of the project budgets that have already been spent for the various needs of project construction, the technology of data warehouse should be present in order to extract and record data more consistently and time-saving. Data warehouse technology to support the development of Data Warehouse is Pentaho because Pentaho is database application that is supported with database MYSQL and OLAP feature as a means to analyze and process data contained in multidimensional tables once the process of ETL is executed. ETL means the process of selecting and extracting data from one source into the designated source in order to produce better-quality data result. OLAP is developed by using pivot 4j view integrated by pentaho server to map the structure of fact and dimension table properly so that, the tables can get a proper connection to display desired information in term of graphs for the need of dashboard display. The dashboard that is coded through mdx queries in CDE Dashboard shows the final result of either monthly or yearly project budget obviously and reliably so that the company can learn the report and make decision that needs to be taken to control the budget costs and maintain the company profit. Both CDE Dashboard and pivot 4j view are integrated with pentaho server. Finally, report is presented by using pentaho report designer as a means to extract needed data more rapidly and consistently because data are called through id stored in different table, but the same field.

Extract Transform Load (ETL) Process in Distributed Database Academic Data Warehouse

While data warehouse is designed to support the decision-making function, the most time-consuming part is Extract Transform Load (ETL) process. Case in Academic Data Warehouse, when data source came from faculty’s distributed database, although having a typical database but become not easier to integrate. This paper presents the ETL detail process following Data Flow Thread in data staging area for identifying, profiling, the content analyzing including all tables in data sources, and then cleaning, confirming dimension and data delivery to the data warehouse. Those processes are running gradually from each distributed database data sources until it merged. Dimension table and fact table are generated in a multidimensional model. ETL tool is Pentaho Data Integration 6.1. ETL testing is done by comparing data source and data target and DW testing conducted by comparing the data analysis between SQL query and Saiku Analytics plugin in Pentaho Business Analytic Server.

Model data warehouse untuk Operasional petugas pemadam kebakaran Pada dinas pemadam kebakaran provinsi DKI Jakarta

In this paper we design 2 data warehouse design models for the DKI Jakarta fire department. The first data warehouse design model is categorized as a fact constellation schema, where there are 3 fact tables, 2 dimensional tables and 1 sub dimension table, while the second data warehouse design model is a fact constellation schema which consists of only 3 fact tables as well. Both of these data warehouse models are displayed with the intention that the implementation of the data warehouse based on the fire extinguisher data will look different and that will certainly be faster by using the 2nd model which is categorized as an non normal data warehouse model which only consists of 3 fact tables which the lack of a join process in the fact table and the dimensions of the data warehouse will speed up the query process in building the data warehouse report. The design model of the data warehouse is designed based on 4 excel table files, each of which has 1 table downloaded from the DKI Jakarta fire department's website on the website data.jakarta.go.id.

Modeling of image, video and text fusion quality data packet system for aerospace complex products based on business intelligence

For the construction of image, video and text fusion quality data packet system during the whole life of complex products, a business intelligence-based logic modeling method is proposed in this paper. As the amount of Polymorphic data from multiple distributed sources continues to grow exponentially, automation tools are becoming critical to decision makers. The balanced scorecard method is used as the basis for modeling, and the traditional dimensions are modified slightly to meet the requirements of quality data management in aerospace enterprises. A data warehouse with predefined fact and dimension tables is created, and a technical solution is provided to meet the requirements and scales of enterprises. In terms of model applications, any enterprise quality manager can extract value from data in the quality data package system for improvement. Online analytical processing (OLAP) cubes support major tasks, such as key quality feature source tracing and analysis, quality issue data mining and integrated quality data delivery.

Chabok: a Map-Reduce based method to solve data warehouse problems

Currently, immense quantities of data cannot be managed by traditional database management systems. Instead, they must be managed by big data solutions using shared nothing architectures. Data warehouse systems are systems that address very large amounts of information. The most prominent data warehouse model is star schema, which consists of a fact table and some number of dimension tables. It is necessary to join the facts and dimensions for query executions on the data warehouse. In shared nothing architecture, all of the required information is not placed on a single node so it is necessary to retrieve information from other nodes, which causes network congestion and low speeds of query execution. To avoid this problem and achieve maximum parallelism, dimensions can be replicated over nodes if they are not too large. However, if there are dimensions with data volumes greater than the capacity of a node or dimensions where the data volume summation exceeds node capacity, the query execution is confronted with serious problems. In big data problems, the amount of data is immense, and thus replicating immense data cannot be considered an appropriate method. In this paper, we propose a method called Chabok, which uses two-phased Map-Reduce to solve the data warehouse problem. In this method, aggregation is performed completely on Mappers, and intermediate results are sent to the Reducer. Chabok does not need data replication for join omission. The proposed method was implemented on Hadoop, and TPC-DS queries were executed for benchmarking. The query execution time on Chabok surpassed prominent big data products for data warehousing.

Convolutional Neural Network Based Structured Data Field Granularity Classification Method

The data warehouse provides data support for enterprise decision-making and online analysis. In the process of building a data warehouse, many heterogeneous source system data needs to be integrated. In the integration process, these heterogeneous data needs to be classified and put into different topics. The diversity of the source systems of large and medium-sized enterprises poses difficulties for granularity at the field-level automated classification. However, the accuracy of previous methods cannot satisfy users. Therefore, this paper proposes a neural network-based classification technology to classify the data in the granularity field. This method adopts a sampling method to construct the characteristics of the field and innovates a novel classification framework based on the database field on the basis of the CNN network. Accurately achieve 89% by testing the data in the TPC-DS’s dimension tables, and achieve 93% accuracy in real-world data testing. This method was validated in the actual environment of the three banks in China and achieved satisfactory results.

A big data analytics platform to support simulation modeling for osteoarthritis care pathways

IntroductionTechnical solutions have been used in industry settings for many years to facilitate efficient management and analyses of big data sources. An initiative to apply a business solution to support development of simulation models for health systems research using nearly two decades of provincial administrative health data is described.&#x0D; Objectives and ApproachAdministrative data including practitioner claims, hospitalizations and ambulatory care visits for patients with a diagnosis of osteoarthritis were obtained from Alberta Health for the period 1994/95 to 2012/13. These data were incorporated into a multidimensional data cube using Microsoft SQL Server Analysis Services. Initial steps required dimensional modeling to restructure the data into a star schema format. This involved appending several data sets and defining additional reference tables to contain stratification variables and denominator data for rate calculations. The modeling expert worked closely with the information technology team throughout the process and assessed validity of the output.&#x0D; ResultsDevelopment and validation of the multidimensional cube occurred in iterations over approximately 12 months. The final solution resulted in an analytics platform that compiled data from approximately 400 million records obtained from four different administrative data sources. Ten dimension tables containing 102 variables provided enhanced flexibility to conduct ad hoc stratified analyses in a fraction of the time that would be required using conventional methods. For example, some analyses that previously required a day of analyst time could be performed in less than 15 minutes. The efficiencies in analytic time were achieved by the pre-aggregated measures and slice and dice capability of the data cube, which negated many intermediary steps for data extraction and time consuming iterative analyses required for development of the simulation models.&#x0D; Conclusion/ImplicationsThis project demonstrated how a technical solution applied in industry can be utilized to address challenges encountered by researchers related to managing and analyzing large administrative health data sets. The methods could be applied in many other research settings to facilitate access to and analyses of information using big data.