Graphical web based tool for generating query from star schema

This paper presents the development of a graphical SQL query tool that allows novice and non-technical users to navigate through database tables and generate their own queries.The tool enables the query output to be presented in graphical and tabular forms, which can help users, especially top management in better understanding and interpreting query results.The algorithms to construct complex SQL query from star schema in databases is also presented

Graphical Web Based Tool for Generating Query from Star Schema

Novice users have difficulty to generate structured query language from the star schemas because they are not familiar with formulating SQL queries and SQL syntax. This study proposed graphical web based tool to generate queries from star schema and represent the data in tabular or graphical forms which help novice user to formulate SQL query. A prototype for a web based tool to generate the query has been developed using Java Server Pages programming language. The developed tool can facilitate complex query construction which is faced by non-technical and/or novice users. The output of SQL query is presented in tabular and graphical forms which can help users especially top management in better understanding and interpreting query results

An Automatic Tool to Transform Star Schema Data Warehouse to Physical Data Model

Data warehouse is used to store very large data for supporting company to perform data analysis. Star schema is data warehouse model most widely used by companies today. Sometimes, data stored in star schema need to be exported to conventional model so that others may use them without knowing the OLTP (Online Transaction Processing) or source model, particularly for backup and recovery case. Therefore, this research aimed to transform star schema data model to physical data model. Two cases have been identified case, which are: 1) the star schema with simple star schema and the multifact star schema (standard case); and 2) the multi star schema (nonstandard case). There are five processes to build the physical model from the star schema model, namely: 1) finding fact table, 2)finding dimension table, 3) deleting time dimension table, and adding date attribute to fact table, 4) changing fact table to relational table, and 5) changing dimension table to relational table. The prototype was built to implement this phase, and it was tested using some cases. The prototype transformed star schema to physical data model properly (complete design with table, attribute, relation, data type). Some results were different (were not consistent) from the source model because there are many possibilities of star schema for one model, and there is no metadata that are stored when the star schema model was built

A Design of Intelligent Pre-fetching Materialized Views Mechanism for Enhancing Summary Queries on Data Warehouses

To build up a materialized view that perfectly satisfies the need of the specific enterprise it serves is now the biggest challenge especially when it comes to larger and larger scale enterprises as well as more and more complicated and yet necessary socio-economical information. In this paper, we shall develop an Intelligent Materialized VIews Pre-fetching mechanism, also known as an IMVIP, from the characteristics of affinity grouping so as to enhance the efficiency of summary data warehouse querying. The IMVIP mechanism consists of the following two methods: the Apriori-Model association method and the Linear Structure Relation. The Apriori-Model association method explores and deduces the combination of the relations among individual user session. It is especially suitable for applications where the combinations of the relations are to be explored among multi-objective queries made by more than one decision maker. On the other hand, the Linear Structure Relation Model develops a set of principles as to the explorations into the deduced relation combination above with an aim to constructing a series of causal-effect association rules. Thus, we can not only pre-fetch and materialize views that really satisfy the needs of the decision makers so as to enhance the efficiency of summary data warehouse queries but also build up intelligent query paths according to the cause-and-effect association rules in order to attain the goal of providing helpful suggestions for decision-making

Dimension Updates in Data Warehouses

Department of Computer Scienc

Analysis of building performance data

In recent years, the global trend for digitalisation has also reached buildings and facility management. Due to the roll out of smart meters and the retrofitting of buildings with meters and sensors, the amount of data available for a single building has increased significantly. In addition to data sets collected by measurement devices, Building Information Modelling has recently seen a strong incline. By maintaining a building model through the whole building life-cycle, the model becomes rich of information describing all major aspects of a building. This work aims to combine these data sources to gain further valuable information from data analysis. Better knowledge of the building’s behaviour due to high quality data available leads to more efficient building operations. Eventually, this may result in a reduction of energy use and therefore less operational costs. In this thesis a concept for holistic data acquisition from smart meters and a methodology for the integration of further meters in the measurement concept are introduced and validated. Secondly, this thesis presents a novel algorithm designed for cleansing and interpolation of faulty data. Descriptive data is extracted from an open meta data model for buildings which is utilised to further enrich the metered data. Additionally, this thesis presents a methodology for how to design and manage all information in a unified Data Warehouse schema. This Data Warehouse, which has been developed, maintains compatibility with an open meta data model by adopting the model’s specification into its data schema. It features the application of building specific Key Performance Indicators (KPI) to measure building performance. In addition a clustering algorithm, based on machine learning technology, is developed to identify behavioural patterns of buildings and their frequency of occurrence. All methodologies introduced in this work are evaluated through installations and data from three pilot buildings. The pilot buildings were selected to be of diverse types to prove the generic applicability of the above concepts. The outcome of this work successfully demonstrates that the combination of data sources available for buildings enable advanced data analysis. This largely increases the understanding of buildings and their behavioural patterns. A more efficient building operation and a reduction of energy usage can be achieved with this knowledge