Enhancement: MongoDB Data Source

Release 2.2.12 includes support for MongoDB as a data source.

MongoDB is a popular NoSQL DBMS, and Ajilius is the first data warehouse automation platform to support MongoDB as a first-class citizen.

Want proof? Here’s a shot of Ajilius displaying data from the MongoDB hosted TCPH database:



More? Here’s Ajilius showing data from the Restaurants sample database:


Ajilius makes it easy to work with MongoDB, as simple as working with any other data source we support.

Ajilius. MongoDB to your data warehouse.

Enhancement: Log to Data Warehouse

This week’s Release 2.2.12 includes a feature to write ELT log entries to the data warehouse.

Previous releases logged ELT performance to a log file. This file could be named through the Warehouse Edit screen, and defaulted to ajilius.log.

Now, in addition to the log file, a table AJILIUS_LOG is created in the AJILIUS schema of the data warehouse. On job completion, as long as a data warehouse connection is available, the results of the job will be inserted to this table.

The logged columns are:

  • log_stamp / Timestamp
  • log_script / Script name
  • log_elapsed / Elapsed time in seconds
  • log_status / Job status, 0=success
  • log_files / Number of files processed during job
  • log_inserts / Number of rows inserted during job
  • log_updates / Number of rows updated during job
  • log_deletes / Number of rows deleted during job
  • log_message / Descriptive error message if log_status<>0

Ajilius. Keeping track of performance.


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.

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.