Automatic Database Discovery
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:
- Right-click on a Data Source.
- Click Add All Complete Objects, Joins and Metadata in the context menu
- Click Okay on the confirmation dialog, or Cancel to abort the process.
- Wait for the summary tab to open.
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
- 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 icon at the top of the main menu
- right-click the Data Source then click Discover Object/Join Metadata from the context menu
This will open a discovery tab for the Data Source.
In the discovery tab you can:
- Select the Tables, Views, Functions, and Stored Procedures you would like to add by either:
- checking individual item checkboxes
- clicking Select All Complete Objects to select only those objects with unique key values already defined
- clicking Select All Objects to select all data objects regardless of whether or not unique key values are defined
Table A — Icons Icon Description This object has identified unique key values This object has associated joins This object is incomplete
- Add Unique Key fields by right-clicking on an object and selecting Add/Edit Primary Key Info.
- Check Preview Only and then Add Objects to preview the selected objects and joins.
- Uncheck Preview Only and then add the selected Data Objects to the system by clicking Add Objects or Add Objects and Joins to add the selected objects and any associated joins.
If any selected Objects are missing unique key values they can be completed individually in a new tab which will open entitled Incomplete Objects.
Exago can retrieve primary and foreign key constraint information from the Data Sources by either:
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:
- Refer to Managing the dbconfigs.json File for the location of the file to edit. Follow the instructions in the Overriding
dbconfigsInformation section to correctly override the default behavior depending on your version of Exago.
- Locate the property for your data source type and edit the SQL for either or both primary and foreign keys.
- Save the file, then run Database Discovery to see your changes.
dbconfigs.json showing the database discovery SQL statements for Primary and Foreign Key discovery on a MySQL Data Source:
"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%'"
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
dbconfigs.overrides.json) file contains the
Exago provides the definition of the
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.
An example of implementing ADO.NET GetSchema() primary/foreign key constraint discovery for a MySQL Data Source follows:
"CollectionName": "Foreign Key Columns",