In the modern IT life whenever systems are upgraded, replaced, or consolidated the first and most important thing that comes into the picture is the “Existing Data” in the system. Before the thought of moving to a new and advanced system for better efficiency in work can flourish fully one has to take necessary measures to maintain the data as it and have it up and running in the new system as well. This is where Data-Migration Techniques play a vital role. At first, the word Data-Migration might mean very less to most of you … “all that one needs to do is maintain a backup of the data and restore it in the new system!!” Well, unfortunately, it’s not as simple as this. Data Migration entails more challenges, to name a few are high volume data, complex mappings, and transformations, sometimes it also demands real time synchronization between the old and the new systems. There are many tools introduced in the market that ensure accurate data transfers. Talend Open Studio is one of the most cost-effective tools available. Talend is fast and has the ability to support a wide range of business application software that includes Accounting, ERP, E-commerce, CRM moreover Talend has the ability to connect to Big Data infrastructures as well. Talend provides rich functionality that dramatically speeds project implementation and boosts developer productivity.

In the past, undertaking real time data migration projects involved intensive labor, manual coding, which could be extremely time-consuming and expensive. Consequently, even after realizing that real time data synchronization was the need of the hour, many organizations had to still keep it on their “wish list”. We at Bista Solutions have explored the use of Talend and achieved data migration, be it one-time migration or be it a real-time migration with very less or almost no human interference by automating the processes.

Major challenges in a data migration projects are being able to validate thousands of tables containing a huge number of records in a defined time frame. In fact, Gartner has reported that more than 50% of data migration projects will exceed the budget and end up not achieving the end result due to flawed execution.

Amid data migration, there is dependably a probability of missing data or data debasement. Therefore, it is necessary to ensure whether the given data set has migrated completely and correctly, considering both historical and incremental data migration. Incremental loading of data, specifically, can be challenging on the grounds that every database has its own particular structure. This makes it essential to test that the fields and jobs are loaded effectively and that files/databases are not corrupted. Having said this manually testing billions of records is also not feasible and realistic. And so, automating the validation processes to check accuracy in migrated data is the best solution.

To automate the validation processes, we at Bista Solutions make use of Talend Open Studio to write scripts that compare and validate the data present in both the legacy and new database. Through this post, we are sharing few of these techniques of achieving automation in data migration and data validation.

Automation Tips and Tricks using Talend Open Studio for Data Migration:

Tip 1: Using tRunJob to run multiple Jobs in a single Job Design

A job in Talend is nothing but a logic that we build to perform a specific operation. Be that as it may, imagine a scenario in which you have a few such individual jobs and you might want to execute them one after another.The first step of automation begins with having several migration scripts what we call as talend jobs placed in one frame and execute the tasks in sequence. Talend Open Studio facilitates this by providing the “tRunJob” component. The Talend tRunJob Component lets you put one Talend Job within another so that two or more talend jobs can be executed one after another in a row.

Say you are doing this data migration to support your ERP system that takes care of your daily data from Sales, Purchase, Inventory, Accounts and many other aspects that need to be migrated one after another because of the database dependencies. Also, you have built different jobs to migrate data for each of these tasks, in this case, tRunJob component will be of great help. You can create a master sequence of these tasks as Talend Subjobs and leave it for execution.

Here is a snapshot of a system described above:

1.Master sequence of daily transactional data

Each Child Job(Sales, Purchase, Accounts) is placed in tRunJob. A checkpoint is placed after completion of every Child Job. This checkpoint will log the execution activity in a given table which helps in tracking the Job Status. At the end of the Job, the tSendMail component will send out mails to the authorized people i.e the Stakeholders of the Job to indicate or to mark the end of the master sequence job.

2.Individual child jobs can have the main logic to be executed as shown below

Each individual child job will have a series of logics that will achieve the desired data migration.

Tip 3 : Scheduling a Talend Job to run at given time interval

Once you’ve written your Talend Job you would want to run this job every day or even multiple times in a day at specific time intervals. Imagine how tiresome this would become after some point of time if you had to run the same job, again and again, every day. To help our situation, it would be wonderful if the job could somehow run on its own??. Thankfully, there is one such provision in Talend to achieve this and the process of running the job all by itself is called Scheduling a Job in Talend Open Studio.

In Talend Open Studio you can export your job in a number of ways which are as listed below:

1. Standalone Jobs
2. Axis WebService (WAR)
3. Axis Webservice(ZIP)
4. JBoss ESB
5. Petals ESB
6. OSGI Bundle for ESB

In our example snapshots, we have used the standalone type and we export the job as an Archive file. There are a couple of other settings one needs to perform while exporting the jobs such as exporting the context parameters along with the job, and selecting the build type as standalone etc.

Once you build your job as an archive file you need to run this through the command line.

• Running a Talend Job through command line in Windows

The archive file will have a bundle of executable files for the different variation of OS. For windows, the .bat is used. You can run this file using the following command

            “<jobName>_run.bat –context_param input=<not standard>”

(if your job uses dynamic parameters)

Now we can easily achieve scheduling of this job through Windows Task Scheduler. By setting the trigger you select the desired time interval at which the job is expected to run.

• Running a Talend Job through command line in Linux

For running the executable file in Linux you can use the command given below

bash <JobName>_run.sh –context_param input=<not standard>”

To set up a task in Linux to run on a repeat, you need to program a cron job. To open up the list of cron jobs, type “crontab -e”, here you can schedule tasks on each line with the following format:

1 2 3 4 5 /path/to/command arg1 arg2

Each number means:

1: Minutes, 2: Hours, 3: Day, 4: Month, 5: Day of the Week

The following entry runs every day on an hourly basis.

Tip 4 : Few other Components in Talend which help to achieve Automation in Data Migration

One of the major advantages of Talend Open Studio is the variety of components it offers, No other ETL tool can get close to these many options.Here are a few other components that help you run the jobs without human interaction:

1. tPreJob and tPostJob

When you automate your processes you need to keep track of where your scripts start from and where do they end. These two components help control what happens before and after the content of a job executes. tPreJob is the entry point to the script and tPostJob is the exit point in between this two component the core logic of data migration can be written

2. tSendMail

The tSendMail component is used to send the mail to the concerned stakeholders as and when required such as to indicate start and end of the process through an email service, sending important data to the stakeholders, sending daily reports as attachments etc.

3. tPOP along with tFileInputMail

Consider a scenario, wherein the data migration for the daily activities such as sales and purchase is been performed on one particular server and a visualization report using tools like Tableau, PowerBI, Jaspersoft and so on to determine the health check of these activities is been calculated on a different and remote server. And say, after execution of every activity one CSV file is generated which contains the records required for the visualization report which is to be generated on another server. In this case, the user will have to manually send the files to another server. To help our situation, in this case, Talend offers tPOP component along with tFileInputMail which fetches email messages and attachment files from an email from any server and downloads them automatically to the desired destination server which can then for the visualization reports.

Here is a snapshot of a system which describes all of the above automation components in Talend.

Conclusion:

We can conclude saying that Talend Open Studio can help in automating most of your data migration tasks and thus ensure accurate data migration by reducing errors introduced by human interactions. For more info on data migration using Talend Open Studio, you can get in touch with us through sales@bistasolutions.com.

Introduction

Business Intelligence as a concept has been existing for a few centuries. With the breakthrough of supercomputers came the technological side of BI which meant completing a task faster than what it would take if it was to be completed by a human. Because the technology was expensive and required a lot of space it was not common for organizations to invest in technology for the purposes of using BI. Only when personal computers were invented and then tools like Lotus Notes and Microsoft Excel were accessible, organizations of all sizes ventured into using technology for BI purposes. These tools also gave users a chance to represent data in a graphical format thus allowing a better way of analyzing data and their business. But some organizations today still work in an archaic environment when it comes to data extraction and consumption. BI tools have made life much easier for processing data and organizations looking to adopt them should start evaluating their options.

Below are five signs which organizations can use as a checkpoint to evaluate if they need to implement a BI solution or not:

1 – When the ERP/CRM systems become data centers rather than business drivers

IBM had done some research a while back and came up with a conclusion that 85% of the data stored in different companies at various organizations is unstructured data. With technology becoming cheaper, it had become easier to store data for every organization be it small, medium or large. Furthermore, organizations wanted to store every form of data with the pretext that it might get used in the near or distant future. Whether the need would be for creating reference points in your business, having an audit trail of how your business behaved or making use of that data to get meaningful insights; organizations had no clarity about the objective and today some of them still don’t.

Most organizations are on still sitting on large amounts of data and are not aware of how and when to use it. This had led to experts coining the term ‘Dark data’, data that is sitting idle without being used for direct or indirect monetization.

However, over the last few years, certain organizations have found it difficult to optimize their business whether it was to decrease costs or increase their earnings. In such scenarios, they can make use of this huge data mass which can give them enough intelligence about what part of the business they need to focus on. Furthermore, it can help them drive the positive and negative decisions they had made thus helping them change their business strategy.

2 – When your goals and results do not follow the same trend

Every organization can stay competitive and grow only if it sets challenging goals. And the direction of goals whether increasing or decreasing signals the direction the organization is moving in. This is the reason why quarterly results are a much-awaited event for the executives running the organization. The reason for a poor quarter or a poor year cannot be attributed to a few factors without investigating if they directly contribute to that result. And this investigation into whether your hunch i.e. human intelligence and the actual contributor’s match is where business intelligence comes into the picture.

For example, if airlines and transport companies want to identify the reasons for reduced earnings or a loss during a specific period, they do not have to search for a variety of reason. They can immediately look at fuel prices first and attribute the poor result due to its price instability. But every other organization from a different industry must investigate the exact reasons for the bad performance and even more so in the case of increased earnings and/or profit. Unless the underlying factors for the growth or downfall are not identified the direction in which future goals are set and the results they achieve might be completely opposite. The only way organizations can identify them is by using business intelligence to their advantage.

3 – When you refuse to get rid of the spreadsheet virus

Spreadsheets gave us the easiest and fastest way to process, manipulate and consume data in a structured format. Even today the first tool organizations use to view data is a spreadsheet. But their choice to view and process data remains static while the business scales and expands. This brings out most of the hindrances in identifying business gaps while using data. The growth in business directly increases the size of data and type of data thereby breaking the size barrier which spreadsheets have. Furthermore, the lack of scalability of spreadsheets leads to data silos because the different types of data cannot be accommodated in one single source.

At a high level, spreadsheets work for reporting purposes but when it comes to drilling down into the various levels of data they pose a huge hindrance. BI tools have therefore involved encountering such situations providing the flexibility to slice and dice data without any limits to the size of data. Furthermore, they also help link data sources from the different platform in your architecture and derive conclusions if one affects the other or not. Once the analysis has been done BI tools also provide an easier way of sharing that information irrespective of data size being used which the spreadsheets are not capable of.

4 – When all your information exists in silos and cannot communicate with each other

Traditional ERP systems have been structured in a way that each business function has had its own modules and data, therefore, resides on its own. Also, organizations trying to get the best out of the ERP systems ended up getting different ERP systems for each business function. Now each ERP system might have a different architecture and different ways of storing and retrieving data, creating a nightmare for the those trying to mash all of it at one place.

BI systems right from the beginning have given the freedom to blend data from different platforms. And today they have made data joining even more revolutionary by allowing users to integrate it in any which way they want without worrying about the different types of source of data. BI tools have thus allowed organizations to gain insights into their data without the requirement of a large turnaround time which was required for extraction, joining and data validation. Moreover, BI tools have also added the functionality to save this conjoined data set from different sources into a single source for analysis and reporting. This has made decision making based on data analysis much easier and faster.

5 – When you are overly dependent on your IT team for every report

Historically the IT department was the first business unit to have access and permission to automate and use technology for improving business decision making. With the advent of complex ERP systems and reporting environments, the business became even more dependent on IT to get any data or information about the business. This progressed onto the decision to choose which systems and tools would help the organization make better business decisions; the IT department had all the control. Because report making and creating complex dashboards was a technical process business users stayed away from it thus making IT a bottleneck to get information and data from.

The evolution of BI tools removed this barrier and allowed an easy way of accessing data and reports as required by the end user. Most BI tools today are extremely intuitive in their functionality and the process of creating various reports, visualizations, and complex dashboards. As explained earlier, business users do not have to depend on IT to get data from various sources as BI tools give them the platform to get data into one single location.

For more insights on Business Intelligence  feel free to get in touch with us through sales@bistasolutions.com, you can also write your feedback on how this blog has helped you at feedback@bistasolutions.com.