Exago Logo
Search
Generic filters
Exact matches only

Automatic Database Discovery

This article applies to the Admin Console > TreeData.png Data TreeDataSource.png Sources settings.


To discover and add data objects, joins and metadata to the configuration automatically use:

Automatic Database Discovery is available for all Exago supported Data Sources except files (Excel & XML), .NET Assemblies and Microsoft OLAP. Google BigQuery Data Sources do not support primary and foreign key constraints, so the Automatic Database Discovery tools cannot add primary key columns or suggest joins.

Add All Complete Objects, Joins and Metadata v2021.2+

To automatically add all complete data objects, joins and column metadata in a single operation, use the Add All Complete Objects, Joins and Metadata feature.

This feature adds new tables, views, stored procedures or database functions that have at least one primary key defined in the data source (called a complete object). Joins are added when there is a foreign key from one complete object to another.

Any data objects or joins that already exist in the configuration will not be updated with information discovered with this method, they are skipped instead. To add missing metadata or join information, use the Discover Database Metadata method described below.

To do so:

  1. Right-click on a Data Source.
  2. Click AddNew.png Add All Complete Objects, Joins and Metadata in the context menu
  3. Click Okay on the confirmation dialog, or Cancel to abort the process.
    GqNYnO1Lx7.png
  4. Wait for the summary tab to open.
    Y4S9hjHFbZ.png

    The summary tab shows the result of the operation:

    • Total complete objects added — number of complete data objects added to the configuration
    • Total incomplete objects found — number of incomplete objects found. Incomplete objects are not added to the configuration with this feature. Use Discover Database Metadata to add them.
    • Total objects skipped — number of complete data objects found that already exist, or otherwise not added to the configuration
    • Total joins added — number of joins added to the configuration
    • Total joins skipped — number of joins found that already exist, or otherwise not added to the configuration
    • Total metadata added — number of data object columns that had column metadata added
  5. If in step 4 there were any incomplete objects found, either use Discover Database Metadata or manually add the metadata to make them complete.

Discover Database Metadata

The Discover Database Metadata option is available for granular control over the automatic discovery and import of Data Source schema information.It is also used when incomplete objects exist in a Data Source and are not detected with the Add All Complete Objects, Joins and Metadata v2021.2+ feature.

To start using Automatic Database Discovery, select a Data Source from the list and then either:

  • click the Discover Database Metadata AdminDiscoveryLarge.png icon at the top of the main menu
  • right-click the Data Source then click AdminDiscoverySmall.png Discover Object/Join Metadata from the context menu

This will open a discovery tab for the Data Source.

explorer_df40UvksDn.png
Automatic Database Discovery tab

In the discovery tab you can:

  1. Select the Tables, Views, Functions, and Stored Procedures you would like to add by either:
    • checking individual item checkboxes
    • clicking DiscoverySelectAll.png Select All Complete Objects to select only those objects with unique key values already defined
    • clicking DiscoverySelectAll.png Select All Objects to select all data objects regardless of whether or not unique key values are defined
      Table A — Icons
      IconDescription
      skeleton keyThis object has identified unique key values
      converging right pointing arrowsThis object has associated joins
      DiscoveryObjectInfoNeeded.pngThis object is incomplete
  2. Add Unique Key fields by right-clicking on an object and selecting Add/Edit Primary Key Info.
  3. Check Preview Only and then ApplyBtn.pngAdd Objects to preview the selected objects and joins.
  4. Uncheck Preview Only and then add the selected Data Objects to the system by clicking ApplyBtn.png Add Objects or ApplyBtn.pngAdd Objects and Joins to add the selected objects and any associated joins.

    Note

    If any selected Objects are missing unique key values they can be completed individually in a new tab which will open entitled Incomplete Objects.

Schema Discovery

Exago can retrieve primary and foreign key contraint information from the Data Sources by either:

SQL Queries

Customizing Data Discovery SQL v2016.3.6+

The SQL statements used for Automatic Database Discovery can be customized if necessary, in order to accommodate non-standard key names.

To customize the discovery SQL:

  1. Refer to Managing the dbconfigs.json File for the location of the file to edit. Follow the instructions in the Overriding dbconfigs Information section to correctly override the default behavior depending on your version of Exago.
  2. Locate the property for your data source type and edit the SQL for either or both primary and foreign keys.
  3. Save the file, then run Database Discovery to see your changes.
"mysql": {
     "PrimaryKeySql": "SELECT CONSTRAINT_NAME AS indexname, TABLE_SCHEMA as schemaname, TABLE_NAME as tablename, COLUMN_NAME as columnname FROM information_schema.key_column_usage WHERE table_schema = schema() AND CONSTRAINT_NAME = 'PRIMARY'",
     "ForeignKeySql": "SELECT TABLE_SCHEMA As schemaname, TABLE_NAME as tablename, COLUMN_NAME as columnname, REFERENCED_TABLE_SCHEMA as referencedschemaname, REFERENCED_TABLE_NAME as referencedtablename, REFERENCED_COLUMN_NAME as referencedcolumnname FROM information_schema.key_column_usage WHERE  table_schema = schema() AND CONSTRAINT_NAME Like 'FK%'"
}
Excerpt from dbconfigs.json showing the database discovery SQL statements for Primary and Foreign Key discovery on a MySQL Data Source

ADO.NET GetSchema() v2021.1.8+

Exago will use the standard GetSchema() methods to request schema information from a Data Source when:

  • The database driver in use to connect to the Data Source is an ADO.NET driver
  • The dbconfigs.json (or according dbconfigs.overrides.json) file contains the PrimaryKeysCollection and/or the ForeignKeysCollection properties.

Exago provides the definition of the PrimaryKeysCollection and ForeignKeysCollection properties on data source-by-data source basis. The values for each property vary with the Data Source type and the driver in use.

{
 "mysql": {
  "PrimaryKeysCollection": {
   "CollectionName": "Indexes",
   "ConstraintNameColumn": "INDEX_NAME",
   "SchemaColumn": "INDEX_SCHEMA",
   "TableColumn": "TABLE_NAME",
   "IsPrimaryColumn": "PRIMARY"
  },
  "PrimaryKeyColumnsCollection": {
   "CollectionName": "IndexColumns",
   "ConstraintNameColumn": "INDEX_NAME",
   "SchemaColumn": "INDEX_SCHEMA",
   "TableColumn": "TABLE_NAME",
   "ColumnColumn": "COLUMN_NAME"
  },
  "ForeignKeysCollection": {
   "CollectionName": "Foreign Key Columns",
   "FromSchemaColumn": "TABLE_SCHEMA",
   "FromTableColumn": "TABLE_NAME",
   "FromColumnColumn": "COLUMN_NAME",
   "ToSchemaColumn": "REFERENCED_TABLE_SCHEMA",
   "ToTableColumn": "REFERENCED_TABLE_NAME",
   "ToColumnColumn": "REFERENCED_COLUMN_NAME"
  }
 }
}
An example of implementing ADO.NET GetSchema() primary/foreign key constraint discovery for a MySQL Data Source
Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents