Creating Derived Views¶

This section describes how to create derived views based on the base views that retrieve data from different sources.

The following sections describe the process of creating the following types of views using our example to illustrate the process:

• Union views: see section Creating Union Views

• Join views: see section Creating Join Views

• Selection views: see section Creating Selection Views

• Flatten views: see section Creating Flatten Views

• Intersect views: see section Creating Intersection Views

• Minus views: see section Creating Minus Views

• Interface views: see section Creating Interface Views

The wizards to create derived views have the feature Query Edited. Use this execute the view as you have it, without saving it. This very convenient to avoid having to save the view to test that the view returns the results you want.

We will use the following example as a guide when describing the process:

Example: Unified data about customer sales and incidents.

A telecommunications company offers phone and internet services to its clients. Data on the incidents reported in the phone service are stored in a relational database, which is accessed through JDBC. In addition, data on the incidents reported in the Internet service are stored in another relational database also accessed through JDBC.

In our example, the director of the I.T department wants to monitor the number of incidents (either telephony or Internet) notified by the clients with the greatest sales volume to establish whether measures should be taken to increase client satisfaction.

Data on customer sales volumes are managed by another department of the company. That department provides a Web Service so the other departments can access to that data.

In this example, we will see how Virtual DataPort can be used to build a unified data view to meet the needs of the I.T department, by obtaining the total number of incidents from clients with the greatest sales volumes.

The path <DENODO_HOME>/samples/vdp/incidents contains:

• SQL scripts for creating the tables used in the examples of this manual (version for the MySQL, Oracle and PostgreSQL databases).

  To follow the examples of this guide, use one of these scripts to create the required tables in a database.

• A .war file with the implementation of the Web Service used in the examples. It has been tested with Apache Tomcat 10.x and Apache Axis 1.x.

• A WSDL file with the description of the Web service used

• VQL scripts (VQL stands for Virtual Query Language) that create the objects (data sources, views, stored procedures…) that we will learn to create in this manual. You do not need to use them if you are going to follow this guide. Otherwise, edit the VQL script that matches your database:

  • Replace @HOSTNAME with the host name of your database.

  • In the CREATE WRAPPER JDBC statements, change the parameter RELATIONNAME, so it matches the name of the schema in your database. E.g. change RELATIONNAME='VDB.INTERNET_INC' for RELATIONNAME='test.INTERNET_INC'.