8 Not-So-Common Ways You Can Use TechnologyOne ETL

Building Mini Solutions That Make Your Organisation More Efficient and Effective

The pursuit of efficiency and effectiveness has become more crucial than ever for organisations striving to stay competitive and operate with tighter budgets. As data continues to be the lifeblood of modern enterprises, harnessing its power has become a paramount objective. One key tool that has emerged as indispensable in this endeavour is ETL (Extract, Transform, Load) software.

Like most technologies, ETL software – including TechnologyOne’s ETL solution – traditionally associated with data warehousing and large-scale analytics, has evolved beyond its initial role. What was once limited to the confines of a data centre has now become a cloud-based, versatile solution for crafting mini, targeted solutions that drive big impact by the way of efficiency and effectiveness in various facets of an organisation’s operations.

Throughout this article, we’re going to explore the multifaceted uses of TechnologyOne ETL software in diverse scenarios, showcasing how it empowers organisations to tackle complex challenges with simplicity and precision. We’ll delve into real-world examples and use cases where TechnologyOne ETLs have played a transformative role in streamlining processes, optimising data workflows, and ultimately enhancing the overall agility and competitiveness of organisations.

What is ETL?

Before diving into the crux of real-life scenarios, it’s important that we cover all bases; starting with defining what an ETL is.

ETL stands for Extract, Transform, Load, and refers to the process of data management and analytics. ETL is a set of procedures and tools used to collect data from various sources, transform it into a suitable format, and load it into a target data repository or data warehouse. This process is essential for organisations looking to make informed decisions based on data-driven insights.

Here’s a breakdown of each component of ETL:

Extract: In this phase, data is extracted from multiple sources such as databases, files, applications, or external systems. These sources can be structured or unstructured and may include data from relational databases, spreadsheets, web services, logs, and more.

Transform: Once data is extracted, it often needs to be transformed to ensure it’s in a consistent, standardised format that can be used for analysis. Data transformation involves tasks like cleaning, filtering, aggregating, joining, and applying business rules to the data. Transformation ensures that the data is accurate, complete, and ready for analysis.

Load: After data has been extracted and transformed, it is loaded into a data warehouse, or another storage system designed for efficient querying and reporting, such as TechnologyOne. The loading process involves populating the target repository with the transformed data, typically in a structured and optimised format for analysis.

ETL processes (such as those that make up the TechnologyOne ETL solution) are fundamental in data integration, data migration, and business intelligence initiatives. They play a crucial role in enabling organisations to consolidate data from disparate sources, ensure data quality and consistency, and provide a foundation for reporting, analytics, and business decision-making.

Getting Started With TechnologyOne ETL

Extracting, transforming and loading data efficiently and effectively requires careful planning and a structured approach. That’s why, when implementing TechnologyOne ETLs, we always start by evaluating the below fundamental considerations:

Describe the Use Case

When considering the use of TechnologyOne ETLs, it’s important that specific use cases and/or scenarios where the software can be applied effectively are identified. Of course, use cases can vary far and wide depending on the industry, goals and data-related challenges. Here are some common use case examples that we’ve worked on:

Data Integration: Using TechnologyOne to integrate data from various sources to create a unified view of customer and citizen interactions and internal workflows.

Data Warehousing: TechnologyOne ETL processes are commonly used for loading data into TechnologyOne from operational databases. This enables organisations to perform historical analysis, generate reports and support business intelligence efforts.

Data Quality Improvement: Using TechnologyOne ETL for cleaning and standardising data, removing duplicate information, correcting errors and ensuring data consistency.

Near Real-time Data Processing: In some cases, TechnologyOne ETL processes have been adapted to handle near real-time streaming, which has enabled organisations to make quick decisions based on the most up-to-date information.

Migration and Upgrades: TechnologyOne ETL can be employed during system migrations or updates to transfer data from old systems to new ones, while ensuring data integrity and compatibility.

Outline the Impact

Over our decades of experience, helping organisations enhance their TechnologyOne solutions, we’ve seen firsthand the profound impact that the TechnologyOne ETL processes have on an organisation’s efficiency, decision-making and overall competitiveness. Here are some impacts we’ve seen:

Improved Data Accuracy: Utilising TechnologyOne ETLs ensures that data is cleansed and standardised, therefore reducing errors and improving data accuracy, which leads to better decision-making.

Enhanced Efficiency: By automating the extraction and transformation of data, TechnologyOne ETL processes have saved hundreds of organisations time and resources, which has enabled employees to focus on higher-value tasks.

Data Accessibility: TechnologyOne ETL makes data from disparate sources accessible in a centralised repository, making it easier for analysts and decision-makers to access the data they need.

Informed Decision-Making: TechnologyOne enables organisations to generate meaningful insights from their data, which can inform strategic decisions, improve customer and citizen experiences and optimise operations.

Competitive Advantage: Organisations that effectively use TechnologyOne ETL to consolidate their data gain a competitive advantage by responding to market and societal changes more quickly and making data–driven decisions.

What to Consider Before Building a Specific TechnologyOne ETL

Before committing to building out a TechnologyOne ETL process for a specific use case, there are several considerations that need to be addressed. This includes:

Data Source Analysis: Understanding the structure, format and quality of data from various sources.

Data Transformation Logic: Defining the rules and logic for transforming the data.

Scalability: Considering the scalability requirements of the TechnologyOne ETL process.

Data Security and Compliance: Ensuring that the TechnologyOne process adheres to data security and compliance regulations.

Monitoring and Error Handling: Planning for monitoring the TechnologyOne ETL process in real-time and establishing error-handling procedures to address issues as they arise.

Tech-Stack: Choosing the appropriate tools and techniques that align with business infrastructure and budget.

Testing and Validation: Thoroughly testing the developed TechnologyOne ETL process to validate that it produces accurate results and meets the intended objectives.

8 Not-so-common Ways You Can Use TechnologyOne ETL

Having established the critical considerations for the TechnologyOne ETL (Extract, Transform, Load) processes and their pivotal role in data management, it’s time to dive into the practical application of the solution. Beyond the well-trodden paths of data integration and warehousing, there exists a world of creative possibilities for leveraging TechnologyOne ETLs to redefine how organisations operate.

In this section, we explore eight not-so-common, yet remarkably innovative, ways to employ TechnologyOne ETLs.

Scenario 1: TechnologyOne integration with other systems to load transactions

If transactions are available in another system, TechnologyOne can be easily configured, enabling information to be extracted with specific details and loaded into TechnologyOne.

Once a file is loaded, TechnologyOne runs an ETL to extract data. As part of the process, the ETL needs to complete several validations before creating a transaction. Once the validations are successful, the ETL populates the transactions in TechnologyOne.

Scenario 2: Alerts and notifications

Using an ETL, TechnologyOne is able to generate email and SMS notifications.

Email notifications are a direct configuration in TechnologyOne, while SMS generation requires a third-party integration.

Scenario 3: TechnologyOne automatic creditor/debtor creation with minimal information

TechnologyOne ETL enables the creation of a creditor/debtor with auto-numbering. In this scenario, all the user needs to do is create creditor/debtor information files with mandatory fields and load it into the TechnologyOne server folder. Once the folder is loaded, the ETL automatically picks the file and creates the creditor/debtor in the system.

Scenario 4: Automating accounts receivable receipts from remittance advice

Utilising a manual process to generate accounts receivable receipts, and apply them to invoices, can be cumbersome to say the least. From processing and creating the accounts receivable receipt to applying the receipt to the accounts receivable invoice, utilising TechnologyOne ETL, and a remittance advice, this process can be automated. This process means that common anomalies in the remittance can be easily detected, and the appropriate stakeholder informed so it can be resolved prior to the receipt being posted.

Scenario 5: Automating accounts receivable receipts from bank statement transactions

Like remittance receipt automation, TechnologyOne ETL can also be used to automate the process for several debtors. In this scenario, the ETL can process one or multiple bank statement transaction files and create the accounts receivable receipt, before processing the receipt to invoice applications.

Scenario 6: Validating motor vehicle lease accounts payable invoice 

Traditionally, accounts payable invoices are sent through for motor vehicles leased by an organisation. The vehicle details contained in the invoices need to be checked against the details the organisation holds, for example, the term, monthly amount charged and vehicle registration number. Using TechnologyOne ETL, all of these checks can be performed and captured in the accounts payable invoice document file.

Scenario 7: Automating bank statement loads for multiple reconciliation groups

Typically, an organisation will have multiple reconciliation groups within bank accounts. As part of the manual process, daily statement transactions are downloaded from the bank or received via Secure File Transfer Protocol (SFTP) and manually loaded into the bank reconciliation. Using TechnologyOne ETL, consolidated bank statements can be written to process that bank statement file in a way that enables the statement load to be scheduled. By copying files to the reconciliation statement server folder, multiple file statements can be configured and scheduled to load statements.

Scenario 8: Automating complex manual spreadsheet processes

In many organisations, it is common to have a complex spreadsheet model that works with TechnologyOne data, and potentially other data, to complete a task. For example, creating a journal, or an accounts receivable invoice. Utilising TechnologyOne ETL, supported by tools like worksheets and warehouse tables if required, complex spreadsheet models can be moved to a system setting. Increasing efficiency and reducing the likelihood of errors.

Unlocking Efficiency: Leveraging TechnologyOne ETL for Big Impact Mini Solutions

TechnologyOne’s ETL processes are the bedrock of efficiency and effectiveness. The impact of integrating third-party data and transactions through these processes is substantial. They save time by automating manual tasks, reduce errors with built-in error handling and advanced data cleansing, enhance data quality and consistency, cut costs, and boost system performance.

However, successful ETL implementation requires careful consideration. It begins with a clear understanding of business needs, followed by defining transformation and validation methods, and establishing archiving strategies.

In essence, TechnologyOne ETLs, are often the mini engines driving organisational excellence. They streamline operations, bolster data integrity, and contribute to cost-efficiency. Simply put, ETL processes are indispensable tools for organisations striving to excel in a data-driven landscape.

At Lánluas, we help our clients unlock the potential of their enterprise systems seamlessly, by delivering impactful solutions that solve business challenges and provide tangible value. To read more about how we’ve helped our clients to unlock their full potential from their TechnologyOne systems, be sure to check out our case studies.

Written by Dennis Williams and Hershini Abeygunawardena

Dennis is a Chartered Accountant with over 15 years of experience in accounting, finance, reporting, and process improvement, and 4 years working with and configuring TechnologyOne. Process improvement and automation of manual processes is his area of interest and expertise.

Hershini has over 16 years of experience in the ICT industry and over 6 years working with ERP systems, and takes great pride in client success. She has expertise in Agile and Waterfall implementations, user training, operational support, troubleshooting and defect management.