Three-Click Migration

A technical sales manager at Microsoft recently argued that our claim of three-click migration between supported platforms was impossible. “It can’t be done,” he argued, “they’re completely different syntax and architectures.”

So we demonstrated it.

We took a data warehouse we’d built on AWS Redshift.

We ported it to Azure SQL Data Warehouse in three clicks.

We demonstrated the differences in the code that was generated for each platform, and the best practices it entailed.

We then ported the Azure SQL Data Warehouse version to SQL Server on Linux, in three more clicks, as a low-cost development and test platform where the results could be automatically deployed back to Azure SQL Data Warehouse.

Again, we demonstrated how it was done in code.

Finally, we added tables from Dynamics365 and SharePoint Online to the data warehouse on SQL Server, and deployed it back to Azure SQL Data Warehouse.

“OK,” he said, “I’m convinced.”

Ajilius. We walk what we talk.

Faster CRM Reporting

One thing we’ve noticed from our CRM integrations with Salesforce, Dynamics and NetSuite is that their APIs are glacially slow. If your reporting solution gets its data from an application interface rather than a database, your reports could take minutes to complete rather than seconds.

That isn’t surprising, an API gets in the way between your report and the database, but just how slow has been a real eye opener. We’ve seen data volumes that would move in less than a second on a direct database read take 5-6 minutes to retrieve from an application interface.

You might think that one report taking 6 minutes instead of a second or two isn’t too bad, but what if there are 100 reports to be run? Or you need to surface that report on an interactive dashboard?

Not only are there performance issues, but some APIs also have concurrency and volume limits. You might not be able to run more than two reports at once, and if you have a large group of users, their report queries may exceed the extract limits imposed by your CRM provider.

The solution is a data warehouse, where you extract the data from your application API just once, then all your reports interact with a faster database. You can integrate your CRM data with other systems in your organisation; and optimise the data structure into facts and dimensions, the best solution for reporting and analytics.

Here’s what it might look like from a time perspective:

Keep in mind, you also get the benefit of fast, interactive analytics and visualisation, for the entire day. Interactive dashboards are a reality, not a dream.

With our pre-built interfaces to Salesforce, Dynamics365 and NetSuite (and more coming soon), Ajilius makes it fast and simple to integrate your CRM into a data warehouse.

Ajilius profiles your data sources, screens your data to ensure its quality, ingests it into your data warehouse, transforms it into facts and dimensions, and creates direct integrations in business terminology with great products like Yellowfin, Tableau, Qlik, PowerBI and Excel. We’re the best value solution in data warehouse automation.

Ajilius. Faster CRM reporting.

Terminal Emulators

When your software lives in the cloud, you get used to the world of terminal emulators. We run over 50 cloud servers on 5 different cloud platforms, and the right terminal emulator is a real boost to productivity.

When I was using OSX as my development platform, I used iterm2 as my terminal. Far better than the standard Mac terminal program, it offers features like paned windows, search, autocomplete, hotkeys, and more.

On switching back to Windows I was stumped. Putty was the most often recommended terminal, but it just felt ancient after iterm2, and I started looking for alternatives. After much searching, I found the wonder that is ZOC Terminal.

Tabbed windows! 3270! 5250! Windows and OSX! This product isn’t just a Putty replacement, but Attachmate as well.

The file transfer feature means that I never have to scratch my head trying to remember SCP syntax. There’s a full scripting language built-in, that we’re using to automate some of our automated deployment processes. Think of something you want to do in a terminal session, and it is probably built into the extensive feature list.

ZOC Terminal is a paid, commercial product. Its productivity and usability gains are worth every cent.

Strongly recommended, if you’re deploying Ajilius on a cloud / Linux environment.

Current Module Highlight

Something we hear from many new users of Ajilius is “I’m lost!” When screens follow common patterns, a new user can get confused about which screen is in front of them.

We’ve fixed that problem by adding a current module highlight, which subtly tracks the user’s position in the navigation bar.

Regardless of what action you are taking within the Load module, for example, the Load menu will retain a highlight. This makes it fast and easy to check your workflow.

Ajilius. Continuing to act on user feedback.

Display Settings

Something often overlooked by new users is the ability to adjust the size of tables and editors to match their screen size and browser zoom level.

Not everyone likes small, squinty fonts … especially those of us on the wrong side of 50 … and Ajilius makes it easy to adjust.

Click on your user name (top right of navigation bar) and select the User Preferences option:

Then, use the Table Rows and Documentation Lines options to adjust the display. Table Rows adjusts the number of rows displayed in tables, and Documentation Lines adjusts the number of lines displayed in the User and Tech Notes fields.

Ajilius. Readable data warehouse automation.

Custom Driver Settings

This week we’re adding custom driver settings to Ajilius.

While our driver settings are chosen for optimal performance, there have been times when customers have asked for the ability to tailor connections to specific requirements.

We’re now supporting that request through two settings:

  • Connection Parameters. These are extensions to the connection string used by JDBC drivers. Specify connection string parameters in a single line, including delimiters after every parameter.
  • Driver Properties. These are JDBC properties which modify the behaviour of drivers through an API call instead of the connection string. Specify properties as a combination of property-value pairs, with one property per row.

 

The above example shows a modification to the connection string for the Salesforce driver, which adjusts the Salesforce API to version 36, and reduces the timeout setting to 30 seconds.

Please use custom driver settings with extreme care, as invalid settings could cause your ELT jobs to fail. We recommend you check with us before making changes regarding the specific format requirements of each driver.

Ajilius. Customisable automation.

 

Ajilius 2.4.10

This week we’ve got a mix of incremental enhancements and error corrections.
Here’s what you’ll find in Ajilius 2.4.10:
  • Enhancements:
    • Salesforce Sandbox. Ajilius now supports sandbox accounts for Salesforce.
    • Tableau Connections. Tableau connections have now been validated against SQL Server, PostgreSQL, Snowflake, Redshift, Azure SQL DW, Exasol and MariaDB ColumnStore.
    • Dedup Calculations. We now support the use of Transforms and Calculations in Dedup transformations.
    • Redshift Sort Keys. If you’re a Redshift user, you now have interleaved sortkey support to improve performance of queries against facts and dimensions.
    • Column Sorting. The Warehouse List and Extract List forms now support column sorting, but clicking the column name. We’ll roll out more sortable lists in future releases.
    • BI Lineage. We now show the lineage of a column on the tooltips in Tableau and Yellowfin.
    • Table Page Numbering. Table page numbers now show at the bottom of lists, to show your position in the list.
  • Error Corrections
    • Better Locale Support. We’ve made some adjustments that give better support for locale-specific number and date formatting.
    • Coalesce Empty. The Coalesce Empty transform, which replaces null values with an empty string, was not working correctly on Snowflake.

Registered users should already have received notification of the download location, please let us know if you’ve not received your email.

 

Better BI Lineage

Ajilius generates metadata-based models for Yellowfin, Tableau, Qlik and PowerBI. We use the descriptions and notes that you maintain in Ajilius to generate business-friendly models and documentation.

The quality of the model depends on the capabilities of the BI product. Yellowfin and Tableau have a  richer metadata capability than Qlik and PowerBI.

We’ve taken advantage of that metadata capability to add a great new feature to the models for Yellowfin and Tableau – data lineage. Note the last two lines of the highlighted tooltips in the following screenshots:

Not only do we present the documentation you provided for the column, but did you see the last two lines? We track right back through the data warehouse lineage to give you the data sources, tables and columns from which each dimension or measure was sourced.

Now there can be no arguments about the source of data in reports, it is right in the model so that users know exactly where each number and value in their report originated.

Ajilius. Better BI Governance.