> For the complete documentation index, see [llms.txt](https://documentation.connexica.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://documentation.connexica.com/reporting-guides/etl-data-processing-guide.md).

# 05. ETL Data Processing Guide

## 04. ETL Data Processing Guide

This guide outlines the steps required to index, join, shape, and flatten data using the \[Database Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Wizards.Database Data Source Wizard.WebHome), \[Amalgamated Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Wizards.b. Amalgamated Data Source Wizard.WebHome), and \[ETL Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.c. ETL.WebHome) to automate the creation of a customer master record. The final output summarises customer activity across the business.

To facilitate this level of data processing, the \[Extract, Transform & Load (ETL)]\(doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.c. ETL.WebHome) functionality is vital due to how \[CXAIR]\(doc:Technical Documentation.CXAIR.WebHome) processes indexed data.

When building an Index, \[CXAIR]\(doc:Technical Documentation.CXAIR.WebHome) handles retrieved data row-by-row. Therefore, there is no grouping at this point and no way to relate rows to each other. For example, the same Customer ID appearing on each row is inconsequential—\[CXAIR]\(doc:Technical Documentation.CXAIR.WebHome) simply stores the rows in the order they are retrieved.

To discover and specify these relationships, these Indexes can then be used in the \[Crosstab]\(doc:Technical Documentation.CXAIR.User Guide.02. Reporting.2c. Crosstabs.WebHome) functionality, where fields can be grouped at row or column level before aggregated values are derived at run-time. For example, using a date field as a column value will order the values in the report and update the output accordingly.

With this in mind, the following questions pose a problem when simply viewing raw data:

* When was a customer’s first transaction? What was the value?
* What is the total balance value for a single customer across multiple accounts?
* What is the most recent transaction value?

The answers to these questions cannot be determined without prior grouping at customer level, as each transaction is stored as a single row.

This is where the \[ETL]\(doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.c. ETL.WebHome) functionality and its fundamentally different approach to data processing is crucial. It is possible to loop through data based on a set of keys to group items together. Using the wizard, grouped aggregations can be written out to the Index, negating the requirement for report-level relationship discovery.

For the purpose of this guide, demonstration data has been used containing three separate customer, account, and transaction tables. For a copy of this sample dataset, please contact Connexica.

## Creating the Source Indexes

Three Indexes will be created, each serving a specific purpose and providing key information for an area of the business.

{% stepper %}
{% step %}

### Create the Accounts Index

Using the \[Database Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Wizards.Database Data Source Wizard.WebHome), select Excel XLSX as the **Database Type** and use the **File** option to select the supplied Excel file.

Enter `Accounts` into the **Sheet** textbox.

!\[Screenshot 1.png]\(image:Screenshot 1.png)
{% endstep %}

{% step %}

### Create the Customers Index

Repeat the process and enter `Customers` into the **Sheet** textbox.
{% endstep %}

{% step %}

### Create the Transactions Index

Repeat the process and enter `Transactions` into the **Sheet** textbox.
{% endstep %}
{% endstepper %}

Ensure the three Indexes are added to the same Search Engine. The Home screen should resemble the following screenshot:

!\[Screenshot 2.png]\(image:Screenshot 2.png||height="330" width="950")

The Accounts Index contains multiple account information for a single customer:

!\[Screenshot 3.png]\(image:Screenshot 3.png||height="309" width="950")

The Customers Index contains a record per customer, detailing personal information:

!\[Screenshot 4.png]\(image:Screenshot 4.png||height="207" width="950")

The Transactions Index contains transactional-level information for a single customer across multiple accounts:

!\[Screenshot 5.png]\(image:Screenshot 5.png||height="417" width="950")

## Amalgamating the Data

With three separate Indexes created, the \[Amalgamated Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Wizards.b. Amalgamated Data Source Wizard.WebHome) can be used to join the data into a single Amalgamated Index.

{% stepper %}
{% step %}

### Join Customers and Accounts

Join the Customers and Accounts Indexes using a Left Outer join and select `Customer_ID` as the common field.

This adds account information for every customer where the `Customer_ID` field matches.

Leave the **Display Fields** checkboxes blank to include every column in the resulting output.

!\[Screenshot 6.png]\(image:Screenshot 6.png||height="497" width="950")
{% endstep %}

{% step %}

### Join Accounts and Transactions

Create a second join that combines the Accounts and Transactions Indexes using a Left Outer join with `Account_Number` as the first common field.

A second common field is also needed. Click **Add Link** and select `Account_Type` and `Acc_Type`. Establishing this second condition ensures that a unique account number is not matched to the wrong type of account.

From the Accounts Index **Display Fields**, leave the checkboxes empty to bring through every column. From the Transactions Index, select the `Account Name`, `Trans ID`, `Transaction Date`, `Transaction Type`, and `Transaction Value` fields to ensure duplicate fields are not in the output.

!\[1697452418525-512.png]\(image:1697452418525-512.png||height="734" width="1310")
{% endstep %}

{% step %}

### Create the Amalgamated Index

Use the **Add to Search Engine** drop-down list to specify the Search Engine the resulting Index will be added to, enable the **Build Now** option, and click **Create**.

The resulting Index contains a row for every transaction with additional customer and account-level columns present:

!\[Screenshot 8.png]\(image:Screenshot 8.png||height="530" width="950")
{% endstep %}
{% endstepper %}

## Extra Field Calculations

With an Amalgamated Index created from the three source Indexes, the output can be supplemented using the \[Extra Fields]\(doc:Technical Documentation.CXAIR.Administration Guide.4. Manual Index Creation.b. Creating a Data Source Group.WebHome||anchor="Extra Fields") functionality. Written at Data Source Group level, calculations can derive additional columns at build-time.

The first extra field concatenates the Account Number and Account Name fields to create a composite key called Product Key. This is used in later stages to denote the number of unique accounts opened by a customer.

**AccountNumber + '-' + AccountName**

!\[Screenshot 9.png]\(image:Screenshot 9.png)

The second extra field, Months as Customer, dynamically compares the Inception Date value for every row to the current system date. The difference is written out in months.

**DateBetween(InceptionDate , TODAY , MONTHS)**

!\[Screenshot 10.png]\(image:Screenshot 10.png)

Once the two calculated fields have been saved, rebuild the Index and validate the results in the \[Query]\(doc:Technical Documentation.CXAIR.User Guide.02. Reporting.2a. Query.WebHome) screen.

## Saved Queries

With rows from multiple accounts joined in a single Index, use the following queries to access key areas of interest:

```
+Account_Status:"Active"
+Account_Status:"Closed"
+Account_Status:"Offer"
```

These queries provide a cohort of data for active and closed accounts, along with accounts that have been offered to a customer. Save these queries to use this dynamic filtering when creating aggregations in the \[ETL Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.c. ETL.WebHome).

## ETL Processing

In this example, data processing is split into five distinct stages to shape the data into the final output.

Using the \[ETL Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.c. ETL.WebHome), select the previously created Amalgamated Index from the **Index** drop-down list to reveal the necessary processing options.

The \[ETL Wizard]\(doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.c. ETL.WebHome) allows additional steps to be added directly within the ETL to create these Database and Amalgamated Indexes. Clicking the `+` button lists these optional steps.

{% stepper %}
{% step %}

### Stage 1

Flag the first and last transaction for each customer, then count their accounts, products, and transactions.

Each ETL stage requires a Primary Key value in the **Key** drop-down list and a field used to order the output in the **Order** drop-down list.

As the output is based at customer level, select `Customer ID` from the **Key** drop-down list. Select `Transaction Date` from the **Order** drop-down list to order the data by each transaction date.

!\[Screenshot 11.png]\(image:Screenshot 11.png)

From the **Flags** tab, select the `Transaction Date` field from the **Is First Minimum** and **Is Last Maximum** drop-down lists. This identifies the first and last transaction date for each customer.

!\[Screenshot 12.png]\(image:Screenshot 12.png||height="620" width="720")

In the **Aggregations** tab, use **Count Unique** to count the accounts, products, and transactions for each customer. Select `Account Number`, `Product Key`, and `Trans ID`.

Also select `Transaction Value` from the **Sum** drop-down list to sum values from multiple rows.

!\[Screenshot 13.png]\(image:Screenshot 13.png)

As the **Is First Minimum** and **Is Last Maximum** flags have been set, the **Additional** tab provides options to tailor output when a flag is true. Set the `Account Type` and `Transaction Date` fields for the first transaction, and the `Transaction Date`, `Transaction Type`, and `Transaction Value` fields for the last transaction.

!\[Screenshot 14.png]\(image:Screenshot 14.png)

Each customer’s first transaction is now flagged and supplemented with the account type. Each customer’s last transaction is supplemented with the date, transaction type, and transaction value.
{% endstep %}

{% step %}

### Stage 2

Count closed products per customer. The data does not need to be output in a particular order, so select `Customer ID` for both the **Key** and **Order** drop-down lists.

!\[Screenshot 15.png]\(image:Screenshot 15.png)

In the **General** section, select the previously saved closed accounts query and enable **Others**.

!\[Screenshot 16.png]\(image:Screenshot 16.png)

Using a saved query filters this stage to rows of interest. Without **Others** enabled, rows not returned by the saved query are discarded. When enabled, these results are appended to the bottom of the stage output for use in future stages. This means stages three and four can still use other cohorts of data not used in this stage.

From the **Aggregations** tab, select `Product Key` from the **Count Unique** drop-down list. Due to the added query, this counts the number of closed accounts for each customer.

!\[Screenshot 17.png]\(image:Screenshot 17.png||height="362" width="720")
{% endstep %}

{% step %}

### Stage 3

Repeat the options specified for Stage 2, selecting the Active Accounts query instead. This counts each active account per customer.
{% endstep %}

{% step %}

### Stage 4

Repeat the options specified for Stage 2, selecting the Offered Accounts query instead. This counts each account offer per customer.
{% endstep %}

{% step %}

### Stage 5

Deduplicate records and squash the data into a single row per customer. Select `Customer ID` in both the **Key** and **Order** drop-down lists, then select **Squash**.

!\[Screenshot 2019-09-12 at 16.50.10.png]\(image:Screenshot 2019-09-12 at 16.50.10.png)

When **Squash** is enabled, specify the fields present in the output using the **Copy** tab.

By default, every field is added to the **Copy** drop-down list. As the output relies on unique values, remove all entries from the **Copy** drop-down list and populate the **Distinct** drop-down list with the required fields.

!\[Screenshot 2019-10-22 at 14.39.56.png]\(image:Screenshot 2019-10-22 at 14.39.56.png)

If preferred, add these fields to the **Unique** drop-down list instead. This performs the same function but orders the values. As ordering the values does not provide meaningful insight, this is not required.

Use the **Add to Search Engine** drop-down list to specify the Search Engine the resulting Index will be added to, enable the **Build Now** option, and click **Create**.
{% endstep %}
{% endstepper %}

## Renaming Key Fields

To enhance the end-user experience and make fields easier to understand, navigate to the Index level of the created ETL Index and open **Index Fields**.

Change the **Display Field** text to better represent the field contents. For example, change `Account_Number_COUNTUNIQUE` to `No. of Accounts`.

For a full list of recommended field names, see the following screenshot:

!\[Screenshot 20.png]\(image:Screenshot 20.png||height="740" width="950")

In the \[Query]\(doc:Technical Documentation.CXAIR.User Guide.02. Reporting.2a. Query.WebHome) screen, the Index should resemble the following screenshot:

!\[Screenshot 21.png]\(image:Screenshot 21.png||height="199" width="950")

This ETL output contains one row per customer, outlining key metrics that provide a view of activity across numerous areas of the business.

## Automating the Output

With data processing complete, establish schedules to automate the entire process and account for new and changed records.

Rather than creating individual schedules for each component, use three Index schedules:

1. Rebuild the Indexes from the source Excel sheet.
2. Rebuild the subsequent Amalgamated Index.
3. Rebuild the ETL Index.

{% stepper %}
{% step %}

### Create the source data schedule

Navigate to the \[System Overview]\(doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Overview\.WebHome) screen and select the three source Indexes.

!\[Screenshot 22.png]\(image:Screenshot 22.png||height="265" width="950")

Click \[Collections]\(doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Overview\.WebHome||anchor="Collections"), then **Save**, and name the Collection `Source Data`.

By grouping the three Indexes into one Collection, a single Index schedule can queue all contained Indexes at build-time, negating the requirement for multiple Index schedules.

Navigate to the \[Index Schedules]\(doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.e. Index Schedules.WebHome) screen and click **New**. Enter `Source Indexes` into the Name textbox and select the `Source Data` collection from the Items drop-down list.

!\[Screenshot 24.png]\(image:Screenshot 24.png)

Use the **Frequency of Execution** drop-down list to specify how often the schedule will run, then click **Create Schedule**.
{% endstep %}

{% step %}

### Create the Amalgamated Index schedule

Create a new schedule called `Amalgamated Indexes` and add the previously created Amalgamated Index using the **Items** drop-down list.

From the **Frequency of Execution** drop-down list, select **Dependent**. This allows the schedule to run only after another schedule has completed, ensuring the Index does not build until all source data has been refreshed successfully.

Select the `Source Data` schedule from the **Dependent Index Schedules** drop-down list.

!\[Screenshot 25.png]\(image:Screenshot 25.png)
{% endstep %}

{% step %}

### Create the ETL Index schedule

Create a third schedule titled `ETL Indexes`. Select the previously created ETL Index from the **Items** drop-down list and select **Dependent** from the **Frequency of Execution** drop-down list.

Select the `Amalgamated Indexes` schedule. This instructs the system to perform ETL processing only after the Amalgamated Index has built.

!\[Screenshot 26.png]\(image:Screenshot 26.png)
{% endstep %}
{% endstepper %}

With all three items created, the following schedules should be visible:

!\[Screenshot 27.png]\(image:Screenshot 27.png)

Creating these dependent schedules guarantees that the Indexes automatically build in the correct order, accounting for new information and changed records over time.