Tekslate Data Warehouse Free Tutorial
Introduction to Data Warehouse What is a Data Warehouse? According to Inmon, famous author for several data warehouse books, "A data warehouse is a subject oriented, integrated, time variant, non volatile collection of data in support of management's decision making process". Example In order to store data, over the years, many application designers in each branch have made their individual decisions as to how an application and database should be built. So source systems will be different in naming conventions, variable measurements, encoding structures, and physical attributes of data. Consider a bank that has got several branches in several countries, has millions of customers and the lines of business of the enterprise are savings, and loans. The following example explains how the data is integrated from source systems to target systems.
Data Warehouse Architecture
Data Warehouse & Data Mart A data warehouse is a relational/multidimensional database that is designed for query and analysis rather than transaction processing. A data warehouse usually contains historical data that is derived from transaction data. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources. In addition to a relational/multidimensional database, a data warehouse environment often consists of an ETL solution, an OLAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.
Types of Data Warehouses There are three types of data warehouses Enterprise Data Warehouse ODS(Operational Data Store) Data Mart Enterprise Data Warehouse - An enterprise data warehouse provides a central database for decision support throughout the enterprise. ODS(Operational Data Store) - This has a broad enterprise wide scope, but unlike the real entertprise data warehouse, data is refreshed in near real time and used for routine business activity. Data Mart - Datamart is a subset of data warehouse and it supports a particular region, business unit or business function.
Types of Schema's in Data Warehouse There are four types of schemas are available in data warehouse. Star Schema Snow Flake Schema Galaxy Schema Fact Constellation Schema Out of which the star schema is mostly used in the data warehouse designs. The second mostly used data warehouse schema is snow flake schema When we consider an example of an organization selling products throughtout the world, the main four major dimensions are product, location, time and organization. Dimension tables have been explained in detail under the section Dimensions. With this example, we will try to provide detailed explanation about STAR SCHEMA.
Popular Database Vendors Database - RDBMS There are a number of relational databases to store data. A relational database contains normalized data stored in tables. Tables contain records and columns. RDBMS makes it easy to work with individual records. Each row contains a unique instance of data for the categories defined by the columns. RDBMS are used in OLTP applications(e.g. ATM cards) very frequently and sometimes datawarehouse may also use relational databases. Please refer to Relational data modeling for details to know how data from a source system is normalized and stored in RDBMS databases.
ETL Concepts in Data Warehouse ETL Concepts Extraction, transformation, and loading. ETL refers to the methods involved in accessing and manipulating source data and loading it into target database. The first step in ETL process is mapping the data between source systems and target database(data warehouse or data mart). The second step is cleansing of source data in staging area. The third step is transforming cleansed source data and then loading into the target system.
Note that ETT (extraction, transformation, transportation) and ETM (extraction, transformation, move) are sometimes used instead of ETL
And Also Issues Interview Questions How to’s For More Data Warehouse Free Tutorials Please Visit: http://bit.ly/143T8Ym