1 / 17

Understanding ETL Processes in Data Warehousing: Extraction, Transformation, and Loading

This document explores the Extract, Transform, Load (ETL) process vital for moving data from transactional databases to analytical databases. It discusses the challenges of data consistency, particularly when legacy systems evolve over time, leading to redundant and varied data formats. By identifying the source of data required for a data mart, and addressing issues like divergent naming conventions and unique identifiers, organizations can achieve a unified view of data, crucial for accurate analysis and reporting.

cade-foley
Download Presentation

Understanding ETL Processes in Data Warehousing: Extraction, Transformation, and Loading

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Getting the data into the warehouse:extract, transform, load MIS2502 Data Analytics

  2. Getting the information into the data mart Now let’s address this part…

  3. Extract, Transform, Load (ETL) • The process of copying data from the transactional database to the analytical database • Going from relational to dimensional • Basically, it’s a matter of identifying where the data should come from to fill the data mart

  4. ETL Defined

  5. The Actual Process Extract Transform Load Transactional Database 1 Query Data conversion Query Data Mart Transactional Database 2 Data conversion Query Query

  6. Main ETL Issues: Conversion Stage

  7. Data Consistency: The Problem with Legacy Systems • An IT infrastructure evolves over time • Systems are created and acquired by different people using different specifications • This can happen through: • Changes in management • Mergers & Acquisitions • Externally mandated standards • General poor planning

  8. This leads to many issues • Redundant data across the organization • Customer record maintained by accounts receivable and marketing • The same data element stored in different formats • Social Security number (123-45-6789 versus 123456789) • Different naming conventions • “Doritos” versus “Frito-Lay’s Doritos” verus “Regular Doritos” • Different unique identifiers used • Account_Number versus Customer_ID What are the problems with each of these ?

  9. What’s the big deal? • This is a fundamental problem for creating data cubes • We often need to combine information from several transactional databases • How do we know if we’re talking about the same customer or product?

  10. Now think about this scenario Hotel Reservation Database Café Database

  11. Solution: “Single view” of data • The entire organization understands a unit of data in the same way • It’s both a business goal and a technology goal and really more this… ..than this

  12. Closer look at the Guest/Customer Guests Guest_number Guest_firstname Guest_lastname Guest_address Guest_city Guest_zipcode Guest_email Customer Customer_number Customer_name Customer_address Customer_city Customer_zipcode vs.

  13. Organizational issues • Why might there be resistance to data standardization? • Is it an option to just “fix” the transactional databases? • If two data elements conflict, who’s standard “wins?”

  14. Data Quality • The degree to which the data reflects the actual environment

  15. Finding the right data Adapted from http://www2.ed.gov/about/offices/list/os/technology/plan/2004/site/docs_and_pdf/Data_Quality_Audits_from_ESP_Solutions_Group.pdf

  16. Ensuring accuracy Adapted from http://www2.ed.gov/about/offices/list/os/technology/plan/2004/site/docs_and_pdf/Data_Quality_Audits_from_ESP_Solutions_Group.pdf

  17. Reliability of the collection process Adapted from http://www2.ed.gov/about/offices/list/os/technology/plan/2004/site/docs_and_pdf/Data_Quality_Audits_from_ESP_Solutions_Group.pdf

More Related