We all make mistakes, don’t we? I think it’s forgivable so long as it’s a once-off. Never make this mistake again.
Do you do that thing at the end of each month where you produce your end of month reports, save all your files, close them down and then take a big sigh of relief?
Only to find that next month, you have to refresh your memory about what you did, how you got the data, why your links are broken, why your spreadsheets work, and how you copied over those values and why these cells are raw values and not formulas.
Never make this mistake again, there’s a better way.
There are some really good tools out there that will not only perform the data transformation you need to do, but they will record the processes so that you can reuse it in the future, and you will never make this mistake again.
Automated monthly reports? Show me the way!
What does ETL stand for, I hear you ask:
Retrieve data from a database, flat file or other data source
Modify or convert the extracted data into the required format. (Nothing happens to the source data)
Load the transformed data into the target database or repository so it can be presented
These things are a breeze to use and do most of the hard work for you. They automate most of the processes you perform to extract data out of. Various systems transform it into the right shape and then load it into whichever database or excel spreadsheet or reporting format you need at the end.
It’s like recording a macro in excel.
Each month is then only a matter of pressing the refresh button.
Easier than ever
ETL tools have been around for ages, but most of these required some level of technical skill in programming, SQL script, or other languages. And they were quite restricted in what data sources you could interrogate.
Nowadays, we have ETL tools that are drag and drop, point and click, and you can save them as files to share with co-workers.
These tools also connect to virtually any data source you’d come up against, including excel, databases, and cloud data sources,s including non-structured data.
Anyone can use them
The beauty about them being visual is that anyone can understand the processes performed.
The best thing of all is that when you open up next months’ end of month reports, everything is ready to go, and the update process is nice and simple.
Here are 7 options which are also pretty easy to use:
- MS Power BI
- Groovy ETL
- KNIME Analytics Platform
- Rapid Miner