Dimension Tables Research Articles

Of snowstorms and bushy trees

Many workloads for analytical processing in commercial RDBMSs are dominated by snowstorm queries, which are characterized by references to multiple large fact tables and their associated smaller dimension tables. This paper describes a technique for bushy join tree optimization for snowstorm queries in Oracle database system. This technique generates bushy join trees containing subtrees that produce substantially reduced sets of rows and, therefore, their joins with other subtrees are generally much more efficient than joins in the left-deep trees. The generation of bushy join trees within an existing commercial physical optimizer requires extensive changes to the optimizer. Further, the optimizer will have to consider a large join permutation search space to generate efficient bushy join trees. The novelty of the approach is that bushy join trees can be generated outside the physical optimizer using logical query transformation that explores a considerably pruned search space. The paper describes an algorithm for generating optimal bushy join trees for snowstorm queries using an existing query transformation framework. It also presents performance results for this optimization, which show significant execution time improvements.

An Approach for Automatically Generate Star Schema from Natural Language

The star schema is a form of data warehouse modelling, which acts primary storage for dimensional data that enables efficient retrieval of business information for decision making. Star schemas can be generated from business needs that we refer to as a user business key or from a relational schema of the operational system. There are many tools available to automatically generate star schema from relational schema, such as BIRST and SAMSTAR; however, there is no application that can automatically generate it from a user business key that is represented in the form of human language. In this paper, we offered an approach for automatically generating star schema from user business key(s). It begins by processing the user business key using a syntactical parsing process to identify noun words. Those identified words will be used to generate dimension table candidates and a fact table. The evaluation result indicates that the tool can generate star schema based on the inputted user business key(s) with some limitations in that the star schema will not be formed if the dimensional tables do not have a direct relationship.

Dynamic discovery of query path on the lattice of cuboids using hierarchical data granularity and storage hierarchy

Analytical processing on multi-dimensional data is performed over data warehouse. This, in general, is presented in the form of cuboids. The central theme of the data warehouse is represented in the form of fact table. A fact table is built from the related dimension tables. The cuboid that corresponds to the fact table is called base cuboid. All possible combination of the cuboids could be generated from base cuboid using successive roll-up operations and this corresponds to a lattice structure. Some of the dimensions may have a concept hierarchy in terms of multiple granularities of data. This means a dimension is represented in more than one abstract form. Typically, neither all the cuboids nor all the concept hierarchy are required for a specific business processing. These cuboids are resided in different layers of memory hierarchy like cache memory, primary memory, secondary memory, etc. This research work dynamically finds the most cost effective path from the lattice structure of cuboids based on concept hierarchy to minimize the query access time. The knowledge of location of cuboids at different memory elements is used for the purpose.

Research and Application of Multi-Dimensional Model in 110 Incidents Data Warehouse

This paper studied the multi-dimensional model and designed a 110 incidents data warehouse application system based on above mentioned model and Oracle database for further 110 incidents data analysis and mining. It provides a more flexible and efficient statistical analysis environment for decision-makers with timely and reliable reports. Firstly, we studied multi-dimensional model used to construct the 110 incidents data warehouse, and then detailed the design of fact table and dimension tables in star schema. Finally, we implemented the 110 incidents data warehouse analysis system which included the processing of slowly changing dimensions, data extraction, transformation, loading process and performance analysis.

A proposed model for data warehouse user behaviour using intrusion detection system

Data Warehouse (DW) systems maintain sensitive and crucial information, which is integrated from various heterogenous sources of organization. With the ever increasing deployment and usage of networks, these systems are becoming more vulnerable to malicious attacks. With the increased number of attacks, intrusion detection has become vital part of Information Security. In this paper, we have proposed a model for analyzing and detecting anomalous events based on user behavior analysis through usage patterns, user profiles and session management. After monitoring the events in the system, if any intrusion activity occurs, then alerts are issued to system administrators. Since a user profile is not necessarily fixed but rather it evolves with changing time, so a dynamic user behavior modeling is represented as a sequence of events and combination of fact and dimension tables accessed by the users. In this way, DW systems may be protected by the malicious attacks.

Integrating Star and Snowflake Schemas in Data Warehouses

A fundamental issue encountered by the research community of data warehouses (DWs) is the modeling of data. In this paper, a new design is proposed, named the starnest schema, for the logical modeling of DWs. Using nested methodology, data semantics can be explicitly represented. Part of the design involves providing a translation mechanism from the star/snowflake schemas to a nested representation. The novel schema proposed in this paper is accomplished by converting the fact-dimension schema to a fact-nested dimension schema. The transformation of the denormalized dimension tables to nested dimension tables increases the efficiency of query execution by reducing the number of tuples accessed for query retrieval since dimensional attributes can be used directly in the Group-by clause. In order to facilitate the implementation of the proposed approach, specific algorithms are built based on the starnest schema.

중소기업성장지원 OLAP 데이터베이스 시스템 구축 - 중소기업 정책금융지원 사업을 중심으로-

본 연구에서는 중소기업청의 정책자금지원 사업을 도와 줄 OLAP 데이터베이스 시스템을 구축하는 것을 목적으로 하고 있다. 정책자금을 단계적으로 지원하기 위해서는 필수적으로 이질 차원 스키마를 채택해야 한다. 따라서 본 연구에서는 단계적 정책금융을 지원하도록 하는 FREQUENCY 차원테이블을 작성한 것이다. 또한 본 연구에서는 중소기업의 크기를 측정할 모델을 작성하였는데, 이 모델은 매출액, 고용인력, 기술보유, 매출액 영업이익률, 부채 비율, 유동비율의 6가지 기업크기 측정지표로 구성되어 있다. FREQUENCY 차원을 포함한 3개 차원과 기업크기 측정모델을 사용하여 OLAP 데이터베이스 시스템을 구축한 것이다. 아울러 이렇게 구축한 OLAP 데이터베이스 시스템을 이용하여 2004년~2007년 간 기 시행된 중소기업 정책자금 지원사업의 적정성을 평가해 보고 있다. The purpose of this paper is to develop an OLAP (online analytical processing) database system that supports the SMBA (Small and medium Business Administration) policy funding. A heterogeneous dimension schema will be central in staged support of policy funds. In this paper, therefore, we designed the FREQUENCY dimension table which has a heterogeneous dimension schema structure. In this paper, we made a model of measuring SME (small and medium-sized enterprise) size first. The model is composed of six determinants of firm growth such as sales, employment, own technology, the operating profit to sales ratio, the debt ratio, and the current ratio. We developed the OLAP database system by using three dimensions including the FREQUENCY dimension, and using the model of measuring SME size. Also we assessed past decisions on policy funding in the Small Business Policy Funds Support Project (2004-2007) by using the OLAP database system.

UNSTRUCTURED MULTIDIMENSIONAL ARRAY MULTIMEDIA RETRIVAL MODEL BASED XML DATABASE

Unstructured Data derived from the thought of data warehouse, data cube and xml, this paper presents a new database structure model which organizes the unstructured data in a multidimensional data cube based on XML Database. In this data cube of XML, clustered data are stored in instance table. A leading data corresponding are stored in dimension table. The relational model is helpful to construct data model, but it lacks flexibility, now the new data model can complement the defect of relational model. When querying, a leading data is gained from dimension table of XML then receiving the unstructured data through XQuery. Thus we increase the flexibility of XML database.

관계형 데이터 웨어하우스의 복잡한 질의의 처리 효율 향상을 위한 비트맵 조인 인덱스 선택에 관한 연구

As the size of the data warehouse is large, the selection of indices on the data warehouse affects the efficiency of the query processing of the data warehouse. Indices induce the lower query processing cost, but they occupy the large storage areas and induce the index maintenance cost which are accompanied by database updates. The bitmap join indices are well applied when we optimize the star join queries which join a fact table and many dimension tables and the selection on dimension tables in data warehouses. Though the bitmap join indices with the binary representations induce the lower storage cost, the task to select the indexing attributes among the huge candidate attributes which are generated is difficult. The processes of index selection are to reduce the number of candidate attributes to be indexed and then select the indexing attributes. In this paper on bitmap join index selection problem we reduce the number of candidate attributes by the data mining techniques. Compared to the existing techniques which reduce the number of candidate attributes by the frequencies of attributes we consider the frequencies of attributes and the size of dimension tables and the size of the tuples of the dimension tables and the page size of disk. We use the mining of the frequent itemsets as mining techniques and reduce the great number of candidate attributes. We make the bitmap join indices which have the least costs and the least storage area adapted to storage constraints by using the cost functions applied to the bitmap join indices of the candidate attributes. We compare the existing techniques and ours and analyze them in order to evaluate the efficiencies of ours.

중소기업성장지원 OLAP 데이터베이스 시스템 구축 -중소기업기술개발지원사업을 중심으로-

본 연구에서는 중소기업성장을 지원할 OLAP 데이터베이스 시스템을 구축하는 것을 목적으로 하고 있다. 본 연구의 특징은 중소기업의 크기를 측정할 모델을 작성하는 것이다. 이 모델은 고용인력, 매출액, 수출액, 기술보유, 매출액 대비 기술개발투자비 비율 등의 5가지 기업크기 측정지표로 구성된다. 또한 단계적 기술개발자금 지원을 가능케 할 FREQUENCY 차원 테이블을 설계하는 것이다. FREQUENCY 차원을 포함한 3개 차원과 기업크기 측정모델을 사용하여 OLAP 데이터베이스 시스템을 구축한 것이다. 그리고 이 OLAP 데이터베이스 시스템을 이용하여 기 시행된 중소기업기술개발지원사업의 적정성을 평가해 보고 있다. The purpose of this paper is to develop an online analytical processing (OLAP) database system for small and medium-sized enterprises (SMEs) growth support. In this paper, we made a model of measuring SME size first. The model is composed of five determinants of firm growth such as employment, sales, the amount of export, own technology, and the ratio of R&D expenditure to sales. Second, we designed the FREQUENCY dimension table which will make staged support of R&D expenditure. We developed the OLAP database system by using three dimensions including the FREQUENCY dimension, and using the model of measuring SME size. Also we assessed past decisions on R&D expenditure support in the Small Business R&D Support Project by using the OLAP database system.

Dimensional Modeling of Indian Materials Database

Indian Materials Database (IMDB) is a national project aiming to develop a database through compilation of materials property data available in different laboratories in India. The database contains data on mechanical, corrosion, nondestructive evaluation, thermal and optical properties of a wide variety of materials. Selecting the appropriate data modeling technique is crucial for the successful deployment of such a database. Dimensional modeling is a logical design technique to present data in a standard, intuitive framework that allows for high-performance access. Dimensional modeling of data results in a Star Schema, where the data constitutes a central fact table surrounded by dimension tables. This paper discusses the model and architecture of the material database using a Snowflake Schema, which is a variation of Star Schema, where some of the dimensions are normalized into multiple related tables. The database contains a central fact table linked to multiple dimensions namely, 1) Materials 2) Properties of materials 3) Details of experiments conducted on materials and 4) Source from which data is obtained. KeywordsModels, Material Database, Data warehousing,

A Schema Selection Framework for Data Warehouse Design

Data schema represents the arrangement of fact table and dimension tables and the relations between them. In data warehouse development, selecting a right and appropriate data schema (Snowflake, Star, Star Cluster …) has an important Impact on performance and usability of the designed data warehouse. One of the problems that exists in data warehouse development is lack of a comprehensive and sound selection framework to choose an appropriate schema for the data warehouse at hand by considering application domain-specific conditions. In this paper, we present a schema selection framework that is based on a decision tree for solving the problem of choosing right schema for a data warehouse. The main selection criteria that are used in the presented decision tree are query type, attribute type, dimension table type and existence of index. To evaluate correctness and soundness of this framework, we have designed a test bed that includes multiple data warehouses and we have created all the possible states in decision tree of schema selection framework. Then we designed all types of queries and performed the designed queries on these data warehouses. The results confirm the correct functionality of the schema selection framework.

MINING STARS WITH FP-GROWTH: A CASE STUDY ON BIBLIOGRAPHIC DATA

Traditional data mining approaches look for patterns in a single table, while multi-relational data mining aims for identifying patterns that involve multiple tables. In recent years, the most common mining techniques have been extended to the multi-relational context, but there are few dedicated to deal with data stored following the multi-dimensional model, in particular the star schema. These schemas are composed of a central huge fact table linking a set of small dimension tables. Joining all the tables before mining may not be a feasible solution due to the usual massive number of records. This work proposes a method for mining frequent patterns on data following a star schema that does not materialize the join between the tables. As it extends the algorithm FP-Growth, it constructs an FP-Tree for each dimension and then combines them through the records in the fact table to form a super FP-Tree. This tree is then mined with FP-growth to find all frequent patterns. The paper presents a case study on bibliographic data, comparing efficiency and scalability of our algorithm against FP-Growth.

Design and Implementation of Short Messages Analysis System Based on Multidimensional Data Modeling

Multidimensional data modeling is the foundation of OLAP. This paper presents methods of multidimensional data modeling and applies it to the SMS(short messages) communication service. In order to analyze SMS data, a OLAP model must be designed. After the data from SMS communication are divided into fact data and dimension data in accordance with needs for SMS data analysis, the fact table and dimension tables are created. The multidimensional SMS data analysis model is built on the tables and then implemented by SQL server.

DESAIN ETL DENGAN CONTOH KASUS PERGURUAN TINGGI

Data Warehouse for higher education as a paradigm for helping high management in order to make an effective and efficient strategic decisions based on reliable and trusted reports which is produced from Data Warehouse itself. Data Warehouse is not a software, hardware or tool but Data Warehouse is an environment where the transactional database is modelled in other view for decision making purposes. ETL (Extraction, Transformation and Loading) is a bridge to build Data Warehouse and transform data from transactional database. In every fact and dimension table will be inserted with fields which represent the construction merge loading as an ETL (Extraction, Transformation and Loading) extraction. ETL needs an ETL table and ETL process where ETL table as table connectivity between tables in OLTP database and tables in Data Warehouse and ETL process will transform data from table in OLTP database into Data Warehouse table based on ETL table. The extraction process will be run with a table database as differentiate ETL process and an ETL algorithm which will be run automatically in idle transactional process, along with daily transactional database backup when the information system are not used.

Optimizing Multiway Joins in a Map-Reduce Environment

Implementations of map-reduce are being used to perform many operations on very large data. We examine strategies for joining several relations in the map-reduce environment. Our new approach begins by identifying the “map-key,” the set of attributes that identify the Reduce process to which a Map process must send a particular tuple. Each attribute of the map-key gets a “share,” which is the number of buckets into which its values are hashed, to form a component of the identifier of a Reduce process. Relations have their tuples replicated in limited fashion, the degree of replication depending on the shares for those map-key attributes that are missing from their schema. We study the problem of optimizing the shares, given a fixed number of Reduce processes. An algorithm for detecting and fixing problems where a variable is mistakenly included in the map-key is given. Then, we consider two important special cases: chain joins and star joins. In each case, we are able to determine the map-key and determine the shares that yield the least replication. While the method we propose is not always superior to the conventional way of using map-reduce to implement joins, there are some important cases involving large-scale data where our method wins, including: 1) analytic queries in which a very large fact table is joined with smaller dimension tables, and 2) queries involving paths through graphs with high out-degree, such as the Web or a social network.

Improving performance by creating a native join-index for OLAP

The performance of online analytical processing (OLAP) is critical for meeting the increasing requirements of massive volume analytical applications. Typical techniques, such as in-memory processing, column-storage, and join indexes focus on high performance storage media, efficient storage models, and reduced query processing. While they effectively perform OLAP applications, there is a vital limitation: mainmemory database based OLAP (MMOLAP) cannot provide high performance for a large size data set. In this paper, we propose a novel memory dimension table model, in which the primary keys of the dimension table can be directly mapped to dimensional tuple addresses. To achieve higher performance of dimensional tuple access, we optimize our storage model for dimension tables based on OLAP query workload features. We present directly dimensional tuple accessing (DDTA) based join (DDTAJOIN), a technique to optimize query processing on the memory dimension table by direct dimensional tuple access. We also contribute by proposing an optimization of the predicate tree to shorten predicate operation length by pruning useless predicate processing. Our experimental results show that the DDTA-JOIN algorithm is superior to both simulated row-store main memory query processing and the open-source column-store main memory database MonetDB, thanks to the reduced join cost and simple yet efficient query processing.

Scatter-Gather-Merge: An efficient star-join query processing algorithm for data-parallel frameworks

A data-parallel framework is very attractive for large-scale data processing since it enables such an application to easily process a huge amount of data on commodity machines. MapReduce, a popular data-parallel framework, is used in various fields such as web search, data mining and data warehouses; it is proven to be very practical for such a data-parallel application. A star-join query is a popular query in data warehouses that are a current target domain of data-parallel frameworks. This article proposes a new algorithm that efficiently processes star-join queries in data-parallel frameworks such as MapReduce and Dryad. Our star-join algorithm for general data-parallel frameworks is called Scatter-Gather-Merge, and it processes star-join queries in a constant number of computation steps, although the number of participating dimension tables increases. By adopting bloom filters, Scatter-Gather-Merge reduces a non-trivial amount of IO. We also show that Scatter-Gather-Merge can be easily applied to MapReduce. Our experimental results in both cluster and cloud environments show that Scatter-Gather-Merge outperforms existing approaches.

Architecture of Urban Ecosystem Assessment Based on Data Warehouse

The assessment architecture is designed in this paper so that the urban ecosystem health can be exactly evaluated. Using data warehouse technology, the writer builds the network to receive every kind of index information, sets the system structure of data warehouse, designs data dimension tables and fact tables. Star model of data warehouse is make up on basis of above all, the assess platform is build. Taking Shanghai city as an example, the results are satisfied, which are consistent with the facts.

Referential Horizontal Partitioning Selection Problem in Data Warehouses

Horizontal Partitioning has been largely adopted by the database community, where it took a significant part in the physical design process. Actually, it is supported by most commercial database systems (DBMS), where a native Data Definition Language for decomposing tables/materialized views using various modes is proposed. In traditional databases, horizontal partitioning has been largely studied, where several fragmentation algorithms were proposed to partition tables in isolation. In the relational data warehouse environment, horizontal partitioning consists in decomposing the whole warehouse schema into sub schemas, where each schema contains fragments of dimension and fact tables. Dimension tables are fragmented using the primary partitioning mode, whereas the fact table is divided using referential mode. In this article, the authors first focus on the evolution of horizontal partitioning in commercial DBMS motivated by decision support applications. Secondly, they give a formalization of the referential fragmentation schema selection problem in the data warehouse and they study its hardness to select an optimal solution. Due to its high complexity, they develop two algorithms: hill climbing and simulated annealing with several variants to select a near optimal partitioning schema. Finally, extensive experimental studies are conducted using the data set of APB1 benchmark to compare the quality the proposed algorithms using a mathematical cost model. Based on these experiments, some recommendations are given to advise database administrator for well using horizontal partitioning.