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 many real-world scenarios we are not the owners of the data, just consumers, so changing the data schema will not be possible).
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 the Denodo Platform, of existing data in a remote data source. This base view is only metadata that describes how the information is stored and accessed in the original datasource 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 thePerformance 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 referring 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 this driver added to the Denodo installation, you are ready to create the JDBC Data Source
following these steps:
"New > Data source > JDBC".
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 button.
Later, you will be able to query these base views or combine them with other views.
When the importing process is finished, you will see the new views in the elements tree panel. If you double-click on the view name, the schema of the base view will be shown in the workspace.
As you can see, 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 by dragging 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.