Mitigating ETL pain
ETL is undeniably application-centric, but even in the era of cloud, there are still key areas where we can help de-stress your ETL project or migration. Finding data residing in ERP or CRM applications relies on understanding the underlying data models which is no small task as ERP vendors typically tend to be rather opaque about such data models (which can be very extensive) and may not supply (usable) dictionaries within their respective applications. The key to solving these issues lies with the application metadata where Safyr can help, enormously.
Big (or extreme) data volumes
Slice the elephant already! In order to locate the data you need – & only the data you need – and to avoid extract, transform or load scenarios where data volumes are too large or too slow, you will need the metadata of your ERP & CRM system. Typically, table and column names in these applications may be machine- rather than business-friendly so finding the data you want means you need to extract a meaningful data model and metadata. Using a tool like Safyr® can reduce the time, cost and resources absorbed by ERP metadata discovery by as much as 90% whilst at the same time improving confidence in your data selection.
Query efficiency & performance issues
Following on from that, your query efficiency and overall performance of queries, can be improved again, by filtering for the data in scope rather than polling the entire repository.
Complex data relationships & dependencies
Having reliable information about data provenance and relationships is vital, not just for your ETL project, but for making sure that when you need to integrate with other systems or repurpose that data for BI, AI, ML or knowledge graphing initiatives, that its semantic value is retained. Having the wrong data with the wrong relationship information can be very bad news. Dependencies will determine in what order tables are loaded into the target system which can get rather complicated if you are merging data from different applications of different lifecycles or from multiple instances where certainty over sibling data and data precedence is really important. Visual confirmation via metadata discovery can alleviate much of that uncertainty.
Mismatches between source & target systems
In similar vein, especially during migrations, you want to ensure the closest possible match between your source and target systems. This is complicated by the fact that cloud environments are not as expansive in terms of data structures, as on premise ones, so a data haircut is needed. Again, you need to filter for the only the data you really want, data whose semantic and relationship metadata is also reliable, so that you minimise the chances of your load failing. Using a tool like Safyr® also gives you the chance to head off blank cells at the pass.
Bad data means data warehouse rollback
If your ETL procedure did load bad data or spam data then you can look forward to a anxiety-filled rollback of your data warehouse or data lake (even worse). Why not save yourself the wear & tear? When you identify those data structures, a usable data dictionary and relevant metadata, you can also view related data enabling you to spot bad data before you pass it through to the target system. This also applies to personally identifiable data (PII) which you may not want to migrate to a 3rd party hosting that does not meet GDPR or CCPA criteria and which would result in you being non-compliant. The same principle applies if you need to restore data from back up – you may inadvertently restore data that shouldn’t be there.
So metadata discovery is vital before you embark on a project that involves ETL. And of those three elements, the ‘load’ part is the one that is capable of doing the most damage if your upload or migration fails. Those kinds of problems tend to be time and resource hungry and can adversely affect project economics, particularly if they happen repeatedly. A tool like Safyr can not only reduce those risks but save significant time on waiting (if you need to call in external resource), cost (in man hours) and delay (which can adversely impact other project schedules).
If you’d like to improve your ETL time from 90 hours to mere minutes; if you want be confident you have the right ERP data and that it is suitable for loading, then talk to us about Safyr®.
Reg. Office:
5 Calico Row,
Plantation Wharf,
London, SW11 3YH.
Reg. in England & Wales.
Reg. Co. No. 5203890.
