8 - Dimensions

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.

Why is the snowflake schema a good data warehouse design?

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.

A data warehouse can store very large amounts of data that should be processed in parallel in order to achieve reasonable query execution times. The MapReduce programming model is a very convenient way to process large amounts of data in parallel on commodity hardware clusters. A very popular query used in data warehouses is star-join. In this paper, we present a fast and efficient star-join query execution algorithm built on top of a MapReduce framework called Hadoop. By using dynamic filters against dimension tables, the algorithm needs a single scan of the fact table, which means a significant reduction of input/output operations and computational complexity. Also, the algorithm requires only two MapReduce iterations in total-one to build the filters against dimension tables and one to scan the fact table. Our experiments show that the proposed algorithm performs much better than the existing solutions in terms of execution time and input/output. Copyright © 2014 John Wiley & Sons, Ltd.

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.

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.

This paper proposes the compression of data in Relational Database Management Systems (RDBMS) using existing text compression algorithms. Although the technique proposed is general, we believe it is particularly advantageous for the compression of medium size and large dimension tables in data warehouses. In fact, dimensions usually have a high number of text attributes and a reduction in their size has a big impact in the execution time of queries that join dimensions with fact tables. In general, the high complexity and long execution time of most data warehouse queries make the compression of dimension text attributes (and possible text attributes that may exist in the fact table, such as false facts) an effective approach to speed up query response time. The proposed approach has been evaluated using the well-known TPC-H benchmark and the results show that speed improvements greater than 40% can be achieved for most of the queries.KeywordsText AttributeData CompressionData WarehouseCompression AlgorithmEfficient CompressionThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Tanah Bumbu is one of the most endemic areas in Indonesia for patients diagnosed with malaria diseases. Currently, available malaria case data were stored in disparate sources. Hence, it is difficult for the public health department to quickly and easily gather the useful information for determining strategic actions in tackling these cases. The purpose of this research is to build a data warehouse that integrates all malaria cases from disparate sources. This malaria data warehouse is a centralized architecture of galaxy or constellation scheme that consists of three fact tables and 13 dimension tables. SQL Server Integration Services (SSIS) is utilized to build ETL packages that load data from various sources to stages, dimensions, and fact tables in malaria data warehouse. Finally, a timely report can be generated by extracting the salient information located in malaria data warehouse.

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 retail industry faces significant challenges in managing massive transaction data volumes. Reliance on operational systems (OLTP) often hinders complex historical data analysis. This study aims to design and implement a Data Warehouse using the Star Schema approach to support efficient retail sales analysis. Utilizing the Sample Superstore dataset, the development follows Kimball’s methodology with technical ETL implementation on PostgreSQL. The research produced a Star Schema architecture consisting of one fact table and five dimension tables. Validation testing confirmed complete data integrity during migration. The results demonstrate the system's capability to present rapid multidimensional insights, including positive annual sales trends and profitability disparities across product categories. This implementation proves that PostgreSQL effectively serves as a robust infrastructure for business intelligence, providing a solid foundation for strategic management decision-making.

In the current industrial era, clothing companies face increasingly complex challenges in maintaining the quality and sales of their products. To overcome these obstacles, companies must have a good vision, mission, and decision-making capabilities supported by efficient data management. Data is becoming a valuable asset in Industry 4.0, pivotal in process management, increased productivity, and competitive advantage. This research explores the implementation of Data Warehouse (data warehouse) and ETL (Extraction, Transformation, and Load) processes on star schema for PT Golden Flower Tbk. A data warehouse is a container for storing company data, supporting decision-making, and providing business insights. Meanwhile, the ETL process ensures that the data entered into the warehouse is clean, structured, and ready for analysis. PTA garment manufacturing and exporter company, USPT Golden Flower Tbk, uses the Pentaho application to carry out the ETL process. Pentaho, a Business Intelligence tool, facilitates easy data processing and analysis. This research also explains the use of dimension tables and fact tables in star schemas, which form the basis for more in-depth data analysis. Through visualization and analysis using star schema, Mondrian, and schema workbench, companies can identify sales patterns in each branch and understand which products are most in demand by consumers. The results of the star schema, moMondrianand schema workbench provide a better understanding of the company's sales performance, enabling more informed decision-making and more effective strategies.

This chapter describes two topics related to dimension keys, which are surrogate keys and dimension-less keys. A surrogate key is a unique identification for each record in a dimension table, whereas a dimension-less key is an attribute in the fact table that does not refer to any dimensions. This chapter studies these two concepts using case studies showing the complexity of the design and implementation.

The pre-computation of data cubes is critical to improving the response time of on-line analytical processing (OLAP) systems and can be instrumental in accelerating data mining tasks in large data warehouses. However, as the size of data warehouses grows, the time it takes to perform this pre-computation becomes a significant performance bottleneck. This work presents a fast parallel method for generating ROLAP data cubes on a shared-nothing multiprocessor based on a novel optimized data partitioning technique. Since no shared disk is required, this method can be applied on highly scalable processor clusters consisting of standard PCs with local disks, connected via a data switch. The approach taken, which uses a ROLAP representation of the data cube, is well suited to large data warehouses on high dimensional data, and supports the generation of both fully materialized and partially materialized cubes. In comparison with previous approaches, our new method does significantly improve the scalability with respect to both, the number of processors and the I/O bandwidth (number of parallel disks). We have implemented our new parallel shared-nothing data cube generation method and evaluated it on a PC cluster, exploring relative speedup, scaleup, sizeup, output sizes and data skew. For a fact table with 16 million rows and 8 attributes, our parallel data cube generation method achieves close to optimal speedup for as many as 32 processors, generating a full data cube in under 7 minutes. For a fact table with 256 million rows and 8 attributes, our parallel method achieves optimal speedup for 32 processors, generating a full data cube consisting of /spl ap/7 billion rows (200 Gigabytes) in under 88 minutes.

ABSTRACTThe traditional query optimization method is to reduce the size of the intermediate relationship as much as possible when calculating the multi relation connection. It does not take into account the mass of data in the data warehouse, which is read mainly and the fact table is generally indexed often cannot obtain the disadvantage of the optimal effect, and puts forward the data warehouse suitable for frequent additions and deletions query environment. Multi connection query optimization in data warehouse, MCQODW) method: By using multiple linear regression algorithm to write the detection sequence of data, and then to optimize the data warehouse multi connection query without changing the sequence of other query detection sequences, in order to reduce repeated calculation. Theoretical analysis and experimental results show that the algorithm reduces significantly in the data warehouse multi join query optimization and execution time, and the method is effective.

Data cube has been playing an essential role in fast OLAP (online analytical processing) in many data warehouses. The pre-computation of data cubes is critical for improving the OLAP response time of in large high-dimensional data warehouses. However, as the sizes of data warehouses grow, the time it takes to perform this pre-computation becomes a significant performance bottleneck. In a high dimensional data warehouse, it might not be practical to build all these cuboids and their indices. In this paper, we propose a hierarchical cubing algorithm to partition the high dimensional data cube into low dimensional cube segments. It permits a significant reduction of CPU and I/O overhead for many queries by restricting the number of cube segments to be processed for both the fact table and bitmap indices. Experimental results show that the proposed method is significantly more efficient than other existing cubing methods.