Skip to Content

Wiki source code of 03d. Metadata Wizard

Last modified by Tom Jardine on 06/11/2025, 14:23
Hide last authors
Tom Jardine 1.1 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 The Metadata Wizard enables an Index to be built from database schemas to analyse and manipulate the associated metadata.
6
7 To create a Metadata Index, click **Wizards**, then **Metadata**.
8
9 Click the **New** button to begin the process.
10
11 = Creation Process =
12
13 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.
14
15 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.
16
17 The following options are displayed depending on the choice of **Database Type**:
18
19 (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %)
20 |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1.0pt; width:150.15pt" width="186" %)(((
21 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
22 (% style="color:white" %)Option
23 )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:150.15pt" width="186" %)(((
24 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
25 (% style="color:white" %)Applicable To
26 )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:150.2pt" width="193" %)(((
27 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
28 (% style="color:white" %)Description
29 )))
30 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
31 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
32 Server
33 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
34 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
35 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
36 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
37 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
38 The name or IP address of the database server.
39 )))
40 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
41 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
42 System
43 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
44 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
45 DB2 AS/400
46 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
47 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
48 (% style="color:black" %)The name of the database system.
49 )))
50 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
51 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
52 Advanced Connection Details
53 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
54 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
55 Oracle 12 Advanced
56 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
57 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
58 See **Extra Parameters**.
59 )))
60 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
61 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
62 Database Name
63 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
64 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
65 DB2, HSqlDB, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
66 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
67 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
68 The name of the target database.
69 )))
70 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
71 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
72 Extra Parameters
73 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
74 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
75 SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
76 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
77 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
78 Specify the SQL Server instance that will be connected to. Enter the name of an instance in the following format:
79
80 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
81 **instance=<instance name>**
82
83
84 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
85 If using a Windows SQL Server account, use:
86
87 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
88 **domain=<domain name>**
89
90
91 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
92 If using more than one parameter, separate them using a semicolon.
93 )))
94 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
95 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
96 Context
97 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
98 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
99 CXAIR
100 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
101 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
102 See **Extra Parameters**.
103 )))
104 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
105 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
106 Port
107 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
108 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
109 CXAIR, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
110 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
111 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
112 The port number of the database server.
113 )))
114 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
115 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
116 Account Name
117 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
118 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
119 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
120 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
121 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
122 (% style="color:black" %)The account name of a database user that can execute the underlying SQL.
123 )))
124 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
125 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
126 Account Password
127 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
128 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
129 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
130 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
131 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
132 (% style="color:black" %)The account password for the account detailed above.
133 )))
134 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
135 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
136 SQL
137 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
138 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
139 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
140 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
141 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
142 (% style="color:black" %)The SQL statement that will retrieve the data from the data source.
143 )))
144 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
145 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
146 (% style="height:14.35pt" %)Delimiter
147 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
148 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
149 (% style="height:14.35pt" %)CSV
150 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
151 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
152 (% style="height:14.35pt" %)The character that splits the field values. By default, this is a comma (**,**).
153 )))
154 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
155 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
156 (% style="height:14.35pt" %)Text Qualifier
157 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
158 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
159 (% style="height:14.35pt" %)CSV
160 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
161 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
162 (% style="height:14.35pt" %)The character that qualifies text within a value. By default, this is quotation marks (**“** **“**).
163 )))
164 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
165 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
166 (% style="height:14.35pt" %)First Row is Header
167 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
168 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
169 (% style="height:14.35pt" %)CSV, Excel
170 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
171 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
172 (% style="height:14.35pt" %)Sets the contents of the first row to be used as the column headings in the resulting output.
173 )))
174 (% style="height:16.6pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
175 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
176 (% style="height:16.6pt" %)Sheet
177 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
178 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
179 (% style="height:16.6pt" %)Excel
180 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
181 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
182 (% style="height:16.6pt" %)Specify the required Excel sheet.
183 )))
184 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
185 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
186 (% style="height:14.35pt" %)File
187 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
188 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
189 (% style="height:14.35pt" %)CSV, Excel, U Can Access
190 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
191 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
192 (% style="height:14.35pt" %)Click the **…** icon to open the file browser, where the relevant file can be selected.
193 )))
194 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
195 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
196 (% style="height:14.35pt" %)First Row is Header
197 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
198 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
199 (% style="height:14.35pt" %)CSV, Excel
200 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
201 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
202 (% style="height:14.35pt" %)Enable this option if the first row of data in the spreadsheet contains column headings.
203 )))
204 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
205 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
206 (% style="height:14.35pt" %)Character Set
207 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
208 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
209 (% style="height:14.35pt" %)CSV
210 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
211 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
212 (% style="height:14.35pt" %)The encoding of the CSV file. The default option is UTF-8, but this will differ depending on the source file.
213 )))
214
215 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.
216
217 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.
218
219 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.
220
221 == Joins ==
222
223 A number of join options are revealed when clicking the **>** icon next to the **Joins** option.
224
225 (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %)
226 |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1.0pt; width:225.25pt" width="283" %)(((
227 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
228 (% style="color:white" %)Join Option
229 )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:225.25pt" width="283" %)(((
230 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
231 (% style="color:white" %)Description
232 )))
233 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:225.25pt" width="283" %)(((
234 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
235 Join with Table Prefix
236 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:225.25pt" width="283" %)(((
237 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
238 Join tables where the column names match and the column has the table name as a prefix.
239 )))
240 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:225.25pt" width="283" %)(((
241 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
242 Join with Abbreviated Table Prefix
243 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:225.25pt" width="283" %)(((
244 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
245 Join tables where the column names match and the column has the abbreviated table name as the prefix.
246 )))
247 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:225.25pt" width="283" %)(((
248 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
249 Join with No Table Prefix
250 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:225.25pt" width="283" %)(((
251 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
252 Join tables where on the column names match.
253 )))
254 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:225.25pt" width="283" %)(((
255 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
256 Join Using Primary Keys
257 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:225.25pt" width="283" %)(((
258 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
259 Join tables based on the predefined Primary Key values for each database.
260 )))
261 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:225.25pt" width="283" %)(((
262 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
263 Ignore Date Columns
264 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:225.25pt" width="283" %)(((
265 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
266 Ignores date columns when joining tables
267 )))
268 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:225.25pt" width="283" %)(((
269 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
270 (% style="height:14.35pt" %)Ignore Columns
271 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:225.25pt" width="283" %)(((
272 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
273 (% style="height:14.35pt" %)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.
274 )))
275
276 == Build Options ==
277
278 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.
279
280 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.
281
282 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.
283
284 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.
285
286 Use the **Add to Search Engine** drop-down list to select the relevant search engine the Index will be added to upon completion.