Denormalizing Through Views - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā„¢

You cannot denormalize a physical database using views, though views can be used to provide the appearance of denormalizing base relations without actually implementing the apparent denormalizations they simulate.

Denormalized views can be a particularly useful solution to the conflicting goals of dimensional and normalized models because it is possible to maintain a normalized physical database while at the same time presenting a virtual multidimensional database to users through the use of a semantic layer based on dimensional views (see "Dimensional Views" below and Design for Flexible Access Using Views).

Prejoin with Aggregation

The following example creates a prejoin view with aggregation. Note that you can create a functionally identical object as a join index.

    REPLACE VIEW LargeTableSpaceTotal
     (DBname,Acctname,Tabname,CurrentPermSum,PeakPermSum,   NumVprocs)
    AS SELECT DatabaseName,AccountName,TableName,
    SUM (CurrentPerm)(FORMAT '---,---,---,--9'),
    SUM (PeakPerm)(FORMAT '---,---,---,--9'),
    COUNT(*)(FORMAT 'ZZ9')
    FROM DBC.TablesizeV
    GROUP BY 1, 2, 3
    HAVING SUM (currentperm) > 10E9;
    SELECT DatabaseName (CHAR(10), TITLE 'DbName'),
     AccountName (CHAR(10),TITLE 'AcctName'),
     TableName (CHAR(16),TITLE 'TableName'), Vproc,
     CurrentPerm (FORMAT '---,---,---,--9'),
     CurrentPerm * 100.0 / CurrentpermSum (AS PctDist, TITLE '  %  //
     Distrib',FORMAT         'ZZ9.999'),PctDist * NumVprocs
    (AS PctofAvg,TITLE '%         of//AVG ', FORMAT 'ZZ9.9')
    FROM LargeTableSpaceTotal, DBC.TablesizeV
    WHERE DBname   = TablesizeV.DatabaseName
    AND   AcctName = TablesizeV.AccountName
    AND   TabName  = TablesizeV.TableName
    AND   PctofAvg > 125.0
    ORDER BY 1, 2, 3, 4;

Dimensional Views

A dimensional view is a virtual star or snowflake schema layered over detail data maintained in 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 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 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). 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:
    • Develop an enterprise E-R model.
    • 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.