Traditional Data Architectures: Data Warehousing and ETL Systems

Data Architecture 2021    |    Beginner
  • 12 videos | 38m 56s
  • Includes Assessment
  • Earns a Badge
Likes 97 Likes 97
Data warehouses are actively used for business intelligence and, because they integrate data from multiple sources, are advantageous to simple databases in many instances. Considering modern companies often have ETL-based data warehousing systems, decision-makers need to comprehend how they operate and are appropriately managed. In this course, learn the necessary concepts and processes required to work with and manage projects related to data warehousing. Study data warehousing architectures and schemas and investigate some core data warehouse elements, such as dimension, fact tables, and keys. Furthermore, examine the extract, transform, and load (ETL) approach for working with data warehouses, specifying process flow, tools, and software as well as best practices. When you're done, you'll know how to adopt data warehousing and ETL systems for your business intelligence and data management needs.

WHAT YOU WILL LEARN

  • discover the key concepts covered in this course
    describe how a data warehouse is different from a database and how data warehouses are used for business intelligence
    name and define three main tiers of a data warehouse
    compare and contrast various data warehousing schemas, such as Star, Snowflake, etc.
    name use cases of dimension tables and define different types of dimensions and their granularity
    define fact table measures, describe how measures are added and loaded, and outline the steps for implementing a fact table in a data warehouse
  • describe how data warehouse keys work, specifying the importance of surrogate keys
    describe extract, transform, and load (ETL) functionality and specify how the movement between transactional OLTP databases and a data warehouse is performed and how to organize and design your extraction, transformation, and loading capabilities to keep your data warehouse up-to-date
    describe the ETL framework and it's three main components - extraction, transformation, and loading
    name and describe the most commonly used ETL tools and software
    specify best practices to be followed when dealing with ETL to perform operations as efficiently as possible
    summarize the key concepts covered in this course

IN THIS COURSE

  • 1m 35s
    Discover what a data warehouse is and explore data warehouse architecture. You’ll take a look at the two main schemas used in data warehousing, the star schema and the snowflake schema. You’ll then define what fact tables and dimension tables are. FREE ACCESS
  • 3m 54s
    Explore data warehousing for business intelligence. A data warehouse is a central repository to store data from multiple sources. Data warehouses are a fundamental pillar for forming business intelligence. Data warehouses are where fact tables and dimension tables used by BI tools are created. FREE ACCESS
  • Locked
    3.  Data Warehouse Architecture
    3m 55s
    Explore data warehouse architecture further. This architecture is a combination and amalgamation of historical data from multiple sources. There are a total of three approaches for building a data warehouse infrastructure: the single-tier approach, the two-tier approach, and the three-tier approach. FREE ACCESS
  • Locked
    4.  Data Warehousing Schemas
    3m 55s
    Take a look at data warehousing schema. A schema is a logical description of a database and its various elements and their attributes. Similar to databases, data warehouses also have schemas. The most common schema types used in a data warehouse are the star schema and the snowflake schema. FREE ACCESS
  • Locked
    5.  Dimension Table Use Cases
    3m 11s
    Learn about dimension table-use cases. A dimension table is a collection of references in a measurable table. Those measurable tables are fact tables. A data warehouse organizes descriptive attributes using a primary key, which contains a unique identifier for each dimension record. That primary key is embedded as a foreign key in the associated fact table. FREE ACCESS
  • Locked
    6.  Fact Tables in a Data Warehouse
    3m 15s
    Explore fact tables in data warehouses. A fact table is the table within a data warehouse that contains the primary key of the dimension table. Fact tables contain continuous measure values such as numerical data related to sales and finances. A fact table is used in the dimensional model in data warehouse design. FREE ACCESS
  • Locked
    7.  Keys in Data Warehouse Schemas
    3m 28s
    Take a look at the most common keys used in databases. These keys include primary keys, foreign keys, composite keys, candidate keys, and surrogate keys. Discover why the surrogate key is the best type of key to use in a data warehouse. In a dimension table, every row is identified by a unique value, generally known as the primary key. FREE ACCESS
  • Locked
    8.  What Is ETL?
    2m 56s
    Learn about ETL. ETL stands for Extract, Transform, and Load. It's the process of extracting the data from different source systems, then transforming the data through the use of rules, formulas, and algorithms, and finally loading the data into the data warehouse system. The ETL process requires active inputs from various stakeholders, including analysts, developers, testers, and executives. FREE ACCESS
  • Locked
    9.  What Is ETL, ETL Framework, and Process Flow?
    5m 11s
    Explore three main steps of the ETL process. These steps are extraction, transformation, and loading. First, data is extracted from the storage systems into the staging area. Before data can be moved to a new destination, it must be extracted from its sources. Structured and unstructured data are imported and consolidated into a single repository. FREE ACCESS
  • Locked
    10.  Extract, Transform, and Load (ETL) Tools
    3m 11s
    Learn about ETL tools. Effective ETL tools provide a visual flow of the system's logic. These tools offer a structured system design. They are designed to help populate a data warehouse. They provide a metadata-driven structure to the development team. ETL tools should provide functionality and practices for operating and monitoring ETL systems and production. FREE ACCESS
  • Locked
    11.  Extract, Transform, and Load (ETL) Best Practices
    3m 21s
    Take a look at ETL best practices. Explore nine of the best practices of using ETL: minimize data input, never cleanse all the data, maximize data quality, use incremental data updates, speed up query processing, try to automate as much as possible, use parallel processing, keep databases small and track progress. FREE ACCESS
  • Locked
    12.  Course Summary
    1m 3s
    Review what you’ve learned in this course. You’ve learned what a data warehouse is and learned the data warehouse architecture. You discovered the two main schemas used in data warehousing. You then defined and provided context on what fact and dimension tables are. You also delved into ETL processes and how they relate to data warehouses. FREE ACCESS

EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

YOU MIGHT ALSO LIKE

Course Database Fundamentals: Database Administration Basics
Course PL-300: Using Time Intelligence in Power BI
Book Enterprise Architect's Handbook: A Blueprint to Design and Outperform Enterprise-level IT Strategy

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Course SQL Developer
Likes 285 Likes 285  
Course Oracle Database 12c R2 SQL: Using Structured Query Language (SQL)
Likes 268 Likes 268  
Course Data Flow Implementation & ETL
Likes 56 Likes 56