Now you are ready to explore the capabilities of the Denodo Platform that make Data Virtualization a very powerful tool. In the previous sections, you have connected to the CRM database and queried its tables. Client, client_type and address data is now available within Denodo, so you can start to build a unified view of a customer that will contain the information of the three tables and remove the need for each consumer of the data to create this unified 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 it easier to manage changes to our definition of the customers (For example, if, in the future, we only want to expose residential customers to the consuming applications or the source database is changed.) With Denodo, you will maintain the proper data definition and your final applications won't require any changes.
The way of creating this unified view of a customer is through a JOIN operation, just like it's done in a traditional relational database.
Join operation
Let's see an example of a derived view creation process using the join Operation. Before we begin, let's create a new folder named 3 - Derived Views to stay organized. Then, right-click in the elements tree and select New > Join. Now, 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:
- Drag & drop the client, client_type and address views into the workspace.
- When using the Design Studio for join, first we will map the views and then we will map the fields of the view. Drag the client view and connect the arrow to the address field to set one of the join conditions. Now map the fields by dragging the client.client_id to address.client_id on the panel.
- Drag the client view and drop the arrow on the client_type. Map the fiels by dragging the client.client_type to client_type.code
- 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" button.
You can now use Ctrl+Shift to select multiple fields in the "Output" tab of derived views
TIP - Rename the view "personal_data_crm" by typing in the input box labeled "View name" at the top.
- Click on the
button to save the view. - Drag & drop the new element into our new 3 - Derived Views folder.
The Design Studio 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.
The Denodo Web Design Stduio also provides a full set of relational operations, in addition to the join, to create new views:
- UNION(extended)
- UNION(Standard SQL)
- PROJECTION
- SELECTION
- AGGREGATION
- MINUS/INTERSECTION
- FLATTEN
These operations can be used in the same way than the Join operation (Right-click > New > OPERATION), you can try yourself!
The next section (Advanced Operations) will show examples of Aggregation and Flatten operations.