Case Study: Bringing Real-Time Analytics to Construction Logistics at Command Alkon

April 12, 2021

,

Construction projects are hives of constant activity, sustained by steady incoming streams of building materials. Think construction logistics and one pictures a flow of trucks transporting concrete and other necessary materials from suppliers to construction sites. Yet for every physical delivery made, many more exchanges of data occur in the background in order to seamlessly orchestrate supply chain operations.

Command Alkon, a leading provider of cloud-based construction software, provides the software backbone for much of the data flowing through construction materials logistics today, handling over 80% of concrete delivery tickets in North America and tracking millions of material and haul tickets on a daily basis. Its CONNEX platform surfaces data and analytics to users across the supply chain to keep construction projects running according to plan.

Real-time construction logistics

Digitizing Construction Logistics

The intricate movement of construction materials is coordinated through the generation and use of tickets that detail all the relevant information for each job: supplier, delivery location, truck data, status updates, products and services ordered, and data on what was actually delivered. Command Alkon processes hundreds of millions of these tickets annually, a number that will only grow with the increasing adoption of electronic tickets. In addition, they also capture numerous signals from IoT devices on trucks and at quarries and plants. With a mission to digitize every aspect of construction materials logistics, the company launched CONNEX in 2019 to provide a SaaS application where suppliers, transportation providers and contractors on jobsites can collaborate on all the data collected by Command Alkon’s systems.

digitizing-logistics

Command Alkon's CONNEX platform enables collaboration across the construction materials supply chain

In CONNEX, users can analyze tickets in various ways, filtering and searching across fields in the ticket data. For construction materials logistics, the time taken to mix and transport products like concrete needs to be tightly monitored and controlled, so users expect to be able to analyze data in real time. Not only does ticket data have to be available in seconds, but the application has to support fast search and analytics on that data for a positive user experience.

Performance Challenges When Usage Patterns Are Not Known

Providing real-time visibility into ticket data proved challenging. It wasn’t possible, at the outset, to retrieve data quickly enough to enable the real-time analytics Command Alkon wanted and their users expected.

As strong proponents of serverless architecture, the Command Alkon team had chosen Amazon DynamoDB as the transactional database for their application. It was ideal for storing incoming ticket data, but a different solution would be required for analytics. Building secondary indexes would speed up specific queries, but given the multitude of ways users could query the ticket data, anticipating access patterns and optimizing for all of them was not a practical solution.

Command Alkon had a product objective to support any type of data access at any time. However, the performance challenges associated with unanticipated user queries meant that the application could not deliver real-time views of ticket data. They needed to find a way past these performance issues to meet the demands of their largest customers and scale CONNEX usage.

Using Rockset for Real-Time Analytics

The team considered using another database from which they could serve real-time search and analytics. After researching several alternatives, they decided to use Rockset to index and analyze the data from DynamoDB.

As experienced data practitioners, the team had recognized early on that indexing could help accelerate queries and deliver the performance needed by the CONNEX application. Defining all the indexes needed to adequately support every access pattern was not possible, but in Rockset they found a solution that indexed all its data, thus eliminating the need to configure any indexing.

Rockset stores all data in its Converged Index™, which combines an inverted index, a columnar index and a row index. Rockset's Converged Index is the most efficient way to organize your data and enables queries on new data to be available almost instantly and to perform incredibly fast. Since all fields, including nested fields, are indexed multiple ways, Command Alkon could obtain good query performance regardless of how users accessed the data and how the data was structured.

Unparalleled query and schema flexibility

The Command Alkon team pointed to the flexibility that Rockset offered as the most significant benefit they experienced when building on Rockset. Having worked with NoSQL and non-relational technologies for almost two decades, they knew that implementing performant search and analytics in the NoSQL context was a difficult task. With Rockset, however, they were able to decouple access patterns from the schema design, so that queries would perform well without requiring upfront work to reshape data or define indexes.

SQL access was another advantage Rockset provided, even though it wasn’t a primary consideration in the beginning. SQL gained importance as more developers started building features on CONNEX data. It was much easier to get developers productive using SQL, rather than have them learn an unfamiliar NoSQL paradigm and query language.

Minimal effort to configure and operate

Given their lean engineering team, delivering real-time analytics with minimal operational effort was a priority for Command Alkon. As a serverless database, like DynamoDB, Rockset fit perfectly into Command Alkon’s existing data stack and freed them from the need to manage and optimize a separate analytics system.

Command Alkon introduced Rockset into their environment in the course of an afternoon, an experience they described as “magical.” Rockset has a built-in connector that uses DynamoDB Streams to capture updates in DynamoDB tables, so connecting Rockset to DynamoDB was straightforward. Simply referencing Rockset documentation and examples, the team got data flowing from DynamoDB and ran queries on that data in Rockset, all in a matter of hours.

“Flexibility was by far the most important thing for us. Our users want to search on any field, anywhere, and we needed to give them that ability in CONNEX,” said Todd McPartlin, senior solutions architect at Command Alkon. “To have this unique capability offered as a service was exactly what we needed to deliver real-time search months ahead of plan.”

Compressing Development Time with Rockset

Incorporating Rockset into the CONNEX architecture allowed Command Alkon to shave significant time off the delivery of real-time analytics features compared to the Elasticsearch alternative they had considered. Elasticsearch also employed an indexing strategy to accelerate queries, but the cluster would have taken a lot of effort to stand up and manage.

The Command Alkon team had prior experience with Elasticsearch and knew it would demand substantial configuration to make work. They would have had to perform more optimization of schemas and queries, investing significant effort defining mappings up front to format their data correctly for their queries. Elasticsearch would not offer the same benefits of a serverless database, instead requiring management and maintenance of the cluster, including frequent reindexing. The team anticipated that it would take the equivalent of one full-time engineer to manage Elasticsearch had they proceeded with this solution.

In contrast, the Rockset-based solution required none of these data preparation and cluster management tasks. Within 3 days, Command Alkon had verified that the queries they needed for CONNEX product features would run well on Rockset. They estimated it would have taken 2 engineers 6 months to develop the equivalent functionality on Elasticsearch, in addition to the ongoing burden of managing that system. The 6-month advantage Command Alkon gained allowed them to pull in their product roadmap and build features faster than they had planned.

Expanding Real-Time Analytics to More Products at Command Alkon

Command Alkon’s immediate need was to improve the performance of searches in their CONNEX SaaS offering to enable real-time visibility for users, regardless of what questions they were asking of the data. With Rockset being used in production to back real-time analytics, CONNEX now consistently delivers query responses in around 100 milliseconds on ticket data that is a few seconds old, greatly enhancing user experience. Having established the performance and productivity gains with this approach, the team was subsequently able to build further real-time analytics offerings on Command Alkon data.

command-rockset-techstack-diagram@2x

Command Alkon uses Rockset to power multiple real-time analytics use cases

Command Alkon also offers a CONNEX Insights product that provides information to customers on various measures of efficiency, such as their number of loads per hour. Command Alkon embeds Tableau-based dashboards in their product, providing high-level views and allowing customers to drill down as desired. Originally driven through AWS Glue and Amazon Athena, these dashboards are now served by Rockset as well, to benefit from better query performance and less upfront data preparation.

Providing developers access to Command Alkon data is the third product area the new architecture enables. Partners commonly build applications on data in Command Alkon systems for purposes such as accounting, project estimation and carrier management. They would previously have to build their own adapters to obtain the data, but Command Alkon now uses Rockset to provide public data APIs needed by partner developers, without having to customize them for specific types of queries.

Command Alkon has the ability to move a lot faster on new real-time analytics features and enhancements with Rockset in their stack. The flexibility Rockset provides along with its serverless architecture make it a great fit for organizations like Command Alkon seeking to maximize the impact their development teams have.

Over 80% of North America’s concrete delivery tickets are generated from our systems. We track millions of material and haul tickets on any given day and being able to search, analyze and act on this data in real-time is mission critical for us. We have embraced a modern serverless stack, and we chose Rockset over Elasticsearch for our application,” said Doug Moore, VP of Cloud at Command Alkon. “We started out using Rockset for real-time search on ticket data, and we now look to use Rockset for any search and analytics feature on any data. We absolutely love Rockset. It’s a game changer for us.




Image by ly452740370 from Pixabay

Amazon DynamoDB® is a registered trademark of Amazon.com, Inc.

Elasticsearch® is a registered trademark of Elasticsearch BV.

Tableau® is a registered trademark of Tableau Software, LLC.