Types of DWH systems

There are mainly 2 types of DWH systems.

                         I. EDW (Enterprise Data Warehouse):

                                          It contains the historical business data at the enterprise level to support the business needs of top management in the organization.

      -  Contains data drawn from multiple operational systems.

      -  Supports time- series and trend analysis across different business areas.

      -  Can be used to populate data marts.

-  Can be used for everyday and strategic decision making.

                             

                        II.  Data Marts:

                                       A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject, which may be distributed to support business needs.

     -   Subset of enterprise data warehouse.

     -   Organized around a single business process.

     -   May or may not contain aggregates.

     -   Less expensive and much smaller than a full blown corporate data warehouse.

What is Data Acquisition

Data Acquisition means Extraction, Transformation & Loading.

Here we will extract data from different sources like COBOL, ERP, Operational etc and bring into our Staging Area. Staging Area is a temporary storage area. From Staging Area we will load data into DWH or DM’s.

§  Data acquisition process is defined with Data extraction, Data transformation and Data loading.

                                I.Data Extraction:

                                             It is a process of reading the data from different sources like Operational sources, EPR systems, COBOL files, Flat files etc.

                              II.Data Transformation:

                                             It is a process of transforming data from one format to required business format. In Data transformation we are having 4 types.

                                                      i.Data Merging: It is a process of integrating the data from similar sources with the         

                                                                                 similar structure and data type.

                                                       ii.Data Cleansing: It is a process of identifying and changing the inconsistencies and

                                                                                     in accuracies.

                                                      iii.Data Scrubbing: It is a process of deriving new definitions from existing source

                                                                                     definitions.

                                                     iv. Data Aggregation: It is a process of where multiple detail values are summarized  

                                                                                      into a single summary values typically numeric like Sum,

                                                                                     Average, Min, Max etc

Dimensions

          Dimension Tables:

·         The dimension tables contain attributes (or fields) used to constrain and group data when performing data warehousing queries.

·         In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions.

·         For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt.

5.       Types of Dimensions Tables:

                    I. Conformed Dimension: The dimension that is shared across multiple fact tables. At the most basic level, conformed dimensions mean the exact same thing with every possible fact table to which they are joined. The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts. Ex: Time Dimension.

                  II.Junk Dimension:  Junk dimension is just a dimension that stores unwanted attributes. A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.

                III. Degenerated Dimension: In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.

                IV.  Slowly Changing Dimension: Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule. It’s further classified into 3 types.

·         SCD Type 1: This type of dimension table maintains the latest or current data.

·         SCD Type 2: This type of dimension table maintains complete history.

·         SCD Type 3: This type of dimension table maintains partial history.

Fact Table

            Fact Tables:

 A fact table contains composite keys (More than one key) where each candidate key is a foreign key to the dimension table.

 A fact table contains facts. In DWH, facts are generally numeric.

 A measure is a numeric attribute of a fact, representing the performance or behavior of the business relative to dimensions.

 A fact table contains the fact information at the lowest level granularity.

 The level at which fact information stores in a fact table is called as Fact Granularity or Grain of fact.

 A fact table can contain fact information either in 1NF or 2NF or 3NF. (NF: Normalization Form).

 To provide the meaningful business context to the facts design the dimension tables with a de-normalized business information.

2.         Types of Fact Tables:

                    I. Additive Fact table:

 A fact which can be summed up for any of the dimensions available in the fact table is called as Additive fact.

                  II.  Semi Additive Fact table:

 A fact which can be summed up for few dimensions but not for all the dimensions present in the fact table.

                III.  Non Additive Fact table:

 A fact which cannot be summed up for any of the dimensions available in the fact table.

               IV.    Fact less Fact Table:

 A fact which contains only Keys but not measures.

3.       Types of Facts:

                    I. Accumulative Fact Table:

 Generally these fact tables describe what has happened over the period of time. A cumulative fact table contains Additive or Semi additive facts. Ex: Transactional fact table, Orders fact table.

                  II. Snap shot Fact Table:

 This type of fact table describes the status of things at a particular instant of the time.

Schema

     Star Schema:

A star schema is a logical database design which contains a centrally located fact table surrounded by at least one or more dimension tables.

 A Fact table contains composite keys (More than one key) where each candidate key is a foreign key to the dimension table.

The facts that the data warehouse helps analyze are classified along different dimensions:

·         The fact table holds the main data. It includes a large amount of aggregated data, such as price and units sold. There may be multiple fact tables in a star schema.

·         Dimension tables, which are usually smaller than fact tables, include the attributes that describe the facts. Often this is a separate table for each dimension. Dimension tables can be joined to the fact table(s) as needed.

·         Dimension tables have a simple primary key, while fact tables have a set of foreign keys which make up a compound primary key consisting of a combination of relevant dimension keys.

 Example: Fact.Sales is the fact table and there are three dimension tables Dim.Date, Dim.Store and Dim.Product. Each dimension table has a primary key on its PK column, relating to one of the columns (viewed as rows in the example schema) of the Fact.Sales table's three-column (compound) primary key (Date_FK, Store_FK, Product_FK). The non-primary key [Units Sold] column of the fact table in this example represents a measure or metric that can be used in calculations and analysis. The non-primary key columns of the dimension tables represent additional attributes of the dimensions (such as the Year of the Dim.Date dimension).

1.       Snow Flake Schema:

 In a Star schema database design, if the dimension table is split into a one or more dimension tables which results in Normalization. Since the database design looks like a snow flake. Hence it is known as Snow flake schema.

  Generally these types of schema designs are not recommended for the warehouse implementations because dimension tables results in Normalization and decrease the performances.

                  The snowflake schema is similar to the star schema. However, in the snowflake schema,
                   dimensions are normalized into multiple related tables, whereas the star schema's dimensions are
                   denormalized with each dimension represented by a single table

·         The advantages and disadvantages of snow flake schema are given below.

1.       Galaxy Schema:

  Sophisticated applications may require multiple fact tables to share dimension tables. This kind of schema 
  can be viewed as a collection of stars, and therefore it's called a Galaxy Schema or a Fact Constellation. As
  we see in the two star schemas as above, the two fact tables, sales table and purchase table are now
  sharing both the 'product' and 'time' dimension tables. Therefore we decide to choose Galaxy Schema as
  the model for our data warehouse, which is displayed as follow:

   Before applying Galaxy schema:

  After applying Galaxy schema:

Data Warehouse Concepts

• A DWH is a historical database because the database contains many years of historical business data for Decision making purpose.
• A data warehouse is a repository (collection of resources that can be accessed to retrieve information) of an organization's electronically stored data, designed to facilitate reporting and analysis. In simple form data warehouse is a collection of large amount of data.
• A DWH system is designed to read the business data for business analysis processing but not for Transactional processing. Hence it is called as a Read only database.
• A DWH is designed to take the decision. Hence it is also known as DSS (Decision Supportive System).

1.   The fathers of DWH are W.H. Inmon & Ralph Kimball. W.H.Inmon defined the DWH as

Time Variant, Non Volatile, Integrated and Subject Oriented.

                                I.Time Variant:

                        In order to discover trends in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive. A data warehouse's focus on change over time is what is meant by the term time variant.

                          A business user can analyze the business data in the warehouse to the different time periods like Year, Quarter, Month, and Weeks etc.

                              II. Non Volatile:

                        Nonvolatile means that, once entered into the warehouse, data should not change. This is logical because the purpose of a warehouse is to enable you to analyze what has occurred. The data that is present in the DWH is Static.

                            III. Integrated:

                        Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.

                            IV. Subject Oriented:

                        Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case makes the data warehouse subject oriented.

2. 

1. Staging Area:

   - A storage area and set of process that clean, transform, combine, removing duplicate, archive and prepare source data for use in data warehouse.

   -  It accepts data from different sources.

   - The structure is closer to the Operational Systems rather than the DW.

   - Data arriving at different point of time is merged and then loaded into the DW.

   - Usually does not maintain history; only a temporary area.

2.  Types of DWH approach:-

                         I. Top – Down approach:

                                                    According to W.H.Inmon, we need to develop the enterprise DWH system and then from the EDW develop subject oriented databases called Datamarts according to the business needs.

                              II. Bottom – Up approach:

                                                   According to Ralph Kimball, 1^st develop the datamarts according to business needs and then integrate all datamarts into EDW.

3