Conceptual design of data warehouses from E/R schemes

TLDR

Data warehousing systems allow enterprise managers to integrate heterogeneous data sources and query large databases efficiently, but building a warehouse requires design and implementation techniques distinct from those of traditional information systems. This work introduces the Dimensional Fact model, a graphical conceptual framework, and a semi‑automated methodology for deriving it from existing entity‑relationship schemes. The Dimensional Fact model comprises tree‑structured fact schemes with facts, attributes, dimensions, and hierarchies, supports additivity, optionality, and non‑dimension attributes, permits overlapping compatible fact schemes, and incorporates workload query patterns to generate logical and physical warehouse designs.

Abstract

Data warehousing systems enable enterprise managers to acquire and integrate information from heterogeneous sources and to query very large databases efficiently. Building a data warehouse requires adopting design and implementation techniques completely different from those underlying information systems. We present a graphical conceptual model for data warehouses, called Dimensional Fact model, and propose a semi-automated methodology to build it from the pre-existing entity/relationship schemes describing a database. Our conceptual model consists of tree-structured fact schemes whose basic elements are facts, attributes, dimensions and hierarchies; other features which may be represented on fact schemes are the additivity of fact attributes along dimensions, the optionality of dimension attributes and the existence of non-dimension attributes. Compatible fact schemes may be overlapped in order to relate and compare data. Fact schemes may be integrated with information of the conjectured workload, expressed in terms of query patterns, to be used as the input of a design phase whose output are the logical and physical schemes of the data warehouse.