Generating multidimensional schemata from relational aggregation queries

Designing a multidimensional model is a non-trivial task: the requirements collected from senior managers can be inaccurate and difficult to use, especially when they are not database practitioners. On the other hand the pressing need for data warehousing comes from the fact that managers submit analytical summary queries against operational relational databases. In this paper, we view these analytical queries as a more reliable basis for multidimensional schema design. We first study how to use such queries to automatically generate measures, dimensions and dimension hierarchies and their representation in a star schema. We then use the results studying the schema evolution problem: If a new query Q′ can’t be answered by the schema previously built, how to develop a new schema from the old one so that query Q′ can be answered in the new schema. Finally, we consider rewrite OLAP queries on conventional database (multidimensional data warehouse) using materialized views in data warehouse. In our method we analyze the attributes from the given relational queries and the dependency relationships among them to design a schema in which each dimension is a lattice. The schema can answer the queries that it was generated from and the number of dimensions is minimal. Furthermore, the schema can answer many more queries that are similar to the given ones.