MSBI : Data Warehouse Design Concept

Data Warehouse Design can be done by two most common approaches,There are two persons as “Gods of BIDW”: Ralph Kimball and William Inmon.
These two persons given some important Data warehouse concepts which need to be followed as standards for BIDW.

- Ralph Kimball provided “Bottom – Up approach”
- Bill Inmon provided “Top – Down approach”

In simple words definition, A data warehouse is a central repository for all an organization's data. The goal of a data mart, is to meet the particular demands of a specific group of users within the organization, such as human resource management (HRM). Generally, an organization's data marts are subsets of the organization's data warehouse.

Data warehouse (DW) Vs Data Mart (DM)

Data Warehouse:
- Holds multiple subject areas
- Holds very detailed information
- Works to integrate all data sources
- Does not necessarily use a dimensional model but feeds dimensional models.
Data Mart:
- Often holds only one subject area- for e.g. Finance, HR, IT or Sales
- May hold more summarised data (although many hold full detail)
- Concentrates on integrating information from a given subject area or set of source systems
- Is built focused on a dimensional model using a star schema.

Bill Inmon

says “A Data warehouse is a Subject-oriented, Integrated, Time-variant and Non-volatile collection of Data in support of Management’s decision making process”.

It means, First make a normalized data model then split into data marts based upon subject areas.Dimensional data marts are created only after the complete data warehouse has been created.
To built data Warehouse it will take more time compare to Ralph Kim-ball approach.

Ralph Kimball

says "The data warehouse is nothing more than the union of all the data marts".

It means, First create data marts and then make a schema relation. Dimensional data marts (DI mention and Fact table) provide a narrow view into the organizational data, then they related by start or snow flex schema based on requirement.
To built data ware house it will take less time compare to Bill Inmon approach.

MSBI : Data Warehouse

What is a Data Warehouse?

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but can include data from other sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources.

There are two persons as “Gods of BIDW”: Ralph Kimball and William Inmon.
These two persons given some important Data warehouse concepts which need to be followed as standards for BIDW.
- Ralph Kimball provided “Bottom – Up approach”
- Bill Inmon provided “Top – Down approach”

In the world of BIDW, William Inmon provided most popular definition: “A Data warehouse is a Subject-oriented, Integrated, Time-variant and Non-volatile collection of Data in support of Management’s decision making process”.

What makes a Data warehouse?

1. Subject-Oriented : Data warehouse can be used to analyse a specific subject area. e.g – In an Organization, Subject area can be different Depts like HR, IT, Sales and many more. So, it can be used to analyse particular Dept like HR or IT.
2. Integrated : With Integrated we mean data from heterogeneous data sources is integrated in Data warehouse. e.g – Excel Sheet Source stores date in different format and MySQL DB source stores date in another format, but in a Data warehouse, dates will be stored in a single format only.
3. Non-volatile : Nonvolatile means that, once entered into the data warehouse, data should not change. This is logical because the purpose of a data warehouse is to enable you to analyse what has occurred.
4. Time-Variant : Data warehouse deals with historical data only. It acts as a pit where Transactional data is dumped every day. We can retrieve data which is 6 months, 12 months old or even more than this from a data warehouse. For example – You hold a SBI bank account and updated your contact details. So, Transaction system can only provide you the current contact details whereas Data warehouse holds all the contact details associated with your account (Current and Previous both).
This is very much in contrast to On-Line Transaction Processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive.

In addition to a relational database, a data warehouse environment can include an Extraction, Transformation, and Loading (ETL) solution, statistical analysis, reporting, data mining capabilities, client analysis tools, and other applications that manage the process of gathering data, transforming it into useful, actionable information, and delivering it to business users.

MSBI Introduction

Microsoft Business Intelligence Introduction

BI stands for Business Intelligence, it's a set of techniques which helps in gathering, storing, analysing and come up with best information from huge/big data which helps to business decision making.
In other words, BI is Integration of data from different data sources, then Analyse the Integrated data for BI engagement and Representation of the analysed result in the form of reports, dashboards, Charts etc. which helps to understand the data easily.

MSBI stands for Microsoft Business Intelligence, The MSBI suite is composed of tools which helps in providing best solutions for Business Intelligence and Data Mining Queries. This tool uses Visual studio along with MS SQL server. It empower users to gain access to accurate and up-to-date information for better decision making in an organization. It offers different tools for different processes which are required in Business Intelligence (BI) solutions.

MSBI is divided into 3 categories:-

1. SSIS – SQL Server Integration Services – Integration tool
2. SSAS – SQL Server Analytical Services – Analysis tool
3. SSRS – SQL Server Reporting Services – Reporting tool

Microsoft Business Intelligence (MSBI) Architecture

Example: Lets take an example to understand how BI beneficial for an organization: Let’s understand above picture by taking an example of an organization. Let’s take the example :- Adidas. We have outlets of Adidas in most parts of India. Every outlet stores their customer data in their respective database and it’s not mandatory that every outlet is using the same database. Some outlets may have Access database, some might be using Excel sheet or some stores prefer storing their data in simple text files, oracle etc.

Before proceeding ahead with our explanation, we should know “what OLTP is”? It stands for Online Transaction Processing. These are basically the online transactions which is heavy DML operation (Insert, Update, Delete) is performed on database at every Adidas outlet.

Phase 1- “SSIS” After storing daily data of the customers who visited Adidas outlet at different stores, the data is then integrated and saved it in a centralized database. This is done with the help of OLTP component of MS SQL Server. Integration means merging of data from different data stores (i.e. it may be a flat file, Excel sheets, Access, Oracle, etc.), refreshing data in data warehouses and to cleanse data (e.g. -Date format may be different for different outlet’s database, so same format is made to make it even) before loading to remove errors. Now, you must be clear with the Integration concept.

Phase 2- “SSAS” Next step is to analyze the stored centralized data. This huge data is then divided into Data Marts on which analytic process is carried on. Analysis services use OLAP (Online Analytical Processing) component and data mining capabilities. It allows to build multi-dimensional structures called CUBES to pre calculate and store complex aggregations, and also to build mining models to perform data analysis which helps to identify valuable information like what are the recent trends, patterns, dislikes of customers. Business analyst then perform data mining function on multi dimensional cube structure to look data from different perspectives. Multi Dimensional analysis of huge data completes.

Phase 3- “SSRS” Now, represent analysis graphically so that an organization (Adidas) can make effective decision to enhance their revenue, gain maximum profit and to reduce time wastage. So, this is done in forms of Reports, Scorecards, Dashboards, Excel workbooks, etc. This data reports will tell the organization what is the revenue of Adidas in specific time at specific place, where they captured the market, in which part they are lacking and needs to boost up and many other things which end users wish to look into. This reporting is done with a tool SQL Server Reporting Services and completes.