Using the Database Explorer to Explore Data Relationships (Administration and Setup Required)
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.
The Database Explorer 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 how to setup the Database Explorer, reference the article Administration and Setup for the Database Explorer.
Database Explorer Sample
Accessing the Database Explorer
Database Explorer Components
Database Explorer Tree Navigation and Nesting
Navigating Data Relationships Using Show Related (Example)
Database Explorer Sample
The Database Explorer contains a pre-configured View example with the Andi sample database, and enables users to work with test data (University themed). 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: Users can install the sample database at any time by selecting the Help menu, then selecting Getting Started.
Accessing the Database Explorer
To access the Database Explorer select the Tools menu, and then the Database Explorer:
The Database Explorer will appear with any existing file paths containing Database Explorers and their stored pre-configured Views:
Note: The Dynamic Database Explorer is a similar data navigation tool to the Database Explorer that does not require the same administration and setup as the Database Explorer (and does not use the same View navigation style as the Database Explorer). However, there are data context limitations and database referential integrity is required.
Open Existing Database Explorer
To open an existing Database Explorer, select the Folder icon in the top right corner of the Database Explorer:
Note: The schools Database Explorer options are samples included with the test database.
Database Explorer Components
It is important to understand the components / terminology of the Database Explorer prior to jumping in and navigating through your database tables! This will help provide insight into the context of how you are viewing and navigating data to get the most value and accuracy out of the viewed data.
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. These Filters are often a great place to start when needing to limit data returned for a starting point before jumping into navigation. A View Filter is represented by the following icon:
To use a View Filter, simply double click on the desired Filter and use the Parameter Entry Dialog to define the variables:
What is a View?
A View can be stored within a View Collection and are represented by a titled tab and results tab. The following icon represents a stored View:
What is a View Link?
A View Link hyper-links from one View to another within the available Database Explorers. This allows for a more logical representation of a large database with multiple Views. A View Link is represented by the following icon:
To navigate to a new View using a View Link, right mouse click on the View Link and select Go to Referenced View. The linked View will automatically expand:
Database Explorer Tree Navigation and Nesting
Database Explorer Navigation
In the below example, this University Database Explorer includes three pre-configured View Collections for Schools, Students, and Classes:
By using the plus and minus symbols within each View Collection users can quickly locate pre-configured Views, View Filters, and View Links.
Database Explorer Nesting
Database Explorers should be setup in a way that makes logical sense to the user. The way that Database Explorers are created should have a logical flow through the database in the way that they are nested.
As an example, the Students Database Explorer below has been setup to include View Filters at the very top. This ensures users can jump in and start filtering data quickly:
Logically, users may want to view Student Details associated with the basic Student records, which is included as another View Collection within Students. From here additional Filters can be applied for Student Details, or a View Link has been created that can jump to Class information for these Students which could be useful information:
Navigating Data Relationships Using Show Related (Example)
This example will show how to utilize a View Filter to search for students that have a last name of JONES. Then, this list will be filtered down further using unique Andi filtering tools, and data relationships will be explored using just a few mouse clicks.
In the below example, the View Collection for Students has been expanded, along with the View Filters.
These View Filters are a great place to quickly begin narrowing down a data result set using the Parameter Entry dialog. The Parameter Entry Dialog allows users to enter values for variables, and any values entered as a variable will filter the resulting records, without having to manually create any SQL.
The Value JONES% has been added as a Last Name variable, to filter to only the Students that contain JONES in the last name:
After selecting Run, the data set will appear with only the Students that include JONES in the last name. To view further related details for these students one or multiple desired records can be selected:
By selecting Show Detail the related data to the selected Students will appear. For this particular Table, it includes the Schedule information and the Address information displayed as result tabs along the bottom of the Database Explorer:
If further navigation is needed through additional data relationships (for example, wanting to find out more information about specific Classes that are offered to these students), the desired records can be selected and Show Details selected again to create another set of tab results:
The results include showing which Universities currently offer the selected classes, as well as data for the teachers that teach those classes at the University:
At any point the tabs lining the top of the Database Explorer can be used to navigate back and forth between the related data, making it simple to create logical data exploration paths: