The ETL process: Relational Databases

Description

This session introduces relational databases and the use of keys, tables, and relationships to organize and combine structured data. Understanding relational data is important because many accounting and business analyses require information to be integrated across transactions, entities, periods, and data sources.

Motivation

How do datasets relate to one another?

Review

The prior session focused on obtaining data from external sources through APIs. This session considers how data can be extracted from structured relational database, extending the extraction process from retrieving individual datasets to understanding relationships among them.

Class plan

• We will begin with a brief review of API materials from the prior class.
• We will next discuss joins and use PowerBI to connect excel tabs together using unique keys across the tables.
• Finally, we will introduce VSCode as an analytical tool and use SQL commands to work through the same exercise we did in PowerBI.

Team format

Discussion Teams

Materials

• Case

  EYARC Joins Case

  A popular Joins Case from EYARC, this version references Alteryx, but we will use PowerBI and SQL in this version.

  View Online: EYARC Joins CasePDF: EYARC Joins CaseWord: EYARC Joins Case

• Slides

  Class 04 Slides

  View slides: Class 04 Slides

Tools

• VSCode
• SQLite
• PowerBI

Concepts

• Relational Databases
• Unique Keys

Data

Use git pull in datasets.io/ to extract ACCTG522/05_joins/* datasets. One is a SQL db file, the other is an xlsx file.