The MySQL database that you installed in the Installation & Bootstrapping section contains the data of the CRM of a company. This data is split into several tables:
The diagram of the organization of this database is the following:
Your goal here is to combine this data using a Data Virtualization approach: this will enable us to create views that are more meaningful for the consumers of the data, without having to modify the underlying data source (in most scenarios we are not the owners of the data, just consumers, so changing the data schema is not something doable).
The first step we need to follow to virtualize this relational database is to connect to it using the Denodo Platform. Connecting to the data source will allow us to introspect it and graphically select which of its tables are to be virtualized within the Denodo Platform. Once connected, we will create one base view per table in the CRM.
A base view is a representation in Denodo of existing data in a remote data source. This base view is only metadata that describes how the information is stored and accesses in the original data source but it does not contain any data. When a base view is queried the data is retrieved in real time from the source, unless it is cached (see the Performance tutorial).
First, Let's create the data source for the CRM database. In this case we will create a JDBC connection to MySQL, but other possibilities are Oracle, Microsoft SQL Server, DB2, PostgreSQL, Hive, Nettezza, Teradata, Denodo VDP, etc.
The recommended way to connect to databases when using Denodo is through JDBC (this is an acronym referred to Java Database Connectivity), so let's start this tutorial creating a new JDBC data source to import a table with a primary key.
In the Installation & Bootstrapping section you installed a MySQL database server and copied its driver into the Denodo Virtual DataPort
<DENODO_HOME>/extensions/thirdparty/lib. With the driver added to the Denodo installation, you are ready to create the Data Source
following these steps:
“New > Data source > JDBC”.
When you click in Create base view the Administration Tool will show the introspected schema of the relational database:
To incorporate some of the tables into the Denodo virtual schema, you have to check the box near the tables or views you want to import. In this case check address, client and client_type and then click on the Create selected base views button.
Later, you will be able to query these base views or combine them with other views.
When the importing process is finished, in the elements tree panel you will see the new views. If you double-click on the view name, the schema of the base view is shown in the workspace.
As you are seeing, each new base view will be listed in the Elements Tree in two places:
Finally, let's move the base views to the folder that we created for them, so one by one drag them to the "2 - Base Views" folder.
After these steps are completed we have a virtual representation of our CRM in Denodo. In the next section we are going to learn how to query it to see how the data comes in real time from our MySQL database, and after that we will start creating data combinations that will add semantic value to the client applications that are consuming this data.