- Research Article
11
- 10.1016/j.infsof.2008.01.003
Multiversion join index for multiversion data warehouse
- Jan 26, 2008
- Information and Software Technology
- Jan Chmiel + 2 more +2
Multiversion join index for multiversion data warehouse
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.
Multiversion join index for multiversion data warehouse
Multiversion join index for multiversion data warehouse
Data Warehouse Design for Firefighters Operational at the DKI Jakarta Fire Department
This paper proposed two models of data warehouse schema for the fire department of DKI Jakarta, where the 1st model contains six tables consisting of 3 fact and 3-dimensional tables, and the 2nd model only contains three fact tables. The 2nd model denormalises the 1st model, where the number of tables is less than the 1st model, where at the end of the day, the 2nd model will reduce the join table process, which increases the SQL performances. These two models have been recognised as fact constellation schema with more than one fact table and sharing dimension and sub-dimension tables. The database resources were collected from http://data.jakarta.go.id under the Fire and Rescue Service Agency. Those two data warehouse schema models were developed based on a report sector list, a report on Hydrants list, and vehicle register reports. This paper proposes to support Automatic Identification Systems (AIS) research, particularly implementing the data warehouse concept.
Read moreData 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.
Read moreModel data warehouse untuk Operasional petugas pemadam kebakaran Pada dinas pemadam kebakaran provinsi DKI Jakarta
In this paper we design 2 data warehouse design models for the DKI Jakarta fire department. The first data warehouse design model is categorized as a fact constellation schema, where there are 3 fact tables, 2 dimensional tables and 1 sub dimension table, while the second data warehouse design model is a fact constellation schema which consists of only 3 fact tables as well. Both of these data warehouse models are displayed with the intention that the implementation of the data warehouse based on the fire extinguisher data will look different and that will certainly be faster by using the 2nd model which is categorized as an non normal data warehouse model which only consists of 3 fact tables which the lack of a join process in the fact table and the dimensions of the data warehouse will speed up the query process in building the data warehouse report. The design model of the data warehouse is designed based on 4 excel table files, each of which has 1 table downloaded from the DKI Jakarta fire department's website on the website data.jakarta.go.id.
Read moreReferential 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.
Read moreDimension table driven approach to referential partition relational data warehouses
Most of business intelligence applications use data warehousing solutions. The star schema or its variants modelling these applications are usually composed of hundreds of dimension tables and multiple huge fact tables. Referential horizontal partitioning is one of physical design techniques adapted to optimize queries posed over these schemes. In referential partitioning, a fact table can inherit the fragmentation characteristics from dimension table(s). Most of the existing works done on referential partitioning start from a bag containing selection predicates defined on dimension tables, partition each one based on its predicates and finally propagate their fragmentation schemes to the fact table. This procedure gives all dimension tables the same probability to partition the fact table which is not always true. In order to ensure a high performance of the most costly queries, the identification of relevant dimension table(s) to referential partition a fact table is a crucial issue that should be addressed. In this paper, we first study the complexity of the problem of selecting dimension table(s) used to partition a fact table. Secondly, we present strategies to perform their selection. Finally, to validate of our proposal, we conduct intensive experimental studies using a mathematical cost model and the obtained results are verified on Oracle11G DBMS.
Read moreDimension Table Selection Strategies to Referential Partition a Fact Table of Relational Data Warehouses
Enterprise wide data warehouses are becoming increasingly adopted as the main source and underlying infrastructure for business intelligence (BI) solutions. Note that a data warehouse can be viewed as an integration system, where data sources are duplicated in the same repository. Data warehouses are designed to handle the queries required to discover trends and critical factors are called Online Analytical Processing (OLAP) systems. Examples of an OLAP query are: Amazon (www.amazon.com) company analyzes purchases by its customers to come up with an individual screen with products of likely interest to the customer. Analysts at Wal-Mart (www.walmart.com) look for items with increasing sales in some city. Star schemes or their variants are usually used to model warehouse applications. They are composed of thousand of dimension tables and multiple fact tables [15, 18]. Figure 2.1 shows an example of star schema of the widely-known data warehouse benchmark APB-1 release II [21]. Here, the fact table Sales is joint to the following four dimension tables: Product, Customer, Time, Channel. Star queries are typically executed against the warehouse. Queries running on such applications contain a large number of costly joins, selections and aggregations. They are called mega queries [24]. To optimize these queries, the use of advanced optimization techniques is necessary.
Read moreDesign and Implementation of an Improved Data Warehouse on Clinical Data
Data Warehouse is a repository to store huge detailed and summaries data for historical data analysis. In a decision support system which stores data from remote, complex and heterogeneous operational data sources . A clinical data warehouse contains complex, heterogeneous data from different data sources. In literature, there are different data warehouse architectures are present with there own design issues, which are relevant to different application areas. In this paper, we proposed a conceptual and logical view of data warehouse architecture along with physical implementation of the data warehouse. Our main focus in this paper is to efficiently handle the complex heterogeneous medical data stored into the warehouse and improve the performance of data warehouse for data analysis. Here, we proposed a partitioning concept of the dimension tables and fact tables for optimizing the response time, minimizing the disk IO, along with reducing the joining cost of the data warehouse. To show the effectiveness of our system, we, compare with different joining techniques of the dimension and fact tables of fact-consolidated data warehouse schema. A mathematical cost model of disk IO optimization is being calculated. SQL window partitioning techniques are being used for data analysis of the proposed data warehouse. After storing complex heterogeneous data in well organized and efficient way in a data warehouse, efficient searching techniques need to be incorporated. Here, bitmap indexing technique is used for the purpose.
Read moreCache conscious star-join in MapReduce environments
With the popularity of big data and cloud computing, data parallel framework MapReduce based data warehouse systems are used widely. Column store is a default data placement in these systems. Traditionally star join is a core operation in the data warehouse. However, little related work study star join in column store and MapReduce environments. This paper proposes two new cache conscious algorithms Multi-Fragment-Replication Join (MFRJ) and MapReduce-Invisible Join (MRIJ) in MapReduce environments. All these algorithms avoid fact table data movement and are cache conscious in each MapReduce node. In addition, fact table is partitioned into several column groups for cache optimization in MFRJ; One group contains all of foreign key columns and each measure column is a group. In MRIJ, each column is separately processed one by one which has higher cache utilization and avoids frequently cache miss from one column to the other column. MRIJ is composed of several map operation on dimension tables and one MapReduce job. We also apply MRIJ on RCFile in Hive. All operations are processed in mapping phase and avoid high cost of shuffle and reduce operation. If the dimension tables are big enough and cannot cache in local memory, MRIJ is divided into two phases, firstly each dimension table join with corresponding foreign key column in fact table as commonly map reduce join concurrently or serially; secondly all internal results joined for final results based on position index. This strategy also can be applied to other multi-table join. In order to reduce network I/O, dimension table and the fact table foreign key column are co-location storage. Our experimental results in cluster environments show that our algorithms outperform existing approaches in Hive system.
Read moreSlowly Changing Dimension Handling in Data Warehouses Using Temporal Database Features
This paper presents the use of temporal database features to solve the Slowly Changing Dimension (SCD) problem of data warehouses. The SCD problem is presented and existing solutions, together with their limitations are shown. Temporal database features of SQL are described. Temporal data retrieval and temporal data manipulations, together with illustrated examples are demonstrated. The solution to the SCD problem is shown with illustrated examples. The data warehouse whose dimension tables are validtime state tables, but the fact table is a conventional fact table without any timestamp or validtime period, is proposed. The identifier integrity of dimension instances is preserved. The sample fact table, dimension tables, and the SQL codes which perform temporal operations to solve the problem are presented. The proposed solution gives correct results regardless of the number of changes made to the attribute of the dimension table, thus completely solves the Slowly Changing Dimension problem.
Read moreA Framework to Convert XML Schema to ROLAP
Data warehouse provides architecture and tools for business executive to systematically organize, understand and use their data to make strategic decision. On the other hand, XML is used for e-commerce and Internet based application. Since many organizations use web for their business purpose, researches has been done for integrating XML data into data warehouse. This paper illustrates an approach for integrating XML data, modeled by XML schema to its compatible data warehouse based on relational online analytical processing (ROLAP). In this paper, this modeling is performed in three steps. Firstly, a schema tree is constructed from the given XML schema. In the next step, an entity-relationship diagram (ER Diagram) is derived from this schema tree. At the end, dimension tables as well as the corresponding fact table have been identified from the ER Diagram to provide a suitable multidimensional data model for online analytical processing (OLAP). The relationship between the fact tables and dimension tables have been organized in the form of star schema or snowflake schema as required.
Read moreHandling measurement function changes with Slowly Changing Measures
Handling measurement function changes with Slowly Changing Measures
8 - Dimensions
8 - Dimensions
Performance comparison slowly changing dimensions using model relational and object-relational
Data warehouses are designed with a multidimensional structure based on fact and dimension tables, oriented towards indicator systems that inform decision making. The most frequent loads associated to this kind of system are performed on the fact table, assuming that all dimensions are common or time-independent, which can lead to serious consequences in terms of the integrity and completeness of the obtained information. A number of proposals have been made on how to update the dimension table, calling this process Slowly Changing Dimensions (SCD), focused on relational data design. This article focuses on proposing strategies to improve the response time from queries associated to a data warehouse design for dimension tables than change, comparing SCD designs in relational and object-relational databases. Finally, a case is presented that allows to compare the performance from both designs.
Read moreDESAIN 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.
Read more