03d. Metadata Wizard
The Metadata Wizard enables an Index to be built from database schemas to analyse and manipulate the associated metadata.
To create a Metadata Index, click Wizards, then Metadata.
Click the New button to begin the process.
Creation Process
Enter the name that will be given to the associated Data Source, Data Source Group and Index in the Name textbox. This name must be unique.
Select the source database from the Database Type drop-down list to reveal the relevant connection fields below. Previously saved database connections are displayed at the top of the list. Enable the Show Driver and URL option to display the underlying details used to provide the database compatibility.
The following options are displayed depending on the choice of Database Type:
Option | Applicable To | Description |
Server | 4D, CXAIR, DB2, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | The name or IP address of the database server. |
System | DB2 AS/400 | The name of the database system. |
Advanced Connection Details | Oracle 12 Advanced | See Extra Parameters. |
Database Name | DB2, HSqlDB, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | The name of the target database. |
Extra Parameters | SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | Specify the SQL Server instance that will be connected to. Enter the name of an instance in the following format: instance=<instance name> If using a Windows SQL Server account, use: domain=<domain name> If using more than one parameter, separate them using a semicolon. |
Context | CXAIR | See Extra Parameters. |
Port | CXAIR, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | The port number of the database server. |
Account Name | 4D, CXAIR, DB2, DB2 AS/400, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Oracle 12c Advanced, PostgreSQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase, U Can Access | The account name of a database user that can execute the underlying SQL. |
Account Password | 4D, CXAIR, DB2, DB2 AS/400, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Oracle 12c Advanced, PostgreSQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase, U Can Access | The account password for the account detailed above. |
SQL | 4D, CXAIR, DB2, DB2 AS/400, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Oracle 12c Advanced, PostgreSQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase, U Can Access | The SQL statement that will retrieve the data from the data source. |
Delimiter | CSV | The character that splits the field values. By default, this is a comma (,). |
Text Qualifier | CSV | The character that qualifies text within a value. By default, this is quotation marks (“ “). |
First Row is Header | CSV, Excel | Sets the contents of the first row to be used as the column headings in the resulting output. |
Sheet | Excel | Specify the required Excel sheet. |
File | CSV, Excel, U Can Access | Click the … icon to open the file browser, where the relevant file can be selected. |
First Row is Header | CSV, Excel | Enable this option if the first row of data in the spreadsheet contains column headings. |
Character Set | CSV | The encoding of the CSV file. The default option is UTF-8, but this will differ depending on the source file. |
Select the relevant database schema from the Schema drop-down list and enable the Count Tables and Count Views options to count the number of rows found in the tables of the schema, displaying the count as an additional field in the Table and View display options, respectively.
The Analyse option, if enabled, will count the number of unique values and display the results in two fields. The Join Unique Count field displays the number of unique fields brought from the container to the join table, and Unique Count details the number of unique primary key values.
Specify the Alias Length to be used in the Export section of the Data Model view. An alias can be used to shorten field names in the SQL Statement pane.
Joins
A number of join options are revealed when clicking the > icon next to the Joins option.
Join Option | Description |
Join with Table Prefix | Join tables where the column names match and the column has the table name as a prefix. |
Join with Abbreviated Table Prefix | Join tables where the column names match and the column has the abbreviated table name as the prefix. |
Join with No Table Prefix | Join tables where on the column names match. |
Join Using Primary Keys | Join tables based on the predefined Primary Key values for each database. |
Ignore Date Columns | Ignores date columns when joining tables |
Ignore Columns | Specify the columns to be ignored in the Colum Name textbox. Enable the Contains option to ignore every column with the specified text, or leave it disabled to match the column name exactly. Click Add to save the column. To remove added columns, click the X icon. |
Build Options
Use the Index Size drop-down list to specify the expected number of records the resulting Index will contain. This is an indicative value and does not need to be exact.
Using this drop-down list allows the system to work out how many folders to split the Index into. The higher the specified Index size, the less folders created. A lower number of folders results in less threads used to query the Index, as a single thread is allocated per folder. This decreases the speed of individual queries, but reduces the performance impact of multiple concurrent users querying the system simultaneously.
To set the Index to automatically rebuild at a specified interval, select the relevant Schedule radio button to automatically create the required Index schedule. Clicking Every will reveal the Minutes drop-down list, where a certain minute of every hour can be specified.
Enable the Build Now option to build the Index as soon as the creation process is complete. If disabled, the settings are saved for the Index to be built at a later time.
Use the Add to Search Engine drop-down list to select the relevant search engine the Index will be added to upon completion.