First steps - Simple Derived Views

Now, you are ready to explore the capabilities of the Denodo Platform that make Data Virtualization a powerful tool. In the previous sections you have connected to the CRM database and queried its tables. Customer, customer-type and address data is available within Denodo, so you can start to build a unified view of a customer that will contain the information of the three tables, removing the need for each consumer of the data to create this unified view (derived view).

We create this unified view within the virtualization platform so it can be reused by all clients. Having it defined in a single point will make easier to manage changes to our definition of the customers (for example, if in the future we only will want to expose residential customers to the consuming applications or maybe the source database will be different). With Denodo, you maintain the data definition and your final applications won't require any changes.

The way of doing this unified view of a customer is through a join operation, in the same way as it's done in a traditional relational database.

Join operation

Let's see an example of the derived view creation process using the Join operation. The starting point is to right-click in the elements tree and select New > Join.

This way, an empty Join View panel will be shown in the Administration Tool workspace.

To select the views on which the join operation is going to be executed, you have to drag&drop them from the list of views that appear on the Elements Tree. As input views are added, the schema of the resulting join view is generated automatically.

In our example, you have to follow these steps:

  1. Drag & drop (or double-click) client, client_type and address views into the workspace.
  2. Drag the “client.client_id” field and drop it on the “address.client_fid” field to set one of the join conditions.
  3. Drag the “client.client_type” field and drop it on the “client_type.code” field.
  4. Then, go to the Output tab. Here you will see both "address" and "client" views are producing the same field with different names: client_id and client_fid. You have to remove one of them selecting the field and clicking on "Remove selected" button.
  5. Rename the view to "personal_data_crm".
  6. Finally, click "Ok" to save the view. The Administration Tool will show the schema of the new derived view:

Once these steps are completed, you will have a derived view (virtual, data is not stored in Denodo) that represents the concept of a customer within your organization. This view can be queried in the same way that you did for the base views (check how to in the previous section). Now that this data is defined, your client applications can just retrieve this information directly from the virtualization server without having to define the data combination themselves.

As additional information, Denodo provides a full set of relational operations, in addition to the join, to create new views:

  • UNION
  • PROJECTION
  • SELECTION
  • AGGREGATION
  • MINUS/INTERSECTION
  • FLATTEN

These operations can be used in the same way than the Join operation, you can try yourself!

Next section (Advanced Operations) will show examples of Aggregation and Flatten operations.

NOTE