Businesses today are required to manage more data than ever before. Change is also occurring at a faster rate. Organizations therefore need to be agile with regard to data management and this means being able to move data around from one system to another or between parties with relative ease. In this article we’ll discuss one of the more common aspects of data management – data conversion, and the practical role that a Business Analyst can play in data conversion projects.
What is a Data Conversion Project?
Data conversion is usually carried out as a process within larger data migration projects. A migration project typically involves the migration of the storage and application layers, the data itself and business processes.
As the term implies, data conversion relates to the activities required when data needs to be converted from one format to another. A simple example could be when migrating from one Accounting system to a new one, the invoice number on the new system is of the format INV000000 whereas the old system is simply 000. In this case, a simple transformation is needed as part of the conversion.
What is a Business Analyst?
Business analysis involves identifying business needs and determining solutions to business problems. Solutions often include new software-systems development, but also include process improvements, organizational change or strategic planning and policy development. Business Analysts (BAs) are popular in those industries where businesses seek to grow and change consistently while meeting the needs of their clients and customers.
On a general level, a BA is often a subject matter expert, with experience of how ‘things fit together’, but they are not usually seen as being hands-on technical people. This latter observation is not always valid, however, as we’ll see below.
Business Analyst role in a data conversion project
In a data migration project, a BA would typically take the lead in the following activities:
- Identification of all current data sources
- Analysis of data fields to be migrated, including mappings/transformations to be carried out (ie data conversion)
- Identify future state business processes to support the migrated system
- Documentation of meta data and business rules relating to data and transformations
- Creation of test case materials
There are cases, however, especially in smaller organizations, where the BA role is extended to include the actual completion of tasks, not just analysis and documentation. So, for example, in the data conversion tasks, the BA could carry out the analysis of which fields need to be migrated (and possibly transformed in the process) and also the actual migration itself. This implies that the BA has the relevant technical skills (e.g. SQL server) to complete the migration tasks.
The actual involvement of a BA in the technical tasks therefore depends on several factors:
- The availability of resources – i.e. are there sufficient skilled resources to get the work done in the allotted time?
- The size of the organization – i.e. in smaller organizations, people often fulfil multiple roles such as a project manager also being a business analyst.
- The skills of the BA – i.e. does the BA haveSQL skills (or other technical data skills) that could be utilized?
- The culture and norms of the organization – i.e. it may be the norm that only technical resources have permissions or access to make changes to databases.
It is often the case that the BA is ready and willing to help out, but a lack of skills and/or a reluctance to give ‘non-technical’ users access rights prevents them from doing so.
In the next section we’ll look at a simple way that a BA can work on data migration or data mapping tasks in a SQL environment using the SQL Spreads Excel Add-In. We’ll then look at at some other tools that could be used for completing the standard Extract Transform Load (ETL) processes.
SQL Spreads: a simple tool for ETL tasks
SQL Spreads is an Excel to SQL Server Add-In that lets you use Excel to update and manage data in Microsoft SQL Server. With SQL Spreads you can:
- easily create solutions to manage and update SQL Server data using Microsoft Excel,
- improve data quality by utilizing automatic validation and integrity checks, and
- enable data owners to reach their centralized data through Excel.
SQL Spreads wouldn’t be classified as an ETL tool, but many organizations have successfully used it as a key part of their data migration projects. Further, because it is easy to use, many non-technical users (such as Business Analysts) can use it for data migration and conversion tasks.
A good use case for using SQL Spreads as opposed to one of the ETL tools listed below is to move an existing product listing and price ‘database’ from a series of Excel spreadsheets to tables in a SQL database. The process with SQL Spreads would involve the following steps:
- Create a new database in SQL server (or you can add the new tables to an existing one)
- Clean up the existing product and price data in the existing Excel spreadsheets using standard Excel functions (or by also making use of Power Query)
- Use the SQL Spreads Table Creator feature to create the tables in SQL server
- Optionally set up additional configuration such as:
– change tracking to record who changed what and when
– data-driven drop-down lists to ensure data integrity
– data post-processing for purposes such as cleaning, copying, validation, etc.
SQL Spreads can also be used for ongoing data maintenance after the initial migration and conversion has been completed.
Other Data Conversion Tools
The list below is nowhere near exhaustive, and is simply meant to highlight a few of the more commonly used solutions.
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS) is part of Microsoft SQL Server and is used to perform data integration, transformation, and migration tasks. From an ETL process perspective, SSIS includes the following key features:
- Numerous Data Source and Destination Connectors – the latest version of SSIS includes compatibility with the most modern data sources through its built-in connectors.
- Tasks and Transformations – SSIS has many tasks and transformations such as aggregation, merge, modify, distribute and transform.
- Fuzzy Grouping and Lookup Transformations – in addition to the standard transformations there are ones that specifically help with cleaning, standardizing and correcting data.
SSIS is a mature and well-established tool, although it’s UI can be a littlie clunky and the learning curve can be steep compared to some of the newer ETL-specific solutions.
Talend Open Studio for Data Integration
Talend Open Studio for Data Integration is an open source solution for ETL. It includes the kind of standard features needed for ETL projects, such as data connectors, data transformations and data flow orchestration. It uses an eclipse-based IDE which most developers will be familiar with.
Talend also offers a paid for version (“Talend Data Fabric”) that includes enterprise-type features such as collaboration, scheduling, management and monitoring capabilities and built-in data quality functionality.
Microsoft Power Query
Power Query is a lightweight ETL tool that forms part of Microsoft products like Excel and Power BI. With Power Query you can connect to a range of data sources, import data, clean and shape it and then load it into Excel or a data model so that you can create charts and reports in Power BI.
Because of its tight integration with Excel and Power BI, many people use it to some extent or another for ETL-type tasks. It could be argued, however, that it’s not really a ‘proper’ ETL tool, and you wouldn’t use it on large scale migration projects, but for day-to-day use it is a good option.
Power Query is available in all Excel 2016 or later Windows stand-alone versions and Microsoft 365 subscription plans.
Other solutions: Informatica, Hevo, Integrate.io, etc
There are a number of “data integration/data pipeline/master data management” solutions that include the traditional ETL functions in their feature set. These are platforms that aim to achieve a multitude of things to help you “manage your data better”.
Most of these solutions are SAAS offerings and the key focus is on making it easier to set up and maintain data pipelines through the use of pre-built connections, automation tools and extensive API support.
Pricing for these solutions is normally based on volume of records that are either updated or inserted in the destination (data warehouse, database, etc.). As an example, the Hevo Pipeline product starts at $239 per month for 5 million data events.
Summary
In this article we’ve reviewed the role of the Business Analyst (BA) with a particular focus on data conversion tasks.
Typically a BA will be involved in the analysis and documentation aspects of data migration and data conversion projects, but they can also be involved at a technical level if they have the relevant skills and tools. There is a variety of tools available today that make it easier to complete data migration and data conversion tasks for technical and non-technical users. These range form established tools like SQL Server Integration Services (SSIS) to new data pipeline management platforms like Hevo and lightweight data management solutions like SQL Spreads.
The bottom line is that the Business Analyst and other users can and should be more involved with data conversion projects. If you have any questions relating to data conversion or data migration, please contact us. Also be sure to download a free trial of SQL Spreads to see how easy it is to manage SQL Server data from within Excel.
