Query real time data

As we saw in the previous sections, the Denodo Information Self-Service tool has a way to:

  • View the graphical representation of entities and relationships
  • Perform searches for data and metadata
  • Have an easy-to-use user interface (browser) to discover relevant data

But this tool still provides even more options, and one of the most important capabilities is executing queries directly on the sources where the data is stored. To achieve that, Denodo Information Self-Service Tool provides an Advanced Query Wizard for users to create ad-hoc queries.

Creating a "live" query

You already know how to search within the data, but now you are going to work with fresh data coming directly from the sources. For example, the user Peter Bradley (the Chief Sales Officer of the company) wants to know the sales data during the second quarter (Q2) of year 2014.

In the previous step, Peter discovered that the view sales_marketing includes a sale_date field, so he needs to execute that view over that field.

Getting the Q2 2014 sales data is as simple as follows:

  1. Click on the Views tab.
  2. Click on the view sales_marketing.
  3. Go to the query tab and add the filter sale_date >= to_date('MM/dd/yyyy','04/01/2014').
  4. Click on the + icon.
  5. Add the filter sale_date < to_date('MM/dd/yyyy','07/01/2014').
  6. Click on the query icon and voila! There is all the data they needed:

 

Clicking on the icon it is possible to open the advanced editor where it is possible specify different filters, aggregations and selections to customize the query (only needed for advanced Denodo users).

As you can see, is it possible to include as many filters as desired using the filter conditions.

Saving queries

Maybe one of the best features of the Information Self-Service Tool is the ability of save queries for reuse. Let's see how to do this:

  1. Below the results table, there is a Save Query as field. Give this query a name like 2014 Q2 Sales.
  2. Click the button.
  3. Add a description like "All product sales information during Q2 period" in the new pop-up.
  4. Click on the Save button. Easy!

Reusing saved queries

Now you are probably wondering where to find the saved queries. They are in the left pane of the Information Self-Service Tool below the Databases list (you can click the blue Databases tab along the left side to view the lists):

From here, you can select any query of the list for loading in the workspace!

This is very useful because now Peter can work with this query. For example, tomorrow he needs to report the number of products sold during Q2 grouped by Country. After loading the query...:

  1. Open the Advanced Query editor and add a Group by using the country field:
  2. Add a new field to the output getting the total amount of products, like so:
  3. Finally, click on the Query button.

Looking at the results, Peter knows that Italy is his favorite country:

 

What's next? We suggest you try the button. YES! you can export the results to data tools like Tableau!

 

With this section, you have just finished the Denodo Data Discovery Tutorial set.

Great work and thank you!