Difference between Data warehouse and Business Intelligence

Difference between Data warehouse and Business Intelligence 
Data warehouse is a way of storing data and creating information through leveraging data marts. Data marts are segments or categories of information and/or data that are grouped together to provide 'information' into that segment or category. Data warehouse does not require Business Intelligence to work. Reporting tools can generate reports from the DW.

Business Intelligence is the leveraging of DW to help make business decisions and recommendations. Information and data rules engines are leveraged here to help make these decisions along with statistical analysis tools and data mining tools.
The Business Intelligence tool that we are going to learn is SSIS which uses SQL as the Backend, Sonow let us learn the SQL BasicsDifference between Data warehouse and Business Intelligence
Data warehouse is a way of storing data and creating information through leveraging data marts. Data marts are segments or categories of information and/or data that are grouped together to provide 'information' into that segment or category. Data warehouse does not require Business Intelligence to work. Reporting tools can generate reports from the DW.

Business Intelligence is the leveraging of DW to help make business decisions and recommendations. Information and data rules engines are leveraged here to help make these decisions along with statistical analysis tools and data mining tools.
The Business Intelligence tool that we are going to learn is SSIS which uses SQL as the Backend, Sonow let us learn the SQL Basics

What is a Dimension Table and Types of Dimension table

What is a Dimension Table:

If a Table contains “primary keys” and it gives the detailed information about business then such a table is called dimension table.

A Dimension table is a table which holds a list of attributes or qualities of the dimension most often used in queries and reports.

E.g. “Store” dimension can have attributes

Street,

Block Number,

City,

Region,

Country where it is located in addition to its name.

Dimension tables are ENTRY POINTS into the fact table.

1. The number of rows selected and processed from the fact table depends on the conditions (“WHERE” clauses) the user applies on the dimensional attributes selected.

2. Dimension tables are typically DE-NORMALIZED in order to reduce the number of joins in resulting queries. Dimension table attributes are generally STATIC, DESCRIPTIVE fields describing aspects of the dimension Dimension tables typically designed to hold IN-FREQUENT CHANGES to attribute values over time using SCD concepts Dimension tables are TYPICALLY used in GROUP BY SQL queries Every column in the dimension table is TYPICALLY either the primary key or a dimensional attribute Every non-key column in the dimension table is typically used in the GROUP BY clause of a SQL Query.

Types of Dimension table:

1.Conformed Dimension: 
If a dimension table is shared by multiple fact tables then that dimension is known as conformed dimension table.

2.Junk dimension: 
Junk dimensions are dimensions that contain miscellaneous data like flags, gender, text values etc and which are not useful to generate reports.

3.Slowly changing dimension: 
If the data values are changed slowly in a column or in a row over the period of time then that dimension table is called as slowly changing dimension. 

Ex: Interest rate, Address of customer etc 

There are three types of SCD’s:

Type – 1 SCD: A type-1 dimension keeps the most recent data in the target.

Type – II SCD: keeps full history in the target. For every update it keeps a new record in the target.

Type – III SCD: keeps the current and previous information in the target (partial history).

Note: To implement SCD2 we use Surrogate key.

Example1:

Example2:

        Surrogate key:

1. Surrogate Key is an artificial identifier for an entity. In surrogate key values are generated by the system sequentially (Like Identity property in SQL Server and Sequence in Oracle). They do not describe anything.

2. Joins between fact and dimension tables should be based on surrogate keys

3. Surrogate keys should not be composed of natural keys glued together

4. Users should not obtain any information by looking at these keys

5. These keys should be simple integers

6. Using surrogate key will be faster

7. Can handle Slowly Changing dimensions

4.Degenerated dimension: 
A degenerate dimension is data that is dimensional in nature but stored in a fact table. For example, if you have a dimension that only has Order Number and Order Line Number, you would have a 1:1 relationship with the Fact table. Therefore, this would be a degenerate dimension and Order Number and Order Line Number would be stored in the Fact table. Fast Changing Dimension: A fast changing dimension is a dimension whose attribute or attributes for a record (row) change rapidly over time. Example: Age of associates, Income, Daily balance etc.

5.Roll playing dimension:

One dimension plays multiple roles to retrieve data from fact tables.

6.Dirty dimension:

In this dimension table records are maintained more than once by the difference of non-key attributes.

Surrogate key in Datawarehousing

Surrogate key:

1. Surrogate Key is an artificial identifier for an entity. In surrogate key values are generated by the system sequentially (Like Identity property in SQL Server and Sequence in Oracle). They do not describe anything. 

2. Joins between fact and dimension tables should be based on surrogate keys 

3. Surrogate keys should not be composed of natural keys glued together 

4. Users should not obtain any information by looking at these keys 

5. These keys should be simple integers 

6. Using surrogate key will be faster 

7. Can handle Slowly Changing dimensions well

Types of Facts

Types of Facts:

 There are three types of facts: 

1. Additive - Measures that can be added across all dimensions. 

2. Semi Additive - Measures that can be added across few dimensions and not with others. 

3. Non Additive - Measures that cannot be added across all dimensions.

Data warehouse ETL Products

Data warehouse ETL Products:

1. CODE BASED ETL TOOLS
2. GUI BASED ETL TOOLS

Code Based ETL Tools: In these tools, the data acquisition process can be developed with the help of programming languages.
1. SAS ACCESS
2. SAS BASE
3. TERADATA ETL TOOLS
    a. BTEQ (Batch TEradata Query)
    b. TPUMP (Trickle PUMP)
    c. FAST LOAD
4. MULTI LOAD Here the application Development, Testing, and Maintenance is costlier process compared to GUI based tools.

GUI based ETL Tools:
1. Informatica
2. DT/Studio
3. Data Stage
4. Business Objects Data Integrator (BODI)
5. AbInitio
6. Data Junction
7. Oracle Warehouse Builder
8. Microsoft SQL Server Integration Services
9. IBM DB2 Ware house Center

Star Schema and Snowflake Schema

Star Schema and Snowflake Schema:

Schema : A schema is a collection of objects including tables, views, indexes and synonyms

Star Schema: A Star schema is a logical database design which contains a centrally located fact table surrounded by dimension tables. The data base design looks like a star. Hence it is called as “Star schema”.

The star schema is also called "star-join schema".

Advantages of Star schema:
1. Star schema is very easy to understand , even for non technical business managers.
2. Provides better performance and smaller query times.
3. Star schema is easily understandable and will handle future changes easily.

Snowflake Schema: 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. A large dimension table is split into multiple dimension tables. 

E-R Modeling and Dimensional Modeling

E-R Modeling and Dimensional Modeling:

E-R Modeling :In this E-R Modeling technique for the purpose of the retriving  data, we need to apply "More" number of joins .

DWH projects we need to deal with Current data and History data  for that reason all tables are having huge amount of data.On huge amount of data tables if we are apply more number of Joins Query Performance completely "degrade".

Dimensional Modeling:In this Dimensional Modeling technique for the purpose of the retriving  data, we need to apply "Less" number of joins .

DWH projects we need to deal with Current data and History data  for that reason all tables are having huge amount of data.On huge amount of data tables if we are apply Less number of Joins Query Performance "Increased".