Database Systems with SQL - Full Course - Database Systems - Data Warehouses and Data Mart - ETL and Star Schema Explained - APPFICIAL

Operational and analytic data Operational Data is exactly what it sounds like - data that is produced by your organization's day to day operations. The data is volatile (in memory), and detailed. Analytical Data is used to make business decisions, as opposed to recording the data from actual operational business processes. The data is detailed, could be a summary or history of data, and the scope can be company-wide. Data warehouses A data warehouse is a type of database that is optimized for analytics rather than business operations. A data mart is a subset of the data warehouse and is usually oriented to a specific business line or team. Extract, transform, load There is a 3 step process called extract-transform-load (ETL) which extracts the data from different source systems, then transforms the data (like applying calculations, concatenations, etc.) and finally loads the data into the Data Warehouse system. Extract: Retrieves raw data from an unstructured data pool and migrates it into a temporary, staging data repository Clean/Transform: Cleans data extracted from an unstructured data pool, ensuring the quality of the data prior to transformation. Structures and converts the data to match the correct target source Restructure/Load:  Loads the structured data into a data warehouse so it can be properly analyzed and used There are ETL tools that companies use to help make this process easier. Tools include PowerCenter, SQL Server Integration Services, or Oracle Data Integrator. Dimensional design star schema is a modeling approach that is widely adopted by relational data warehouses. It makes analytic queries easier. It requires modelers to classify their model tables as either dimension or fact. Fact table – stores observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc. A fact table contains dimension key columns that relate to dimension tables, and numeric measure columns. Dimension table - describe business entities. It is a structure that categorizes facts and measures in order to enable users to answer business questions. The primary key of a fact table is the composite of all foreign keys referencing dimension tables. Dimension hierarchies organizes data at various levels. It show relationships are represented graphically in a collapsible hierarchy diagram. The levels below the database name are dimensions, and the levels below each dimension are members. Data and time dimensions Historical data needs to also track the date and time. The date dimension table is for the day, and time dimension table is for the time of the day in minutes. In a fact table, foreign keys establish the date and/or time. Fact tables contain foreign keys StartDateID and EndDateID for referencing date and time dimensions, to establish the time of a fact. The EndDateID for current rows is set to some time much later in the future until the real time is set. This method of adding start and end times to the fact table as foreign keys is called the type 2 design for slowly changing dimensions. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!