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 OEM partners with CData, the world’s leading provider of data access and connectivity solutions. We now support more data sources, out of the box, than any other DWA vendor.

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/

In keeping with our policy of delivering new features with unbeatable value, use of these drivers is included in the Ajilius licence. Depending on the drivers you use, this represents a potential value of thousands of dollars per year in licence costs alone.

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 these drivers over the next 1-2 months, please contact us if you have an urgent need to prioritise a data source.

Ajilius. Unbeatable value 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.

We’re now including OEM-licensed third-party software components – more news on that topic shortly – that greatly increase the capabilities of Ajilius, and we need to increase our prices slightly to reflect the costs of those components.

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.

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.

 

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.

http://ajilius.com/custom-query-loads/

Ajilius. The features keep coming.