Data Model Source

Centerprise Data Model Source provides the ability to extract data from multiple tables. Data Model Source presents your database as a tree that looks and works similar to other layouts. The primary source table you select becomes the starting node in the table and all the related tables appear as child nodes. With this approach, all you have to do is map data from the tree. Centerprise features a sophisticated query engine that generates queries based on the fields you map and the criteria you specify. No matter how complex your extraction is, Centerprise generates the correct query to extract data for you.

Data Model Source is driven by a logical data model that you can create using Centerprise Data Model document. This data model specifies relationships between the tables and the keys for these relationships. Using this relationship information, Centerprise generates source layout tree that you can use to map data. At runtime, the same relationship information is used by Centerprise Query Engine to generate SQL queries for data extraction.

Filter Criteria

Data Model Source enables you to define filter for data extraction. As with result columns, filter column can be selected from any table that is related–directly and indirectly–with the primary source table.

Tree Structures

If you are building a complex XML document or EDI transaction using data from database, you can use data model structure to eliminate the manual query building.

Steps

Adding a data model source object allows you to transfer data from one or more related tables that make up your data model. An example of what a data model source object looks like is shown below.

To configure the properties of a Data Model Source object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

Source Connection screen – allows you to enter the connection information for your source, such as server name, database and schema, as well as credentials for connecting to the selected source.

General Properties screen:

You can load an existing data model, or create a data model on the fly by reverse engineering an existing database.

• To load an existing data model, select Data Model radio button and enter the file path to the saved data model.
• To create a data model by reverse engineering an existing database, select Database Schema and specify database connection in the Connection dialog.

Next, select a table that will serve as the entry point, or root for your data model. Centerprise will automatically add related tables to the data model tree.

Query Source Layout screen – shows the tree layout of the selected root and related tables.

Using the Filter tab, you can also specify an optional filter expression controlling which records will be excluded.

In the WHERE clause tab, you can enter an optional SQL expression serving as a filter for incoming records. The expression should start with the WHERE word followed by the filter you wish to apply.

For example, WHERE FICOScore >= 740

Finally, in the Sort Order tab, you can select one or several fields to sort incoming records by, as well as sorting directions, such as Ascending or Descending.

General Options screen:

The Comments input allows you to enter comments associated with this object.