Enhancement: User / Tech Documentation

This week’s download for Release 2.2.9 brings separation of User and Technical documentation.

Previously, Sources, Tables and Columns had one documentation panel. For tables and columns, that documentation was carried forward to subsequent tables in the ELT process. While editable, it gave rise to misleading documentation if a technical note from a previous table was overlooked.

We’ve now provided separate tabs for User and Tech(nical) documentation.


User documentation is carried from table-table, and column-column. Technical documentation is specific to the source, table or column for which it is defined.

Also, we’ve provided a feature to tune the size of the documentation panel. User Preferences now contains a field for Documentation Lines. Modify this value to increase or decrease the number of lines displayed by the documentation panel, tuning the fit for screen size and zoom level.


Keep the requests coming, your ideas make Ajilius better for every user.

Ajilius. Listening to our users.

Enhancement: Load Metadata Columns

Release 2.2.8, out today, includes the ability to add metadata columns to load tables.

The columns which may be added are:

  • DateTime
  • GUID
  • Hash
  • Server
  • Database
  • Table

The availability of these columns is dependent on the capabilities of the source DBMS. Where a given column is not supported by the data source, an empty string will be supplied in its place.

Metadata columns may be added from the Column List screen for a given table. When adding a column, all you need to do is select the appropriate Column Role. Column names, data types, etc. will be added automatically.

Ajilius. Enriching source data.

Query Based Load Dependencies

The new Ajilius scheduler uses metadata to figure out the dependencies and run sequence of your ELT jobs.

These dependencies can currently break through custom code.

Here’s an example, contrived from a customer query earlier today:




In this query there is a dependency between the tables load.load_from_table and load.load_earlier_table. Unless load.load_earlier_table has been populated before processing load.load_from_table, then duplicate rows may be selected.

In the long term we will use a SQL parser to extract these dependencies from custom queries, but we’re still working on the evaluation of parsers.

Meanwhile, these dependencies can be resolved through multiple steps in the scheduler. Here is an example of scheduler parameters that would ensure the correct sequence of loads in a full data warehouse refresh:

-w <dwname> -b reset
-w <dwname> -l load_earlier_table
-w <dwname> -f all

This could also be written as:

-w <dwname> -b reset -l load_earlier_table -f all

These commands will ensure that load_earlier_table is the first table processed by the scheduler, and it will be available when needed by load_from_table.

Remember, any time you’re not sure how to do something using Ajilius, you are most welcome to contact us and discuss.

Enhancement: Table and Column Comments

Ajilius now generates database comments for tables and columns, improving the self-documentation of your data warehouse.

We use the description field from table and column metadata to add the comments to the create script for each table.

Example: PostgreSQL

// Add table and column comments.

section = 'Comments'

dw.execute ("""comment on table load.load_album is 'Album';""");

dw.execute ("""comment on column load.load_album.album_id is 'Album ID';""");
dw.execute ("""comment on column load.load_album.title is 'Album Title';""");
dw.execute ("""comment on column load.load_album.artist_id is 'Artist ID';""");

Example: SQL Server

// Add table and column comments.

section = 'Comments'

dw.call ("""
exec sys.sp_addextendedproperty 
@name       = N'MS_DescriptionExample', 
@value      = N'Album', 
@level0type = N'SCHEMA', @level0name = 'load', 
@level1type = N'TABLE',  @level1name = 'load_album'; 

dw.call ("""
exec sys.sp_addextendedproperty 
@name       = N'MS_DescriptionExample', 
@value      = N'Album ID', 
@level0type = N'SCHEMA', @level0name = 'load', 
@level1type = N'TABLE',  @level1name = 'load_album', 
@level2type = N'COLUMN', @level2name = 'album_id'; 

A side comment. Look at the elegant syntax of the PostgreSQL version, then look at the SQL Server version. Shake your head, and mutter “WHAT were they thinking???”.

Ajilius. Continuous value.

Enhancement: Schemas

Rounding out the enhancements in Release 2.2.7, Ajilius now automatically organises tables into schemas.

We have implemented four schemas:

Ajilius-specific control tables

Data loaded from external sources

Data undergoing transformation

Data warehouse dimensions and facts for public access

The main benefit of this feature is to simplify end-user access to fact and dimension tables. Previously, when a user accessed the database using any visualisation or query tool, all tables were visible to the user, including temporary load and staging tables. This was confusing to the user, and required work from a database administrator to selectively hide tables from the user.

Now, end-users need only be granted access to the dw schema by default, and they will only see dimensions and facts that have been created specifically for analytics and visualisation. Internal working tables will no longer be visible, unless access is specifically granted.

Ajilius. Simplifying end user analytics.



Enhancement: Surrogate Key Names

Ajilius V1 used a fixed naming convention for surrogate keys. The dimension surrogate was named “id”, and the fact foreign key was named “<dimension>_id”.

While this might have been correct from a data modelling perspective, it didn’t work well with modern BI tools which expect the surrogate and foreign keys to have the same name.

In V2 we modified the convention to use the same name, which we constructed as “<dimension>_key” in each case. This worked well with BI tools, but Kimball modellers were more used to seeing column names like “store_key” instead of “dim_store_key”.

Now we’re releasing an enhancement that enables you to set the surrogate key name when you create a dimension, and to modify it like any other column name.

Update to version 2.2.7, and you’ll be able to use this feature.

Ajilius. Responding to user feedback.


Enhancement: Batch Processor

When we designed Ajilius, we expected that customers would have an existing batch scheduling solution in place. Scripts generated by Ajilius would be deployed to production servers, and scheduled for execution by the operations team.

It hasn’t worked that way in practice. To our surprise, most customers to date have had no existing scheduler in place. Our most common enhancement request has been to provide this functionality.

Well, we’ve done it. Ajilius now incorporates a batch scheduler for dependency-based execution of data warehouse scripts.

Ajilius now incorporates a command line processor that supports the following arguments.

-w / –warehouse
Generate scripts from the warehouse_name metadata

-b / –batch
Execute batch-level operations

-s / –source
Load all tables from named source/s

-l / –load
Load all tables, or named tables

-t / –transform
Transform all tables, or named tables

-d / –dimension
Process all dimensions, or named dimensions

-f / –fact
Process all facts, or named facts

The -t, -d and -f options process all dependencies for the selected tables. For example, the arguments:

-w chinook -b reset -f all

will conduct a full end-to-end load of the “chinook” warehouse.

You might choose to only load two specific fact tables, in which case the arguments might look like:

-w chinook -b reset -f fact_sale fact_budget

Multiple commands can be included in the one command line, or jobs may be separately scheduled. Steps that have been completed will be retained between jobs, until they are reset by the “-b reset” option. For example:

-w chinook -b reset
-w chinook -l all
-w chinook -t all
-w chinook -d all
-w chinook -f all

If executed as a series of jobs, this batch would reset the internal dependency log, load all load tables, transform all stage tables, process all dimensions, then process all facts.

It might, alternatively, been written as:

-w chinook -b reset -l all -t all -d all -f all

But given that all dependencies are processed for each table, it could be simplified to:

-w chinook -b reset -f all

The batch scheduler will be in Release 2.2.6, available from Monday, July 25.

Ajilius. Now with enhanced scheduling.


Enhancement: Custom Query Incremental Loads

Ajilius uses change data columns to govern incremental extracts from source tables. You flag these columns on the Column data entry screen.


When we implemented Custom Query Loads, this feature was not supported.

Now, thanks to some great work from the dev team, we’re pleased to announce that incremental loads are now fully supported on Custom Query Loads. Same method, same result.

Include parameter markers in your custom queries, flag the columns for change data detection, and we’ll automatically generate the rest.

Ajilius. Every day, in every way, we’re getting better and better.

New feature: Custom Query Loads

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, and finally execute the ELT job created from the metadata.

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 our newest feature, Custom Query Loads.

Ajilius. The features keep coming.


New feature: Merge Transform

We’ve just finished a new feature to combine multiple streams of data within the data warehouse. Known as a “Merge Append”, this feature merges (appends) the contents of one table into a target table, with user-defined mappings between columns.

We recently delivered this feature for customers with multiple, similar, sources of data; and now we’re making it publicly available. Examples of use cases have been collectives of independent schools, financial aggregators, and re-insurance organisations.

Our content editor has done a great job on a tutorial for this feature, setting a standard we’ll be applying to all features from now on.

Let us know what you think.

Ajilius. Committed to delivering value.