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.
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:
The next two chapters explain both in detail.
Explore possibilities of KeepTool.
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.
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.
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.
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:
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
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.