Star Schema Research Articles

Information gems from criminal mines

Purpose The purpose of this study is to demonstrate how existing volumes of big city crime data could be converted to significantly useful information by law enforcement agencies using readily available data warehouse and OLAP technologies. During the post-9/11 era, criminal data collection by law enforcement agencies received significant attention across the world. Rapid advancement of technology helped collection and storage of these data in large volumes, but often do not get analyzed due to improper data format, lack of technological knowledge and time. Data warehousing (DW) and On-line Analytical Processing (OLAP) tools can be used to organize and present these data in a form strategically meaningful to the general public. In this study, the authors took a seven-month sample crime data from the City of Houston Police Department’s website, cleaned and organized them into a data warehouse with the hope of answering common questions related to crime statistics in a big city in the USA. Design/methodology/approach The raw data for the seven-month period was collected from the website in Microsoft Excel spreadsheet format for each month. The data were then cleaned, described, renamed, formatted and then imported into a compiled Access database along with the definition of Facts and Dimensions using a STAR Schema. Data were then transferred to the Microsoft SQL Server data warehouse. SQL Server Analysis Services and Visual Studio Business Intelligent Tool are used to create a Data Cube for OLAP analysis of the summarized data. Findings To prove the usefulness of the DW and OLAP cube, the authors have shown few sample queries displaying the number and the types of crimes as a function of time of the day, location, premises, etc. For example, the authors found that 98 crimes occurred on a major street in the city during the early working hours (7 am and 12 pm) when nobody virtually was at home, and among those crimes, roughly two-thirds of them are thefts. This summarized information is significantly useful to the general public and the law enforcement agencies. Research limitations/implications The authors’ research is limited to one city’s crime data, whose data set might be different from other cities. In addition to the volume of data and lack of descriptions, the major limitations encountered were the lack of major neighborhood names and their relation to streets. There are other government agencies that provide data to this effect, and a standard set of data would facilitate the process. The authors also looked at data for a nine-month period only. Analyzing data over many years will provide time-trend of crime statistics for a longer period of time. Practical implications Many federal, state and local law enforcement agencies are rapidly embracing technology to publish crime data through their websites. However, more attention will need to be paid to the quality and utility of this information to the general public. At the time, there exists no compiled source of crime data or its trend as a function of time, crime type, location and premises. There needs to be a coherent system that allows for an average citizen to obtain this information in a more consumable package. DW and OLAP tools can provide this information package. Social implications Having the crime data of a big city in a consumable form is immensely useful for all segments of the constituency that the government agencies serve and will become a service that these offices will be expected to deliver on demand. This information could also be useful in many instances for the decision makers, ranging from those seeking to start a business, to those seeking a place to live who may not necessarily know which neighborhoods or parts of the city are more prone to criminal activity than others. Originality/value While there have been few reports of possible use of DW and OALP technologies to study criminal data, the authors found that not many authors used actual crime data, the data sets and formats used in each case are different, results are not presented in most cases and the actual vendor technologies implemented can be different as well. In this paper, the authors present how DW and OLAP tools readily available in most enterprises can be used to analyze publicly available criminal datasets and convert them into meaningful information, which can be valuable not only to the law enforcement agencies but to the public at large.

The Impact of Partitioned Fact Tables and Bitmap Index on Data Warehouse Performance

The design process is the most considered task of a data warehouse designer, this because of its performance criticality while going to production. Even if technology has developed in term of memory volume and speed, storage... the fact to minimize the execution time and the storage space still being a preoccupation for data warehousing specialists. This papers shows that using bitmap index and partitioned fact tables in data warehouse based on a star schema is more advantageous based on compression. General Terms Business Intelligence, Data Warehouse.

Star Schema Advantages on Data Warehouse: Using Bitmap Index and Partitioned Fact Tables

he data warehouse designer should consider it effectiveness while the design process, this might be a part of it work by analyzing the update frequency of production databases. Actually, to decide in a small time interval becomes the most important concern for deciders, this because it's always depending on data warehouse refresh using the ETLs and the cube generate process based on data warehouse schema type. To present the KPI's to the management quickly, we need to minimize the query execution time. This papers shows that the star schema is more advantageous when using a bitmap index based on ETL query execution time and data access time.

Temporal data warehouse logical modelling

Temporal data warehouses (TDWs) have been developed for the management of time-varying data in dimensions. This paper presents a new approach for the logical modelling of TDWs. The novel design is based on the integration of two schemata, the star schema and the snowflake schema, to the temporal starnest schema. Time in the temporal starnest schema is not treated as another dimension but as time attributes in every temporal dimension, i.e., dimension tables dependent on time. Time manipulation functions for the treatment of time attributes are provided in the query language. The temporal starnest schema proposed in this paper expresses naturally hierarchy levels by the clustering of data in nested tables, with resulting description of aggregation levels for a dimension in a natural way. The proposed extension is applied to a TDW for accident monitoring in crossroads where the expressive power of the model is exemplified with several temporal queries expressed in a suitable extension of SQL standard.

Faster cloud Star Joins with Reduced Disk Spill and Network Communication

Combining powerful parallel frameworks and on-demand commodity hardware, cloud computing has made both analytics and decision support systems canonical to enterprises of all sizes. Associated with unprecedented volumes of data stacked by such companies, filtering and retrieving them are pressing challenges. This data is often organized in star schemas, in which Star Joins are ubiquitous and expensive operations. In particular, excessive disk spill and network communication are tight bottlenecks for all current MapReduce or Spark solutions. Here, we propose two efficient solutions that drop the computation time by at least 60%: the Spark Bloom-Filtered Cascade Join (SBFCJ) and the Spark Broadcast Join (SBJ). Conversely, a direct Spark implementation of a sequence of joins renders poor performance, showcasing the importance of further filtering for minimal disk spill and network communication. Finally, while SBJ is twice faster when memory per executor is large enough, SBFCJ is remarkably resilient to low memory scenarios. Both algorithms pose very competitive solutions to Star Joins in the cloud.

Facilitate Effective Decision-Making by Warehousing Reduced Data

The authors' aim is to provide a solution for multidimensional data warehouse's reduction based on analysts' needs which will specify aggregated schema applicable over a period of time as well as retain only useful data for decision support. Firstly, they describe a conceptual modeling for multidimensional data warehouse. A multidimensional data warehouse's schema is composed of a set of states. Each state is defined as a star schema composed of one fact and its related dimensions. The derivation between states is carried out through combination of reduction operators. Secondly, they present a meta-model which allows managing different states of multidimensional data warehouse. The definition of reduced and unreduced multidimensional data warehouse schema can be carried out by instantiating the meta-model. Finally, they describe their experimental assessments and discuss their results. Evaluating their solution implies executing different queries in various contexts: unreduced single fact table, unreduced relational star schema, reduced star schema and reduced snowflake schema. The authors show that queries are more efficiently calculated within a reduced star schema.

Implementation of Star Schemas from ER Model

A Star Schema is representation of a data warehouse which is used in strategic decision making and analysis. In this paper we present a method ER Diagram is converted into a star schema.

Query Interaction Based Approach for Horizontal Data Partitioning

With the explosion of data, several applications are designed around analytical aspects, with data warehousing technology at the heart of the construction chain. The exploitation of this data warehouse is usually performed by the use of complex queries involving selections, joins and aggregations. These queries bring the following characteristics: (1) their routinely aspects, (2) their large number, and (3) the high operation sharing between queries. This interaction has been largely identified in the context of multi-query optimization, where graph data structures were proposed to capture it. Also during the physical design, the structures have been used to select redundant optimization structures such as materialized views and indexes. Horizontal data partitioning (HDP) is another non-redundant optimization structure that can be selected in the physical design phase. It is a pre-condition for designing extremely large databases in several environments: centralized, distributed, parallel and cloud. It aims to reduce the cost of the above operations. In HDP, the optimization space of potential candidates for partitioning grows exponentially with the problem size making the problem NP-hard. This paper proposes a new approach based on query interactions to select a partitioning schema of a data warehouse in a divide and conquer manner to achieve an improved trade-off between the optimization algorithm's speed and the quality of the solution. The effectiveness of our approach is proven through a validation using the Star Schema Benchmark (100 GB) on Oracle11g.

The design of valid multidimensional star schemas assisted by repair solutions

Multidimensional schemas are used to model data warehouse systems, a special type of large databases dedicated for decision support. Several approaches have been proposed in this domain to ensure the satisfaction of decision makers needs and the accuracy of the generated schemas. This paper presents an approach for the validation of multidimensional star schema assisted by repair solutions. Our approach aims to assist designers by detecting constraint violations and proposing repair solutions based on a number of error-based rules formalized in Prolog. Its efficiency stems from using both linguistic similarity computation and a set of heuristics developed by bottom-up design methods to produce warnings about semantic constraint violations and their impact on the quality of the analysis results.

Efficient query processing framework for big data warehouse: an almost join-free approach

The rapidly increasing scale of data warehouses is challenging today's data analytical technologies. A conventional data analytical platform processes data warehouse queries using a star schema -- it normalizes the data into a fact table and a number of dimension tables, and during query processing it selectively joins the tables according to users' demands. This model is space economical. However, it faces two problems when applied to big data. First, join is an expensive operation, which prohibits a parallel database or a MapReduce-based system from achieving efficiency and scalability simultaneously. Second, join operations have to be executed repeatedly, while numerous join results can actually be reused by different queries. In this paper, we propose a new query processing framework for data warehouses. It pushes the join operations partially to the pre-processing phase and partially to the post-processing phase, so that data warehouse queries can be transformed into massive parallelized filter-aggregation operations on the fact table. In contrast to the conventional query processing models, our approach is efficient, scalable and stable despite of the large number of tables involved in the join. It is especially suitable for a large-scale parallel data warehouse. Our empirical evaluation on Hadoop shows that our framework exhibits linear scalability and outperforms some existing approaches by an order of magnitude.

CineCubes: Aiding data workers gain insights from OLAP queries

In this paper we demonstrate that it is possible to enrich query answering with a short data movie that gives insights to the original results of an OLAP query. Our method, implemented in an actual system, CineCubes, includes the following steps. The user submits a query over an underlying star schema. Taking this query as input, the system comes up with a set of queries complementing the information content of the original query, and executes them. For each of the query results, we execute a set of highlight extraction algorithms that identify interesting patterns and values in the data of the results. Then, the system visualizes the query results and accompanies this presentation with a text commenting on the result highlights. Moreover, via a text-to-speech conversion the system automatically produces audio for the constructed text. Each combination of visualization, text and audio practically constitutes a movie, which is wrapped as a PowerPoint presentation and returned to the user.

Comparing SQL and NoSQL approaches for clustering over big data

Data mining is the process of discovering patterns in large datasets. With the exponential growth of available information, new machine learning, statistics and other analytics techniques have to be developed to solve the processing needs required to do such analysis fast enough to be used successfully. In this study, techniques like cluster analysis are used over generated data in order to do customer segmentation, and the system performance is evaluated by measuring the processing time. The data used in the current paper is generated using the Star Schema Benchmark (SSB). Our main goal is to find a scalable solution to run data mining over a decision support benchmark. Four different systems will be tested: single node MySQL, MySQL cluster, Apache Mahout and R. By running MySQL cluster and Mahout, each system distributed by four nodes, the paper compares the performance of k-means run in parallel. MySQL and R will allow for comparison of this kind of execution against methods running on a single machine, both on relational and non-relational systems.

Testing SQL and NoSQL approaches for big data warehouse systems

Decision support systems are increasingly used in the management of industrial enterprises, the state sector and the scientific community. With the increase of size of transactional and log data sources, as well as the increase in size and speed needs for data warehousing systems that support decision-making, the data becomes big and queries used in these systems are very complex and time-consuming. This leads to the need for large-scale processing in acceptable time intervals. In this paper, we assess whether both SQL and NoSQL scalability platforms are able to process efficiently big data warehouses. We evaluate the database engine MySQL cluster, which enables clustering and scalability of in-memory databases in a shared nothing platform, and we compare it to traditional database engine Microsoft SQL Server 2012 (non-parallel) and to Hive. In the experimental evaluation, we use one appropriate benchmark to evaluating decision support systems, the Star Schema Benchmark (SSB). The results obtained allow us to estimate the improvement obtained with the increase of resources allocated to the MySQL cluster or Hive, and to obtain a better understanding of scalability limitations and characteristics when these engines are used for decision support systems.

Multi-relational pattern mining over data streams

The data storage paradigm has changed in the last decade, from operational databases to data repositories that make easier to analyze data and mining information. Among those, the primary multidimensional model represents data through star schemas, where each relation denotes an event involving a set of dimensions or business perspectives. Mining data modeled as a star schema presents two major challenges, namely: mining extremely large amounts of data and dealing with several data tables at the same time. In this paper, we describe an algorithm--Star FP Stream, in detail. This algorithm aims for finding the set of frequent patterns in a large star schema, mining directly the data, in their original structure, and exploring the most efficient techniques for mining data streams. Experiments were conducted over two star schemas, in the healthcare and sales domains.

Designing of the CASE Tool to develop Star Schema using the XML Schema consisting of Clinical Data

Data Warehouse is most widely used for data analysis which supports the management’s decision making process. The sources for the Data Warehouse are commonly taken from the online transactional systems in various formats.XML is one of the standard format used to represent and transport the data in web based systems.XML allows easy sharing of data between different internet applications which enhances the decision making in organizations. This paper focuses on conversion method of XML Schema into the Star and Snowflake schema.

Requirements Engineering for a User Centric Spatial Data Warehouse

Spatial data warehouses (SDW) are used for executing analytical multidimensional queries along with spatial analysis. With recent boom in technology, access and integration of multiple, distributed, heterogeneous and autonomous information sources storing spatial or non-spatial data has become the need. For this purpose, we have proposed a simple star schema to store spatial and non-spatial information in data warehouse. We have also given a framework of activities for collecting data from information sources and organizing it in a geo-spatial data store for querying data and visualizing results. The paper has also discussed the most basic requirements for handling spatial data that will help to develop a user-centric spatial data warehouse.

A Novel Multi-Secret Sharing Approach for Secure Data Warehousing and On-Line Analysis Processing in the Cloud

Cloud computing helps reduce costs, increase business agility and deploy solutions with a high return on investment for many types of applications, including data warehouses and on-line analytical processing. However, storing and transferring sensitive data into the cloud raises legitimate security concerns. In this paper, the authors' propose a new multi-secret sharing approach for deploying data warehouses in the cloud and allowing on-line analysis processing, while enforcing data privacy, integrity and availability. The authors' first validate the relevance of their approach theoretically and then experimentally with both a simple random dataset and the Star Schema Benchmark. The authors also demonstrate its superiority to related methods.

Conceptual model for spatial data cubes: A UML profile and its automatic implementation

The increasing availability of geo-referenced data has increased the need to enrich OLAP with spatial analysis, leading to the concept of Spatial OLAP (SOLAP). The conceptual modelling of spatial data cubes requires the definition of two kinds of metadata: (i) warehouse metadata that model data structures that maintain integrated data from multiple data sources and (ii) aggregation11In this paper, the term “aggregation” does not refer to UML aggregation associations. In the text of our article, “aggregation” is used in the sense of calculating a result (as in the field of databases) — the terms “aggregation level” and “aggregating relationship” refer to OLAP aggregations. metadata that specify how the warehoused data should be aggregated to meet the analysis goals of decision makers.In this paper we provide a review of existing conceptual spatial data cube models. We highlight some limits of these models concerning the aggregation model design, and their implementation in existing CASE tools and SOLAP architectures.Firstly, we propose a new UML (Unified Modeling Language) profile for modelling complex Spatial Data Warehouses and aggregations. Our profile is implemented in the MagicDraw CASE tool.Secondly, we propose a tool for the automatic implementation of conceptual spatial data cube models, designed using our profile, in a SOLAP architecture. In particular, our solution allows: (i) generating different logical representations of the SDW (Spatial Data Warehouse) model (star schema and snow-flake schema) and (ii) implementing complex SOLAP analysis indicators using MDX (MultiDimensional eXpressions language).

Dynamic cubing for hierarchical multidimensional data space

Many modern applications such as sensor-based monitoring or certain business applications raise the need for real-time analysis. These applications very often operate in fast-evolving, dynamic environments. Therefore, traditional data warehouses, with their scheduled offline batch update strategy and mandatory ordered dimensions, are no longer suitable. In this paper, we present a multidimensional model for dynamic data warehousing in a hierarchical non-ordered multidimensional data space. We propose a dynamic partial cube materialisation and a tree storage structure that groups the multidimensional data in data partitions called minimum bounding spaces. Algorithms for building and maintaining the tree after a new fact is integrated and for querying the so-constructed cube are provided. We use Star Schema Benchmark and various synthetic and customisable data sets to compare the performance of our solution with the existing dynamic indexing technique. Experimental study shows performance improvement in both insertion time and queries over the data space.

A Query Beehive Algorithm for Data Warehouse Buffer Management and Query Scheduling

Analytical queries, like those used in data warehouses and OLAP, are generally interdependent. This is due to the fact that the database is usually modeled with a denormalized star schema or its variants, where most queries pass through a large central fact table. Such interaction has been largely exploited in query optimization techniques such as materialized views. Nevertheless, such approaches usually ignore buffer management and assume queries have a fixed order and are known in advance. We believe such assumptions are too strong and thus they need to be revisited and simplified. In this paper, we study the combination of two problems: buffer management and query scheduling, in both static and dynamic scenarios. We present an NP-hardness study of the joint problem, highlighting its complexity. We then introduce a new and highly efficient algorithm inspired by a beehive. We conduct an extensive experimental evaluation on a real DBMS showing the superiority of our algorithm compared to previous ones as well as its excellent scalability.