MSBI -- Data Warehousing Concepts -- Part-6

Dimension and Fact Table

Dimension Table:

1.     The table which stores Master data OR Organizational level data is known as Dimension table.

2.     Each record in this table gives a complete description of a particular entity.

3.     The Dimension Table data will not change frequently but over a period of time dimension table data also changes.

                 

Fact Table:

1.     The table which stores actual day-to-day business transactional data is known as Fact table.

2.     Each Record in this table is a business transaction at particular point of time.

                                  

MSBI -- Data Warehousing Concepts -- Part-5

                                    As we know that Data warehouse is nothing but a Collection of Data.

  Based on the behaviour of the data, it has been classified into two types.

Types of Data:

                            Any Organization running business has two kinds of data

1.   Master Data

2.   Transaction Data

Master Data:

                          Master data is nothing but a Organizational or High Level Data

                           For Example:

1.     Store

                                                

2.     Customer

3.     Date

4.     And Product so on….

Transactional Data:

                                   Transactional Data is nothing but a Day-to-Day business transaction details.

For Example:

1.     Each store wise Sales Amount Information.

2.     Each employee wise Salary Details.

                               

MSBI -- Data Warehousing Concepts -- Part-4

Data Warehouse Architecture:

MSBI Architecture:

MSBI -- Data Warehousing Concepts -- Part-3

What is BI(Business Intelligence):

                               The Process of Converting Raw-data into useful information & set of tools which is used to do this, Is called Business Intelligence

Any BI Process includes two Phases:

   1.  ETL (Extract, Transform and Load):

Ø Which means Extracting data from different source systems

Ø Transforming the data by applying business logic & formatting them

Ø And finally load the data into Data warehouse

Different ETL Tools in the Market:

·        SSIS(SQL Server Integration Services)

·        Informatica

·        Data Stage

·        Abinitio

   2.  Reporting and Analysis Phase:

             Once the data is ready in the Data Warehouse tables, then we can generate the reports by reading the data from DWH Data.

Different Reporting & Analytic Tools in the Market:

·        SSRS(SQL Server Reporting Services)

·        SSAS(SQL Server Analysis Services)

·        Tableau

·        Cognos

·        Excel

·        Business Objects.

MSBI -- Data Warehousing Concepts -- Part-2

Why Data Warehouse:

§  Now-a-days most of the Organizations were suffering lack of information even they had a huge amount of Raw-Data.

§  With the current competitions, in order to get success in their business, they need to analyse their own business trends, by converting the raw-data which is not properly formatted into useful information.

§  As a process of it, Collect the data from different source systems & Convert into useful format & put it in a common place, which is nothing but a Data Warehouse.

§  Using the Data Warehousing data,we can generate useful information interns of Reports & Dashboards.

MSBI -- Data Warehousing Concepts -- Part-1

What is Data Warehousing:

             A Data Warehouse is 

1.     Subject-Oriented

2.     Integrated

3.     Time-Variant

4.     Non-Volatile 
                                        Collection of Data, which is used for effective decision making of organisations High & Middle Level Management.

1.   Subject-Oriented: 

                              In Data Warehouse we can store each subject related data separately in terms of Data marts.

DataMart: 

                     DataMart is a subset of Data Warehouse which deals with particular Department data.

2.   Integrated: 

                              Data Warehouse data is Integrated data from multiple source systems.

3.   Time Variant: 

                              Historical It is not possible to take Analysis with referring time.
Each Record in Data Warehouse will be related to particular point of time.

4.   Non-Volatile: 
                                  The Data Present in Data Warehouse is Non-Editable.