The Scenario:

Automated Clearing Houses

An Automated Clearing House (ACH) is an electronic network that processes financial transactions. It facilitates the transfer of funds between banks, enabling direct deposit of payroll funds, Social Security payments, tax refunds, and other types of transfers, such as electronic bill payments. These transactions are usually processed in batches and settled within one to two business days. Typically, high-volume and low-value transactions use ACH transfers since they are more affordable than other methods.

Main Challenges

The growth of ACH transactions, the rise of real-time payments, and the shift away from cash can overburden legacy database systems with limited scalability. As the volume and complexity of ACH transactions continue to increase, legacy database systems may struggle to keep up with the demands of processing and managing this data. Issues such as batch windows becoming too tight are likely to arise. To address these challenges, ACH operators need scalable databases that provide strong consistency and deal effectively with aggregations and high-rate data ingestion, in order to ensure that they can provide reliable and competitive payment processing services.

But what database can blend these different features?

In this short demo, LeanXcale will insert 25 million transactions from 50 banks in less than 5 minutes while clearing in real time. To give an idea of the size, around 7 million transactions are cleared in Spain daily.

The Lab

The Lab Scenario

In this hands-on lab package, you will gain experience with our tool lx-ld, a method for inserting synthetic data and reproducing the aforementioned demo. The demo consisted of three parts:

Lx-ld, to create and ingest synthetic data.
LeanXcale, to store while aggregating data.
Apache Superset, to display the data. .

Lab Components

The following components are required:

Linux
LeanXcale Database
Superset (for the sample dashboard)
- Docker-composer (required to install Superset, next step)
- Superset installation
Lab package
- lxloader
- Sample dashboard
- Running the sample

If this isn’t your first hands-on exercise from LeanXcale University lab repository, you can skip steps 1-3 and go straight to step 4.

1- Linux

The Linux distribution recommended is: - Ubuntu 20.04.6 LTS

There are three options:

You already have a Linux Ubuntu 20.04.x running and there is enough space for LeanXcale. Please look at the system requirements below. You can skip step 1 and go for step 2.
You can setup a new server by downloading the required iso image from: https://releases.ubuntu.com/focal/. Please select the adequate image for your local environment.
You can also download a pre-installed virtual machine for VirtualBox or VMWare from here: https://www.linuxvmimages.com/images/ubuntuserver-2404/

Please select the adequate VM for your host OS. If you don’t have one already. You can install any of them from the following links:

Oracle VirtualBox: https://www.virtualbox.org/wiki/Downloads
VMWare: https://www.vmware.com/info/workstation-player/evaluation (There is a free version of the vmware player you can use)

2- LeanXcale Database

This demo is ready for the following leanXcale version:

2.3.240707	https://artifactory.leanxcale.com/artifactory/lxpublic/lx.2.3.240707.zip
2.3.240715	https://artifactory.leanxcale.com/artifactory/lxpublic/lx.2.3.240715.zip
2.3.240723	https://artifactory.leanxcale.com/artifactory/lxpublic/lx.2.3.240723.zip

Please follow the default instructions to install and run an instance of the LeanXcale database. Some minor adjustments might be required for a particular demo. These additional steps will be described in the link below. https://docs.leanxcale.com/leanxcale/v2.3/install/index.html#_background

Installing the license: To run this demo a license is required. The license for the ACH demo can be requested from this link: https://www.leanxcale.com/join-leanxcale-community

3- Superset

Before we can install Superset, Docker-composer is required in your system, the following steps might be skipped if you have docker-composer already available in your system.

a. docker-composer

To run the prebuilt dashboard, it’s required to install docker-composer. Please follow the steps below:

To download and install Compose standalone, run:

$ curl -SL https://github.com/docker/compose/releases/download/v2.29.1/docker-
compose-linux-x86_64 -o /usr/local/bin/docker-compose

Apply executable permissions to the standalone binary in the target path for the installation.
Test and execute compose commands using docker-compose.

$ sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose

For more detailed reference go to this link.

https://docs.docker.com/compose/install/standalone/

B. Superset (installation)

Now, that docker-compose is installed, we can install Superset, the steps are:

Get Superset

$ git clone https://github.com/apache/superset

Start Superset

# Enter the repository you just cloned
$ cd superset

# Fire up Superset using Docker Compose
$ docker-compose -f docker-compose-image-tag.yml up

This may take a moment as Docker Compose will fetch the underlying container images and will load up some examples. Once all containers are downloaded and the output settles, you're ready to log in.

Now head over to http://localhost:8088 and log in with the default created account:

username: admin
password: admin

🎉 Congratulations! Superset is now up and running on your machine! 🎉

We are ready to install and run this hands-on sample.

# to stop superset
$ docker-compose down

4- Lab package: ACH Compensation

a. lxloader (lxld)

The lxloader in LeanXcale is a data loading tool designed to facilitate the efficient transfer of data into a LeanXcale database. It supports various data formats like CSV and JSON, making it versatile for batch data processing. The loader uses a combination of readers (for data ingestion) and writers (for storing data in the database).

The key advantage of lxloader is its capability to handle large volumes of data quickly while maintaining consistency and reliability, which is especially useful in scenarios involving batch processing, such as ACH transactions. With the correct configuration, you can parallelize tasks, making it ideal for environments where real-time analytics are required as the data is ingested, ensuring low latency between data ingestion and its availability for analysis.

This sample requires the following version of our loader. https://artifactory.leanxcale.com/artifactory/lxpublic/lxld.v2.4.port.tgz

For the loader it’s required the appropriate LeanXcale JDBC Driver, you may download the JDBC driver from the following link: https://repo1.maven.org/maven2/com/leanxcale/lxjdbcdriver/3.2.2405310852/lxjdbcdriver-3.2.2405310852.jar

Configuration: Once the lxloader is downloaded we can test it by issuing the following command:

$ cd lxld
~/lxld $ bin/lxldr -h
usage: Lxld [-nhvq] [-i n] [-Dflags] [-b batch] [-C ncache] [-c count]
		[-r nprocs] [-p nprocs|-s nprocs] cfgfile [csvfile...]

If you get the message above, you are ready to continue with this sample configuration.

More information about the lxloader tool can be found here: https://docs.leanxcale.com/leanxcale/v2.4/global_docs/lxld.html#_loader_usage

B. Steps to install LeanXcale Python driver to work with Superset

Superset requires the LeanXcale Python API to connect to the sample database, to ensure this, we need to perform the following steps, make sure you stop Superset. You may find the command line for this a few lines above.

Installing Drivers in Docker Images

The connector library installation process is the same for all additional libraries.

1. Determine the driver you need: Consult the list of database drivers and find the PyPI package needed to connect to your database. In this example, we're connecting to a LeanXcale database, so we'll need the lxdbapi connector library.

2. Install the driver in the container: We need to get the lxdbapi library installed into the Superset docker container (it doesn't matter if it's installed on the host machine). We could enter the running container with docker exec -it bash and run pip install lxdbapi there, but that wouldn't persist permanently.

To address this, the Superset docker compose deployment uses the convention of a requirements-local.txt file. All packages listed in this file will be installed into the container from PyPI at runtime. This file will be ignored by Git for the purposes of local development.

Create the file requirements-local.txt in a subdirectory called docker that exists in the directory with your docker-compose.yml or docker-compose-non-dev.yml file.

# run from the repo root:
$ touch ./docker/requirements-local.txt

Add the driver identified in step above. You can use a text editor or do it from the command line like:

# run from the repo root:
$ echo “lxadapi” >> ./docker/requirements-local.txt

Now, you can start Superset again and we are ready to install our sample dashboard.

If you need more information about he lxdbapi you can go to the link: https://pypi.org/project/lxdbapi/

For any issues configuring tht. Python API or learning more about how to install API drivers in Docker images, you may go to this link: https://superset.apache.org/docs/configuration/databases/#installing-drivers-in-docker-images

5- Lab package

If you have reach to this point, it means that you have downloaded the lab package from our University Lab Repository.

The lab package contains the following:

Lab package installation document (this document)
lx_lab_ach_dashboard.zip
lx_lab_ach_data.zip

Configuring our database:

Unzip the ach_sample_data.zip file.
In the lxdb_objects folder, there are several .sql files that have the SQL to create the database structure for this lab package. Using any preferred SQL client tool, execute the SQL script files in the same sequence they are listed in the package directory.
In the database, you should see the following:
- Table: ACH
- Table: AGG_BANKS_CATEGORY (On-line aggregation)
- Table: COMPENSATION_TIME

Now, we can continue, and install the sample dashboard:

Login to Superset.
Install the dashboard by following these next steps:

1. In the Superset home screen, go to Dashboards.

2. In the right side of this screen, there is an option to import a pre-defined dashboard.

3. Select the zip file ach_sample_dashboard.zip that came with this lab package.

4. Click IMPORT. You should see the dashboard listed.

5. We are ready to run the sample data package.

Execute the sample data package:

# run from the package directory:
 $ export LX_HOME=<path to the bin directory of the leanxcale database instalation>
$ ./run_ach_sample.sh

In this package, we are using lxloader to persist data into the leanxcale database. The script run_ach_sample.sh is invoking several instances on the lxloader, each of these instances is ingesting data as one bank part of the compensation process. In this sample we have configured 9 banks, but you can play around this same sample with less or more bank as your needs. For this sample also, each bank in ingesting -c this parameter can also be modified to test several scenarios. Please refer to the lxloader documentation for other options when creating synthetic data for testing.