Fact Table Research Articles

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.

The Design and Implementation of Monitoring Data Warehouse of Ambient Air Quality

This paper provides the schemata and arithmetic of the city ambient air quality monitoring data warehouse. And describes the Modeling Theory and Method for Constructing Fact Table of Data Warehouse,;At last The implementation of the CAQMADW prototype validates the feasibility of domain data model

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.

Time–HOBI: Index for optimizing star queries

One of the important research and technological problems in data warehouse query optimization concerns star queries. So far, most of the research focused on optimizing such queries by means of join indexes, bitmap join indexes, or various multidimensional indexes. These structures neither support navigation well along dimension hierarchies nor optimize joins with the Time dimension, which in practice is used in most of the star queries. In this paper we propose an index, called Time–HOBI, for optimizing the star queries that compute aggregates along dimension hierarchies. Time–HOBI, created on a dimension hierarchy, is composed of (1) a Hierarchically Organized Bitmap Index (HOBI), where one bitmap index is maintained for one dimension level, and (2) a Time Index (TI) that implicitly encodes time in every dimension. HOBI allows to quickly search for fact rows satisfying predicates defined on different levels of dimension hierarchies. With the support of TI joining a fact table with the Time dimension is avoided. Thus, Time–HOBI supports a broad class of star queries. In this paper we explain how query execution plans for star queries can profit from Time–HOBI. We show, based on experiments, the efficiency of Time–HOBI for different classes of queries, as compared to HOBI and a traditional bitmap index. Based on the experiments, we also demonstrate how sensitive Time–HOBI is to variable selectivity of queries. We also analyze the maintenance time of Time–HOBI as compared to HOBI and a traditional bitmap index. The experiments used in the paper have been conducted on a real dataset, coming from the biggest East-European Internet auction platform Allegro.pl. The experiments show that Time–HOBI can be successfully applied to the optimization of star queries as it offers promising performance improvement.

An ETL Framework Based on Data Reorganization for the Chinese Style Cross-Tab

This paper proposes a framework for the extract-transform-load (ETL) of the Chinese style cross-tab. It makes a reverse engineering process for the Chinese style report. That is to say, converting the report to its original scheme of relational database table, and then making an ETL processing on the two-dimensional table. And ultimately load the data into the fact tables. Finally, we discuss implementation issues and we present the design and experiment of the system based on our framework.

Schema Evolution for Data Warehouse: A Survey

warehouse is considered as the core component of the modern decision support systems. Due to the major support of data warehouse in the daily transaction of an enterprise, the requirements for the design and the implementation of DW are dynamic and subjective. This dynamic nature of the data warehouse may reflect the evolution in the data warehouse. Data warehouse evolution may be focused on three approaches namely schema evolution, schema versioning and view maintenance. Evolution of the data warehouse may often change their data and structure (schema changes). These schema changes may be consider according to the change in structure, software and users requirement. Schema evolution in data warehouse consists of various level namely structural level, conceptual level and behavioural level. This paper mainly focuses on schema evolution and proposes the operators to handle the creation and evolution of aggregated fact table. Our work is to do comparative study for various approaches of schema evolution.

The Application of Data Warehouse in Enterprise Resource Planning

Through building a data warehouse which is used to support manufacturing inventory analysis, according to the analysis theme, this paper extracted, purified and integrated the historical data which is accumulated by the enterprise in a long term and distributed in the various heterogeneous systems, and store them in the data warehouse fact table; through the establishment of the data cube, it generate the aggregated data with different particle size, as well as provide on-line analytical processing technology (OLAP), to provide users data query analysis with multi-angle, multi-granularity, visualization, which can solve many problems difficult to solve in the inventory analysis in traditional enterprise information processing systems.

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.

CONCURRENT DATA MATERIALIZATION FOR XML-ENABLED DATABASE WITH SEMANTIC METADATA

For a company with many databases in different data models, it is necessary to consolidate them into one interchangeable data model and present data in more than one data model concurrently to different users or individual users who need to access the data in more than one data model. The benefit is to let the user stick to his/her own data model to access database in another data model. This paper presents a semantic metadata to preserve database constraints for data materialization to support the user's view of database on an ad hoc basis. The semantic metadata can store the captured semantics of a relational or an XML-enabled database into classes. The stored constraints and data can be materialized into a target database upon user request. The user is allowed to perform data materialization many times alternatively. The process can provide a relational as well as an XML view to the users simultaneously. This concurrent data materialization function can be applied into data warehouse to consolidate heterogeneous database into a fact table in a data model of user's choice. Furthermore, a user can obtain either a relational view or an XML view of the same dataset of an XML-enabled database interchangeably.

Populating Data Warehouses with Semantic Data

The Semantic Web has become a new environment that enables organizations to attach semantic annotations taken from domain and application ontologies to the information they generate. As a result, large amounts of complex, semi-structured and heterogeneus semantic data repositories are being made available. In this paper, we present an automatic method for on-demand construction of fact tables aimed at analyzing semantic data expressed as instance stores in RDF/OWL. The starting point of the method is a multidimensional star schema (i.e. topic of analysis, dimensions and measures) designed by the analyst by picking up concepts and properties from the ontology of the instance store. The method exploits the underlying semantics of ontology axioms in order to derive facts, that is, “valid combinations” of instances and literals that fulfill the analyst needs. Moreover, both the ontology axioms and the instances are indexed in order to reach scalability. We have tested the method with a synthetically generated instance set in order to show its scalability and effectiveness.

A Virtual Data Warehouse for Manufacturing Industry

Data warehousing is the best solution to relieve the database management systems to hold the long standing data which will go on mounting from day to day business transactions. But creating and managing a data warehouse are complex tasks and in addition, data warehouse tools available in the market are very expensive. To make the data warehouse solutions cost-effective, creation of virtual data warehouse is one option in the case of applications having limited scope or limited duration. This paper deals with the development of such virtual data warehouse for effective supply chain management for a multi-national software development and consultancy company dealing with big semiconductor manufacturing industries. The design and development of the virtual data warehouse is described in detail using the existing database management systems and the existing huge database of the companies. The data warehouse has been created based on the important business requirements, identifying the relevant existing data sources that can be integrated together to generate respective dimensional tables and thereby the fact tables and then finally completing the multi-dimensional models to derive answers to the respective queries. The paper also presents some screen shots of the application interface.

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.

Spatial aggregation: Data model and implementation

Data aggregation in Geographic Information Systems (GIS) is a desirable feature, only marginally present in commercial systems nowadays, mostly through ad hoc solutions. We address this problem introducing a formal model that integrates, in a natural way, geographic data and non-spatial information contained in a data warehouse external to the GIS. This approach allows both aggregation of geometric components and aggregation of measures associated to those components, defined in GIS fact tables. We define the notion of geometric aggregation, a general framework for aggregate queries in a GIS setting. Although general enough to express a wide range of (aggregate) queries, some of these queries can be hard to compute in a real-world GIS environment because they involve computing an integral over a certain area. Thus, we identify the class of summable queries, which can be efficiently evaluated replacing this integral with a sum of functions of geometric objects. Integration of GIS and OLAP (On Line Analytical Processing) is supported also through a language, GISOLAP-QL. We present an implementation, denoted Piet, which supports four kinds of queries: standard GIS, standard OLAP, geometric aggregation (like “total population in states with more than three airports”), and integrated GIS-OLAP queries (“total sales by product in cities crossed by a river”, also allowing navigation of the results). Further, Piet implements a novel query processing technique: first, a process called subpolygonization decomposes each thematic layer in a GIS, into open convex polygons; then, another process (the overlay precomputation) computes and stores in a database the overlay of those layers for later use by a query processor. Experimental evaluation showed that for a wide class of geometric queries, overlay precomputation outperforms R-tree-based techniques, suggesting that it can be an alternative for GIS query processing.

A user-centered design for a spatial data warehouse for data exploration in environmental research

The integration of data from diverse fields of ecological research is paramount in the discovery of new ecological patterns and processes. The spatial exploration of an integrated dataset that spans multiple studies and disciplines can allow researchers to gain unforeseen insight into their data, spawn new research questions and hypotheses and identify data gaps. A user-centered approach was taken to design a spatial data warehouse and online analytical processing (OLAP) tools for data exploration in ecological research. The users in this study had diverse needs and current methods of data management do not easily allow for integration and exploration of data in multidimensional space. A generalizable data warehouse design methodology was created based on the results of a user study. This methodology was then demonstrated in the design of a data warehouse for data exploration in stream ecology resulting in a multidimensional data model with a fact table representing biological stream survey measurements and dimension tables representing spatial and categorical site and landscape variables. A generalizable extraction transformation and loading (ETL) workflow was created to integrate data across spatial dimensions before it was loaded into the data warehouse. A prototype data warehouse was implemented using biological stream survey, hydrologic, and vegetation data to observe spatial patterns in biological community distributions. Based on the exploration requirements identified in the user study, prototype OLAP queries were designed to facilitate spatial data cube exploration. Finally, a web-based interface was implemented to allow for multidimensional spatial visualization of biological stream survey data. The data warehouse and interface will allow researchers to explore biological assessment data at multiple spatial scales across many dimensions.

Multiversion join index for multiversion data warehouse

The data warehouse (DW) technology is developed in order to support the integration of external data sources (EDSs) for the purpose of advanced data analysis by On-Line Analytical Processing (OLAP) applications. Since contents and structures of integrated EDSs may evolve in time, the content and schema of a DW must evolve too in order to correctly reflect the evolution of EDSs. In order to manage a DW evolution, we developed the multiversion data warehouse (MVDW) approach. In this approach, different states of a DW are represented by the sequence of persistent DW versions that correspond either to the real world state or to a simulation scenario. Typically, OLAP applications execute star queries that join multiple fact and dimension tables. An important optimization technique for this kind of queries is based on join indexes. Since in the MVDW fact and dimension data are physically distributed among multiple DW versions, standard join indexes need extensions. In this paper we present the concept of a multiversion join index (MVJI) applicable to indexing dimension and fact tables in the MVDW. The MVJI has a two-level structure, where an upper level is used for indexing attributes and a lower level is used for indexing DW versions. The paper also presents the theoretical upper bound (pessimistic) analysis of the MVJI performance characteristic with respect to I/O operations. The analysis is followed by experimental evaluation. It shows that the MVJI increases a system performance for queries addressing multiple DW versions with exact match and range predicates.

A web-based architecture for Temporal OLAP

Commercial OLAP systems usually consider OLAP dimensions as static entities. In practice, dimension updates are often necessary in order to adapt the multidimensional database to changing requirements. Query languages are then needed to retrieve historical information from a data warehouse. Although many proposals addressing temporal OLAP exist, none supports full operation over the World Wide Web. After introducing a temporal data model supporting historical dimensions and fact table versioning, we present a three-tier architecture based on web services, SOAP and XML, allowing efficient querying over the web. In this architecture, XML metadata is stored at the application server in the form of XML documents containing the data warehouse structure. This allows addressing most of the requests (navigation through time, updates and queries) without accessing the data warehouse. In the second part of the paper, we present TOLAP-QL, an SQL-like temporal query language. We describe the syntax and semantics of the language, a sequel of its logic-based predecessor, TOLAP. We study query optimisation in temporal OLAP, and introduce a technique based on materialisation of historical views. Last, but not least, we present a system implementation, and a real-world case study, that shows that our proposal achieves good query performance for OLAP queries over large historical databases in a web environment.