MSBI -- SSAS -- Part-7

Fact Table and Types:

Fact Table:-

                       The Table which contains Day-to-Day business Transaction data is known as Fact Table.

 Basically Fact Table contains 2 kinds of data.

1.     Key Session data

2.     Measure Session Data

Types of Fact Table:

                                                Following are the types of Fact Tables.

1.     Transaction Fact Table.

2.     Periodic Snapshot Fact Table.

3.     Accumulating Snapshot Fact Table.

Transaction Fact Table:-

                                           Transaction Fact Table contains more detailed level of information. Each Transaction Line Item will take place in Fact table.

 

                                          The above table contains each Day level Transaction records.

Periodic Snapshot Fact table:-

                                          In a Fact Table, if we maintain data with somewhat aggregate level by rolling up in detailed transaction data for period of time. Such kind of Fact Table is known as Periodic Fact Table.

 

  

                                        The above table contains each Month level aggregated records.

Accumulating Snapshot Fact Table:-

                                                               In a Fact Table, if we maintain process level data with one record from Process begin and Process end by updating the Fact table data those kind of Fact table is known as Accumulating Snapshot Fact Table.

 

                                                             The above table contains only one record which is aggregated from start of the process to end of the process.

MSBI -- SSAS -- Part-6

Dimension Table and Types

 Dimension Table:

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

Types of Dimension Table:

                               Following are the types of Dimension Tables available in Data warehousing.

1.     SCD (Slowly Changing Dimension)

2.     Conformed Dimension

3.     Roll Playing Dimension

4.     Junk Dimension

5.     Bridge Dimension

6.     Degenerative Dimensions

1.   SCD (Slowly Changing Dimension)

                             As we know that, the dimension table data will not change frequently. But after some time the dimension data also be changes, during that time we need to capture the changes and update into the target dimension table, this is known as SCD.

2.   Conformed Dimension

                                                    In a Dimension Modeling Schema, if one Dimension table is shared by more than one Fact Table, such a type of Dimension is known as Conformed Dimension.

3.   Roll Playing Dimension

                                                    In a Dimension Modeling Schema, if there are more number of references to the Fact Table from a particular Dimension Table, Such type of Dimension Table is known as Roll Playing Dimension Table.

4.   Junk Dimension

                                        In a Dimension Modeling Schema, if the Dimension Table maintains Boolean kind of information like Yes/No or True/False. Such kind of Dimension table is known as Junk Dimension.

5.   Bridge Dimension

                                     In a Dimension Modelling Schema, The Bridge table is an intermediate table which is used to handle Many-to-Many relationships.

Many - to - Many Relationship:

Explanation:             

                         There is a possibility of more than one reason to cancel an order and also one reason will be responsible to cancel more orders. Such kind of relationship is called as Many-to-Many Relationship. To handle these kind of situations we have to use Bridge Table.                   

6.   Degenerative Dimensions

                                                            It is a kind of Dimension Table which is not physically present in the database. With one Fact table attribute, we can get all the descriptive information from other Dimension Tables.
For an Example:

In the above table, by using INV001 Invoice Number we can get all the Dimension informations like Product , Order , Date and Customer Details.

MSBI -- SSAS -- Part-5

 Star-Schema and Snow Flake Schema

    Schema:       

             Logical representation of database Object is called schema.

1. Star Schema:

In a Dimensional Modelling Schema, if all the dimension tables are directly connected to the centralized Fact Table and no Dimension Tables are further normalized such a type of schema is known star schema.

2.     Snowflake Schema:

In a Dimensional Modelling Schema, any one or more Dimension Tables are further normalized and normalized child dimension will not directly connected to Fact Table and it is connected to its Parent Dimension Table, Such a type of Schema is known as Snow flake Schema.

MSBI -- SSAS -- Part-4

What is Data Source View:

                                                              Data Source View is a logical representation of the Database Objects. This means that we can import the required database objects for our cube.

MSBI -- SSAS -- Part-3

What is Impersonation in Data Source:

When we Design and deploy the cube OR Process the cube, The SSAS Engine needs to be interact with Source Database to refresh the cube with latest underlying data.

There are four types of impersonation modes as follows:

         





















  a) Use a specific Windows user name & password: 

                            A domain user account created specifically for this purpose. This is the best practice because all the impersonation account need is a read access to the source database.

b) Use the service account:

            The SSAS service account usually set as a powerful domain account which has access to multiple SQL Servers. Using the SSAS service account gives you access to many databases in  SQL Server. You don’t have to worry about which account have access to which databases as a) above

c) Use the credential of the current user:
                        SSAS service will use your windows account to access the database. When the cube is under development this is handy if you have access to the dev database. This way you can start the development straight away, without waiting for any account to be set-up. If you handover the BIDS project to your colleague developer however, your colleague needs to have access to that source database too. Otherwise he can’t process the cube.
Note that if you do this, when the cube is moved to production, you will need to change the account to the proper production account like a) above, or use the service account like b) above. As a developer, your Windows account does not normally have access to production databases. I’ll be surprised if it does because it creates a security issue.

d) Inherit:
           SSAS will use whatever specifies on the Database Properties. To view the Database Properties used for this, in SSMS, right click a SSAS database and choose Properties. Under Security Settings, there’s Data Source Impersonation Info. This is what Inherit uses.

MSBI -- SSAS -- Part-2

What is Data Source:

                                     A Data Source is nothing but a connection information like which server and database are going to use to generate the cube. 
                                    Data Source is used by SSAS to connect to the underlying database to load the data into SSAS during processing. 
                                    A Data Source primarily contains the following information.

·         Provider

·         Server Name

·         Database Name

·         Impersonation Information

 SSAS Supports both .Net and OLE DB Providers. Following are some of the major sources supported by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other relational databases with the appropriate OLE DB provider.

Data Source

MSBI -- SSAS -- Part-1

MSBI -- SSAS-CUBE -- Part-1

What is SSAS:

                   SSAS Stands for SQL Server Analysis Services. SSAS is a one of the OLAP(Online Analytical Solution) and Data Mining Tool. Which is used to Create the Cube.

What is CUBE:

Cube is a Multi Dimensional Object, which stores measures data across different dimensions in the form of cells.

Advantage/Usage of Cube:

1.     More Flexible for Adhoc Reports

2.     Using Cube, we can reduce the query processing time for end user applications

3.     Cube contains the Pre-Calculated aggregated data in cells.

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.