15.00 - Dimensional Views - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Dimensional Views

A dimensional view is a virtual star or snowflake schema layered over detail data maintained in fully-normalized base tables. Not only does such a view provide high performance, but it does so without incurring the update anomalies caused by a physically denormalized database schema.

The following illustration, adapted from a report by the Hurwitz Group (1999), graphs the capability of various data modeling approaches to solve ad hoc and data mining queries as a function of ease‑of‑navigation of the database. As you can see, a dimensional view of a normalized database schema optimizes both the capability of the database to handle ad hoc queries and the navigational ease of use desired by many end users.

Many third party reporting and query tools are designed to access data that has been configured in a star schema (see “Dimensional Modeling, Star, and Snowflake Schemas” on page 187). Dimensional views combine the strengths of the E‑R and dimensional models by providing the interface for which these reporting and query tools are optimized.

Access to the data through standard applications, or by unsophisticated end users, can also be accomplished by means of dimensional views. More sophisticated applications, such as ad hoc tactical and strategic queries and data mining explorations can analyze the normalized data either directly or by means of views on the normalized database.

The following procedure outlines a hybrid methodology for developing dimensional views in the context of traditional database design techniques:

1 Develop parallel logical database models.

It makes no difference which model is developed first, nor does it make a difference if the two models are developed in parallel. The order of steps in the following procedure is arbitrary:

a Develop an enterprise E-R model.

b Develop an enterprise DM model.

2 Develop an enterprise physical model based on the E-R model developed in step 1.

3 Implement the physical model designed in step 2.

4 Implement dimensional views to emulate the enterprise DM model developed in step 1 as desired.

Several Teradata customers use this hybrid methodology to provide a high-performing, flexible design that benefits data manipulation while simultaneously being user‑ and third‑party‑tool friendly.

Martyn (2004) examines dimensional views from a research‑oriented perspective and concludes that dimensional views are an optimal means for overcoming the objections to normalized databases visa‑a‑vis DM models.