Overview

The Database Explorer is a unique and efficient approach to viewing data and navigating complex data relationships. It allows users to easily navigate data using pre-configured Views. Prompts and user friendly tabs provide context into the data relationships, enabling more time spent working with the data, and eliminating wasted time writing SQL.

This article will cover the administration and setup of the Database Explorer. It should be setup by someone who understands the data model, as well as understands logical business needs.

Database Explorers should be configured based on the needs of the users so the defined Views can be shared and understood by users who may have little or no knowledge of the database. For more information on utilizing the Database Explorer from an end users perspective, reference the article Using the Database Explorer to Explore Data Relationships.

Database Explorer Components

It is important to understand the components / terminology of the Database Explorer prior to jumping in and creating new Database Explorers! This will help provide insight into the context of how you should create and nest the Database Explorer trees to get the most value for the end users.

What is a View Collection?

A View Collection is a high level grouping of related Views, View Filters, and View Links. These Views are created to allow other Andi users who may not understand or have access to the database structure to quickly begin navigating data and relationships. It is represented as the first level of a business entry point or the start of a new details view, with the following icon:

What is a View Filter?

A View Filter allows users to run a pre-defined SQL statement which can limit the amount of data displayed for the View. When the query is run, the user can enter filter values which are used when creating the dynamic where clause. These Filters are often a great place for users to start when needing to limit data returned in the initial result set, before jumping into navigation. A View Filter is represented by the following icon:

What is a View?

A View is added be stored within a View Collection. When created, they will represent logical Tabs that can be selected in the Database Explorer user interface to simply point-and-click navigate through data relationships. The following icon represents a stored View:

What is a View Link?

A View Link hyper-links the Views from one View Collection to another View Collection within the available Database Explorers. An example would be a View Collection that will be referenced by multiple other View Collections. This allows for a logical representation making it easier for business users to navigate. A View Link is represented by the following icon:

Example

When a View or View Link is created in a View Collection, it will become a data results Tab that can be selected to quickly access additional related data information:

Sample Database Explorer

The Schools Database Explorer contains pre-configured View Collections for navigating the Andi sample database, and is a great way to gain an understanding for how an Explorer can be setup to provide logical access and flow. The included sample will return one or more defined result sets containing the matching Student Registrations for Universities (test data) and other defined related data:

Note: The sample database can be installed at any time by selecting the Help menu, then selecting Getting Started.

Creating a Database Explorer

The Database Explorer can be found in the Andi Tools menu:

To create a new Database Explorer, select the green + menu icon in the upper right hand corner of the Database Explorer. Navigate to the directory where the Database Explorer file should be saved and provide a file name:

The new Database Explorer will appear under any other existing Database Explorers (such as the sample Database Explorer included with the sample Database):

Right mouse click on the new Database Explorer for the following options:

New View Collection will create the first high level grouping to store related Views, View Filters, and View Links. There can be a single or many View Collections within a Database Explorer, and should be treated as:

The first level of a business logic entry point
The start of a new details view.

Close Database Explorer will remove the Database Explorer from view. It will, however, remain on the file system to retrieve and add again as needed.

Delete Database Explorer will permanently delete the Database Explorer from view and remove it from the file system.

Keep Open on Restart (if checked) will ensure that every time Andi is restarted the Database Explorer remains as an option in the list of Database Explorers. If not checked, open the Database Explorer when needed.

Creating a View Collection

To create a View Collection, right mouse click on the desired Database Explorer and select New View Collection:

A new View Collection will automatically be added within the Database Explorer, and will automatically have a View Filters branch which is a placeholder to add custom Filters for the View Collection:

The View Collection can be renamed, a description added to it, and titles added for the Detail Tab Title Columns by selecting the small arrow at the bottom of the dialog:

Detail Tab Title Columns

The Detail Tab Title Columns section is used to form a Tab Title when viewing related details. This can be very valuable to end users who may need to keep context while navigating through multiple levels of View Collections.

In the example below, the Detail Tab Title is a comma-delimited list of column names from the Schools master table, which will be concatenated and displayed as the details tab title. The title is displayed if the mouse cursor hovers over the tab. The title will not be displayed when multiple master rows are selected:

For example, the below Database Explorer is looking at related information for a School that was selected from the initial View. Although the School Name is not present in the data results (only the Class information), by hovering over the Details tab the SCHOOL.NAME column information appears to provide navigation context:

Creating a View

Single View

Views are created beneath View Collections, and are used to represent logical Tabs that can be selected in the Database Explorer. These translate to a simple point-and-click user interface to navigate through data relationships.

In this example, a View Collection has been created called Schools, which will contain various Filters and Views that relate to navigating School data and it's related information:

To create a View within this View Collection, right mouse click on the View Collection and select New View:

A New View will appear under the View Collection, and includes an option for a Description, as well as the SQL Query that should be defined for what data should be pulled back for that particular Tab Result. In this example, a New View has been created for Registrations:

Now, if the View Collection is selected, a tab with the results will appear which is titled Registrations, and contains School Registration data:

Multiple Views

As many Views as needed can be created for a View Collection. Each View created will be added as a Tab Result along the bottom of the Database Explorer:

Creating a View Filter

View Filters can be created to allow end users to run pre-defined SQL statements with dynamic filters, that limit the amount of data displayed for the View. As many Filters as necessary can be setup for Views to allow for flexible criteria options.

To create a View Filter, right mouse click on View Filter within the View Collection and select Add View Filter:

In this example, a new View Filter is being added for a View that will pull back a list of all Students and their information. This could be an extremely large list of data, so a dynamic View Filter is created with a requirement that the end user should fill in a value for Last Name prior to running:

If a Filter Value is not entered (and it is a requirement for a View to limit data returned), the View will not appear as a Results Tab when run. If a Value is entered for the required Filter Value, then the View will appear as a Results Tab with the appropriate data filtered:

Creating a View Link

When created, a View Link hyper-links Views from one View Collection to another View Collection within the available Database Explorers. This allows for a logical representation making it easier for business users to navigate.

To create a Link to Existing View, right mouse click on the desired View Collection and select Link to Existing View:

The View Link Selector will appear. Select any View Collection (and press OK) to create the View Link:

A new View Link will appear alongside the other create Views and View Links. Right mouse click on the newly created View Link, and select Go to Referenced View:

The referenced section for the View Link will automatically open, guiding the user the right area to select:

When viewing the Database Explorer, the Linked View will also appear as a Tab Result:

SQL Examples

The Andi Tag Language allows definition and creation of data relationships and View presentations. The below sample Database Explorer and Tag samples files are included as a guide to creating your own!

@subst_name( ) Tag

When designing the SQL statement for a detail table, use the @subst_name( ) tag to limit the results displayed in a detail table. Each substitution variable which has selected values in the master table will be replaced by a “variable = ‘value’ ” or “variable in (‘value1’,’value2’)” SQL expression. This allows for the creation of master detail relationships and leads to easy navigation of the database.

Parameter Name	Description	Valid Values	Optional	Default
Master-Table-Column-Name	The master table column name whose selected values will be added to the where clause of the detail SQL statement	-	No	-
Replacement-Name	If present, will be used instead of the Master-Table- Column-Name when creating the Replacement- Name in (n,n,) clause Example: The first_name column in table1 is first_nm in table 2. By default the generated SQL would be “where first_name = ‘SMITH’ ”. With the Replacement-Name value the statement would be “where first_nm = ‘SMITH’ ”.	-	Yes	-
Null-Check	Will add an “or column_name is null” clause if a null column value exists in the master table	true, false	Yes	-
Conversion-Type	If the data type of the master table column is different from the target table column, you can provide a data type for the target column Example: The source tables name_id column is a char data type and the target table is numeric. By default the generated SQL statement would be “where name_id = ‘12345’ ”. Instead, add the num parameter and the generated SQL will be “where name_id = 12345 ”	auto, char, num	Yes	Auto
Single-Selection	Substitute values if only one row is selected in the master table	true, false	Yes	True
Multiple-Selections	Substitute values if two or more rows are selected in the master table	true, false	Yes	True

@hide_columns( ) Tag

The @hide_columns( ) tag is a comma delimited list of column names, which will be hidden when the result table is displayed. In the example, the id columns will be needed when navigating to detail tables, but aren’t useful when displayed. You can display the hidden columns by right-clicking on a column header and selecting Hide Columns... as shown.

@single_substitution_hidden_columns( ) Tag

Use this tag to hide columns, in a result table, if only one row was selected in the master table. This is useful when you would like to provide context columns when multiple master rows are selected but are unnecessary when only one master row is available. When expensive joins are necessary to add the columns, use the @if_ms( ) tag instead.

@context( ) Tag

Surround the sqlFragment text with the @context( ) tag to provide additional context columns to a result table. The sqlFragment text will not be included in the generated SQL statement if only one master table row is selected. The fragment will be included if no master table is available or multiple master rows have been selected.

In the example, an expensive join is made to both the student and name tables, which are only necessary if multiple master rows are selected. When only one master row is selected, the sqlFragments are removed from the generated SQL statement. Note that the single_selection=false is added to prevent the STUDENT.STUDENT_ID substitution as it is no longer necessary for a single master row selection:

select @context(n.last_name, n.first_name,) c.class_name,ss.*, cs.*, cr.*              from student_schedule ss, class_schedule cs, class_room cr @context(, student s, name n), class c
where ss.class_schedule_id = cs.class_schedule_id and cs.class_room_id = cr.class_room_id @context(and ss.student_id = s.student_id and s.name_id = n.name_id) and
cs.class_id = c.class_id    @subst_name(STUDENT.STUDENT_ID,s.student_id,single_selection=false) order by @context(n.last_name,n.first_name,) ss.year

This example shows the simplified SQL statement generated when only one master table row was selected:

select c.class_name,ss.*, cs.*, cr.* from student_schedule ss,
       class_schedule cs, class_room cr, class c
where ss.class_schedule_id = cs.class_schedule_id and cs.class_room_id =                    cr.class_room_id and cs.class_id = c.class_id
order by ss.year

When multiple master rows are selected, the following SQL statement provides the additional first and last name columns to provide context to each row:

select n.last_name, n.first_name, c.class_name,ss.*, cs.*, cr.* from student_schedule ss, class_schedule cs, class_room cr, student s, name n, class c
where ss.class_schedule_id = cs.class_schedule_id and cs.class_room_id = cr.class_room_id and ss.student_id = s.student_id and s.name_id = n.name_id and
cs.class_id = c.class_id and s.student_id in (123,456,789)
order by n.last_name,n.first_name, ss.year

@context_ms( ) Tag

Surround the sqlFragment text with the @context_ms( ) tag to provide additional context columns to a result table, only when multiple master table rows have been selected. The fragment will not be included if no master table is available.

@context_ns( ) Tag

Surround the sqlFragment text with the @context_ns( ) tag to provide additional context columns to a result table, only when no master table rows are available for substitutions. This tag is useful when a user opens a view directly, without a startup query.

Administration and Setup for the Database Explorer