The Data Warehouse Design Problem through a Schema Transformation Approach
Mani Sarma Vittapu · International Journal of Information Technology & Computer Sciences Perspectives · 2013
An operational database is a shared repository of data. The OLTP’s are used for day-to-day transaction processing and do not fulfill the out of the ordering tasks of information processing and data analysis. A new design methodology is required to map the OLTP DB’s data into MD data model. The MD data model is a logical data model comes from the enterprises, they have amount of data, but they cannot access it. They use the same search patterns day to day and data usually is not prepared for an easy queries. The MD model represents a measure that depends on a set of dimensions, which provides the context for the measure it can be represented by hyper cube, that are the graphic way and conceptual model or by the schemas in a logical model. Previously DWH design and development applied ad-hoc methodologies. An increasing number of organizations are implementing data warehouses to strengthen their decision support systems. This comes with the challenges of the population and the periodic update of data warehouses. In this thesis, we present a tool that provides users with features to create a warehouse database and transform structures of the source database into structures for the warehouse database. It is highly interactive, easy to use, and hides the underlying complexity of manual SQL code generation from its users. Attributes from source tables can be mapped into new attributes in the warehouse database tables using aggregate functions. Then, relevant data is automatically transported from the source database to the newly created warehouse. The tool thus integrates warehouse creation, schema mapping and data population into a single general-purpose tool. This tool has been designed as a component of the framework for an automated data warehouse. Users of this framework are the database administrators, who will also be able to synchronize updates of multiple copies of the data warehouse. Warehouse images that need to be updated are taken offline and applications that need to access the data warehouse can now access any of the other image warehouses. The Switching Application built into this framework switches between databases in a way that is very transparent to applications so that they do not realize existence of multiple copies of the data warehouse.