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
Ajilius-specific control tables

load
Data loaded from external sources

stage
Data undergoing transformation

dw
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.

custom_query_control_column

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.