Data Sources and ETL

Understanding where data comes from is fundamental to business intelligence. In a business context, you will encounter two main types of data sources: structured and unstructured data.

Structured data is highly organized and fits neatly into tables or databases. This type of data is easy to search, manage, and analyze using traditional tools like SQL. Examples of structured data sources in business include:

• Transactional databases: customer orders, sales records, inventory logs;
• Spreadsheets: financial reports, staff rosters, budget planning sheets;
• Enterprise Resource Planning (ERP) systems: procurement, accounting, and human resources data.

Unstructured data does not follow a fixed format, making it more challenging to process and analyze. This data is often text-heavy or multimedia content that cannot easily be stored in rows and columns. Common unstructured data sources relevant to BI include:

• Emails and customer feedback forms;
• Social media posts and online reviews;
• Audio recordings from customer service calls;
• Images and video files.

Businesses often need to combine information from both structured and unstructured sources to gain a full picture of their operations and customer behavior.

To make use of data from these various sources, you need a way to collect, clean, and organize it for analysis. This is where the ETL process comes in. ETL stands for Extract, Transform, Load. It is a core concept in business intelligence that describes how raw data is prepared for use in reporting and analytics.

• Extract: The process begins by retrieving data from multiple sources. These sources can include databases, spreadsheets, cloud services, or even web data;
• Transform: Next, the data is cleaned and converted into a consistent format. This step might involve removing duplicates, correcting errors, standardizing units, or combining data from different sources;
• Load: Finally, the processed data is moved into a central storage system—often a data warehouse—where it can be accessed and analyzed by BI tools.

The ETL process is critical because it ensures that data is accurate, consistent, and ready for meaningful analysis. Without ETL, businesses would struggle to make sense of their data or trust the results of their BI efforts.