Continuous Deployment with Ajilius

Many data warehouse automation products are good at single database development, but struggle when asked to cope with multiple environments such as Dev, Test, SIT, UAT and Production. When you’re striving to be Agile, you need the ability to handle CI / CD in any environment, in any state.

Release 3.2 of Ajilius brings full CI / CD to data warehouse automation.

Within the browser UI, we now support immediate generation of alignment scripts that preserve data across multiple generations of deployment. The following picture shows the new UI menu options for deployments, as well as an alignment script in the left-hand pane.

We’ve also extended the Ajilius command line to support scripted deployments across multiple environments. The following screen shot shows the initial deployment of a data warehouse to a new server.

We’ve then modified the structure of one table, and added a new table, through the browser UI. The second command line invocation alters the data warehouse to align with the new table structure.

But what happens when you have multiple environments, in multiple stages of deployment? Ajilius persists the current deployment state for each environment, and has IMPORT and EXPORT functions to reload that state before alignment. This means, for example, that your DEV, UAT and PROD environments can be progressively deployed in different states.

Because these features are implemented on the command line, as well as in the UI, Ajilius supports full automation of the CI / CD process. For example, our own internal CI / CD test server uses CRON to implement an hourly deployment, based on any changes to the metadata in the last hour. Of course, we fully integrate with products like GoCD, Control-M, etc. for enterprise-level automation.

The metadata changes that can trigger an alignment include:

  • Add table or column
  • Rename table or column
  • Change column data type
  • Change column role (ie, SCD2 to SCD0)
  • Change table distribution method
  • Change column sequence

And remember, every change preserves your existing data. We even fully re-key fact tables if an SCD2 dimension changes to any other type.

Ajilius. Enterprise maturity in data warehouse CI / CD.

DWA: Data Warehouse Abandonware?

Frequency of updates is a good indication of the on-going viability of a data warehouse automation platform.

From what we see and hear, too many competitors are good at getting their products released, but not so good at error correction and enhancement.

This came to mind while I was packaging our latest update – huge changes in incremental deployment – and looked at the following:

That’s the releases we’ve made during just the past four months.

  • Every update is cumulative, you don’t have to apply them all
  • Every update contains one or more new features, usually driven directly by customer requests
  • Most updates contain performance enhancements
  • Many updates contain code enhancements and corrections to errors reported by users

Here’s what a couple of weeks looks like in Mercurial:

All our release planning is done through a Trello board. At present it has a backlog of around 18 months for the current development team. New target platforms. New data sources. New transformations. New BI interfaces. New methodology support. New features we’re not yet ready to discuss. We’re going to continue, or even accelerate the rate of new feature deployment over the next  year.

The one thing that won’t change is our licence policy. We’ll continue to be a site-licensed product, with no lock-ins, including the guarantee that your data warehouse will continue to function even if you don’t renew your Ajilius licence, and we’ll never increase your annual renewal for as long as you use Ajilius.

Ajilius. USD 15k / site. The best value in data warehouse automation.

 

Ajilius 3: Adaptive ELT Performance

The new Adaptive ELT feature in Ajilius 3 uses intelligent algorithm selection at runtime to optimise ELT performance.

Combined with the new SQL+Scripts architecture for code generation and execution, we’re seeing orders of magnitude improvement in some use cases.

Here’s a sample of batch output from the standard Ajilius demonstration:

The first two jobs – stage_location and dim_location – used to take around 0.06 and 0.08 seconds to run. Now they’re talking less than .001 seconds.

They’re trivially small tables, but we’re using them as an example because you can replicate the experience in an Ajilius demonstration.

Not every job will benefit to this extent, it depends on factors like the algorithm, and the number of rows in the source and target tables, but every job that processes different data on each run will be faster.

Ajilius. Faster.

 

Cloud Evaluations

We’re pleased to announce that the Ajilius evaluation version now includes all cloud data warehouse platforms.

Previously, we restricted evaluations to on-premise databases. This was to simplify the evaluation environment.

Now, we’re finding that more and more customers are moving their entire workload to the cloud, and there is no on-premise database. And as those workloads move, the knowledge sets of evaluators move with them.

In recognition of this shift, we now include all databases – both cloud and on-premise – in our evaluation version. You can now trial Snowflake, Redshift and Azure SQL Data Warehouse alongside favourites like SQL Server, PostgreSQL, MariaDB and Exasol.

Ajilius. Tomorrow’s data warehouse, today.

 

Why SQL Server on Linux Rules (again)

I just upgraded three servers. It took less than 5 minutes.

On each server:

sudo apt-get update
sudo apt-get install mssql-server

No more gigabyte downloads (it needed 145Mb), no bewildering and befuddling multi-page installer screens, just two simple commands and the job was done.

On Linux, at least, SQL Server has gone from one of the most complicated installers in the industry to one of the most elegant.

I love it 🙂

 

#1 in Data Sources

Ajilius, the world’s most innovative provider of Data Warehouse Automation, is proud to announce that we are now partners with CData, the world’s leading provider of data access and connectivity solutions.

This partnership brings Ajilius direct data sourcing from applications including Salesforce, Dynamics, SAP, Quickbooks, Xero, Reckon, Marketo, ServiceNow; social media integration with Facebook, Twitter and LinkedIn; and information sources such as OData, OFX, PayPal and eTrade.

A full list of the sources now fully supported by Ajilius may be found at http://www.cdata.com/jdbc/

So now, access to the world’s most popular business data processing systems and information sources is just a few mouse clicks away from your data warehouse.

We’ll progressively roll out support for these drivers over the next 1-2 months, please contact us if you have an urgent need to prioritise a data source.

Ajilius. Unbeatable connectivity in Data Warehouse Automation.

Licensing Change

As our customers grow in number and size, and as the features included in Ajilius continue to grow, it has become necessary to make some changes to our licence structure.

The growth in our customer base has increased our support costs, particularly as we have added new data warehouse platforms.

Our new licence fee increases from USD10,000 per year to USD15,000 per year, from January 1, 2017.

No existing customer will be affected by this change. Ajilius customers have a guarantee that the terms under which they licence Ajilius will be maintained for the life of their usage of the product.

No outstanding proposals will be affected by this change. Any customer engaged with Ajilius before January 2017 will have their existing proposal applied to purchases after that date.

No partner terms and conditions will be affected by this change.

Even though our prices will increase, the value of Ajilius continues to grow. We still maintain our commitment, that you will fully repay your investment in Ajilius on your first data warehouse project.

Ajilius. ROI in weeks, not years.

 

Enhancement: Reset Incremental Loads

We like to say that Ajilius is driven by our users, and here is another example.

Release 2.2.16, due Sunday, will include a feature to reset incremental loads, allowing a load to be rerun even after it succeeds. One driver for this feature is detection of a source system error that does not affect the running of a load, but does affect the data content of the load.

The “ajilius” schema of your data warehouse contains a table named “ajilius_control”. This table holds one row for each column for which incremental values are tracked. We have modified this table to include a “previous” and “latest” value.

On successfully processing a load, we update the “previous” value with the contents of the “latest” value (that was used to process the load), and over-write the “latest” value with the maximum column value from the load.

Now, you can select a menu option Schedule | Reset Controls:

reset1

On selecting this option, you will be shown a screen listing all tables for which incremental controls have been defined:

reset2

When you press the Reset button, the control values of any checked table will be reset to their previous state. This enables the incremental load to be repeated.

Ajilius. Tipping the hat to Steve 😉

 

 

Data Quality Reject Limits

Ajilius 2.2.16, due for release later this week, will include a feature to enable a reject threshold to be set for data quality screens.

As shown in the following screen, you can select a limit beyond which a job will be cancelled if it is exceeded by the number of rejects:

reject_limit1

If the limit is exceeded, an error message and exception will be generated. This is an example of how that appears during interactive testing of load scripts:

reject_limit2

When rejects occur that are less than the reject limit, the job will succeed, but a warning message will be placed in the processing log. Here is an example from a test batch:

reject_limit3

Ajilius. Fine tuning data quality.