Data Loader Details
Jailer: Enables the Academy Data Jailer.
Fast Load: Enables the SSIS "Fast Load" option for all tables in this Data Loader. Note that this by-passes data type validation in the Jailer. (Business rule validation is not affected.)
Serial Load: Causes tables to be loaded sequentially. (By default the import tables are loaded in parallel, at the discretion of SSIS.)
Last Updated Date Format Expression: Defines the format of the "Last Updated Date" as passed to the source system for incremental ODBC loaders. As an alternative to the default, the following SSIS Expression, ensures that a date is formatted strictly to YYYY-MM-DD:
(DT_STR, 4, 1252)DATEPART("YYYY", @LastRunDate) + "-" +
replicate("0",2-len((DT_STR, 2, 1252)DATEPART("MM", @LastRunDate))) +
(DT_STR, 2, 1252)DATEPART("MM", @LastRunDate) + "-" +
replicate("0",2-len((DT_STR, 2, 1252)DATEPART("DD", @LastRunDate))) +
(DT_STR, 2, 1252)DATEPART("DD", @LastRunDate)
(The replicate function is used to ensure leading Zeros on the day and month parts.)
Target Table Prefix: A naming prefix applied to all tables in the warehouse. Use this to distinguish between data from different sources, so HR_ for data from your Human Resources system, and PR_ for data from your production system.
Data Loader Table Details
Source Date Modified Field: See Incremental Data Loaders.
Select Distinct: De-duplicates the source data by applying a "SELECT DISTINCT... " SQL query to the source table.
Staging Table: The name applied to the "temp" table in the "staging" database.
Process Deletes: See Incremental Data Loaders.
Source Table Owner: The owner/ domain in which the source table resides.
Source WHERE Clause: Filters the source data by applying the specified SQL WHERE clause.
Truncate Target: For Full Data Loads, calls "TRUNCATE" rather than "DELETE" when clearing-down the warehouse prior to a reload. This will be faster, but in the event of a failure the system will not be able to recover.
Target Table: The name of the table in the warehouse into which the data is finally loaded. This name is subject to the Target Table Prefix.
Fast Load: Enables Fast Load for this table only.
Data Loader Field Details
Source Function: A SQL function applied when the data is pulled from the source. NB that this a function native to your source database type - so if your source database is Oracle, then this would be a PL/SQL function.
Target Only Field: Used to define fields which do not appear in the source. These fields can be added using the "Add" button beneath the field list on the Data Loader Table Details screen.
Staging Field Type / Length: The SQL data type used for the "temp" table in the staging area. When specifying the length for varchar (and similar) fields, use -1 to identify varchar(MAX).
Is Primary Key / Sort Order / Sort Direction: Used to identify the Primary Key for the purposes of the Data Load. Note that this does not have to be a Primary Key in the source system, so long as it can be used as such for the purposes of identifying each record uniquely in an Incremental Load.
Staging Field: The name of the field in the staging table.
Target Field: The name of the field in the warehouse table.
Target Field Type / Length: The SQL data type used in the warehouse table. When specifying the length for varchar (and similar) fields, use -1 to identify varchar(MAX).
Load Function: A SQL function applied to the data as it is transfered between the staging and warehouse tables. This can be used in conjunction with a Target Only Field to create a calculated value; or in conjunction with the Field Types to convert between a text-formatted date and a SQL date type.
Specifying A Source SELECT in Data Academy 5.6+
As of Data Academy 5.6, you can specify a full SELECT statement to retrieve the source data, allowing you to utilize JOINs or any other syntax offered by your source system.
In the Table Details screen, specify your SELECT statement in the Source Where property.
Basically, if the Source Where property starts with "SELECT " then Data Academy will use this statement to construct the data loader, effectively rendering the Source Table a design-time-only property in this scenario. This works for both incremental and full loads, but for incremental loads, the Source Table property will still be used to retrieve the keys. There Source Where SELECT statement may (but does not have to) include a WHERE clause.
For an Incremental load, if the SELECT statement does not end with the WHERE clauses (i.e. there is a HAVING clause, or similar) then you will need to include a ##MODIFIED## placeholder in the WHERE clause, where the date modified filter will be applied.