Most of the time you load data directly from a source system table to a data warehouse table. That is the foundation of Extract-Load-Transform.

There are circumstances, however, when simple extracts and loads can be limiting, and you want more control over the way in which data is extracted. In these cases, a Custom Query Load will provide full control.

To start, select the Custom Query option from the Load menu:

Selec

The Load Query screen is where you define the query. Select the data source against which the query will be executed, and give the new table a name and description. Then enter a query that will be executed against the data source.

The query must be valid syntax for the source system database.

On saving your query, Ajilius will test the query against the source, and return column definitions matching its results. The column names will be transformed by standard Ajilius naming rules.

Note: Calculated columns and literals in the source query must be aliased with column names.

 

You may now treat the table as any other table in Ajilius, and continue to edit its metadata and transform it as you wish.