this paper we<E-382> formalize a graphical conceptual model for data warehouses, called Dimensional Fact model, and<E-380> propose a semi-automated methodology to build it from the pre-existing (conceptual or logical)<E-366> schemes describing the enterprise relational database. The representation of reality built using our<E-381> conceptual model consists of a set of fact schemes whose basic elements are facts, measures,<E-358> attributes, dimensions and hierarchies; other features which may be represented on fact schemes are<E-382> the additivity of fact attributes along dimensions, the optionality of dimension attributes and the<E-381> existence of non-dimension attributes. Compatible fact schemes may be overlapped in order to relate<E-373> and compare data for drill-across queries. Fact schemes should be integrated with information of the<E-382> conjectured workload, to be used as the input of logical and physical design phases; to this end, we<E-382> propose a simple language to denote data warehouse queries in terms of sets of fact instances.<E-334>

Abstract. In this paper we present MD, a logical model for OLAP systems, and show how it can be used in the design of multidimensional databases. Unlike other models for multidimensional databases, MD is independent of any speci c implementation (relational or proprietary multidimensional) and as such itprovides a clear separation between practical and conceptual aspects. In this framework, we present a design methodology, to obtain an MD scheme from an operational database. We thenshowhowanMD database can be implemented, describing translations into relational tables and into multidimensional arrays. 1

. Multidimensional data modeling plays a key role in the design of a data warehouse. We argue that the Entity Relationship Model is not suited for multidimensional conceptual modeling because the semantics of the main characteristics of the paradigm cannot be adequately represented. Consequently, we present a specialization of the E/R model - called Multidimensional Entity Re- lationship (ME/R) Model -- that is suitable for the conceptual modeling of OLAP applications. In order to express the multidimensional structure of the data we define two specialized relationship sets and a specialized entity set. The resulting ME/R model allows the adequate conceptual representation of the multidimensional data view inherent to OLAP, namely the separation of qualifying and quantifying data and the complex structure of dimensions. We demonstrate the usability of the ME/R model by an example taken from an actual project dealing with the analysis of vehicle repairs. 1 Introduction Mul...

Though designing a data warehouse requires techniques completely different from those adopted for operational systems, no significant effort has been made so far to develop a complete and consistent design methodology for data warehouses. In this paper we outline a general methodological framework for data warehouse design, based on our Dimensional Fact Model (DFM). After analyzing the existing information system and collecting the user requirements, conceptual design is carried out semi-automatically starting from the operational database scheme. A workload is then characterized in terms of data volumes and expected queries, to be used as the input of the logical and physical design phases whose output is the final scheme for the data warehouse. Keywords Data warehouse, design methodology, conceptual model. 1. INTRODUCTION The database community is devoting increasing attention ...

. Modeling data warehouses is a complex task focusing, very often, into internal structures and implementation issues. In this paper we argue that, in order to accurately reflect the users requirements into an error-free, understandable, and easily extendable data warehouse schema, special attention should be paid at the conceptual modeling phase. Based on a real mortgage business warehouse environment, we present a set of user modeling requirements and we discuss the involved concepts. Understanding the semantics of these concepts, allow us to build a conceptual model-namely, the starER model-for their efficient handling. More specifically, the starER model combines the star structure, which is dominant in data warehouses, with the semantically rich constructs of the ER model; special types of relationships have been further added to support hierarchies. We present an evaluation of the starER model as well as a comparison of the proposed model with other existing models, pointing out ...

A data warehouse is an integrated and timevarying collection of data derived from operational data and primarily used in strategic decision making by means of online analytical processing (OLAP) techniques. Although it is generally agreed that warehouse design is a non-trivial problem and that multidimensional data models and star or snowflake schemata are relevant in this context, hardly any methods exist to date for deriving such a schema from an operational database. In this paper, we fill this gap by showing how to systematically derive a conceptual warehouse schema that is even in generalized multidimensional normal form. 1 Introduction A data warehouse is generally understood as an integrated and time-varying collection of data primarily used in strategic decision making by means of online analytical processing (OLAP) techniques. It is essentially a database that stores integrated, often historical, and aggregated information extracted from multiple, heterogeneous,...

Data warehouses are databases devoted to analytical processing. They are used to support decision-making activities in most modern business settings, when complex data sets have to be studied and analyzed. The technology for analytical processing assumes that data are presented in the form of simple data marts, consisting of a well-identified collection of facts and data analysis dimensions (star schema). Despite the wide diffusion of data warehouse technology and concepts, we still miss methods that help and guide the designer in identifying and extracting such data marts out of an enterprisewide information system, covering the upstream, requirement-driven stages of the design process. Many existing methods and tools support the activities related to the efficient implementation of data marts on top of specialized technology (such as the ROLAP or MOLAP data servers). This paper presents a method to support the identification and design of data marts. The method is based on three basic steps. A first top-down step makes it possible to elicit and consolidate user requirements and expectations. This is accomplished by exploiting a goal-oriented process based on the Goal/Question/Metric paradigm developed at the University of Maryland. Ideal data marts are derived from user requirements. The second bottom-up step extracts candidate data marts The editorial processing for this paper was managed by Axel van Lamsweerde.

A data warehouse is an integrated and time-varying collection of data derived from operational data and primarily used in strategic decision making by means of OLAP techniques. Although it is generally agreed that warehouse design is a non-trivial problem and that multidimensional data models and star or snowflake schemata are relevant in this context, there exist neither methods for deriving such a schema from an operational database nor measures for evaluating a warehouse schema. In this paper, a sequence of multidimensional normal forms is established that allow to reason about the quality of conceptual data warehouse schemata in a rigorous manner. These normal forms address traditional database design objectives such as faithfulness, completeness, and freedom of redundancies as well as the notion of summarizability, which is specific to multidimensional database schemata.

Though designing a data warehouse requires techniques completely different from those adopted for operational systems, no significant effort has been made so far to develop a complete and consistent design methodology for data warehouses. In this paper we outline a general methodological framework for DW design discussing the relationships between the different steps and the difficulties in carrying them out. Within this framework, conceptual design is based on the Dimensional Fact Model, while logical design exploits multiple cost functions at increasing levels of detail in order to improve both the efficiency and efficacy of the algorithms. A workload is characterized in terms of data volumes and expected queries, to be used as the input of the logical and physical design phases whose output is the final scheme for the data warehouse. In particular, drill-across queries are explicitly taken into account throughout the design steps. Keywords Data warehouse, design methodology, conce...

Database design for data warehouses is based on the notion of the snowflake schema and its important special case, the star schema. The snowflake schema represents a dimensional model which is composed of a central fact table and a set of constituent dimension tables which can be further broken up into subdimension tables. We formalise the concept of a snowflake schema in terms of an acyclic database schema whose join tree satisfies certain structural properties. We then define a normal form for snowflake schemas which captures its intuitive meaning with respect to a set of functional and inclusion dependencies. We show that snowflake schemas in this normal form are independent as well as separable when the relation schemas are pairwise incomparable. This implies that relations in the data warehouse can be updated independently of each other as long as referential integrity is maintained. In addition, we show that a data warehouse in snowflake normal form can be queried by joining the relation over the fact table with the relations over its dimension and subdimension tables. We also examine an informationtheoretic interpretation of the snowflake schema and show that the redundancy of the primary key of the fact table is zero. Key words. Data warehouse design, star and snowflake schema, independent and separable database schema, acyclic database schema. 1