Learn how to build queries graphically to get data

• < Previous
• Next >

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 can see a summary of the selected view. It will show the metadata of the selected view such as the database name, the list of the categories, the list of the tags, collaboration information like Endorsement and Warnings provided by the user. Clicking on the Edit button beside the Description option you will be able to edit the description of the view. In case, the view is deprecated, an indication will appear in the summary tab at the top.

Additionally, the Summary tab includes buttons like Add Tags/Categories (more details in this section of the tutorial), Collaboration options to customize the view further and also buttons like Connection URLs, Tableau to show different ways to connect to the view/datasource.

Schema Tab

Under the Schema Tab, we can see a schema of the view, with the view description, all the fields and types. Clicking on the Edit button beside the column we can add the field description. We can also search for fields, data types and descriptions using the search option on top of each section.

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

Now click Execute, to get the results:

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

 

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 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 surname 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.

 

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

 

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. In the Output columns section, click on three dots and then click onAdd option.
2. In the “New output field” dialog, click on the Edit button beside the Field name column and provide field name as full_name and Expression as concat(name,' ',surname)
3. Our results include the newly created full_name field.
4. 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.

 

Relationships tab

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

This is useful for the business user to understand how certain views are related. You can click on the ‘i’ icon to see the related view information.

 

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):

 

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

 

Data 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.

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).

 

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 Data lineage tab and select the primary_phone field.

 

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

 

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 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.

• < Previous
• Next >