Introduction

The normal workflow to define an extract/load job in Ajilius is to browse the source system for the required table or view, then load the metadata for that table.

Complex load requirements can occasionally be represented as views in the source system, but often it is not possible to create a custom view in a source system. It might be a hosted application where you don’t have the ability to create new objects, or you might simply lack the authorisation to create a view.

In this circumstance, you might like to use a Custom Query Load.

This load takes its metadata from a custom query that you define. At execution time, the custom query is executed for the extract portion of the job.

In this tutorial, we will walk through the creation of a Custom Query Load.

Create metadata

Using the CHINOOK sample data warehouse, select the Custom Query option from the Load menu:

cql01

Select the data source against which the query will be executed, and set the name and description of the data warehouse table you are creating:

cql02

Now enter your SQL query, and ADD the table definition to your metadata:

cql03

The metadata for your load_country table has now been loaded:

cql04

Load table

To load the data for this table, select the SCRIPTS option from the drop-down menu for the load_country table:

cql10

Scroll down the script page, and note that the extract query is using the custom SQL statement that you entered earlier:

cql05

Now, use the action buttons to create and load the table, then verify that the table contents have been loaded correctly:

cql06

Change query

If you change your query, the metadata you loaded previously will be reset to reflect the new query.

Change the query. Here we reverse the column order:

cql07

After you change the metadata, any tables that were sourced from this table need to be re-mapped to the new column definitions. Assume that a table stage_country had previously been created, but now its columns do not have a source:

cql08

Select the MAP option from the list title bar, and re-map the columns to their source:

cql09

Summary

Custom Query Loads  may be used to load metadata and data using complex source system logic. If you can’t create a view on your source system, a Custom Query Load may save a lot of data movement and transformation within Ajilius.

The steps to create a Custom Query Load are:

  • Select Custom Query from the Load menu
  • Enter a query
  • Run scripts as normal

If you change a custom query, you will have to re-map any tables that depend on the changed table.

If you have any questions, please contact us for support.