Learn how to build queries graphically to get data

Data Catalog

We are now going to explore the features that offer more in depth interrogation of a view in the Data Catalog. This includes:

  • Querying a view and filtering results
  • Exporting results to a file
  • Creating new fields
  • Saving queries
  • Exploring view relationships
  • Exploring data lineage
  • Querying views with relating fields

Data Catalog View Exploration

From the previous section, we have selected our client view. We can now explore the contents of this view.

Summary Tab

Under the Summary Tab, we see a summary of the selected view, with our view description, all the fields and types, as well as the Metadata description (if any). Clicking on Edit button you will be able to edit the description of the view and/or the description of the fields of the view, and assign Tags/Categories to the view (more details in this section of the tutorial).

View Summary

Query Tab

The next tab is the Query Tab. Here Ad-hoc queries can be run against the view (the query is created graphically).

For our view, select the following fields all and drag the fields into the Output columns area.

  • client_id
  • name
  • surname
  • client_type

Query

Now click Run, to get the results:

Query Results

This query is executed in real time against the Virtual DataPort Server.

NOTE

Of course, the Data Catalog allows to export the results! You can select CSV, HTML or Excel as output format by clicking the Export button.

View Results Export

 

More options are available when querying a view

If we want to filter the results of the view, and order the results by price, we can easily do so. Click the Query Definition bar to bring back the query options.

Begin by dragging the field by which we want to filter, for example dragging field client_type to the Filters section.

We will now need to add an expression, we can add = and '02'. We also add the package_id field to the Order By section for which we want to order the results by, and click the arrow to change the Order By to descending order.

Filter View Input Order By

 

Now click Run. The results now are filtered to only include results for client_type = '02', and the results are ordered by the surname field.

Filter View Results

 

We can further manipulate the resulting set by using the Add feature!

Let us consider the scenario where we want to combine the name and surname fields into a new full_name field. We can do this by concatenating the name with the surname following these steps:

  1. First We can click Add > New field. Here we can create a field full_name with the following expression: concat(name,' ',surname) New Field
  2. Our results include the newly created full_name field. New Field Results
  3. If we would like to save this query for later use, we can click Save. This will save the query under the My Queries section. Save Query

 

Relationships tab

The next tab is the Relationships tab, which shows the associations created between views.

Views need to have associations defined in the VDP Server, which you have already configured in the Linked Data section of the Data Virtualization Basics.

NOTE

This is useful for the business user to understand how certain views are related.

View Relationships

 

Queries involving views with relationships

It is possible to join and execute simple queries in the Data Catalog by using the Relationship Fields option. These relationships are the same as explored in the Relationships tab, which are defined in the Virtual DataPort Server.

Let's return to the Query tab of the client view. In the Relationship Fields section, we see address. This is due to the relationship defined in the Linked Data tutorial. Now you can add the field address / state (see screenshot below):

View Join Fields

 

If we execute this view, we will see the results set contains the newly added address / state field.

View Join Fields Results

If we click on VQL, we can get the VQL statement that is executed against the server. Notice the LEFT OUTER JOIN that is automatically created.

TIP

 

Lineage tab

The lineage tab displays a tree graph with all the data sources and views used to build the current view.

If we click on one of the fields under View fields, we will be able to see the lineage of a specific field. This is especially useful when dealing with complicated derived views, as we will explore later.

View Lineage

By clicking on a node, you can see the details of the corresponding data source or view (e.g. Name, Type, Description, Projected fields, Join conditions, etc).

View Lineage_Node

 

Lineage of Complex Views

Let us now view the lineage of a more complex view.

Return to the Search page and search for client_with_bills. Open this view and navigate to the Lineage tab and select the primary_phone field.

View Lineage Complex

 

We can now see the value of the Lineage tab, where we can identify the lineage of the primary_phone field. including all of the operations involved with the field.

View Lineage Complex Node

 

In the next section we will explore Indexing data to enable the Content search functionality (note that until now, the Search form was only searching in the Metadata: views, tags, etc. but not in the data returned by those views!).

Note: the next section is oriented to technical people who wants to know how to enable that functionality. If you need only to learn how to use it, please skip that section clicking here.