Denodo Security Enforcement

As the Virtual DataPort Administration Guide, explains in the section “Types of Access Rights” section, on VDP databases, views, rows, and columns. The denodo role-based access mechanism controls how and what a user or user role can use in the virtual layer, including the data catalog.

Import Denodo Security Notes

  • Consumer security authorization is imposed at the object level, then Data Level
  • Consumer security authorization is not imposed on Modeling Layers/VDP Folders
  • Using a virtual database to partition projects or subjects is a Best Practice

Basically, the ability to grant security is as follows:

VDP Database

  •  Permissions grants include connection, creation, read, write and admin privileges over a VDP database.

VDP Views

  • Permissions grants include read, write, insert, update and delete privileges over a view.

VDP Columns Within a VDP View

  • Permissions grants include the denial of the projection specific columns /fields within a view.

Row Level Security

  • Row Level restrictions can be added to allow users to obtain only the rows that match a certain condition or to return all the rows masking the sensitive fields

Denodo Virtual DataPort (VDP) Administration Guide

 For more information, see these section denodo Virtual DataPort Administration Guide:

  • Section 12.2 of the guide describes the general concepts of user and access rights management in DataPort, while
  • Section 12.3 describes how privileges are managed and assigned to users and roles using the VDP Administration Tool.

Virtual DataPort Administration Guide

Related References

Denodo Data Consolidation & Denormalization

As the data modeling process in denodo moves through the conceptual layers of the data warehouse, there is an evolution of the data structure and their associated metadata.

The Base Layer

As the modeling process begins the base layer is the ingestion layer, where the source system data structures are recreated in denodo and field are transformed in denodo Virtual Query Language (VQL) data types. the Business layer is what folks with a traditional data warehousing background would think of as Staging or landing. These base layer views should most closely mirror the technical structure and data characteristics of the input data source and will be the least business friend in their organization, naming, and metadata.

The Semantics layer

The semantics layer is where the major data reorganization, data transformation, and the application of business friend field names and metadata begins. The semantics layer is what folks with a traditional data warehousing background would think of as the Data Warehouse (DW) or Enterprise Data Warehouse (EDW). The semantics layer of the logical data warehouse (LDW) performs serval tasks:

  • Data from multiple input sources are consolidated
  • The model becomes multi-dimensional (Fact and Dimension oriented)
  • Field names and descriptive metadata are changed to meaningful, domain normalized, business-friendly names and descriptions.
  • Domain normalizing business rules and transformations are applied.
  • Serves as a data source for the business layer and reporting layer.

The Business Layer

The business layer, which is considered optional by denodo, is modeled along a more narrow business subject orientation and more specialized business rules are applied. This is what folks with a traditional data warehousing background would think of as a Datamart (DM).

The business layer of the logical data warehouse (LDW) performs serval tasks:

  • Limits and optimizes the data to facilitate business intelligence and report activities concerning a specific line of business or business topic (e.g. Financials, Human Resources, Inventory, Asset management, etc. )
  • Business-specific/customized rules and metadata are applied
  • Supplements the semantic layer and serves as a data source for the reporting layer.
  • Additional data consolidation and data structure denormalization (flattening) may occur in the business layer

The Reporting Layer

The reporting layer, which is considered optional by denodo, is the most customized layer and sees the most reporting topic specialization and specific need transformation. The reporting layer is where a traditional data warehousing may provide customized reporting, or system interface views, interface ETL’s to produce interface files, and reporting team do more of their own development.

The reporting layer of the logical data warehouse (LDW) performs serval tasks:

  • Provides consumer-specific customized rules and metadata
  • Provides consumer-specific data organization/layouts
  • Data is optimized for consumer purposes and may be highly or entirely denormalized to meet consumer needs.

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.
  • 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 indexes on Primary Keys (PK), Surrogate keys, and Foreign Keys (FK)
  • 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. Note:  if you have a governance team, they may want to manage the metadata in the denodo data catalog.
  • 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.
  • Field metadata should be annotated with “Not Used” if the field is always null, blank, or empty.  This saves time and labor when working with levels and researching data issues

Related Reference

Denodo Data Catalog Roles

The denodo catalog provides the data governance and self-service capabilities to supplement the denodo Virtual DataPort (VDP) core capabilities. Six roles provide the ability to assign or deny capabilities with the denodo data catalog and supplement the database, row, and column security and permissions of denodo Virtual DataPort (VDP).

The Tasks The Roles Can PerformDenodo Data Catalog Role Name
Assign categories, tags and custom properties groups to views and web services.data_catalog_classifier
Edit views, web services, and databases. Create, edit and delete tags, categories, custom properties groups, and custom properties.data_catalog_editor
Can do the same as a user with the roles “data_catalog_editor” and “data_catalog_classifier”.data_catalog_manager
Configure personalization options and content search.data_catalog_content_admin
This role can perform any action of all the other data catalog roles.data_catalog_admin
The exporter role can export the results of a query from the Denodo Data Catalog.data_catalog_exporter
denodo Virtualization
denodo Virtualization

Related References

denodo > User Manuals > Denodo Platform New Features Guide

denodo > User Manuals > Data Catalog Guide > Administration

Denodo Model Best Practices For Creation of Associations

What Are Denodo Associations?

In denodo associations follow the same concept as modeling tools, which can be described as an ‘on-demand join.’

Where Should Associations Be Created In the Denodo Model?

You don’t necessarily need to define an Association at every level; usually, the best practice is to apply associations at the following points:

  • On final views published for data consumers, indicating relationships between related views; Especially, on published web services.
  • On the component views below, any derived view that brings together disparate (dissimilar) data sources.  The associations should be defined as Referential Constraints whenever appropriate to aid the optimization engine.
  • On the component views below, any derived view that joins a “Base View from Query” with standard views, since Base Views from Query cannot be rewritten by the denodo optimization engine.  Often Base Views from Query create performance bottlenecks.

These best practices should cover the majority scenarios; beyond these guidelines, it is best to take an ad-hoc approach to create Associations when you see a specific performance/optimization.

Why Are Associations important in Denodo?

In a nutshell, associations performance and the efficiency of the denodo execution optimizer along with other model metadata, such as:  

  • The SQL of the view(s)
  • Table metadata (Table Keys {PK, FK), Virtual Partitions…etc.)
  • Data statistics, which are used by the Cost Based Optimizer (CBO)

Related References

Associations in Denodo

Importing Associations And Joins From A Database Schema in Denodo

A coworker recently asked a question as to whether denodo generated joins automatically from source RDBMS database schema.  After searching, a few snippets of information became obvious.  First, that the subject of inheriting join properties was broader than joins and needed to in modeling associations (joins on demand). Second, that there were some denodo design best practices to be considered to optimize associations.

Does Denodo Automatically Generate Joins From the Source System?

After some research, the short answer is no.

Can Denodo Inherit Accusations From A Logical Model?

The short answer is yes. 

Denodo bridges allow models to be passed to and from other modeling tools, it is possible to have the association build automatically, using the top-down approach design approach and importing a model, at the Interface View level, which is the topmost level of the top-down design process. 

However, below the Interface view level, associations and or joins are created manually by the developer.

Where Should Associations Be Created?

You don’t necessarily need to define an Association at every level, usually, the best practice is to apply associations at following points:

These best practices should cover the majority scenarios, beyond these guidelines it is best to take an ad-hoc approach to create Associations when you see a specific performance/optimization.

Related References

Associations in Denodo

Which Version Control Systems Are supported by denodo Virtualization 7.0?

Using Version Control is a denodo Virtual DataPort (VDP) recommended best practice. Version 7.0 of denodo virtualization supports three Version Control Systems (VCS):

  • Microsoft Team Foundation Server (TFS) 2010 or later
  • Apache Subversion (1.7), and
  • Git

Related References: