Follow

Data Sources

Data Sources

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

  • 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.
  • Custom Data Source:TimeXtender's own providers for niche systems.
  • 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/

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 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. Per default, the ODX server synchronizes objects as the first step of every task, but you can also do it manually.

To synchronize objects on a data source

  • Right click the data source and click Synchronize Objects. The status of the synchronization will displayed in parenthesis after the data source name.

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 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 a new provider, follow the steps below.

  1. Open Manage Data Source Providers
  2. Click Add...
  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 versions of the same provider installed, but you can only install the newest version of a provider through the tool. To install the new version while keeping the old, do not update the old version. Instead, just add the new version of the provider.

To delete a provider

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

Previewing Data Sources

To select the correct data, it is important to know what data is in the data source in the first place. To help you with that, the Query Tool is available on data sources.

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.
Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.