11 Questions to Ask Yourself Before ETLing a New Data Source

by Evan Leybourn on      2 minutes read

Proper analysis and integration of foreign data sources into a Business Intelligence system can be a time consuming and complicated process. For each new data source I always recommend that a brief business case, made up of the following 11 questions, be put together. This helps prioritise the ETLing and identify problem areas for each source prior to beginning the process.

  1. What quantity of data is being dealt with? - e.g. a CMS with 1,000 records or a financial system with 100,000,000 records
  2. How quickly does the data grow? - A CMS which adds 100 new pages a day will be easier to manage than a financial management system which add 1,000,000 records
  3. How frequently does this data change? - Compare the financial system in which the data never changes (and so there is no need to manage data revisions) to a CRM in which the data changes daily
  4. When does this data change? - We can optimise the ETL process if we know the data is updated at the end of the month, rather than try and ETL the same data every day.
  5. How much of this data is duplicated elsewhere? - If the data is duplicated in another system, and the other system is authoritative, we can simplify the ETL process.
  6. How much of this data is obsolete or irrelevant? - This comes down to GIGO. If the data is obsolete or not relevant to current decision making, including it in the data warehouse would be counter productive
  7. How is this data used? - This question is used to inform the necessity/benefit of the data and should be used as part of the cost-benefit analysis.
  8. What reports currently use this data, and are they satisfactory? - Often the transactional system (e.g. finance system) provides it's own reports. If these are satisfactory and do not need to strength of a BI system behind it, it de-prioritises the data source for extraction.
  9. What access is available to the data, and how can we extract it? - This is a purely technical question which informs the effort involved in the ETL process. Is it a database, can we connect to it directly, via ODBC, do we need software engineers to write extraction scripts etc?
  10. Is external or third party permission required before extracting this data, and how much will that cost? - Sometimes there will be additional license costs to extract the data. This is used as part of the cost-benefit analysis of the data source.
  11. Does the organisation have the skills in-house to perform the analysis and extraction or will we have to out-source? - Complex data sources may need specialised skillsets. This will generally increase the cost of extraction and is used as part of the cost-benefit analysis.