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.

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.

Dynamics 365 Data Warehouse

Ajilius 2.4.8 brings full Dynamics 365 integration to your data warehouse.

Connect to Dynamics:

Browse metadata to select your tables and columns:

Profile your data for a better understanding:

Load and blend your data:

Ajilius. Integrating CRM for faster, better reporting and analytics.

Salesforce Data Warehouse

Ajilius 2.4.7 brings full Salesforce integration to your data warehouse.

Connect to Salesforce:

Browse metadata:

Profile tables:

Load and blend your data:

 

Ajilius. The easiest way to deliver Salesforce in your data warehouse.

Ajilius 2.4.7

This week’s release 2.4.7 brings the following new features:

  • Salesforce Adapter. Connect to Salesforce, browse and profile your data, then quickly load it to your choice of data warehouse for transformation to facts and dimensions.
  • Double-Bar delimiter. Delimited files now accept a double bar (||) as a column delimiter.

The following errors have been corrected:

  • Historic Persistent Staging. CTAS versions were not correctly updating the current row flag for this variant of persistent staging.
  • Blank lines in log. Occasional blank lines were being appended to the console, and have now been suppressed.

Registered users will receive an email over the next 1-2 days with download instructions.

Go build some data warehouses 🙂

 

#CloudCred

I saw a great tweet from Snowflake today:

I laughed when I read it, it exactly mirrors the way I feel about Oracle, IBM and Teradata (among others) with their cloudwashing of legacy DW platforms.

We’re cloud-first. Cross-cloud. Multi-cloud. Even hybrid-cloud. We’ve been working with cloud data warehouses since they were born.

Ajilius. CLOUD data warehousing.