Microsoft Business Intelligence

Business Intelligence

2014-04-17T16:00:00.000+05:30

What is Business Intelligence?

The Process of converting raw data into useful information using set of tools which helps an organisation for high level management to take effective decisions to improve the business Processes.

Any BI project includes following two processes:-

ETL Process (Extract Transformation Loading)
Reporting & Analysis Process

We will see the processes in detail in next section.

Linked Dimension Vs Conformed Dimension

2011-01-18T12:16:00.002+05:30

Linked Dimension: This dimension joins different fact tables from two or more cubes

Conformed Dimension: This dimension joins multiple fact tables with in a cube

What is Self-referencing dimensions?

2011-01-18T12:11:00.002+05:30

This occurs when a dimension references itself by a primary and a foreign-key relationship in the same dimension.

Example: Dim_A -> Dim_A

What is Fact Dimension?

2011-01-14T09:22:00.002+05:30

Sometimes we need to analyze our data based on the some specific value which are available only at fact table. In this case, the particular data will act as a dimension. This dimension is called as Fact Dimension.

Fact Dimension also known as Degenerated Dimension

Fact Dimension will be more helpful, when we need to categorize the data based on similar kinds of data.

Example: Invoice Number, Purchase Order No, Etc.,

Of course, we can maintain separate dimension table instead of using Fact Dimensions, but no benefit, because the dimension table grow in the same ratio of fact table growth. So by creating Fact dimension, we can avoid duplicate data and reducing memory consumption. Also it helps to improve the performance during data retrieval time.

Data modelling Vs Dimensional modelling

2010-12-29T09:12:00.003+05:30

Data modelling:
Modelling data storage optimized for Transactional Processing is Data Modelling

Dimensional modelling:
Modelling data storage optimized for Analytical Processing is Dimension Modelling..

SSAS: What is Named Query?

2010-12-29T07:54:00.000+05:30

1. Named Query is a SQL Statements and it act as a another table

2. It is formed by selecting rows and columns from single tables or relating multiple tables

3. It helps to make the data source view with new table structure without modifying the underlying data source

SSAS: What is Linked Dimension ?

2010-12-29T07:11:00.000+05:30

In SQL Server Analysis Services (SSAS), Linked Dimension is a dimension which is existed in other analysis services DB. It may exist in same or different server. it helps the user to keep the common dimension in multiple database.

What is Conformed dimension?

2010-12-28T23:24:00.002+05:30

Conformed dimension is a dimension that has a single meaning or content throught datawarehouse.we can use it accross the fact tables

Example: Time, Product, Staff Dimensions are Conformed dimension, it is shared across multiple fact tables.

What is Role Playing dimension?

2010-12-28T22:59:00.000+05:30

Role Playing dimension is a dimension, it can play different roles in a fact table depending on the context.

For example: Date dimension

Date dimension can be used for ordered date, shipping date, shipment date, and invoice date, etc., in an order line fact.

In the data warehouse, you will have a single dimension table for the dates. You will have multiple warehouse foreign key from the fact table to teh same dimension.

What is junk dimension?

2010-12-28T22:55:00.000+05:30

A junk dimension is a collection of random transcational codes flags and text attributes that are unrelated to any particular dimension.The junk dimension is simply a structure that provides the convienent place to store the junk dimension.

Operational Data Store Vs Data warehouse

2010-11-29T11:10:00.001+05:30

Operational Data Store(ODS)	Data warehouse
ODS is an Online integration of data	Typically it won't provide online Information. It provides offline data.
ODS is used, when a system's data is required to access from some other systems immediately. Ex: records updated from various section, data is lying in different DB, of Hospital is required while billing or some other activities	It is used while making decisions from past and present data(bit old, may be data taken from hours or days back)
ODS is centralized repository of data for online operational use	This is Non-Operational use

What is Data warehouse?

2010-11-29T10:04:00.000+05:30

Data warehouse is repository of data, which are collected from many sources like transactional database or old backup data, etc., and then filtered, cleansed, transformed the data for the purpose of analyzing, decision making and reporting.

Types Of Dimensions:

2010-11-22T10:01:00.006+05:30

Dimensions are classified as followings:

1. Slowly Changing Dimensions or SCD (Type0,Type1,Type2,Type3,Type4,Type6)
2. Slowly Growing Dimensions or SGD (Type1,Type2,Type3 from SCD)

And Other Types are...

1. Conformed Dimension
2. Junk Dimension
3. Degenerated Dimension
4. Role Playing Dimension

Conceptual Data Model or Conceptual Data Schema:-

2010-07-05T09:03:00.001+05:30

Conceptual Data Model Identifies the highest level of relationship between important entities of Data Model. It contains only important entities, no attribute, and no key specification. It contains only entity name and entity relationship as shown below

Degenerate Dimension (DD)

2010-07-02T11:38:00.004+05:30

It is a dimension which is derived from fact table. DD doesn’t have separate dimension table. It is mostly used when we planned use transactional level information on the fact table. The main purpose of DD is to provide the info directly without referring other table.

Primary key of a fact table is subset of table's foreign key. In some cases, we do not need the foreign key in the fact guarantee the uniqueness, in that scenario, DD plays the role.

Dimension keys without corresponding dimension tables is known as Degenerated Dimensions

Process of Preparing Dimensional Model

2010-06-30T12:39:00.003+05:30

Preparing ER Data Model
ER à split into discrete business process
Model Each Process separately
Select Many to many relationship in the ER Model which contains numeric and additive nonkey facts, convert them to fact table
De-normalize remaining table in to flat table with single part keys which will join directly to the fact table. These table become dimension table

Dimension Tables

2010-06-30T11:27:00.000+05:30

It is integral companion to the fact table
It contain descriptive information of the business
Each dimension is defined by its single primary key
It is Entry point of the fact table
Dimension attribute deliver robust analytic slicing and dicing capability
Dimension implements the UI to the DWH

Bus Architecture:

2010-06-30T08:23:00.000+05:30

Conformed dimension are the bus architecture linking different data mart
It is a dimensional model with common dimension linking to different fact table.
This is belongs to fact constellation schema
Distributed DWH systems
DWH developed in Decentralized way

What is Fact Table?

2010-06-30T08:19:00.002+05:30

It is a primary table of a dimension model
It contains numerical information of the business
It contains aggregate values of duplicating records
It represents business measure
Fact table built with list of dimension along with measures
Measurement is taken at the intersection of all the dimension

Types of Facts:
Additive Facts: - Values are summed up combination of all the dimensions
Semi Additive Facts: - It can be summed up only some of the dimension, not with others
Non Additive Facts: - It can not be summed up with any specific dimension
Types of Fact Tables:
Cumulative Fact Table:- It contains mostly additive facts
Snapshot Fact Table:- it includes more semi additive and non additive facts
Factless Fact Table: - It contains no measures or facts, only key relations (FK)

Metadata for Data Warehousing

2010-06-30T08:11:00.000+05:30

All information about the data warehouse environment
It is not actual data
It is similar to databank
It is similar to Encyclopedia

Extract-Transformation-Load (ETL):

2010-06-27T09:57:00.000+05:30

Extraction is an initial step to bring the needed data to staging area for further process
Transformation process involves converts the data to accurate, resolving duplicate, dealing with missing elements, eliminates duplicates and assign data warehouse keys, normalize the data structure etc.,
Loading takes the form of presenting the dimension table to the bulk loading facilities of each data mart

Data Staging Area:

2010-06-27T09:04:00.001+05:30

Storage area + ETL
It is everything between Transaction and data presentation area
It cannot be accessed by the users directly, merely for developer
It can be considered as QA and developer environment
It is off-limit to business users, it cannot provide any serves to users like presenting and Querying, etc.,

Responsibilities of Data warehouse Manager:-

2010-06-27T08:09:00.000+05:30

Understand your users by business area & Job responsibility
Determine the decisions the business users want to make with the help of DWH
Identify the user who really need DWH to support their effective decision
Ensure that data is accurate & trusted
Ensure that user interface & applications are simple & user friendly
Continuously monitor the data for accuracy
Find new sources for DWH
Publish the data regularly
Adapt the Data warehouse depends on new data & new requirement
Makes the business users happy by providing proper information for decision making & makes their work easy

Components of Data warehouse

2010-06-26T23:41:00.000+05:30

The following 4 distinct components to be considered as DWH Component

Operational Source system
Data staging
Data presentation
Data access tools

Diagram Source: The Data warehouse Toolkit - 2nd Edition By Ralph Kimball

Purposes of a Data Warehouse

2010-06-26T23:08:00.000+05:30

Explore our past information for current decision making
Slice & dice our data, in the way we required
It supports people for more fact based decision making
Make the data easy for business people to gather information directly
It supports forecasting
It makes the information easily accessible
It is only business user not developer
Data in DWH must be credible, accurate & reliable
Adaptive and flexible to change
It must provide security to our confidential information.
It must support for improved decision making