Fact Table Research Articles

Implementing Online Analytical Processing in Hotel Customer Relationship Management

Online Analytical Processing (OLAP) is increasingly being used by applying technology concepts that use a multidimensional view of grouping data to provide quick access to strategic information for analysis purposes. In the tourism industry, especially hospitality, this is very useful, especially in processing hotel operational data. Although OLAP technology has been widely applied in the hotel industry in reporting business sales, marketing, and reporting management analysis, there is still little research that discusses customer activity analysis. This study develops an OLAP-based analytical CRM system to analyze customer data and classify it into two main segments: geographically and demographically. Transactional data for hotels that have existed for the last three years are converted into a data warehouse, including the ETL process from the original database to a star schema database. It has a fact table and dimensions. Furthermore, OLAP cube operations are performed and generate customer reports. Testing in this study was carried out by calculating the total data’s access speed and accuracy from 100 to 5000 customers, and the estimated value was 45.50 to 80 milliseconds in 30 experiments. For further research, it can be developed by integrating data from the hotel transactional system so that the analytical process can run in real-time.

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

Data analysis has become a vital requirement in various fields. The results of data analysis can be used as a data warehouse design that is expected to help in solving problems, evaluating learning outcomes and supporting decision making. In the field of education, student achievement is a reference for achieving quality learning. This success can be seen from the achievement of student learning completeness that is the basis for evaluating and making decisions. The data warehouse design can be used as a basis in knowing student learning progress that can be seen from the value obtained. The diversity of data makes managing values difficult and delayed, data warehouse design using Pentaho can help and simplify data integration. The results of the analysis and design of the data warehouse will be presented in a multidimensional form that can be seen through dimension tables and fact tables.

Modeling of digital marketing systems construction using olap – technologies

The provision of information to the marketing information system is investigated in this paper. The collection of information on the server side and on network nodes is considered. It is stated that the addition of any information collection software to the server is not possible or can slow down the server. It is proposed to place sensors in network nodes on the approach to the server, which unloads the server from unnecessary software. An example of such Web Traffic Warehouse system is considered. Here the work is carried out at the level of protocols and data collection takes place at the level of TCP/IP packets. Three different approaches to the analysis of transactions are analyzed: identification of transactions taking into account the visits duration, identification of transactions by the method of maximum reference depth and division of transactions according to temporal principles. The scheme of the system of operative processing of Internet data on the basis of OLAP-technologies which is based on the principle of collecting operative data from various sources which then are cleared, integrated and put in relational storage is constructed. Also, the definition of OLAP as a set of tools for multidimensional analysis of data stored in the repository is investigated. It provides the company with the most convenient and fast means of accessing, viewing and analyzing business information, and provides the user with natural, intuitive data model, organizing them into multidimensional cubes, the axes of the multidimensional coordinate system are the main attributes of the analyzed business process. The problem of integration of data warehouses (accounting system) with formation systems (data showcases) is analyzed. Based on the analysis, the model of the distribution-order-sale data show window is built, which is based on two types of tables – fact tables and measurement tables is constructed. Thus, the table of sales facts in the developed system contains such fields as Cusomerfact ID, Salesperson, Product ID, Quantity Sold, Discount, Total Amount, etc. Due to the model it is highlighted that while creating show windows there is the organization of three key stages – obtaining data from the source systems, converting them into the desired form and then loading them into the target system.

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.

Open Spatiotemporal Data Warehouse for Agriculture Production Analytics

Business Intelligence (BI) technology with Extract, Transform, and Loading process, Data Warehouse, and OLAP have demonstrated the ability of information and knowledge generation for supporting decision making. In the last decade, the advancement of the Web 2.0 technology is improving the accessibility of web of data across the cloud. Linked Open Data, Linked Open Statistical Data, and Open Government Data is increasing massively, creating a more significant computer-recognizable data available for sharing. In agricultural production analytics, data resources with high availability and accessibility is a primary requirement. However, today’s data accessibility for production analytics is limited in the 2 or 3-stars open data format and rarely has attributes for spatiotemporal analytics. The new data warehouse concept has a new approach to combine the openness of data resources with mobility or spatiotemporal data in nature. This new approach could help the decision-makers to use external data to make a crucial decision more intuitive and flexible. This paper proposed the development of a spatiotemporal data warehouse with an integration process using service-oriented architecture and open data sources. The data sources are originating from the Village and Rural Area Information System (SIDeKa) that capture the agricultural production transaction in a daily manner. This paper also describes the way to spatiotemporal analytics for agricultural production using a new spatiotemporal data warehouse approach. The experiment results, by executing six relevant spatiotemporal query samples on DW with fact table contains 324096 tuples with temporal integer/float for each tuple, 4495 tuples of field dimension with geographic data as polygons, 80 tuples of village dimension, dozens of tuples of the district, subdistrict, province dimensions. The DW time dimension contains 3653 tuples representing a date for ten years, proved that this new approach has a convenient, simple model, and expressive performance for supporting executive to make decisions on agriculture production analytics based on spatiotemporal data. This research also underlines the prospects for scaling and nurturing the spatiotemporal data warehouse initiative.

A New Big Data Benchmark for OLAP Cube Design Using Data Pre-Aggregation Techniques

In recent years, several new technologies have enabled OLAP processing over Big Data sources. Among these technologies, we highlight those that allow data pre-aggregation because of their demonstrated performance in data querying. This is the case of Apache Kylin, a Hadoop based technology that supports sub-second queries over fact tables with billions of rows combined with ultra high cardinality dimensions. However, taking advantage of data pre-aggregation techniques to designing analytic models for Big Data OLAP is not a trivial task. It requires very advanced knowledge of the underlying technologies and user querying patterns. A wrong design of the OLAP cube alters significantly several key performance metrics, including: (i) the analytic capabilities of the cube (time and ability to provide an answer to a query), (ii) size of the OLAP cube, and (iii) time required to build the OLAP cube. Therefore, in this paper we (i) propose a benchmark to aid Big Data OLAP designers to choose the most suitable cube design for their goals, (ii) we identify and describe the main requirements and trade-offs for effectively designing a Big Data OLAP cube taking advantage of data pre-aggregation techniques, and (iii) we validate our benchmark in a case study.

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.

Coordinating Efforts to Define Marine Plankton Imagery Data and Metadata Best Practices and Standards

“Imagery data” can be referred as qualitative and quantitative information from a collection of images. Imaging systems are used more and more frequently in the marine domain to generate huge amounts of imagery data. For example, automatic image classification is used to determine the abundance, size and biomass of plankton communities. In addition, the recent advances of imaging sensors and the growing datasets, highlight the importance of the management and storage capacity of these data. Thus, establishing data standards, optimized data flows and quality control procedures will promote the ability to make these datasets findable, accessible, interoperable and reusable (FAIR principles). At the moment, there are a number of online open-access databases that collect marine biodiversity data, such as the Ocean Biodiversity Information System (OBIS), and more specialized in plankton observations such as the COPEPOD for plankton biomass and ECOTAXA for taxonomic annotation of plankton images. However, they currently lack relevant standards to link metadata of the images. International biodiversity data standards, such as Darwin Core (DwC), are already widely used in OBIS and the Global Biodiversity Information Facility (GBIF). OBIS, has recently adopted the OBIS-ENV-DATA, a format that follows the DwC-Archive (DwC-A) standard, consisting of a DwC Event table in combination with an Occurrence an extended Measurement or Fact table. This structure enables the linkage of quantitative and qualitative properties to both sampling events and species occurrences. It also includes additional fields for property standardization, such as the BODC (British Oceanographic Data Centre) controlled vocabularies, the World Register of Marine Species (WoRMS), which provides crucial quality control support for taxonomic data, and Marine Regions, a database that provides standardized marine georeferenced place names and areas. However, there are no clear guidelines on how to include imagery metadata and derived data in OBIS-ENV-DATA. During this TDWG 2020 symposium, we would like to present and discuss our ongoing work to establish best practices and standardized protocols for imaging data and metadata acquired by a large spectrum of bio-optic sensors. Furthermore, we highlight how the integration of the metadata will flow to existing biodiversity data portals, the European EMODnet Biology portal, (EurOBIS) and OBIS.

Image-based soft drink type classification and dietary assessment system using deep convolutional neural network with transfer learning

Nowadays, people are taking soft drinks (carbonated nonalcoholic beverages) at an increasing rate. Health experts around the world have cautioned from time to time that these drinks lead to weight gain, raise the risk of non-communicable diseases, and so on. To develop consciousness among people, the present work describes an image-based tool to self-monitor the nutritional information of soft drinks by using a deep convolutional neural network (CNN) along with transfer learning. At first, a pre-processing function is done through noise reduction and contrast enhancement. Then the location of the drinks region is extracted through visual saliency and mean-shift segmentation technique. After removing backgrounds and segment out only the region of interest from the image a deep CNN-based transfer learning model is employed for the drink classification. Finally, the size of each drink bottle is estimated using the bag-of-feature (BoF) and distance ratio calculation to find the nutrition value from the nutrition fact table. To perform experimentation a dataset is built containing ten most consumed soft drinks in Bangladesh using images from the ImageNet dataset, internet sources and also self-capturing. The experiment confirms that our system can detect and recognize different types of drinks with an accuracy of 98.51%.

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.

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.
 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.
 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.
 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
 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.
 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.
 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.

DASHBOARD PENGUKURAN KINERJA PROGRAM STUDI PERGURUAN TINGGI

Dashboard is a tool that used for serving information in diagram model, visual indicator or graphic that make simply summary of information. Purpose of the thesis is to make an application for development dashboard that could used for measuring the performance of study program. Method that used is Extract, Transform, Load (ETL) and Method Software Engineering. ETL Method is importing the data to SQL SERVER and then transform it into a data master which easier to process to be a fact table data and ready to be the database master, and the software engineering method is a method Business Intelligence development derived from the method of software engineering is justification, planning, Business Analysis, Design, Construction, Deployment. The Result of dashboard design that can show and monitoring data of lecturers, employees, college student, research faculty, and completed research.

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.

Bitemporal versioning of changing measures in bitemporal data warehouses

Bitemporal data warehouse (BTDW) design that extends the multidimensional model uses bitemporal (combination of valid time and transaction time) timestamps on the members of multidimensional data in order to manage time-varying data. Various approaches to manage changes of schema and dimension data in BTDW exist that use the combination of both valid-time as well as transaction time. Also, measures/facts can undergo change with time however current BTDWs do not handle or track changes for dynamic measures. This would pose problem for applications like fraud detection, where it is required to know and record the time when changes have occurred in the system; schema, dimension data or measures. Thus, the changes occurring in the measures need to be recorded indicating the time when a specific measure value is valid or updated. In this work, we propose solution for handling time-varying measures using bi-temporal versioning of fact table schema and fact data along with storage options for handling and storing the separate versions of fact table schema and data.

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.

DPSAaS

Differential privacy has emerged as the de facto standard for privacy definitions, and been used by, e.g ., Apple, Google, Uber, and Microsoft, to collect sensitive information about users and to build privacy-preserving analytics engines. However, most of such advanced privacy-protection techniques are not accessible to mid-size companies and app developers in the cloud. We demonstrate a lightweight middleware DPSAaS , which provides <u>d</u> ifferentially <u>p</u> rivate data-<u>s</u>haring-and-<u>a</u>nalytics functionality <u>a</u> s cloud <u>s</u>ervices. We focus on multi-dimensional analytical (MDA) queries under local differential privacy (LDP) in this demo. MDA queries against a fact table have predicates on (categorical or ordinal) dimensions and aggregate one or more measures. In the absence of a trusted agent, sensitive dimensions and measures are encoded in a privacy-preserving way locally using our LDP data sharing service, before being sent to the data collector. The data collector estimates the answers to MDA queries from the encoded data, using our data analytics service. We will highlight the design decisions of DPSAaS and twists made to LDA algorithms to fit the design, in order to smoothly connect DPSAaS to the data processing platform and analytics engines, and to facilitate efficient large-scale processing.