Definition of a DWH

A DWH is a

1. Subject Oriented i.e. Has the power to mould the transactional data around a particular subject.If we want to answer questions around the sales of a particular product, the DWH would be around the Subject-sales.etc.
2. Integrated i.e. Integrates data from various sources and keeps in form of dimensional model.
3. Non-volatile i.e. -Data once entered in DWH does not change.
4. Time variant - DWH maintains historical data.

collection of data.

Why is a DWH required in an organization?

1.       Data is an asset and has to be retained as per organizational needs.DWH helps us in maintaining          large volumes of data.

2.       To reduce IT involvement in report creation and helps the business to custom build the reports.

3.       MDM is used to maintain the single version of the truth.

Categories of Enterprise Data

1.    Master data -   Business entities of the organization.Data revolves around these business entities. Customer,Employee etc. This is maintained as part of the MDM solution.

2.       Transactional Data – Generated as part of day to day business processes. Also known as the OLTP system Online transactional processing systems)

3.       Analytical Data – Typically related to Key Performance Indicators (KPIs).Data Warehousing systems are used to store and retrieve analytical data.

4.       Meta Data-  Data about data.Describes IT systems, Business rules etc.

5.       Reference Data- Static data elements, sometime transient like currency ,industry standards. A reference data hub is maintained for transaction processing.

6.       Un-Structured data- All the above mentioned data would be classified as structured data.Examples of unstructured data would be Emails,Social Media data i.e. Big Data. Hadoop is a platform used to manage un-structured data.

Product Dimension

The Product dimension has the following hierarchy.

Merchandise hierarchy, important hierarchy of the product dimension
Department > Categories > Brands > SKU (Stock keeping Units)-identifies individual product.

To simplify the above hierarchy the following example could be used. Hierarchy in Flipkart

Men (Department) > Clothing (Categories) >T-Shirts>UCB(Brand)>Blue colored shirt.

Some of the attributes of the Product dimension are as follows:

1. SKU Number (Natural Key\Business Key)
2. Product Key (PK)
3. Category Description
4. Department Description etc.

Date Dimension

Why do we need a separate date dimension, if we can insert the date from the transnational data directly,as is?

A date dimension has 365 rows ,each row representing a particular date and a Date ID. This Date ID is maintained in the fact table rather than the date from the transnational table.The date dimension would have the following attributes:

1. Day of the week

2. Week of the month

3. Month

4. Year

5. Holiday indicator

6. Season {Thanksgiving,Christmas etc.}

7. Fiscal year (Financial year of US)

8. Fiscal month

9. Fiscal Week etc.

All these attributes are very relevant at report level, and could give a whole lot of information combined with the fact, as compared to a single date value coming from the transnational system.

Hierarchy for Date Dimension

Year > Month >Week>Day>Hour>Minute>Second.

Stories of Big Daddies of the industry

Larry Ellison (Oracle) - Never gradated from college, learning was primarily based on Self study. He just picked up a book and started programming.

Bill Gates (Microsoft) - Never completed college

Steven Jobs (Apple) - Epic Misfit!!

The reason I need to write the same in my blog, that every s/w engineer needs to remind himself that he is working in an industry where degrees and certifications do not matter.What matters is your intellect, competence and aptitude. It gives a fair platform to a private engineering college or a correspondence student to compete against the ones coming from the elites institutions.So if you are good enough you are bound to make it big.The question is -Do you have it in you?

Difference between Delta Load and Incremental Load

Scenario:

We have completed the first load into the target table, and wish to implement the Delta load and the Incremental load.

• Delta Data is the parent of Incremental Data.
• What is Delta Data?
• Assuming that in our scenario, the first load happened on 1-Jan-2015. The data present in the source on 2-Jan -2015 is the delta data. 
• How to read delta data?
• Add the date filter in the source query i.e. WHERE load_date>1-Jan-2015
• What is incremental data?
• The data that we have read , as delta data, needs to be compared with the target data for key columns. This is generally done through SCD(Slowly Changing Dimensions) algorithms. Incremental data is the changed or new data found while the delta and target data are compared via SCD algorithms.