How MS BI drives Business Intelligence?. . . Part 2

As part of below architecture we are continuing rest of the important terms in BI Processes with MSBI Tools:

Centralized Database and Data Marts:

To answer the question of “Need all the data four completion of analytics”, we create a Data Warehouse out of different data sources.

To achieve this data warehouse from different data sources we need SSIS as MSBI Tool. Brief definitions for terms covered under this section are:

1. Data Warehouse:
   A complete database for all the data required in analytics for Organization, prepared out of different data sources having all types of data.
2. SSIS:
   MS BI tool which Extract-Transform- Load data from different data sources to one Data warehouse.
3. Data Marts:
   As per basic definition, Data marts are part or say subset of Data Warehouse which stores data specific to a category or department of Organization.
   But mostly in BI Practice we store all the data required in Data Warehouse (in case we don’t have a staging data base) and prepare a Data Mart with specific de-normalized structure of Dimensions and Facts tables.  So this Data Mart will have the immediate (and specific) data for cube preparation.

Cube:
A cube is multidimensional database to view facts with different perspectives. Dimensions are different perspective to view measures and facts in cube.
For example:

1. See last 5 years production (Dimension : Time and Fact: Sales)
2. See top selling cities (Dimension: City or Geography and Fact: Sales)
3. See revenue based on different category of products (Dimension: Product Category and Fact: Revenue)

So we use SSAS, Power Pivot and Tabular Model to create a cube out of Data Warehouse or Data Mart.

Let’s give a look to different terms under this section:

1. BISM (the cube):
   This is multidimensional database which holds data at aggregated level for different perspectives (dimensions). A cube relates and aggregate different tables from DW. So a readymade data for fast retrieval from huge lump (DW) is available in BISM Model.
2. SSAS:
   MS BI Tool for creating a cube from DW. Visual Studio provides Analysis Services Project to create Cubes which use DW as data source and build a multidimensional database on top of it.
3. Power Pivot:
   An Excel add on which attach an extra workbook (green in color ;)) with excel working as Data store. This data can be stored in form of Table and then specify relationship among the tables. And you are done. You can use this workbook as cube, which again provide you aggregated data for different perspectives.
4. BI Semantic Tabular:
   A new version of Power Pivot with Visual Studio Interface. I’ll suggest to look an old post for better understanding on differences from above two MS BI tools (post What is BISM).

Reporting:

Most important part, which prepare final outcome of BI Solutions. This is the part which an end user sees. We query Cubes for data input in the reports. We create these reports for better understanding of the analytics and it could be in any form as Metrics, Tables, Graphs, Charts, KPIs, Gauges, Scorecards, Dashboards etc.

1. SSRS:
   MS BI Tool which provide best solution for different kinds of reports. We get a lot of options for representing a report in different forms (charts, graphs, tables, matrices, KPIs, Bars, Range Charts, Gauges etc.)
2. Performance Point Services:
   A MS BI tool which earlier came integrated with SharePoint but now available individually. It provides rapid creation of PPS reports which could be in any form and at the same time forms can be changed just by right click. An easy tool for report generation along with flexibility of creating custom parts. Most of the scorecards and Dashboards come under this umbrella.
3. SharePoint:
   SharePoint is one of the remarkable tools by Microsoft but here we will talk about only its BI related stuff. It provides different reporting mechanism like integration of SSRS, PPS and Power View etc. Moreover BI solutions in MS BI are finally prepared on SharePoint. Every report, dashboard and scorecard is integrated in SharePoint under different pages of a Portal.
4. Scorecard:
   A form of report which display growth and goals. It compare the performance using KPIs (Key Performance Indicator) and use visual indicators for success, failure, effort, achievements etc.
5. Dashboard:
   A collection of reports and scorecards. It works as a container for different types of reports and pages.
6. Portals:
   A complete web portal dedicated to BI Engagement for an Organization. It covers different reports and all other BI related objectives like security (to authenticate and authorize). It’s actually a SharePoint website for Organization based on its BI needs.
7. Data Mining
   This is a different kind of reporting done on cube which is covered in SSAS. A form of predictive analysis based on historical data. Well we will discuss a lot more about this in upcoming posts.

How MS BI drives Business Intelligence?. . . Part 1

MSBI (Microsoft Business Intelligence), comprises set of tools which provide best nailing solutions for Business Intelligence engagements. These set of tools use Visual Studio interface and a part of SharePoint services along with SQL Server. MSBI offer different tools for different processes required in BI solutions.

Below is a general architecture of BI Projects and set of MS BI tools to facilitate the processes:

Basically division of MS BI tools is in three different categories here it covers all the area of BI solutions as mentioned in processes of BI Solutions (post What is BI).

Different abbreviations used in diagram are:

• OLTP	Online Transaction Processing
  DW	Data Warehouse
  DM	Data Mart
  BISM	Business Intelligence Semantic Model
  DMi	Data Mining

A brief intro to the different terms:

Different Sources:

An upper bound name for all the different kind of data sources an organization can possess. These data sources have data for various purposes and stored in different mediums like SQL Server Database, Oracle, MS Access, Excel, Text, Flat files, etc. It’s about the diversity of information stored.

For example:

1. Organization can have different branches across the globe and so data is spread at different locations
2. Organization can have different departments so type of information is different for each department
3. By the period of time, Organization can have different storage medium for data from legacy to new upcoming technologies for data storage.

Storage medium for data are required here (for e.g. SQL Server) but no other MSBI tool. In spite of this diversity of data in organizations, we need all the data for our completion of analytics.

We are covering other terms in next post as “How MS BI drives Business Intelligence?. . . Part 2”.