ETL Workflows

From DataSelf Knowledge Base
Jump to: navigation, search

{{#breadcrumb:DataSelf ETL}}

<Page under construction>

Import a Table - Basic (Workflow)

Import a new table - simple import. Import data from a single source table and schedule it to be periodically refreshed (usually overnight). The source table could be from any relational database accessible via ODBC or directly from built-in ETL provider for Microsoft Excel, Microsoft Access and MS SQL Server.

Requirements:

Procedure:
  1. Open the ETL
  2. Make an ETL Backup
  3. Open Import Table Wizard
  4. Browse Table window - Modify Structure: Change data types as required.
  5. Browse Table window - Refresh
  6. Browse Table - Browse Data window: Verify if the imported data looks correct.
  7. Refresh Wizard: Add the table to a scheduled batch.
    (Once created, the batches are run manually or by the job scheduler in SQL Server's SQL Server Agent.)

Import a Table with a Filter (Workflow)

Import a new table with a filter.

  1. Execute the Import a Table - Basic workflow
  2. Filter Wizard: Defin e a filter for the imported table, name the filter, and save it.
  3. Browse Table window > Modify Import/Mapping: Select the filter to use with the table. (Also know as "adding the filter" or "using/activating the filter")
  4. Browse Table window - Refresh (CTRL-R).

Report a Imported Table's Metadata

MetaData Reports

Import Linked Tables

Import from more than one source table. For example, import sales order detail records from a sales order detail table along with the associated record from the sales order master table.

  1. Determine the tables to link and the fields to create the links on.


Workflow: Define a Formula for a Table

See also: Formula Wizard - Getting Started Videos

Define a imported table with ?relationships, Filters, and Sums as required.


-Formula Wizard: Create the formula

-check the source field types and sizes against the (upper right) formula field size/type as you create the formula

-Save the formula


-Finish or close the formula wizard

-Refresh formula now or later


-Test the results: Browse Table or view data in SQL

- Troubleshooting Formulas

Define Several Formulas for a Table

-Create additional formulas on the same table – the new formulas are listed at the end of the table’s field list

--change field type/size as needed

-Back to select a different table to create formulas on

Misc

Checklists

Importing from more than one table with Filters, Sums, and Formulas

-Be sure the needed ?relationships exist MetaData Reports

-Be sure any needed Filters exists (link to browse… as above)

- Be sure any needed Sums exist (link to browse… as above)

-For formulas that pull from a ?parent or ?child table, check the Refresh Wizard: Refresh schedule to be sure the associated tables refresh at the same or earlier step as the table that will have the new formula.




to be defined

=

Formulas discussion that has decision tree for when to refresh a formula when creating new formulas).


ETL Solutions to Frequent Questions

Multi-company Consolidation

Connecting to/Importing from Excel files