Oracle 7.3 - Oracle 23ai 20+ years of experience Version (Released: )

Flat Detail Data Browser for Oracle Database

22 Jan 2024

Introduction

Hello again and welcome to our new blog. Today we want to show you another exciting new feature of KeepTool 16: our brand-new flat detail data browser for Oracle database. Before we introduce the new dialog, we want to show you the traditional way of master/detail data analysis using our well known data content browser.

Traditional detail data analysis

We are connected to Oracle’s human resources demo database as HR user. KeepTool’s data content browser allows you to show the content of select database tables. Each table’s data can be opened on its own tab and is ready for filtering.
As an example, we start with querying the COUNTRIES table. It has been filtered by COUNTRY_ID = ‘DE’. That restricts the amount of table data to one single record:

Hora allows you to drill down to detail data via foreign key relations. In our example, we want to show you detail data of the LOCATIONS table. There are two alternative approaches for following the foreign key hierarchy:

  • Master/Detail view
  • “Goto detail table” menu item.

The next two chapters explain both in detail.

See how it works!

Explore possibilities of KeepTool.

i,g

Master/detail data view

The data content browser allows you to display the detail table’s content in another data grid right below the master table grid. There is a tab for each detail table, i.e. table that has a foreign key constraint referencing the master table. Furthermore, you can manually connect another table or view by defining the master-detail relation on your own. In our example we show the content of LOCATIONS in a detail grid below the COUNTRIES master data grid.

The detail grid automatically refreshes each time the master table has been navigated. This is very convenient. The only drawback is, you cannot go further down to subdetail tables. The master/detail view is limited to displaying one master and all of itsĀ  direct detail tables, one at a time.

Goto detail table menu item

Right-click the data grid to open the context menu. Choose the “Goto detail table” item. This displays a submenu item for each detail relation.

That allows you to show the content of a detail table of our choice on another tab next to the master table. The detail data is automatically filtered by foreign key values matching the key values of the master table. In our example you can see the LOCATIONS table has been filtered in the context of the COUNTRIES master data record.

By repeating this workflow, you can subsequently go further down and open subdetail tables such as DEPARTMENTS and sub-subdetail tables like EMPLOYEES. It stops when you have reached a table that is not referenced by any foreign key constraint any more.

Limitations of the traditional approach

Both master/detail analysis approaches are great for finding a specific subdetail data record by subsequently going down the foreign key hierarchy.

But in some cases, you may be interested in all subdetail data of given master data. For example, we want to create an INSERT script for all EMPLOYEES of a select COUNTRIES record. That means, we have to iterate all intermediate data structures such as LOCATIONS and DEPARTMENTS. Neither the master/detail view nor the “Goto detail table” approach give us this result in a single step. Here comes our new flat data browser into play.

Flat detail data browser

The flat detail data browser is a modal dialog that can be opened from the data content browser’s context menu. It provides a third approach of detail data analysis.

On the left, the Flat Detail data browser shows the hierarchy of detail tables:

The root node represents the context table, COUNTRIES in our case. The structure below has been built by analyzing foreign key references. That’s why don’t bother if some table appears more than once in the hierarchy.

The selected table builds the context for the logic on right part of the dialog:

  • The query selects all (sub-)detail data of the select table.
    The root node’s query displays the same data as the underlying data content browser.
    The first detail level selects detail data very similar to the traditional approach.
    But starting with the first sub-detail level, the magic of this dialog comes into play. In our example it selects all DEPARTMENTs of the given COUNTRIES record including all LOCATIONS.
  • The data grid below, shows the data content of the query.

You can modify the SQL on your own and press “Refresh Query” to update the data view.

The data grid works very similar to the well-known data content browser. That allows you to use features such as

  • Create INSERT scripts.
  • Excel Export
  • Use different views such as record view, text view, etc.

Summary

The flat detail data browser allows you to view subdetail data of given master data without the need of iterating intermediate data structures. This allows you to achieve a considerable increase in productivity, as you can obtain a complete overview of all the detailed data of an entity at the touch of a button. The positive feedback from our customers from specific projects confirms that using the new feature saves a considerable amount of time.