Contents

Agenda​ 2
Denodo Overview​ 3
Hands-on Lab​ 6
First Steps​ 6
Denodo Design Studio​ 7
Basic Concepts​ 8
Organizing our Elements in Folders​ 10
Connecting to the Data Sources​ 12
Creating Sales Base Views​ 14
Creating Customer Base Views​ 16
Executing a View in Denodo​ 19
Gathering the Statistics for the Cost-based Optimizer​ 20
Combining Data​ 22
Integration View: Customer Data​ 22
Integration View: Total Sales for each Customer​ 25
Executing the integration views and understanding the optimizations​ 28
All the data in the same database (customer_data)​ 28
Data coming from multiple data sources (sales_by_customer)​ 29
Using Cache​ 30
Security​ 33
Creating Users​ 33
Creating Roles​ 34
Assigning Privileges to the Role​ 35
Assigning Privileges to the User​ 36
Login with the New Denodo User​ 37
Global Security Policies​ 38
Creating a Tag​ 38
Defining the Global Security Policy​ 39
Publishing​ 42
Associations​ 42
Navigating Through Associations​ 44
Denodo Data Catalog​ 46
Data Preparation in Data Catalog​ 51
Example use case for Data preparation​ 54
Analyzing and Visualizing Denodo Data Products​ 59
Next Steps​ 64

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 1
Agenda
This step-by-step tutorial will help you on how to set up a sample Logical Data Warehouse
Scenario and how to configure Denodo appropriately for it. The tutorial is divided into the
following sections:

●​ Denodo Overview: the first step is getting familiar with the Denodo architecture and its
web tools. (15 min)

●​ Connecting to the sources: in this step we will connect to the different datasets. (30 min)

●​ Combining data: here we will combine the different views imported from the data
sources. (30 min)

●​ Executing queries: some sample queries will be executed to illustrate several scenarios
and the optimizations applied in each one. (15 min)

●​ Security: we will see an example of how data can be masked in denodo. (15 min)

●​ Publishing the information: in this section we will see the options available to consume
the views created (15 min)

●​ Denodo Data Catalog: this is a web tool that lets both technical and business users
query, search and browse information and metadata stored in the Denodo Server. In this
section we will see the main features available to end users (30 min)

●​ Visualizing Data: In this section we will connect to Denodo from a Notebook (15 min)

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 2
Denodo Overview
To ensure timely access to actionable data, seamless data integration, robust governance, and
technical interoperability are crucial, and they require a modern data infrastructure. This
simplifies data access, accelerates data delivery, and meets the ongoing need for advanced
analytics. Such an infrastructure must effectively handle data in a variety of formats across
different platforms, including data warehouses, data lakes, data hubs, enterprise applications,
and even flat files, whether on-premises or in the cloud.

The Denodo Platform is the leading logical data management solution, delivering data in the
language of business, at the speed of business.

The Denodo Platform provides:

●​ Logical Data Layer​

The Denodo Platform provides a virtual or logical approach to accessing, managing, and
delivering data without replicating it in a physical repository.

●​ Data Integration​
The Denodo Platform integrates siloed data across all enterprise systems, regardless of
data format, location, or latency.

●​ Data Management​
The Denodo Platform enables organizations to manage related data with a universal
semantic model, providing enhanced metadata and AI/ML functionality, enabling vital data
governance.

●​ Data Delivery​
The Denodo Platform delivers and democratizes data in real time leveraging BI and data
science tools, a powerful data catalog, and APIs.

This enables organizations to acquire timely, trusted, integrated datasets for faster analytics and
informed business decisions, while building a strong foundation of AI-ready data to accelerate
generative AI initiatives.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 3
Denodo Architecture

Some of the unique differentiators of Denodo Platform are:

●​ Logical Data Abstraction​

Represents all data assets in an abstracted form, decoupled from the source systems that
store the data.

●​ Smart Query Acceleration​

Provides great query performance and data access using AI-powered query acceleration,
aggregates-aware query rewrites, and pushdowns, caching, and materialization.

●​ Advanced Semantics​
Offers a data catalog that simplifies data discovery with AI-driven recommendations and
collaboration features that leverage business semantics that adapt to the needs of data
consumers.

●​ Universal Connectivity and Data Services​

Easily connect to a wide range of data sources with 200+ connectors, and publish for
easy sharing using SQL, JSON, REST, and GraphQL APIs.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 4
 ●​ Flexible Data Integration​
The flexibility to adapt to a wide range of use case requirements, from real-time
federation to selective materialization (caching, aggregation-aware summaries), full
replication (ETL, ELT, micro batching), and streaming.

●​ Unified Security and Governance​

The centralized application of governance and fine-grained security policies that apply to
any data and any access method, along with comprehensive data activity audit logs.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 5
Hands-on Lab
First Steps
We are going to start the lab by accessing your assigned instance. After your registration, you will
be presented with the links available for this workshop:

In the Access the Denodo Hands-On Lab Web Tools, you have the links to open the Denodo
web tools you are going to use in the next chapters.

Please note that it may take some minutes to have the web tools completely started!

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 6
Denodo Design Studio
The Denodo Platform has a web application to manage the Virtual DataPort server, the Web
Design Studio. Developers can use the Design Studio to create data sources, base views,
derived views, publish web services, etc. Similarly, the administrators can also use the Design
Studio to modify the cache configuration, setting-up the authentication, perform user
management, etc.

In the registration form window, please click on the Open link under Design Studio to open it:

Once the Design Studio is opened in your web browser, use the following values in the sign in
form:

●​ Login: denodo

●​ Password: denodo

●​ Server URI: //vdp:9999/denodo_hol

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 7
And you will see the Welcome page of the Design Studio:

Basic Concepts

●​ Virtual Database: this is how Denodo organizes elements inside the Virtual DataPort
server. By default, you can see several virtual databases already created. Now we are
connected to the denodo_hol database.​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 8
 ●​ Folder: you can organize elements inside a Virtual Database creating folders. Click on the
+ icon to see some pre-created folders.​

●​ Data Sources: connections to the data sources of the workshop​

●​ Base Views: the first virtual object created in Denodo introspected from our data sources.
No base views are created at this point.

●​ Integration Views: a combination or transformed view created from other base views or
integration views

●​ Associations: similar to a PK-FK restriction in a relational database

●​ Data Services: REST web service publishing data from any of the available views in
Denodo.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 9
Organizing our Elements in Folders
All the elements in Denodo can be organized in folders so they are easier to find and classify. We
are going to start by creating the different folders that will be used during the lab. To do this, click
on your “denodo_hol” database and choose “New > Folder”. ​

Let’s create the following folders to organize our elements appropriately:

●​ “1 - Connectivity” (already created)

●​ “2 - Integration”

●​ “3 - Business Entities”

●​ “4 - Report Views”

●​ “5 - Data Services”

●​ “6 - Associations”

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 10
 Note: when instructed to click on an element in the tree, it means click on the 3 dots to the
right.

If you create any element in Denodo, and if you want to move it into a folder, you can just drag
and drop it into the folder.

Now you have this folder structure:

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 11
Connecting to the Data Sources
Your Denodo instance is already connected to some data sources. In case you need to connect
to more data sources, you can simply, on the welcome page, click on Connect to data source link
and search for your favorite data source.

Another option for creating a new Data Source in Denodo, you just click on the three dots option
of the “1 - data sources” folder and choose "New > Data source" in order to open the Data
Source creation wizard (see screenshot below). ​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 12
For this hands-on lab, you have to unfold the “1 - data sources” folder and double-click each
data source (the wizard will be opened with the url of the cluster used in the workshop. Check
the following parts of the wizard:

●​ Database adapter / Database URI / Authentication

Once the wizard is reviewed, click on “Test Connection” and you should see a Success message
as the connection is correct.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 13
Creating Sales Base Views

Once the data source is created we can create base views for the ds_tpcds_sales data source.
In order to do that follow these steps:

1.​ Double-click the ds_tpcds_sales data source

2.​ Click on "Create Base View" on the top of the screen.​

​

3.​ The Tool will then display a tree with the schemas of the database: ​
​

4.​ Click on any schema to inspect its tables and their fields. To search a view or a schema,
type its name in the box located at the top of the dialog. The list will only show the
elements whose name contains the text you entered. To create a view:

a.​ Find the “public” schema to show the tables available.

b.​ For this workshop, we are only to import the store_sales table. Select it and click
on “Create selected”.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 14
 5.​ Now you can see the schema of the created store_sales base view:

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 15
 6.​ Drag the view store_sales into the “2 - base views” folder to organize it appropriately.

Creating Customer Base Views

Now we need to create the base views for the ds_tpcds_customer data source. This is a
different data source but the process will be pretty similar.

1.​ Double-click the ds_tpcds_customer data source.

2.​ Click on “Create base view”. In this case, we will create three new base views (customer,
customer_address and customer_demographics) just unfolding the tpc_ds schema and
selecting the tables independently or by selecting the checkbox next to “Tables”:​
​

3.​ In the bottom right hand corner, click on the folder icon to change the destination folder.​

4.​ In the pop-up dialog, highlight the “2 - base views” folder and click “Ok”.​
​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 16
 ​

5.​ Finally, click on “Create selected”. Now you have selected three tables from the
database, so the Design Studio will show a dialog with a list of all the views it created.

6.​ Do not click on the “Create associations from foreign keys”, we will cover that later!​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 17
 7.​ Now you have this elements created in Denodo:​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 18
Executing a View in Denodo
To see the data of the “customer” view, we just need to follow these steps:

1.​ Double click on the view “customer” in the elements tree (left panel) to open the view

2.​ Choose “Query”

3.​ Then click “Execute”

4.​ To get:​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 19
Gathering the Statistics for the Cost-based Optimizer
With our base views created, we are going to gather the statistics for them. The cost-based
optimization process in Denodo needs data statistics (the average size of a field, the maximum
and minimum values, the number of distinct values, etc.) to estimate the cost of alternative
execution plans.

To apply the cost-based optimizations on a query, Denodo needs the statistics of at least all the
base views participating in it. The statistics allow the Denodo query engine to decide whether to
apply such optimizations as join reordering, choosing a join execution method (merge, hash,
nested,...), applying ‘aggregation pushdown’, or any other optimization.

To gather statistics:

1.​ Double-click on the “customer” base view.

2.​ Click ‘Options’.

3.​ Click ‘Statistics’.

4.​ Check ‘Enable statistics’.

5.​ Click ‘Gather statistics for selected fields’.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 20
 6.​ Click the ‘Save’ icon to commit the statistics to the view.​

Repeat the process to gather statistics for the rest of base views:

●​ customer_address

●​ customer_demographics

●​ store_sales

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 21
Combining Data
Now that the base views are created and the statistics available we are going to combine the
views. In this section we will create two integration views (combinations):

●​ “customer_data” with customer data from the ds_tpcds_customer data source.

●​ “sales_by_customer”. View that combines and aggregates data from both data sources
in order to obtain the total revenue for each customer.

Integration View: Customer Data

First, we will create a derived view using our three tables “customer”, “customer_address”, and
“customer_demographics”. This will show us the Nation and Region information for each
customer:

1.​ Click on the options icon of the “customer” base view and select “+ New > Join”.​
​

2.​ Drag and drop “customer_address” and “customer_demographics” base views into the
JOIN workspace.​
​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 22
 3.​ Click on the small circle on the ‘customer’ table and drag it to the ‘customer_address'
table to create a join operation. It will open a new window in the bottom part and drag the
column c_current_addr_sk and drop it to ‘ca_address_sk’ of the customer_address table
to create the JOIN condition described before. ​

4.​ Do the same step for the customer and customer_demographics table as described in
step 3. Drag the column ‘c_current_cdemo_sk’ and drop it to ‘cd_demo_sk’ of the
customer_demographics table to create the JOIN condition.​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 23
We have now defined our JOIN. We can finish the creation of the view with the following steps:

1.​ Go to the “Output” tab.

2.​ Rename the view as “customer_data”

3.​ Click on the “Save” button.

4.​ Drag our new “customer_data” view into the “2 - Integration” folder​
​

Well done, your first JOIN view has been created!

Note: This combination only incorporates views that come from our Customer data source. You
can execute it to see the combined data.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 24
Integration View: Total Sales for each Customer

In this case we are going to create a view that incorporates data from both of our data sources.
This view will JOIN our four base views and will group the results by customer in order to
calculate the total sales for each customer. In order to do that:

1.​ Click on the options icon of the “customer_data” base view and select “+ New > Join”.

2.​ Drag and drop “store_sales” base view into the JOIN workspace.

3.​ In the Model ( join) tab, we can drag lines between fields to establish our JOIN conditions.
Create a link between the fields:

●​ customer_data.c_customer_sk=store_sales.ss_customer_sk

4.​ Go to the “Group By” tab

5.​ Select “Use group by”, accept the warning box and add the following fields (select them
and click on the arrow or drag and drop them):

●​ customer_data.c_customer_sk

●​ customer_data.c_first_name

●​ customer_data.c_last_name

●​ customer_data.c_email_address

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 25
 ●​ customer_data.ca_country

6.​ Go to the “Output” tab.

7.​ Click on the “+ New > New Aggregate Field” button. Name the field “total_sales” and use
the following expression: sum(ss_sales_price)

8.​ Rename the view as “sales_by_customer”.

9.​ In the Order By tab, add the column c_email_address and select the condition as ASC

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 26
​

10.​Go to the Metadata tab and enter a meaningful description for this view, for example:

●​ This view calculates the total sales by each customer.

11.​Click on the “Save” button and add it to the “2 -Integration” folder.​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 27
Executing the integration views and understanding the optimizations

In this section we are going to execute the derived views created and understand the
optimizations that Denodo applies based on the type of sources, the metadata and the statistics
available.

All the data in the same database (customer_data)

We are going to start by executing “customer_data”:

1.​ Double click on the view in the elements tree

2.​ Choose “Execution Panel” and Click “Execute”

Now let’s investigate to see how the data was retrieved and how the JOIN was performed! The
information about the queries can be found in the “Execution Trace”. Once the query has
finished:

1.​ Click on “Execution Trace” and click on the bottom node.​

2.​ In this case, since all views are coming from the same source, we can see how Denodo
automatically pushed down the JOIN to our Customer data source. Checking the SQL
sentence property, we can see that only one query was sent to the Customer data
source!.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 28
Data coming from multiple data sources (sales_by_customer)

Here let’s understand how Denodo deals with multiple data source by executing the
“sales_by_customer” view:

1.​ Double click on the view in the elements tree, choose “Query” and click “Execute”. When
the query is finished, click on “Execution trace” to see how this execution differs.

2.​ In this case, the join utilizes two different sources so it cannot be fully delegated.
However, we can see that several optimizations have been applied:.

●​ Aggregation Push-down. Due to the presence of primary keys, Denodo static

optimizer is able to determine that the Group By and aggregate function can be
performed in the Sales data source. This limits the amount of data that must be
transferred through the network.

●​ Cost Optimization: To improve performance of the query, Denodo checks the

statistics of the views (the distinct values of the join fields) to select the best join
method, a HASH INNER JOIN in this case.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 29
Using Cache

Denodo provides multiple cache options with multiple benefits: on one hand it improves
performance as data is gathered directly from the cache avoiding querying data sources and the
subsequent query processing time in the Denodo layer (the performance gain can be further
improved by making use of an in-memory cache); on the other hand, when configured in full
cache mode, it avoids hitting data sources that might be overloaded at business times.

Denodo caching can operate in two main modes: Partial cache mode, and Full cache mode.

●​ Partial Cache: It stores the results of each query individually on an ongoing basis.

●​ Full Cache: Similar to the concept of materialized views

For this exercise we are going to create a new view over “sales_by_customer” view and enable
the full cache. By default Denodo provides an out of the box “Apache Derby” database as cache
but this database is only for testing and it is recommended to change it to any of the other
supported databases

As Apache Derby is already configured by default for this lab, we just need to follow these steps
in order to enable the cache for our view:

●​ Click on the options icon of the “sales_by_customer” view and select “+ New >
Selection”.

●​ Go to the Output tab and enter “report_sales_by_customer” as the view name.

●​ Save it and move it to the “4 - Report views” folder​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 30
 ●​ Click on the tab “Options” of the newly created report_sales_by_customer view, select
“Full” as the cache mode, “Never expire” as Time to Live and click on “Save”

●​ If we try to execute the view now, the result will be empty as we haven’t populated the
cache yet

●​ In order to populate the cache:

a.​ Click on the “Execute” icon at the top.

b.​ Select the “Store results in cache” checkbox and click on “Execute”.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 31
 ●​ Once the query finishes, we can execute the query again to see the performance
difference:

a.​ Go to the “Execute” tab.

b.​ Do not select the “Store results in cache” checkbox

c.​ Click on “Execute”.

●​ The execution trace will show that the data comes from the cache!

More on performance

Performance optimization is the core of the Denodo Platform. These are just some of the
optimizations that Denodo offers out of the box. Many more optimizations, including other cache
options, use of data movement, summaries, MPP processing... are available.

More information about the different optimizations can be found in:

●​ Denodo Community (Performance Optimization category):

https://community.denodo.com/kb/en/category/Performance%20&%20Optimization

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 32
Security
Denodo contains a large number of security options to restrict data access. This includes
authentication credentials, authorization options such as data masking, and security protocols
such as SSL and Kerberos. Security is configured at all stages. On the downstream side, you can
configure SSL or pass through authentication. Within Denodo’s Virtual Dataport, you establish
users and roles. And then upstream, you may also use SSL as well as standard JDBC and ODBC
security mechanisms.

Denodo provides the option of creating users or roles but it is also possible to import them from
an LDAP or Active Directory server. In this exercise we are going to create a user and show how
we can add privileges to mask data to certain users.

Creating Users

1.​ Click on “Administration > User Management”.

2.​ Click on “New” and provide the following credentials:

●​ Login/Password/Retype Password: end_user / D3n0d0;00 / D3n0d0;00

3.​ Click “Save” to create this user.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 33
Creating Roles

1.​ Click on “Administration > Role Management”.

2.​ Click on the New button and provide the name analyst and you can provide optional
description: This role is created for analyst group

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 34
 3.​ Click the save button

Assigning Privileges to the Role

After creating the role, it is important to assign some privileges to the new role over the virtual
database(s) or views we have created previously. A non-admin user would need at least
CONNECT privilege to one virtual database in Denodo to be able to login.

1.​ Click on options icon next to analyst role and select Edit Privileges

2.​ Provide CONNECT access to the “denodo_hol” virtual database.

3.​ Click on the pencil icon for “denodo_hol”

4.​ Expand your “2 - Integration” folder and provide EXECUTE access to “customer_data”
and “sales_by_customer” views.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 35
 5.​ Click the save button. ​

Assigning Privileges to the User

Now that we have created our user and role, let’s give the user some permissions. We will assign
the newly created role to our user end_user.

1.​ From the “User Management” window, check the box next to user “end_user”.

2.​ Click on “Edit Roles”.

3.​ Select the analyst role and click the save button.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 36
Global Security Policies
Another important security feature in Denodo is the Global Security Policies. Global Security
Policies allow you to define security restrictions that apply to all/some users over all/several views
that verify certain conditions.

Global policies are easier to manage than view restrictions (Row Restrictions and Column
Privileges) because when the same security policy applies to several views, you can define it
once instead of having to assign it to multiple views. Global policies are often used together with
“Tags”, which are labels that you can assign to views and their columns.

Let’s create a tag and then we will use that tag to create a global security policy.

Creating a Tag

1.​ On the server explorer screen, click on the Tags tab and then click on the + icon.

2.​ Provide the name confidential to the tag and click on save button.

3.​ Click on the Tagged Columns tab​

4.​ Click back on the Explorer tab

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 37
 5.​ Drag and drop the customer view to the confidential tag window on the right.​

6.​ Select c_first_name and c_last_name columns and click ok (We will be masking the first
and last name of our customers).

7.​ Click Save to save the tags with our columns.​

Defining the Global Security Policy

1.​ Click on Administration > Semantic and governance > Global Security Policies

2.​ Click on the NEW button

3.​ Provide the name as confidential

4.​ Under the Audience tab, for the question: Who does the global security policy apply to?

a.​ Select “Any role in the list” in the dropdown

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 38
 5.​ Click the Add roles button next to it and select the role Analyst.

6.​ Scroll down and under the Restrictions tab for the question: What are the restrictions?
Select:

a.​ Mask columns tagged with any of the these tags

b.​ Click Add tags

c.​ Select confidential and click ok

7.​ Click ok to save the policy

Login with the New User

Now that we have created our user (end_user), a role (analyst), and a global policy (confidential),
let’s use it for connecting to the Design Studio:

1.​ From the top menu, click on “Logout denodo”​

2.​ Login with the credentials end_user / D3n0d0;00

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 39
 3.​ This user can only see the selected views​

4.​ Now let’s try to query “customer_data” to see the results.

●​ Expand “2 - Integration” folder, click on “customer_data”

●​ Click on “Query”

●​ Click on “Execute”.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 40
The results will contain the values where first name and last name are shown as:

Okay, let’s logout and login with the user denodo again!

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 41
Publishing
Denodo provides several ways of consuming created views including JDBC, ODBC, REST and
SOAP Services, and ADO.NET. This section will show how to consume data from multiple client
applications

Associations

Before we publish our data, we are going to create associations between our virtual views.
Associations are similar to the concept of PK-FK constraints in a relational database. They can be
imported directly from a data source for those views that belong to the same database and they
can be created manually too.

The associations are used by the Data Catalog and REST Web Services to traverse/lookup the
relationships and by the Denodo optimizer for applying performance optimizations. They are also
exported as PK-FK through ODBC/JDBC for external applications such as modeling tools.

Just to keep it simple for this exercise we are going to import them automatically for our base
views located in MySQL. In order to do that:

1.​ Login as denodo / denodo in the Design Studio

2.​ Click on three dots next to “customer” view and click on “Discover associations”

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 42
 3.​ Select all the options available and click “Create Selected”

4.​ Drag the two newly created Associations to the “6 - Associations” folder.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 43
Navigating Through Associations

All the views created in Denodo are published as REST services in the Denodo RESTful Web
Service endpoint. Using that endpoint we can select the output representation of the data: HTML,
XML or JSON.

In the registration form window, please click on the link near Denodo RESTful WS to open it:

That action will open the RESTful Web service in a new tab of your browser:

●​ Use denodo / denodo as credentials

​
Click on the “customer” link to see the data for the “customer” view in HTML format. The web
service HTML interface will show the “Associations” created before as links and will allow you to
drill down/traverse the data.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 44
For example, click on the ‘customer_address’ link of the first row.

We allowed this data service to be consumed as XML and JSON and these formats can be
obtained by adding ?$format=json or ?$format=xml to the URL.

●​ For example, <BASE_URL>/denodo-restfulws/denodo_hol/views/customer?$format=json​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 45
Denodo Data Catalog
The Denodo Data Catalog is a web tool that lets both technical and business users query, search
and browse information and metadata stored in the Denodo Server.

In the registration form window, please click on the Open link under Data Catalog to open it:

●​ Login with the username and password denodo / denodo

●​ The first thing we have to do is to synchronize the Data Catalog with the changes we
have done in Denodo in this workshop. Just click on the button that appears in the home
page:​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 46
 ●​ Click on the “Continue” button several times in the wizard to import all the views, data
services and tags.

●​ Your home page will look similar to this:​

Now Business users will be able to explore created views, metadata, lineage, execute queries
and export the results to different formats. Let’s take a look at this:

1.​ Search for ‘sales’ in the Search bar.​

2.​ We are presented with a list of views that match our search:​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 47
 3.​ Click on sales_by_customer to see the summary page of the view, including the
description, categories and tags of the view.​

4.​ We can also see the view schema clicking on the Schema tab.​

​
​
To make it easier for business users to understand what they are looking at we could
have added a description for the columns when defining them in the Design Studio and
they would have appeared here.

5.​ Click on ‘Data Lineage’ to take a look at the data lineage (resize if necessary, to see full
diagram). By clicking in any column, c_customer_sk , we will see what is the path from the
source to the output of the view.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 48
 6.​ Now let’s query the view. Click on “Query” and add the fields to ‘Output columns’. Check
‘Select all’ and drag the highlighted fields to the ‘Output columns’ area:​
​

7.​ In the ‘Filters’ area, click ‘Add Quick Expression’: ​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 49
 ●​ Add the expression: total_sales > 500​

Once done, click the “Execute” button to execute the query. Also, it is possible to export to
different formats by clicking the “Export” button (MS Excel, CSV, …).

When the execution finishes you will see the results:

You can also save and share the query, but we won’t explore this dialog today.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 50
Data Preparation in Data Catalog

The Data Preparation tab provides you another way for querying a view in Denodo. It is intended
for business users with little knowledge on the view schema or VQL, based on the what you see
is what you get principle.

Unlike the other methods, you start retrieving data from the view. Not the entire data set, but a
subset of data that works as a representative example of the view. Apply one action and the data
will be updated in real-time, so you perceive the effect of the action at the moment and decide if
it is what you need or not. Then apply another action and another one until you are satisfied with
the result.

Let’s see Data Preparation in action using another view, for example, customer_data:

1.​ Navigate to Browse > Database/Folders in the top menu

2.​ Expand the database denodo_hol > 2 - Integration and select customer_data view

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 51
 3.​ Click on Data preparation tab

4.​ Let’s assume we only want to see important customer information like customer name
and address and the country in which they live in.

5.​ Under columns tab, we will select the following columns:

a.​ c_customer_sk

b.​ c_first_name

c.​ c_last_name

d.​ ca_street_name

e.​ ca_suite_number

f.​ ca_city

g.​ ca_county

h.​ ca_state

i.​ ca_zip

j.​ ca_country

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 52
 6.​ Drag all these columns to the output columns tab:

7.​ Click on Load data

8.​ Now our data set is ready for data preparation!

9.​ By default Denodo loads the first 100 rows of the dataset. This sample set can be
changed to a different number and also the sampling method can be changed from the
first 100 rows to random 100 rows.

10.​Now we can apply transformations over the dataset.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 53
Example use case for Data preparation

Now we are ready to apply transformations over the data that we loaded in the last step.

1.​ While exploring the dataset, for the column: ca_country, we can see that there are some
empty/missing values in that column. Lets filter those empty values

2.​ The first transformation we are going to do is over ca_country. Select the column
ca_country and under the filters tab select is not

a.​ In the new dialog box, enter a space in the tab <ENTER A TEXT VALUE> and click
ok​

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 54
 3.​ You will notice that a new line has been added under the Data preparation tab on the left
hand side. Data Catalog will maintain a history of all the transformations applied over the
dataset in the current user’s session.

4.​ In the view, we can see that we have two different columns as first name and last name.
Let’s assume we want to combine the two columns as one which we will call Full Name.

5.​ Click on the column c_first_name and then click on concat under transformations tab.’

6.​ In the new dialog box, under the list of values, click on + icon to add another list of values.

a.​ First value (column value) - c_first_name

b.​ Second value (constant value) - <SPACE>

c.​ You have to change the THIRD VALUE from (constant value) to (Columns value) by
using the dropdown box

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 55
 d.​ Third value (column_value) - c_last_name

e.​ Click ok

7.​ Now we can see the full names of our customers in a single column:

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 56
 8.​ Click on three dots, next to c_first_name and select Rename column:

9.​ Rename the column as Full Name and click ok

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 57
 10.​Since we have full name, we no longer need the last name, so click on three dots next
c_last_name and click Remove

11.​We are ready with the final dataset with transformations and now to execute the query,
click the execute button to retrieve the results.

12.​You can also click on Save to save the transformations applied and reuse it in the future!

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 58
Analyzing and Visualizing Denodo Data Products
Denodo Platform allows you to browse the existing datasets and sample data from its Data
Catalog and its advanced execution engine allows you to quickly analyze the data, regardless of
the location, as it uses a logical approach to access data.

The last part of this workshop will consist of showing how to leverage the views created in
Denodo via an external application, a notebook in this case, for creating a visualization.

In the registration form window, please click on the Open link under Notebook to open a
JupyterLab notebook:

1.​ For accessing Jupyter, use the token: denodo and click on the Log in button.

2.​ Click on the work folder and open the notebook

workshop_cloud_data_integration.ipynb

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 59
 3.​ As you can see, the notebook already included some paragraphs. You don’t need to know
python to complete this workshop!

4.​ The first paragraphs install the Denodo Dialect for SQLAlchemy package. SQLAlchemy is
the Python SQL toolkit and Object Relational Mapper that gives application developers
the full power and flexibility of SQL. You can run that cell clicking on the play button to
download and install it.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 60
 5.​ Run the rest of the paragraphs in order (after clicking on the play button you have to wait
until the number of the paragraph is shown)​

6.​ Here is a screenshot of the query to Denodo:

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 61
 7.​ Our final output would be a bar chart showing the total number of customers in each
bucket:

Well done, you have completed this workshop!

We hope you enjoyed it.

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 62
Share Your Thoughts
We value your input!

Your feedback fuels our growth and helps us shape a better experience for
you. Have an idea, suggestion, or just want to add a review of this
workshop?
Please fill our feedback form: https://www.surveymonkey.com/r/denodo-training

We're all ears—let us know what's on your mind. Together, we'll make great things happen!

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 63
Next Steps
The Denodo Platform supports many use cases including IT Modernization, Operational
Resilience and Efficiency, Self-Service for Data Democratization and Improved Customer
Experience.

Denodo Platform is the leading Platform to use Logical Approach to Data

Integration, Management, and Delivery!
Denodo Platform is a critical piece of technology integral to achieving speed and agility. It
provides users with the necessary product capabilities for automating the Logical Data Fabric
design.

Don’t wait! You can start using the Denodo Platform for creating your own scenarios following
different ways:

●​ Denodo Free Trial:

●​ https://www.denodo.com/en/page/agora-getting-started

●​ Experience the full benefits of Denodo Enterprise Plus with Agora, our fully
managed cloud service.

●​ Download Denodo Express for free

●​ http://www.denodo.com/en/denodo-platform/denodo-express

●​ Free to Download. Fully functioning data virtualization platform with certain

restrictions.

●​ Community based support: Tutorials, Documentation, KB articles, Videos, Q&A

section… available for free

●​ Take Denodo for a Test Drive:

●​ https://community.denodo.com/test-drives/

●​ Denodo Test Drives enable anyone to quickly and easily explore the benefits of
using Denodo Platform.

●​ It is completely free of charge for demonstration, education and evaluation

purposes.

Contact us to request more information: http://www.denodo.com/action/contact-us/en/

denodo.com Cloud Data Integration with a Logical Data Fabric Hands-on Lab 64