This is the third video in our Data Warehouse Automation series, Data to Dashboard in 90 Minutes with Ajilius Data Warehouse Automation. In this video, we demonstrate a full end-to-end workflow for the creation of a data warehouse from the Sakila movie rental database using Ajilius.
The following steps are covered during the demonstration:
- Connecting to data sources (MySQL and CSV)
- Extracting data from data sources to loading tables
- Transforming data from load tables to staging tables
- Creating dimension and fact tables
- Exporting metadata to Yellowfin for data analysis
We hope you find the video informative. A transcript of the first component of the video is included below.
Our second video, Introduction to Data Warehouse Automation covered some of the fundamentals of data warehouse automation and how it delivers significant value to organisations.
In this video, I use the Ajilius data warehouse automation platform to demonstrate a full end-to-end workflow to create a data warehouse from scratch. I speed the video up along the way to compress about 90 minutes of work into about 25 minutes. Future videos will show each part of the process at normal speed.
The Sakila Database
First, we’ll talk about the database that we’ll be using for this exercise. We’re going to use the ‘Sakila’ database which is a sample data set for the MySQL database platform.
The story behind the sample database is all about a DVD rental company. The company uses a system to record all the rental activity for the business, and all data entered into the system is saved a transactional database. This transactional database is the dataset we’ll be working with.
The dataset contains a variety of information such as:
- Information about customers and staff (personal details, etc.)
- Information about each store (location, manager, etc.)
- Information about each film (e.g. title, language, genre)
- Information about when when each film was rented and returned
- Metrics such as how much revenue the rental generated
The data has been collected in a transactional database which isn’t structured to be optimised for reporting and analytics. In the first video in our data warehouse automation series, we mentioned that a star schema is the best data structure to support analytics.
Building a Data Warehouse with Ajilius
In this exercise, we’re going to transform the transactional data in the DVD rental database to a star schema. This will allow quick and easy analysis of the rental transactions at the DVD rental company.
We will build the following:
- Fact table containing metrics such as how long each film was rented for and how much revenue was generated
- A series of dimensions allowing this data to be sliced and diced by staff member, customer, store, film and date
The process we’ll go through is as follows:
- Create a destination database for the warehouse in Microsoft SQL server and connect the destination data warehouse to Ajilius
- Create a connection to the source database, which is the Sakila DVD rental database in MySQL
- Extract data from the source database to the loading tables
- Transform the data from the loading tables into the staging tables
- Create dimension and fact tables
- Create relationships between the dimensions and fact table to create a star schema and then export the metadata to Yellowfin to allow immediate analysis of the data
Summary of Timings
The table below summarises the time it took for each of the components of the data warehouse to be completed. All in all, the data warehouse was created in less than 90 minutes!
Stay tuned for future videos in our data warehouse automation series where we’ll use the Ajilius Data Warehouse Automation platform to further build upon the data warehouse we have just created.