Release 1.3.11

This release fixes a couple of minor errors, and makes some productivity improvements to existing functions.

Stage Column Documentation
If a column was manually added to a stage table after initial creation, the documentation entered at this point was not updated. This has been fixed.

First Column Calculation / Literal
If a calculated column, or literal, was moved to the first column position in a table, Ajilius would generate invalid transform SQL. This has been fixed.

We’ve made the following changes to existing functions.

Enable Transformations on New Columns
We made an early design decision that transformations were applied by changing a column. When adding a column, we did not know if it was capable of being transformed until after the addition. This means that if you wanted to re-add a previously deleted column, and upper-case it at the same time, that you had to go through two steps – add the column, then change it to add the transformation.
We have now modified the Add Column function to enable you to enter your choice of source-column, role, transform and calculation. Roles and transforms can only be applied to selected source-columns, not to calculations. If both a source-column and a calculation have been entered, the source-column selection will take precedence.

Enable Column Selection on Join Tables
You might recall a post, many months ago, titled Our Join Editor Sucks. Well, Join Editor V2 was a big improvement, but still had some workflow issues. When you joined a table, all columns from that table were copied into the join set. You could delete columns from the column management functions, but users (self included) did not like this interruption to the thought process that typically happens when joining tables.
The new Join Editor enables column selection at the point of join definition. A toggle supports select-all functionality on the list, or you may select individual columns. This is a small improvement when you want all columns from a joined table, but a huge leap forward in productivity when you only want one or two columns from a larger table.
You can still used the column management functions to add, change and delete individual columns from the join set, as well as sort the columns into your preferred sequence in the table.

Registered users will be notified by email of the download location. If you’re reading this and have not received your email, please contact support@ajilius.com.

PostgreSQL message encoding

We ran into an interesting error this week, where a job was ending with an encoding error on the completion message.

Our first thought was that the database encoding did not match the value in the metadata, but no, both were set to UTF-8.

This is an Italian customer, and after much head-scratching, we discovered that the configuration value for lc_messages was set to ‘Italian_Italy.1252’. Ajilius expects messages returned by the server to be in the same encoding as the data warehouse.

In this case, the DBMS had been installed with settings from the Italian locale on the server, but the data warehouse database had been created with UTF-8 encoding.

We will now recommend that servers are initialised with initdb using the preferred encoding.

In this case, the customer changed the value of lc_messages in postgresql.conf, and the problem was resolved.

http://www.postgresql.org/docs/9.4/static/locale.html

http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-LC-MESSAGES

Release 1.3.10

This release is a quick maintenance update, with a minor enhancement to built-in transforms.

WHERE Test Error
The Test button on the WHERE clause editor may have triggered a fatal error screen. The clause was correctly written, but could not be tested. ELT jobs worked correctly. This error has been corrected.

We’ve made the following changes to existing functions.

New Transforms
We’ve added the following transforms to the Change Column screen:
– Cast to Integer
– Cast to Numeric

Registered users will be notified by email of the download location. If you’re reading this and have not received your email, please contact support@ajilius.com.

Release 1.3.09

The devs have been busy!

This release brings a bunch of new features to make it easier to re-run whole sections of the data warehouse during development and testing, as well as some functions to keep your development environment neat and tidy.

Load | Create All
Create, or drop and re-create, all Load tables.

Load | Process All
Execute the extract, push (for cloud DW), and load processes for all Load tables.

Stage | Create All
Create, or drop and re-create, all Stage tables.

Stage | Process All
Execute the transform processes for all Stage tables.

Dimension | Create All
Create, or drop and re-create, all Dimension tables.

Dimension | Process All
Execute the transform processes for all Dimension tables.

Fact | Create All
Create, or drop and re-create, all Fact tables.

Fact | Process All
Execute the transform processes for all Fact tables.

Warehouse | Clear Scripts
Erases the contents of the SCRIPTS directory.

Warehouse | Clear Extracts
Erases the contents of the EXTRACTS directory.

Warehouse | Drop Tables
This function lists all the tables in your data warehouse. You may check one or more tables, then press the DROP button to remove those tables from your DW database. Use this function with care, once dropped, tables can only be restored from backup.

We’ve also made one small change to the Load Tables list, to bring it into line with the other table displays.

Load | List Load Tables
The column role has been added to the table columns list at the right side of the screen.

There are no metadata changes in this release, and no known errors to be fixed.

Registered users will be notified by email of the download location. If you’re reading this and have not received your email, please contact support@ajilius.com.

Release 1.3.06

We’re now growing fast enough that we need to formalise the release update process.

Each time we publish an update, we’ll post the details of the update in this topic.

This release brings the following user changes:

  • Amazon RDS PostgreSQL as DW.
    We always supported RDS as a data source, now we support it as a first-class target platform as well. This means that you can use PostgreSQL on premise, in the cloud on IAAS, or in the cloud as PAAS. Three-click migration means that you quickly and easily migrate between these platforms, as well as Snowflake, Redshift and Azure SQL Data Warehouse.
  • New Oracle driver offering more simple setup.
    The previous Oracle driver was extremely complex to install, and caused more support issues than any other aspect of installation and configuration. We have now switched to a driver that does not depend on the Oracle client software, with much better results.
  • Optional command line credentials.
    ELT scripts use environment variables to pass credentials and paths. Some users reported difficulty setting environment variables in complex server environments, so we have added the option of command-line credentials to all jobs.
  • Deleting a table or column no longer deletes descendants.
    If a table or column is deleted, we now set the descendant columns to NULL. This leaves downstream tables intact, while enabling you to restructure earlier tables in the ELT flow.
  • A new Fast Map feature to quickly adjust table and column sourcing.
    If you have restructured precedent tables or columns, or changed your mind about the sourcing of a table, you can now quickly update the column sources through this function.
  • A new Fast Delete feature to rapidly delete large numbers of columns.
    You might only require a handful of columns from a set of joined tables. This feature makes it quick and easy to delete all the unwanted columns in one pass, rather than separately deleting each column.
  • Documentation enhancements.
    We have added more obvious references to column roles, transformations and calculations in the data warehouse documentation generated by Ajilius.

All users will receive an email with the new code and instructions. There are no database changes in this release, just copy the executable to your Ajilius directory