- Up next, we meet with credit card processing firm
Clearent by Xplor to see how they built
a fast, scalable merchant transaction reporting system
on Azure SQL Database Hyperscale.
From their approach with micro-batching
to continuously bring in
billions of rows of transactional data
from their on-premises payment fulfillment system at scale,
as well as their optimizations for near real-time
query performance using clustered columnstore indexing
for data aggregation and much more.
So today, I'm joined by Zach Fransen,
who's the VP of data and AI at Xplor.
So welcome to Microsoft Mechanics.
- Thanks. It's great to be on the show.
- Thanks so much for joining us today.
It's really great to have you on.
So if you're new to Clearent by Xplor,
they're a US-based payment solution provider
that enables merchants and retailers to accept
credit card payments from anywhere.
They process over 500 million transactions per year.
And since their inception in 2005,
they've built several intelligent solutions
to reduce credit card processing fees
and significantly speed up
the fulfillment of credit card payments
with next day funding.
Now key to this has been connecting
their back-end payment fulfillment system
with their transaction reporting system.
In fact, they've recently completed
an app and data modernization effort
using Azure SQL Database Hyperscale.
Their new Compass Online Reporting Suite
gives their customers unprecedented and concurrent access
to sales transaction data stored on Azure SQL Hyperscale
for near real-time views of revenue,
sales trends, and much more.
So Zach, to put this effort into context,
can you help us understand where you were coming from?
- Sure.
So when we started this effort, we were fully on-premises.
We had two monolithic legacy apps, Horizon and Compass,
running off the same Clearent database.
Payments from merchants came in
from a legacy third-party mainframe payment gateway
and landed in Horizon as flat files.
Horizon takes care of qualification, billing,
and settling merchant payments.
Part of our value add is to pay our merchant customers
ahead of the credit agencies.
When merchants batch in their sales at the end of the day,
the system qualifies the nature of the transaction
by checking things like if the transaction was made online
or from a card reader via a chip,
magnetic strip, or a rewards card.
These are all things that determine
the cost of the transaction and the fees charged
by Visa, Mastercard, Amex and other credit agencies.
We also charge a derivative fee based on that,
so this is an intricate process to get right.
In parallel, our Compass reporting system running on IIS
had a SQL Server always on cluster
on the same Clearent database back-end
that would pull data from Horizon using a stored procedure
to transform the data into reporting schemas
for merchants to get visibility into their transactions.
- Okay, so how often were you able
to pull the data for reporting?
- Well, we did a two-day lookback once a day,
so we were having to process two full days of data
every day for reporting and we were always a day behind.
There was also contention
because we were using the same database
for processing transactions and reporting.
And as our data and customers grew,
our run times for processing the data
coming into Horizon took longer.
Also, concurrent customer queries in Compass
would sometimes fail.
- That's not too surprising.
Large database operations like these
are prone to performance issues and storage limitations.
Plus, you had to find a better way to aggregate the data
and make it more available for reporting as well.
So what approach then did you take
to remediate some of these issues?
- Well, we adopted a hybrid approach
which allowed us to keep our data on-premises
but migrate the data that we needed for reporting
into the Cloud.
As our foundation was SQL Server,
we wanted to continue to use that skill set
and choose an option where we didn't have to worry
about storage limits or syntax changes.
We went with the Azure SQL Database Hyperscale service tier
that scales to up to 100TB of storage
as the reporting back-end
for our new Compass Online Reporting Suite.
We also modernized the Compass App,
taking an extensible microservices-based approach.
We built an angular app that calls dozens of services
that sit both on-prem and in the Cloud,
depending on what it's trying to do.
And we also fragmented our data model
to match our service topology.
Then, to more consistency bring data in,
we built our own payment gateway called Quest,
which comprises a consistent set of APIs to pull in
merchant data from physical credit card terminals,
e-commerce, or integrated payment systems
in use by our merchant customers.
In fact, as we made it easier to bring data in
and broke the Compass app and data monolith apart,
Hyperscale's role in providing a single hub
pulling all the data together became even more important.
Additionally, we only keep active data on premises,
so Hyperscale is critical in keeping a persistent record
of all our historical data
and to maintaining a lower data footprint on-prem
in those transactional systems.
- Okay, so now you've got a more
scalable and agile app and data layer,
but with so many transactions
that are happening at any one time,
how would you solve for getting the data then
into Hyperscale fast enough to support
near real-time reporting for your merchants?
- So the game changer for us was adopting low-latency,
intraday batch processing, and data streaming.
We've split our Hyperscale instance into two logical layers,
comprising an Operational Data Store
and an Enterprise Data Warehouse.
And we shifted from once-a-day batch processing
to micro-batch processing
where we use SQL Server's Change Data Capture feature
to poll our disparate systems
for the latest change in data every two minutes.
We have data extractors running on our on-premises databases
reading our change capture tables,
and we have a set of configuration tables
sitting in our operational data store
that tells it what columns to read.
The raw data along with the change history for every table
is then loaded into Hyperscale over Express Route
into our ODS.
And we only extract and load the delta tables
in our Enterprise Data Warehouse
for reporting and analytics.
At the same time, as a credit card transaction is made,
Quest immediately places it on a queue,
and we use the SQL Server JSON parsing capability
to extract out the pieces of information
from the message that we are interested in,
such as the date, time, and amount of the transaction,
and then stream it directly
into our star schemas in Hyperscale for reporting
so that the data is available to merchants right away.
- This really sounds like a great solution.
Can we see it in action?
- Sure.
So here is the Compass Online Reporting Suite.
On the homepage, you get a quick summary
of all of your activity from the last recent deposit
through to trends in sales volume.
From there, you can drill in to see the detail.
I'll click into one of my deposits.
For example, one thing that is useful for merchants
is being able to deconstruct a deposit
that may just show up as one line item
on their bank account statement.
In Compass, they can drill in
to see the detail behind the deposit
whether that's multiple terminals or multiple batches
because Hyperscale is bringing all that data together.
Now let me show you what happens
as soon as the transaction is made.
We're in the transaction view within a batch.
I can see all my recent transactions in this batch.
Now I'll show the process of running a transaction
along with where we can view it.
I'm in the virtual terminal built on Quest,
but this could be a physical terminal
or another integrated payment system.
I'll make a credit card transaction.
And you'll see it succeeds
in the virtual terminal in under a second.
Now I'll hop over to Compass
into our batch view, hit refresh,
and you'll see the payment is pending right away.
That's made possible by Hyperscale
instantaneously consuming the data from our messaging queue.
Finally, I'll hop over
to my query in SQL Server Management Studio
and I'll query this batch in our Hyperscale database,
and you'll see the results are also instantly available.
Next, if we look into our list of settled transactions,
you'll see that once the merchant batches in
at the end of the day
if they click into a deposit,
it shows the amounts and fees
along with the net deposit amount
and when payment will be posted.
I'll click in to see transactions in this batch.
Transaction data is also enriched
by the data from our micro-batch processing,
as you can see here by optional column views available.
I'll select Product and POS Entry Mode.
You'll see that information show up in our view
and these are critical elements
that determine the fees for the transaction.
- Okay, so this is an example then of just one transaction,
but what if you have maybe 50,000 or more merchants
that are running millions of transactions a day?
- Yeah.
We process around 1 billion rows of data per month
on our Hyperscale implementation right now.
As you can see here in the portal,
this has amounted to over 28TB.
And a big advantage of Hyperscale
is that the compute is independent to the size of your data.
Let me give you another view from our monitoring tool.
Here, the green chart represents Hyperscale.
It's processing a large quantity of billing information
as the micro-batch comes in at intervals.
It's actually spiking up to almost 100% utilization,
which is around 200,000 IOPS,
and incidentally this is only using 8 cores right now
which is a relatively small amount of compute.
We can elastically scale compute when we need to process
a large amount of data or build an index
on a giant table with all the history and to scale it up.
It only takes a few minutes.
Also in Hyperscale, backups are near instantaneous.
It's constantly taking snapshots of your data,
which helps significantly with data restore.
Through PowerShell, we've automated restore
when we do full-scale load testing,
and that only takes like an hour,
even though it's a 28TB database.
- Okay, so how else then have you configured
your Hyperscale implementation?
- Secondary replicas.
Not just read-only replicas,
but named replicas have been game changing for us.
This allowed us to keep our primary instance
for our compute intensive ETL and data processing.
We can then assign name replicas for specific purposes
like analytics and reporting.
These tap into a copy of the same data,
but have their own dedicated compute.
If you recall, when I showed you the slider screen,
we had a secondary replica configured for customer traffic,
which is part of a scalable pool of replicas
for load balancing.
If I switch tabs, you can see that we also have
a named replica configured to handle analytics traffic.
Routing traffic to these replicas is as simple
as changing the connection string.
This means that critical customer workloads
are not interrupted
by our compute intensive analytic operations.
- So with such massive amounts of data that are coming in,
beyond assigning the right amount of compute,
are you doing anything special then
to speed up query operations?
- Yes, we do a few things.
For example, one of the things
we've really taken advantage of
is clustered columnstore index for data aggregation.
A big advantage of Azure SQL Hyperscale
is that it's very flexible.
It can handle a variety of different types of traffic
and does really rapid aggregation.
Here, we have an example
of an analytical workload where we are going to take
a very large number of records and produce some aggregates.
In this particular table,
we have a little over 3 billion rows of data.
To see some trending,
I have a query that will produce a time series
by collecting data from different months,
which in this case, are in different partitions
in a clustered columnstore index.
When I run it,
you can see it is able to aggregate records
across three months in 2021,
and it's almost instantaneous.
Another way to look at the data
is to look at a cross-section
using dimensional slices, like a state.
And here, we see the total number of transactions
in New York and Washington.
So we're able to look across billions of rows of data
and pull these aggregates together in fractions of a second.
- That's super impressive.
And now you've transformed then your app and data stack
for reporting with Azure SQL Database Hyperscale,
but what's next then for Clearent by Xplor?
- So given our recent merger
with the transaction services group to form Xplor,
we are taking our Clearent platform international
to deliver a service called Xplor Pay
with Azure SQL Database Hyperscale
as our reporting back-end.
And now that we've got our data infrastructure
to this level of maturity,
the next logical step is to leverage Azure Machine Learning.
This will help offer
additional value-added services to our customers,
such as helping them to understand
when to run specific promotions based on existing sales.
- Thanks so much, Zach, for joining us today
and also giving us the deep dive
on your Hyperscale implementation.
In fact, to learn more about Clearent by Xplor
and what they're doing with Hyperscale,
check out, aka.ms/ClearentMechanics.
And for more guidance
on implementing Azure SQL Database Hyperscale,
check out aka.ms/MechanicsHyperscale.
Of course, don't forget to subscribe to our channel
if you haven't already.
Thanks so much for watching. We'll see you next time.
(bright music)