Welcome to the Denodo Basic Tutorial!

All new technologies require some kind of training before you can use them capably on your own. Here you will find a complete set of lessons that briefly describe the main topics you need to know to start your Denodo project.

Problem to solve

First you have to stop and understand the use case we are trying to solve with the Denodo Tutorials. Let's take the following scenario:

• You are working in a company that has information about its customers (CRM) stored in a relational database.
• The complete billing information for any customer is exposed in an internal Web Service.
• End users do not want to use different applications to get available information of customers (CRM, Sales Application, etc.).
• The IT department does not like the idea of creating a specific application for this business need and it would like to reuse this customer global view, if possible, in any other application.

We said in the previous paragraph: "End users do not want to use different applications to get all the information of customers", so the problem here is to connect disparate data to create a single view of the customers, and the IT department wants that single view to be reusable.

If you follow a bad design (not reusing components, creating ad-hoc code/applications, etc.) your systems will grow as in the image below:

This is a typical example of exactly what you do not want to have in your enterprise. Organizations struggle to simplify data access, deliver data more quickly, and satisfy the continuous demand for better analytics due to spread of data across data warehouses, data lakes, data hubs, enterprise applications, and files, both on-premises and in the cloud.

The Denodo Platform, powered by data virtualization, is a logical data integration, data management, and data delivery solution that provides a centralized data access layer that enables all users to find, query, integrate, and securely share datasets, in real time, with breakthrough cost-effectiveness.

This provides unified access for consuming applications, enabling organizations to acquire timely, trusted, integrated datasets for faster analytics and informed business decisions.

In this way, your applications will be independent from the physical sources where the data is stored.

After completing the Denodo tutorials you will learn that the Denodo Platform provides:

• Logical data layer: Denodo provides a virtual or logical approach to accessing, managing, and delivering data without replicating it in a physical repository.
• Data integration: Denodo integrates data across all enterprise systems, regardless of data format, location, or latency.
• Data management: Denodo enables organizations to manage related data with a universal semantic model, providing enhanced metadata, enabling data governance.
• Data delivery: Denodo delivers and democratizes data in real time leveraging BI and data science tools, a powerful data catalog, and APIs.
  

In this first tutorial we will cover the most basic functionalities of Denodo Platform.

The topics that you will find in this tutorial are the following:

• Connect to Denodo
• Create folders for organizing your database.
• Import relational databases.
• Execute queries.
• Create a simple combination view.

If you have already installed all the needed components (Denodo + Tutorial data sources), please go ahead to the next section.

To start working with Denodo you have to connect to the Denodo server from the Denodo Design Studio.

The Design Studio is a web tool for managing the configuration of the Denodo server. It allows you to connect to data sources, create views, publish data services, etc.

If you are not connected yet, go to the URL of the Design Studio and log in using these credentials:

• Login: admin
• Password: admin

Now that you are connected to Denodo, just select the tutorial database clicking on the plus icon on the left.

Now it is time to create elements in Denodo!

But wait, the first thing we have to do is to stop and think about what are best practices when creating elements in Denodo. One of the best practices is to have a good organization of elements inside of our database.

Let's start creating some folders, they will help us to make it easier to work with the elements created in Denodo.

Creating a folder

To create a new folder follow these steps:

1. Click on the Tutorial database and click on the three dots > + New > Folder

2. Name the folder 1 - connectivity
3. Later you can simply drag & drop elements into this folder.

In the next section you will learn how to create elements inside this folder.

The relational database that you have launched in the Installation & Bootstrapping tutorial contains the data of the CRM of a company. This data is split into several tables:

• a table for client data,
• a table for client types (a client can be residential or business)
• and a table for addresses.

The diagram of the organization of this database is the following:

The goal here is to combine this data using a logical 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 is to connect to it using the Denodo Platform. Connecting to the data source will allow us to introspect it and graphically select which tables should be available in Denodo. Once connected, we will create one base view in Denodo per table in the CRM database.

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 data is stored in the original data source, in other words, the base view does not contain any data. When a base view is queried, the data is retrieved in real-time from the data source, unless it is cached (see the Caching tutorial).

Connecting to a Relational Database

Let's create the data source for the CRM database following these steps:

1. Create two new folders for the data sources and base views called 1 - data sources and 2 - base views inside the 1 - connectivity folder.
2. Click on the three dots near the 1 - data sources folder and select "+ New > Data source".

In this case, we will create a connection to MariaDB using the Generic database (JDBC) option, but, as shown in the screenshot below, you have the option to select the specific data source like MySQL, Oracle, Microsoft SQL Server, DB2, PostgreSQL, Hive, Nettezza, Teradata, Denodo VDP, etc.

The default way to connect to databases when using Denodo is through JDBC (this is an acronym referring to Java Database Connectivity), so let's create the data source:

3. Name the data source, for example, ds_crm
4. Change the Database URI to match your MariaDB installation, for example, jdbc:mariadb://mariadb:3306/crm

5. Enter the login and password fields: root / admin

6. Go to the Advanced tab.

• Driver class path: select mariadb-2.7
• Driver class: enter org.mariadb.jdbc.Driver

7. Click on "Test connection" (you will see a success message)
   
8. Click on the "Save" button.

Create Base Views in Denodo

Now it's time to create some base views. Click the "Create base view" button at the top to show the introspective schema of the relational database:

This dialog shows all the databases of the data source that the configured user has privileges to connect to.

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, unfold the crm database, select the address, client and client_type tables and then click on the Create selected button.

Also, you want the base views we are about to create to be inside the 2 - base views folder. You can change it on the bottom right by selecting the Folder or dragging the base views to the folder once created.

In addition, it is recommended to follow the Naming Conventions, so before the name of the Base Views you should add "bv_crm_" as prefix for the base views. This can be accomplished by including it in the field "Prefix view names with".

When the importing process is finished, you will see the new views in the elements tree panel, but you will also see a prompt about creating associations.

The associations in Denodo will mirror the foreign key constraints of the tables/views in the source database, so it is always a good idea to create the associations. You just need to click on the "Create associations from foreign keys" button and then on the Create selected button in the "List of Discovered Associations" window, cool!

As you can see, each new base view and association will be listed in the Elements Tree, inside the folder where they were created (in our case the "2 - base views" folder).

Now let's review our base views. If you double-click on the base view, the schema will be shown in the workspace. For example, let's double-click on the bv_crm_client base view:

After these steps are completed, we have a virtual representation of our CRM tables in Denodo. In the next section, we are going to learn how to execute these views in Denodo to see how the data comes in real time from our relational database, and, after that, we will start creating data combinations that will add semantic value to the client applications that are consuming these data.

After creating the base views in the previous section, we are ready to execute some queries in Denodo to retrieve the data from our CRM. Let's learn how to issue queries to the Denodo server!

There are several ways to perform the execution of a view, let's review the different options using the "bv_crm_client" base view.

Option 1: Context menu

The first option is to use the Context menu. You have to:

1. Click on the name of the view in the Elements Tree panel and click on the Three dots.
2. Select "Query".

3. Then click the Execute button at the bottom of the panel to send the current sentence (shown in the tab) to the Denodo server.

Option 2: View detail window

In this case you have to:

1. Double-click on the name of the view.
2. Press the Query button at the top of the workspace.

3. Then click the Execute button at the bottom of the panel to send the current sentence (shown in the tab) to the Denodo server.

Option 3: VQL Shell

Another option is to use the VQL Shell:

1. Click on the Three dots near to the name of the view and select "VQL Shell > Select..."

2. A VQL sentence is created in the top-right panel (it is a SQL-like query).

3. Then click the Execute button.

Show Results

Whatever option you have selected, the same query is executed:

Code:

 SELECT * FROM tutorial.bv_crm_client

After receiving that query, the Denodo server will create a query plan to hit the data source (in this case, the relational database) in order to get the results:

Of course, you can filter the results obtained in the output using a WHERE condition in the sentence. This can be done using the graphical interface in the execution window shown when using the Context menu or View detail window options.

In this panel, you have to specify the where condition. Click on Ctrl+space for autocomplete

To summarize, now you have learned how to execute queries over Denodo views and how the server is querying the source database in real-time to return the results. In the next section, we are going to learn how to create new views in Denodo creating combinations between existing Denodo base views.

Now you are ready to explore more capabilities of the Denodo Platform that make it a very powerful tool for integrating data.

In the previous sections, you have connected to the CRM database and queried its tables client

,

client_type and address using the base views created in Denodo.

As the metadata is now available within Denodo, you can start to build a unified view that contains the information of the three tables and remove the need for each consumer of the data to create this unified view.

With Denodo, you will maintain the proper data definition and your final applications won't require any additional changes because they are decoupled from the data sources.

The way of creating this unified view is through a JOIN operation, just like it's done in a traditional relational database.

Join Operation

Let's see an example of the creation of a Denodo view using the Join operation.

Before we begin, let's create a new folder named 2 - integration to have our views well organized.

Then, click on the three dots near the 2 - integration folder 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:

1. Drag & drop the bv_crm_client, bv_crm_client_type and bv_crm_address views into the workspace.
2. When using the Design Studio for joining data, as the base views are associated (we did this when we created the associations automatically in a previous section), Denodo will infer the fields that can be used for the join, that's great!

3. In the Join conditions tab you can find the conditions used for the join:
   bv_crm_client.client_type = bv_crm_client_type.code
bv_crm_client.client_id = bv_crm_address.client_fid

4. Then, go to the Output tab. Here you will see both bv_crm_address and bv_crm_client views are producing the same field with different names: client_id and client_fid. You can remove one of them by selecting the field and clicking on the "Remove" button.

5. Rename the view to iv_crm_personal_data by typing in the input box labeled View name at the top.

6. Click on the Save button to save the view into our 2 - integration folder.

The Design Studio now shows the schema of the new view:

Once these steps are completed, you will have an integration or derived view 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, opening the Query panel (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 (the join) themselves.

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

• UNION (extended)
• UNION (Standard SQL)
• PROJECTION
• SELECTION
• AGGREGATION
• MINUS
• INTERSECTION
• FLATTEN

These operations can be used in the same way as the Join operation (+ New > select the OPERATION), you can try yourself following the rest of the tutorials!

Congratulations! You have completed the Denodo Basics Tutorial.

This is just the first step in understanding the Denodo software. Now, you are prepared to go even further with the rest of our tutorials and become a Master Data Ninja!