Exploratory Analysis: Drill-Down on Relationships and Query Results

Learn how to perform exploratory analysis in Data Xtractor with a simple example. Discover related tables through expandable relationship items. Display conceptual many-to-many relationships for less diagram clutter. Design complex queries without typing SQL, with a generic visual interface. Generate database-specific SQL. Create inline and regular charts directly from your query results, with trivial chart type selections.

Exploratory Analysis with Relationship Items

Download and install Data Xtractor, with a Chinook database sample. The images here are taken from a Chinook database on IBM Db2, but Chinook database files in SQLite, Firebird, SQL CE or Microsoft Access can be opened instantly and you can use for free these four featured databases anytime.

In Data Xtractor, after you connected to the Chinook sample, look for the Track table under the Tables folder. Right-click and select Add to New Model > Default Model. A new ER model diagram is created, with the expanded Track shape. If you show all category items, the expandable relationship items may appear as in the following image:

Expanding a Relationship Item into Connector

The Model Xtractor module of Data Xtractor creates two expandable relationship items for each physical relationship between tables. From a shape, you can drag one of them and drop it out.

The image below shows the end result after we dragged and dropped out the InvoiceLine relationship item: the app looked for the related table with the same name, create its shape (if not already there), with a connector link between.

If you select the connector and delete it, the InvoiceLine item will be collapsed back into the Track shape. You’ll also see the related Track relationship item in the other shape, which remains on screen.

If you move the mouse over the connector, you see highlighted the TrackId foreign key and primary key that defined this relationship. The connector also tells us this is a one-to-many relationship between Track and InvoiceLine, in this direction. Each Track can have zero, one or more related entries in InvoiceLine, connected through the TrackId fields.

Exploratory Analysis with Visual SQL Query

Repeat the previous steps, but add the Track table to a new query this time, with the Add to New Built Query contextual command. Uncheck all other shape items but the ones you see below. Click on the large Icons toolbar button, to hide the items you don’t use, and see the diagram as in the screenshot below.

Go over the More label and display just the query builders you see here. We’ll basically group all entries by each track name, and show how many we sold, and for what total price. The sales per track are sums of the quantity multiplied by the unit price. We keep just the top 10 tracks, after we sort them descending by sales, displayed with a Currency format. Disable pagination.

Click on the large SQL toolbar button and look at the generated SQL. The actual SQL query always depends on you current database type. For IBM DB2, here is what we had (yours, in SQLite, Firebird, MySQL, SQL-Server, Oracle or any other supported database, could be slightly different):

SELECT track_."Name" AS "Name",
   SUM(invoiceline_."Quantity") AS "Quantity",
   SUM(invoiceline_."UnitPrice" * invoiceline_."Quantity") AS "Sales"
FROM "HFH83506"."Track" AS track_
   INNER JOIN "HFH83506"."InvoiceLine" AS invoiceline_
      ON track_."TrackId" = invoiceline_."TrackId"
GROUP BY track_."Name"
ORDER BY 3 DESC
LIMIT 10

Drill-Down to Aggregates on Query Results

You can continue to enhance this query, without typing one single line in SQL. Or you can switch to manual mode and make the corrections in the SQL programming language the way you are used. Switching to manual mode will likely create a new text query, that you cannot use in design mode.

The following image shows yet another instant feature: you can drill-down on aggregates and relationships, and check the actual individual entries that have been combined. The Walkabout track below had two sales, and you can see these actual sales. Drill-down even more, for more details.

Exploratory Analysis with Chart and Inline Chart

Data Xtractor and Visual Xtractor have also two simple query builders for charts and graphs. We’ll use both.

On our previous query, you may show inline bars directly within the Sales grid cells. And you can also select the same values for a separate regular chart, display at the bottom. The track names will be used as axis labels.

While for the inline bar chart you may need no adjustments, the regular chart may require a few more steps to get the exact same image as above. However, everything is done with the toolbar buttons, with intuitive and minimal steps. We’ll leave it as exercise…

Conceptual Many-to-Many Relationships

Going back to the data model, expand ALL relationship items from the table Track, and arrange them as below. Beside the InvoiceLine shapes, we see now also clear relationships with MediaType, Genre and Album: Each track can have a related MediaType and Genre entry as category. And a track can belong to an Album.

This helps your exploratory analysis, as you discover all related relationships to a table without looking at anything else.

More interesting is the last relationship on the left, to the Playlist. Initially, the PlaylistTrack is recognized as an intersection table and left minimized. You have one-to-many relationships to this intermediate table from both ends. But Model Xtractor also generates a virtual many-to-many relationship between the end shapes you’ll find in no other data modeling application. You may choose to delete for good the PlaylistTrack intersection table from your model, and keep just this last many-to-many relationship displayed, for less clutter.

Conclusion

• Data Xtractor helps you discover contextual relationships to any single table.
• From one table, you can perform your exploratory analysis by discovering related tables, designing visual SQL queries and pumping directly a query result into charts and graphs.
• Drill-down to aggregates from your group by query results.
• Combine regular charts with grid inline charts for different exploratory analysis perspectives.
• Conceptual many-to-many relationships can simplify model diagrams and eliminate clutter.