Denodo Best Practices For Base Views

The denodo “Base Layer” in the Logical Data Warehouse (LDW) can be thought of as the Data Staging local layer in a more traditional data warehouse (DW) development pattern.  The Base layer is the level at which the source system data structures are transformed into denodo field types and the source data structures are rendered as created in base views (bv).

Base views (bv), the first step in virtualizing data, are the denodo structures reflecting the source system structure and the second step behind the data source connection and, therefore, are essential elements for the other layers of the Logical Data Warehouse (LDW).  To provide some guidance to facilitate the usefulness and performance of base views here are some best practices:

  • Use consistent Object Naming conventions.  It is strongly recommended that the denodo standard naming conventions be used.
  • Create indexes on Primary Keys (PK), Surrogate keys, and Foreign Keys (FK)
  • Import and or create the Primary Keys (PK), Foreign Keys (FK), and Associations.
  • Have Statistics Collection been set and include all critical fields?
  • Base views, as a rule, should not be cached unless absolutely necessary for reasons of performance.
  • Create performance Indexes to mirror sources system to improve performance.
  • Populate view Metadata properties describing the type and the nature of the data which the view contains.
  • Populate field Metadata properties describing Field. This is important for a few reasons:
    • The description can be inherited by view built from the base view.
    • This populates the Denodo Metadata Catalog within which data Stewards can maintain and improve the description.
    • Informs other developers and user of what the field is/means.
    • Field metadata should be annotated with “Not Used”, if the field is always null, blank, or empty.  This saves time and labor when researching data issues
  • Retain the original table name (applying naming convention prefix and field names to facilitate data Lineage traceability.?
  • Use denodo tools against tables, where possible, rather than (Manual) SQL views, Database views, or Stored Procedure.  Denodo cannot rewrite or optimize these objects.

Related Reference