Datawarehousing Concepts Basics (Fact and Dimension Table) - YouTube

HI guys my name is Vivek and today I will be explaining you the data warehousing concepts

in a simplified manner Before we go ahead let’s understand what

do we mean by a data warehouse ? Data warehouse is a system which is used for

reporting and data analysis.

From this data analysis we derive some logical conclusions that helps us in improving our

businesses.

We will be explaining the entire concept with a simple example today.

Mark who is the CEO of an ecommerce website is willing to increase his profit by streamlining

the process

This is the basic model of seller point Seller points has 3 components customers,

products and courier services

So these customers can buy these products and these products are shipped with these

Courier Services So let’s take an example Vivek can buy a

shoe from boot makers and that will be shipped by speed fast.

Mark is analyzing the data for last one month to identify which products or which customer

has made money for him and what are the reasons why he has incurred loss.

So based on that data analysis he will come up with some logical conclusions to improve

his business line.

Let’s analyze the data as I just mentioned we have three entities customers products

and careers and these are the details of the item that have been sold say over the last

one month.

So if you see we have certain components that have been highlighted in red these are the

items that have been returned an item that has been returned has made no profit for the

company rather it has incurred losses and the reason being an item that was returned

means it was shipped and then it was sent back

That means it didn’t make any money for the company,so what are the reasons why these

items were returned For the 1st 2 items the customer said he didn’t

like the product, Vivek is the customer who bought a shirt and

jeans and he said that he didn't like the product also note that the Delivery status

for this particular jeans was delayed.

The other two items, the reason for return were “The items were damaged”

These items were shipped by batman couriers, That means 2 items that were delivered by

batman couriers were damaged also if you see 2 items that were delivered by local couriers

were delayed.

Based on this we can come up with some logical conclusions like

Few items were delivered late Few products were not sold at all

Vivek returned 2 out of his 3 items And batman couriers delivered 2 broken items

out of 3 Obviously mark can come up with logical conclusions

with the data we just saw

He can remove the batman couriers services He can remove the unsold product line with

new similar products He should warn the local couriers service

to expedite their courier process He should remove cash on delivery for vivek

as he is returning majority of his items And he should give an additional discount

to neeraj on his next purchase as his previous experience was not smooth.

This will save him the name of the company because neeraj will not give him a bad review

in case if he gives him an additional discount.

If we want to understand the entire concept in terms of data warehousing it’s very simple

The 3 entities customers, products and courier services are dimensions

And the transaction table that we saw is the fact table.

The fact table has keys of all the dimensions and some additional columns we call measures

A fact table has the primary key of all the associated dimensions and the measures.

So measures are used to evaluate the profit and loss game

For example here the measure we can calculate which product made me how much money

Dimensions are the true driving table as they provide descriptive information for the measurements

recording in the fact The vital thing to remember in a data warehouse

is that the dimensions will always be loaded first.

Because you can’t sell a product that doesn’t exist.

Also if you see dimensions are relatively small in size because a customer can buy multiple

products.

Which means vivek he can buy 10 items and that will be 10 entries in the fact table

but he still is a single customer

In a data warehouse 80% of the data usually is consumed by a fact table

As I already explained the objective of data warehouse is to leverage the data for precise

and effective decision making . Also the data in warehouse is persistent.

If I try to explain the data flow of data for reporting, First of all the data enters

in the OLTP system from there it goes to the integration layer between OLTP and the data

warehouse which is ods (Operational data store).

From the data warehouse the data goes to data mart where the reporting is usually done.

So we still have some open questions

What is an OLTP system ? What is ODS?

What is data mart?

Why the reporting is not usually done on OLTP or ODS and is done on warehouse or data mart?

What kind of reporting tools are being used ?

All these answers I will give in my next tutorial