Note: There is an updated version of this article, which applies to the next generation of TimeXtender.

Data Sources

The ODX Server can connect to a data source through one of five different types of providers:

• CDS: TimeXtender's own providers with tweaks and improvements that usually makes this the best choice if one is available for your data source.
• CData: Providers from CData, a third-party supplier of drivers and providers for connecting to a massive amount of different systems. For help with setting up CData providers, please refer to CData online documentation for ADO.net providers at https://www.cdata.com/kb/help/
• ADF:Providers from TimeXtender that use Azure Data Factory for transferring data.
• ADO: Providers installed on the machine that are built on ADO.NET.
• OLE DB: Providers installed on the machine that supports the OLE DB standard.

Adding and Synchronizing A Data Source

To add a new data source, follow the steps below.

1. Right click Data Sources and click Add Data Source. The Add Data Source wizard appears.
2. On the Name and description page, enter the following information and then click Next.
   1. In the Name box, type the name you want to use for the source.
   2. (Optional) The Short Name box contains a suggested value based on the name. You can customize this if you like. The short name is used by the ODX Server to identify the source and can be up to 10 characters long.
   3. (Optional) In the Description box, type a description of the data source.
3. On the Select Provider page, select the provider you want to use and click Next. If the provider you selected is not already installed on the ODX server, it will be downloaded from the online repository.
4. On the Connection Info page, enter the information needed to connect to the data source and click Next.
   
   The content of the page depends on the provider you have chosen. The ODX server reads the list of options available from the provider and lists them in a property grid. In addition to that, different options for how the ODX server should read the source may also be available.
   
   For the most popular providers, a template is available that exposes the information that will usually be required for a successful connection. When this is the case, click More Options to show all available options in the property grid.
   
   The following are some of the options that may be available:
   • Character Replacements: Here, you can type a Character value to replace with the Replace with value in table and column names from the data source.
   • Command Timeout: The command timeout in seconds.
   • Concurrent execution threads: The maximum number of connections from the ODX server to the data source on execution.
   • Force codepage conversion
   • Query Formatting: Prefix, Suffix and Separator used in the source. Click Read Value to fill in the values automatically if possible.
   • Schema Properties: Type the Schema Name, Table Name, Column Name etc.
5. On the Data page, choose what tables will be available on the data source and then click Next.
   1. All tables in the data source: All tables are available. Choose this setting if all tables on the data source makes sense to have in the data storage.
   2. Let me select the tables: You select the tables that will be available. This is useful if there are temporary or system tables on the data source that it does not make sense to store on the data storage.
6. A data selection page is displayed if you chose Let me select the tables. For more information, please see Data Selection and Filtering.
7. On the Next Step page, click No, I'll do that later if you do not want to add a transfer task after adding the data source and then click Finish.

Editing a Data Source's Connection Settings

To edit the connection settings of a data source, follow the steps below.

1. Right click the data source and click Edit Data Source. The Edit Data Source window appears.
   
2. In the Name and Description boxes, you can update the name and description of the data source.
3. Click on a new provider in the Provider list to change the provider used by the data source. The providers available depend on the type of provider you initially chose for the data source. You cannot change between, for instance, ADO.net and OLE DB providers.
4. In the property grid, you can change any settings you want to update.
5. Click Advanced settings to access additional settings. The Advanced Data Source Properties window opens.
   • In the Query Formatting list, type the Prefix, Suffix and Separator used in the source. Click Read Value to fill in the values automatically if possible.
   • In the Character Replacements list, you can type a Character value to replace with the Replace with value in table and column names from the data source.
   • In the Schema Properties list, type the Schema Name, Table Name, Column Name etc.

Synchronizing Objects on a Data Source

Synchronizing objects on a data source loads the meta data from the source into the cloud repository. No data can be copied from a source where objects have not been synchronized since the ODX server would not know what tables are available on the data source.

When you add a data source, a Synchronize task is added automatically. You can schedule it to synchronize the data source on regular intervals or execute the task manually to synchronize the data source immediately.

See Tasks for more information.

Setting Up Incremental Load

The ODX Server can load data incrementally to get the latest data quickly and with minimal load on the data source.

Since the ODX is built for handling sources with a lot of tables, you do not pick individual tables to load incrementally. Instead, you define the rules for when incremental load should or should not be applied. Each rule consists of a set of conditions and an action. When a condition matches a field, the action is applied to the table. For each field, the first set of conditions that matches is applied.

To add incremental load to a data source, follow the steps below.

1. Right click the data source and click Set Up Incremental Load.
2. Click Add... to add an incremental load setup rule.
3. Under Conditions, define the conditions a field needs to match to have an incremental load rule applied.
4. Under Actions, click on the action you want to apply to the fields that match the conditions. You have the following options:
   • Load records where value is greater than the last maximum value: Applies incremental load. Select Handle primary key updates to update existing records in the storage that have changed and select Handle primary key deletes to remove records deleted in the source from the storage.
5. Click OK to add the rule.
6. When you have set up all the rules you want to, click OK. To handle updates and deletes, the ODX Server needs to know the primary key of the tables that this is enabled on. If you are missing required primary keys, the Incremental Load Primary Key Validation window will open to let you select which fields to include in the primary key on the tables in question.

Setting Up Primary Keys

To handle updated and deleted records in the source on incremental load, the ODX server needs to know the primary key on each table. There is a rule-based interface available for defining primary keys.

To set up primary keys for a data source, follow the steps below.

1. Right click the data source and click Primary Keys....
2. Click Add... to add an incremental load setup rule.
3. Under Conditions, define the conditions a field needs to match to be included in or excluded from the primary key.
4. Under Actions, click on the action you want to apply to the fields that match the conditions. You have the following options:
   • Include in primary key
   • Exclude from primary key
5. Click OK to add the rule.

Overriding data types

To make fields from the data source "fit" the data type on an SQL Server storage, you can add data type overrides.

To add a data type override rule to a data source

• Right click the data source and click Override Data Types.

For more information, see Adding a Data Type Override Rule.

Managing Query Tables

With the query tables feature, you can enter a SQL query that the ODX will turn into a table that works just like any other table in the ODX. This is useful, for instance, when a table cannot be extracted from the data source by the regular ODX logic.

To add a query table, follow the steps below.

1. Right click the data source containing the data you want to extract and click Manage Query Tables.
   
2. Click Add to add a new query table to the list.
3. (Optional) In the Schema box, type the Schema name you want the table to use.
4. In the Name box, type a name for the table.
5.  In some cases, clearing Use subquery can lead to better performance. This includes query tables that will not be incrementally loaded or where the query does not contain an alias or a WHERE clause.
6. In Query, enter the query you want to use for creating the table. The query should contain a SELECT statement and follow the syntax required by the source. You can drag a table in from the list in the right-hand side of the window to add a SELECT statement that includes all fields in the table to use as a starting point.
7. (Optional) Click Validate Query to check if your query can be used for a query table.
8. If you need to add more query tables, repeat step 2-6.
9. Click OK to save the query tables.
10. Synchronize the data source to make the query table(s) available for transfer to the data warehouse.

Note: Row filter rules are not applied on query tables.

Browsing and Previewing Data Sources

To select the correct data to move into the data storage and on to the data warehouse, it is important to know what data is in the data source in the first place. In addition to that, it is useful to see how the ODX "sees" the data source. For these kinds of uses, the Data Source Explorer and the Query Tool is available.

The Data Source Explorer works on the meta data stored in the ODX repository. It allows you to browse the tables and columns in the data source to give you an overview of the structure of the data source. In addition to that, you can see how the different rules, you have set up in the ODX, affect the source.

To open the Data Source Explorer

• Right click the data source and click Data Source Explorer

You can search for specific tables and and filter the search results to see only tables that e.g. are being transferred to data storage or support incremental load. On the field list, two names are listed for each field. The Field source name is the name of the field in the source, while the Field destination name is the name used in the ODX data storage and onwards to the data warehouse. Check marks indicate fields that are "hit" by a primary key, incremental load or data selection rule. The data type of the field is also displayed, along with the new data type if a data type override rule is applied to the field.

To open the Query Tool from the Data Source Explorer with data from the selected table listed

• Select a table and click Preview

The Query Tool works directly on the source and allows you to preview the data stored in the data source using various SQL queries.

To preview a table in the Query Tool, follow the steps below.

1. Right click the data source and click Query Tool.
   
2. Drag a table from the list in the left-hand side to the Query box to generate a SELECT statement that selects all rows from the table. You can edit this statement, for instance adding a WHERE clause.
3. (Optional) In Max. no of rows, enter the maximum number of rows you want the query to return.
4. When you are ready to run the query, click Execute. In the Results list, the returned rows will be listed.

The query tool has a few other features:

• As it is standard in other tools, the Query Tool will execute any selected text in the Query box instead of everything when you click Execute.
• If the Query box is not empty when you drag in a table, a complete SELECT statement will not be generated. Instead, the schema and table name, e.g. [dbo].[Customers] will be inserted.

Managing Data Source Providers

Custom data sources and CData providers can be downloaded and installed from an online repository when you add a new data source and with the Manage Data Source Providers tool. You can also use the tool to update a provider to the newest version or delete it from the ODX server.

To open Manage Data Source Providers

• Right click Data Sources and click Manage Data Source Providers.

To add one or more providers, follow the steps below.

1. Open Manage Data Source Providers
2. Click Add... to install the latest versions of the providers OR click Add Specific Versions... from the Add list if you want to add a version of a provider other than the latest
3. Select the providers you want to install and click OK. The ODX Server will download and install the required files.
4. Click OK to close the tool.

To update a provider

• Open Manage Data Source Providers , select a provider with Update Available in the Status column and click Update.

Note: You can have multiple major versions of the same provider installed, but only one minor version within each major version.

To delete a provider

• Open the tool, select a provider and click Delete.