Wiki source code of 03a. Database Data Source Wizard
Last modified by Ross Beck on 06/11/2025, 14:23
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{box cssClass="floatinginfobox" title="**Contents**"}} | ||
| 2 | {{toc/}} | ||
| 3 | {{/box}} | ||
| 4 | |||
| 5 | The Database Data Source Wizard contains the options to create Index structures from a number of database types. | ||
| 6 | |||
| 7 | To create an index from a database, click **Wizards** then **Database**. | ||
| 8 | |||
| 9 | Click the **New** button to begin the process. | ||
| 10 | |||
| 11 | = Creation Process = | ||
| 12 | |||
| 13 | == Details == | ||
| 14 | |||
| 15 | Enter the name that will be given to the Data Source, Data Source Group and Index in the **Name** text box. | ||
| 16 | |||
| 17 | Use the **Tags** text box to add associated search terms to the Index structure. This allows the components to be searched using alternative strings when using the search bar. | ||
| 18 | |||
| 19 | To automatically restrict access to the Index once built, enable the **Sensitive** option. Only users with explicit permission will be able to access the Index. | ||
| 20 | |||
| 21 | The **Index Method** drop-down list specifies the build method to be used when building the Index. | ||
| 22 | |||
| 23 | The **Complete** option will result in all items in a data source being indexed when a refresh takes place. Once refreshed, a new version number is applied to the Index. Each run completely refreshes the Index. | ||
| 24 | |||
| 25 | Building an **Incremental** Index enables an optimised refresh process when rebuilding Indexes to account for any changes. Rather than rebuilding the entire Index, only data that has been modified or added will be processed. New and changed records are added as new rows. This maintains the history of a record that may have changed over time, and no de-duplication is applied. When selected, the **Incremental Identifier** drop-down list is revealed. This is required to identify changes, using the selected field to detect when new data is greater than the previous highest value. | ||
| 26 | |||
| 27 | The **Timeline** option allows the building of ‘point in time’ indexes. Using an **Effective From** date, comparisons of data between set dates can be made. When this build option is selected, a **Primary Key** is required. Index values will be created for each Primary Key value with an **Effective To** and **Effective From** value. | ||
| 28 | |||
| 29 | The **Cumulative** option works the same as an **Incremental** build, but replaces duplicates as well as adding new data. Duplicate records are removed from the result set based on the **Increment Identifier** and **Primary Key**. When cumulatively building from CSV files, only a **Primary Key** is required. For other database types, an **Incremental Identifier** and a **Primary Key** are required. | ||
| 30 | |||
| 31 | 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. | ||
| 32 | |||
| 33 | 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. | ||
| 34 | |||
| 35 | Use the **Add to Search Engine** drop-down list to select the relevant search engine the Index will be added to upon completion and the **Add to Collection** drop-down list to select an existing Collection to which the Index will be added. | ||
| 36 | |||
| 37 | 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. | ||
| 38 | |||
| 39 | == Setup == | ||
| 40 | |||
| 41 | Select the source database from the **Database Type** drop-down list to reveal the relevant connection fields below. Previously saved [[Database Connections>>doc:Technical Documentation.CXAIR.Administration Guide.6\. Advanced Configuration.Database Connections.WebHome]] 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. | ||
| 42 | |||
| 43 | The following options are displayed depending on the choice of **Database Type**: | ||
| 44 | |||
| 45 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 46 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 47 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 48 | (% style="color:white" %)Option | ||
| 49 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 50 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 51 | (% style="color:white" %)Applicable To | ||
| 52 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:150.2pt" width="193" %)((( | ||
| 53 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 54 | (% style="color:white" %)Description | ||
| 55 | ))) | ||
| 56 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 57 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 58 | Server | ||
| 59 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 60 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 61 | 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 | ||
| 62 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 63 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 64 | The name or IP address of the database server. | ||
| 65 | ))) | ||
| 66 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 67 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 68 | System | ||
| 69 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 70 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 71 | DB2 AS/400 | ||
| 72 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 73 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 74 | (% style="color:black" %)The name of the database system. | ||
| 75 | ))) | ||
| 76 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 77 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 78 | Advanced Connection Details | ||
| 79 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 80 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 81 | Oracle 12 Advanced | ||
| 82 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 83 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 84 | See **Extra Parameters**. | ||
| 85 | ))) | ||
| 86 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 87 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 88 | Database Name | ||
| 89 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 90 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 91 | DB2, HSqlDB, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | ||
| 92 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 93 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 94 | The name of the target database. | ||
| 95 | ))) | ||
| 96 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 97 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 98 | Extra Parameters | ||
| 99 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 100 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 101 | SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | ||
| 102 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 103 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 104 | Specify the SQL Server instance or domain if connecting via Windows Authentication. Separate multiple parameters using a semicolon. | ||
| 105 | |||
| 106 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 107 | Enter the name of an instance in the following format: | ||
| 108 | |||
| 109 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 110 | **instance=<instance name>** | ||
| 111 | |||
| 112 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 113 | If using a Windows SQL Server account*, enter: | ||
| 114 | |||
| 115 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 116 | **domain=<domain name>** | ||
| 117 | |||
| 118 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 119 | Enter the following to send LMv2/NTLMv2 responses when connecting via a domain: | ||
| 120 | |||
| 121 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 122 | **useNTLMv2=true** | ||
| 123 | |||
| 124 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 125 | *Due to driver limitations, Windows Authentication is only compatible with the SQL Server 2000-2005 driver. | ||
| 126 | ))) | ||
| 127 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 128 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 129 | Context | ||
| 130 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 131 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 132 | CXAIR | ||
| 133 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 134 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 135 | See **Extra Parameters**. | ||
| 136 | ))) | ||
| 137 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 138 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 139 | Port | ||
| 140 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 141 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 142 | CXAIR, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | ||
| 143 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 144 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 145 | The port number of the database server. | ||
| 146 | ))) | ||
| 147 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 148 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 149 | Account Name | ||
| 150 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 151 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 152 | 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 | ||
| 153 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 154 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 155 | (% style="color:black" %)The account name of a database user that can execute the underlying SQL. | ||
| 156 | ))) | ||
| 157 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 158 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 159 | Account Password | ||
| 160 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 161 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 162 | 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 | ||
| 163 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 164 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 165 | (% style="color:black" %)The account password for the account detailed above. | ||
| 166 | ))) | ||
| 167 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 168 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 169 | SQL | ||
| 170 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 171 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 172 | 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, Fixed, U Can Access | ||
| 173 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 174 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 175 | (% style="color:black" %)The SQL statement that will retrieve the data from the data source. | ||
| 176 | |||
| 177 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 178 | (% style="color:black" %)With the **Fixed **option selected, the following syntax is used to select the required columns: | ||
| 179 | |||
| 180 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 181 | select | ||
| 182 | column(11), | ||
| 183 | column(21), | ||
| 184 | column(8) | ||
| 185 | from <tablename> | ||
| 186 | ))) | ||
| 187 | (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 188 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 189 | (% style="height:14.35pt" %)Delimiter | ||
| 190 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 191 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 192 | (% style="height:14.35pt" %)CSV | ||
| 193 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 194 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 195 | (% style="height:14.35pt" %)The character that splits the field values. By default, this is a comma (**,**). | ||
| 196 | ))) | ||
| 197 | (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 198 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 199 | (% style="height:14.35pt" %)Text Qualifier | ||
| 200 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 201 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 202 | (% style="height:14.35pt" %)CSV | ||
| 203 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 204 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 205 | (% style="height:14.35pt" %)The character that qualifies text within a value. By default, this is quotation marks (**“** **“**). | ||
| 206 | ))) | ||
| 207 | (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 208 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 209 | (% style="height:14.35pt" %)First Row is Header | ||
| 210 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 211 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 212 | (% style="height:14.35pt" %)CSV, Excel, Fixed | ||
| 213 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 214 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 215 | (% style="height:14.35pt" %)Sets the contents of the first row to be used as the column headings in the resulting output. | ||
| 216 | ))) | ||
| 217 | (% style="height:16.6pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 218 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 219 | (% style="height:16.6pt" %)Sheet | ||
| 220 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 221 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 222 | (% style="height:16.6pt" %)Excel | ||
| 223 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 224 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 225 | (% style="height:16.6pt" %)Specify the required Excel sheet. | ||
| 226 | ))) | ||
| 227 | (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 228 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 229 | (% style="height:14.35pt" %)Location | ||
| 230 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 231 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 232 | (% style="height:14.35pt" %)CSV, Excel, U Can Access | ||
| 233 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 234 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 235 | (% style="height:14.35pt" %)Click the **…** icon to open the file browser, where the relevant file can be selected. | ||
| 236 | ))) | ||
| 237 | (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 238 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 239 | (% style="height:14.35pt" %)First Row is Header | ||
| 240 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 241 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 242 | (% style="height:14.35pt" %)CSV, Excel | ||
| 243 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 244 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 245 | (% style="height:14.35pt" %)Enable this option if the first row of data in the spreadsheet contains column headings. | ||
| 246 | ))) | ||
| 247 | (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)((( | ||
| 248 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 249 | (% style="height:14.35pt" %)Character Set | ||
| 250 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)((( | ||
| 251 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 252 | (% style="height:14.35pt" %)CSV | ||
| 253 | )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)((( | ||
| 254 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 255 | (% 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. | ||
| 256 | ))) | ||
| 257 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)Project|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)BigQuery|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)Name of the BigQuery project | ||
| 258 | |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)Credential File|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)BigQuery|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)Path to your Credentials File | ||
| 259 | |||
| 260 | == Column Mapping == | ||
| 261 | |||
| 262 | Clicking the **Column Mapping** tab will return the metadata from the data source and allows the configuration of how the database fields will be formatted. A loading icon denotes that the data is being processed. | ||
| 263 | |||
| 264 | If **First Row is Header** option from the **Setup** tab is enabled, the column names will be displayed under the **Column** heading. If not enabled when using a CSV as a source file, columns are numbered. | ||
| 265 | |||
| 266 | === Data Type Conversion === | ||
| 267 | |||
| 268 | While the type of data that exists in each field will be automatically recognised, the **Type** drop-down list allows the data format to be changed as it is Indexed. | ||
| 269 | |||
| 270 | CXAIR groups data formats into four distinct types: **Timestamp**, **Date**, **Number** and **Text**. To convert the data format, select the required type from below the **Data Type Conversion** heading. If converting a field to a numeric or date value, use the subsequently revealed **format** text box to specify the format of the incoming values. Multiple date formats can be assigned to a column by pipe (**|**) separating entries. For example, **dd/MM/yyyy | MM/dd/yyyy**. | ||
| 271 | |||
| 272 | ==== {{id name="Obfuscation"/}}Obfuscation ==== | ||
| 273 | |||
| 274 | Using the **Obfuscation** options, selected fields can be obscured to preventing individual records from being identifiable. The following options are available: | ||
| 275 | |||
| 276 | (% border="1" cellspacing="0" class="TableGrid1" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 277 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:224px" width="256" %)((( | ||
| 278 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 279 | (% style="color:white" %)Option | ||
| 280 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:799px" width="309" %)((( | ||
| 281 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 282 | (% style="color:white" %)Description | ||
| 283 | ))) | ||
| 284 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:224px" width="256" %)((( | ||
| 285 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 286 | Anonymise | ||
| 287 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:799px" width="309" %)((( | ||
| 288 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 289 | The field contents are replaced with the field name and a sequential integer value, completely removing the field contents. | ||
| 290 | ))) | ||
| 291 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:224px" width="256" %)((( | ||
| 292 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 293 | Encrypted | ||
| 294 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:799px" width="309" %)((( | ||
| 295 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 296 | Encrypts the field with the [[Encryption Key>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome||anchor="Encryption Key"]] set in the [[System>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome||anchor="System"]] section of the [[System Settings>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome]]. | ||
| 297 | ))) | ||
| 298 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:224px" width="256" %)((( | ||
| 299 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 300 | MD5 / Dual MD5 | ||
| 301 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:799px" width="309" %)((( | ||
| 302 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 303 | Produces a 128-bit hexadecimal hash value in place of the field contents. | ||
| 304 | //Selecting Dual MD5 creates the hashed value alongside the original value, with administrators able to select the field that is made available to users.// | ||
| 305 | ))) | ||
| 306 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:224px" width="256" %)((( | ||
| 307 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 308 | SHA-2 / Dual SHA-2 | ||
| 309 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:799px" width="309" %)((( | ||
| 310 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 311 | Produces a hash value using the Secure Hashing Algorithm 2, with a 256 bit size. Structurally, SHA-2 uses the Davies–Meyer structure with a block cipher. | ||
| 312 | //Selecting Dual SHA-2 creates the hashed value alongside the original value, with administrators able to select the field that is made available to users.// | ||
| 313 | ))) | ||
| 314 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:224px" width="256" %)((( | ||
| 315 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 316 | SHA-3 / Dual SHA-3 | ||
| 317 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:799px" width="309" %)((( | ||
| 318 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 319 | Produces a hash value using the Secure Hashing Algorithm 3, with a 256 bit size. Structurally, SHA-3 uses a sponge structure with the Keccak permutation. | ||
| 320 | //Selecting Dual SHA-3 creates the hashed value alongside the original value, with administrators able to select the field that is made available to users.// | ||
| 321 | ))) | ||
| 322 | |||
| 323 | For URL values, selecting **URL** will add a navigation button into the [[Query>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2a\. Query.WebHome]] screen that end-users can use to follow the link in a new browser tab. | ||
| 324 | |||
| 325 | Use the **Null Values** text box enables the entry of any values that will be converted to NULL within the Index for each column. | ||
| 326 | |||
| 327 | == Index Mappings == | ||
| 328 | |||
| 329 | To further configure the resulting fields, there are three configurable lists that will impact their visibility: **Store**, **Index** and **Free Text**. When new columns are added to the data source and the Index is refreshed, the new columns are not automatically added to these lists. Enable the **Automatically add new columns** option to ensure all new columns are added automatically. | ||
| 330 | |||
| 331 | For a field to be displayed and fully searchable, it must be added to all three lists. | ||
| 332 | |||
| 333 | Add fields to these lists by using the left and right arrows to move the fields from the **Available** to the **Selected** boxes. | ||
| 334 | |||
| 335 | The values specified under the **Store** heading will be physically stored within the Index. By default, all values are selected based on the SQL statement, if provided. This provides the option of removing any unwanted fields when selecting all values from a large database table. | ||
| 336 | |||
| 337 | The values specified under the **Index** heading are the fields that will be accessed when a data query is performed. By default, fields with a large number of unique values will be excluded from this list. The limit is controlled by the [[Maximum Drop List Items>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome||anchor="Maximum Drop List Items"]] option in the [[System Settings>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome]]. | ||
| 338 | |||
| 339 | The Values specified under the **Free Text** heading are the fields that will be accessed when a free text query is performed. Numeric and date fields are automatically excluded. By default, fields with a large number of unique values will be excluded from this list. The limit is controlled by the [[Maximum Drop List Items>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome||anchor="Maximum Drop List Items"]] option in the [[System Settings>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome]]. | ||
| 340 | |||
| 341 | If a field is contained in the **Store** list but not within the **Index** list, the field will be displayed in the table and list view. When running a search, however, the field will be omitted if it is being used in a filter. | ||
| 342 | |||
| 343 | If a field has been added to the **Store** and **Index** lists, but not the **Free Text** list, any filters used to search data will take the field into account, but any free text searches using the query bar will ignore it. | ||
| 344 | |||
| 345 | The {{id name="Analysed"/}}**Analysed** list has been designed to accommodate fields containing multiple words, such as a ‘Comments’ field, to allow for case insensitive searches in the [[Query>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2a\. Query.WebHome]] screen. When added to the list, each word in the field is stored as an individual entity to facilitate field-specific searches, in contrast to regular fields that are stored as a single string value. This allows individual words to be more accurately returned from a field query and is especially useful when creating [[Word Clouds>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2a\. Query.WebHome||anchor="Word Cloud"]] that require the **Stop Words** functionality to display an accurate view of the frequency. Please note that any fields added to this list cannot be used as a row or column when creating a [[Crosstab>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2c\. Crosstabs.WebHome]]. | ||
| 346 | |||
| 347 | If creating a timeline Index, specify the fields that will uniquely identify the required change in the data by moving them into the **Selected** box for the **Timeline Columns** option. | ||
| 348 | |||
| 349 | == URL Template == | ||
| 350 | |||
| 351 | The URL Template options allow a website to be linked to search results. The entered address is constructed using the entered URL along with fields specified as the Primary Key (needed for **Timeline** and **Cumulative** build methods). | ||
| 352 | |||
| 353 | Select the columns that will be used as the Primary Key by using the left and right arrows to move the fields from the **Available** to the **Selected** boxes. | ||
| 354 | |||
| 355 | Enter the required address in the **URL** text box in the following format: | ||
| 356 | |||
| 357 | **http:~/~/<URL>/%<column name>%** | ||
| 358 | |||
| 359 | **<URL>** is the website address and **<column name>** is the name of a Primary Key column. | ||
| 360 | |||
| 361 | == Advanced == | ||
| 362 | |||
| 363 | Enter the name of a stored procedure to execute against the data source before the Index starts building in the **Pre Procedure** text box, in the **Post Procedure** text box to execute it after the Index has finished building or in the **Error Procedure** text box to execute it if an error occurs. Check the **Commit** checkbox to commit the output of the stored procedure. | ||
| 364 | |||
| 365 | Enable the **Initial Capital** option to capitalise the first letter of each column heading. | ||
| 366 | |||
| 367 | To ensure unexpected changes to the source data do not impact the resulting Index, the **Error on Column Changes** and **Error on Column Format Changes** options can be enabled to force the Index build to fail if changes are detected. | ||
| 368 | |||
| 369 | Specify the **Time Zone** if the source data is derived from a different time zone to the server. This option can affect derived date/time number fields. | ||
| 370 | |||
| 371 | Specify the **Time Out** option, in minutes, with the amount of time a row retrieval process will run for before being terminated. To disable this option, enter zero. | ||
| 372 | |||
| 373 | Specify the minimum number of rows that an Index can receive before completing in the **Minimum Rows** text box. To disable this option, enter zero. | ||
| 374 | |||
| 375 | Specify the maximum number of rows that an Index can receive before completing in the **Maximum Rows** text box. To disable this option, enter zero. | ||
| 376 | |||
| 377 | Specify the maximum time, in seconds, that an Index process can run for before being terminated in the **Maximum Time** text box. To disable this option, enter zero. This only applies to incremental indexes. | ||
| 378 | |||
| 379 | == {{id name="Derived Dates"/}}Derived Dates == | ||
| 380 | |||
| 381 | Using the date options allows the individual components of a date/time field to be separately indexed. | ||
| 382 | |||
| 383 | Use the left and right arrows to move the required fields from the **Available** to the **Selected** boxes for the following options: **Day**, **Week**, **Month**, **Quarter**, **Year**, **Fiscal**, **Hour**, **Minute**, **Second**, **Time**. | ||
| 384 | |||
| 385 | The output from these fields can also be output in a number of different formats, as described in the [[System Settings>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome||anchor="Derive Options"]] chapter. Enabling the **Fiscal** checkbox will overwrite any of these selections, however. | ||
| 386 | |||
| 387 | == Index Method Details == | ||
| 388 | |||
| 389 | These options will change dependant upon the **Build** **Method** set in the **Details** tab. | ||
| 390 | |||
| 391 | === Incremental Identifier === | ||
| 392 | |||
| 393 | This will appear when using an incremental or cumulative build method. You will need to specify the field that will identify if a record is new or updated. This must be a sequential value such as a date or numeric value that will increase in value over time. CXAIR will only bring in records where the incremental identifier is greater than the previously store values. | ||
| 394 | |||
| 395 | === Effective Date === | ||
| 396 | |||
| 397 | This will appear when using timeline indexes. It does not have to be set. By default the effective date for each current effective record is 01/01/9999. This can be overridden by using a date field in the Index. | ||
| 398 | |||
| 399 | === Partition Key === | ||
| 400 | |||
| 401 | Using a **Partition** **Key** will split the Index into partitions based on the unique values found in that field. Once the Index has built for the first time, you can specify which partitions are available to the end user by choosing the the relevant value in the Offline Scheme drop list in the **Data Source Group**. Any subsequent builds will store the full set of data but only published the specified partition for front-end reporting. | ||
| 402 | |||
| 403 | === Partition Key Format === | ||
| 404 | |||
| 405 | This will only appear once a **Partition** **Key** has been selected. Simply specify the format based on the data type coming into the system i.e. the expected format of the source data. |