Data warehousing Concepts and Best practices


Introduction to Data warehousing

Have you ever thought why Google, Facebook, Twitter and many other websites are position at the top rank? Have you ever thought how Wall mart, Reliance and other shopping marts are making profits? Have you ever analyzed the ways ebay, flipkart, Snapdeal and other online stores follows to achieve higher revenue each year? There is a single answer for all these questions and it is Data warehousing and Business Intelligence. With this you must get to know the power of this word “DATA”. Data warehousing provides the enterprise with Memory and Data Mining provides the enterprise with Intelligence.

“Data warehousing is a concept and it provides an architectural model for the flow of data from Operational data System to Decision support system.”

Data utilization

Earlier, we were dependent only on Transactional Processing System (TPS) also known as Real-time processing. TPS was used for business transactions to collect daily transactional data. All day-to-day transactions were captured and stored into the database. Then, the point of concern for Business people or Decision makers was to have high network bandwidth to send data to distributed platforms on daily basis and Business Intelligence was given low priority. But until what time can the need of Business intelligence and Data warehousing be ignored.

In 1970’s, Business experts sensed the need of Data warehousing and Business Intelligence and that sense has now become the essence of each organization. With the analysis of the data stored at the Data warehouse, Decision makers can view the immediate impact on the bottom line and top line of the organization. Also, it provides greater insight of customers which helps the organization to retain them and gives better visibility of the organization.

Need for Data warehousing

Today’s mantra of success is “Proactive use of Information than Optimization”. This is a world of competition and organizations start focusing on utilizing data they collects from day-to-day transactions. Organizations realize the Importance of information they hold, to improve decision making and achieve profits. But, Operational systems were never designed to perform these intelligent activities and probably never can be. So, this arouses a need of such a system which is mature enough to support decision makers and analyze business process. As said by Plato (a Greek philosopher) – “Necessity is the mother of Invention”, this need leads to the development of a system known as Decision Support System (DSS) i.e. Data warehouse. Let’s gain more information on these two systems i.e. TPS and DSS.

Transaction-processing systems are built to capture day to day information and update that collectected information quickly. The TPS is constantly changing and is generally online 24 hours a day.

Transaction Processing System Examples – In shopping outlets, Point-of-sales scans your order and generates bill, Railway reservation applications, Order placing at online stores like flipkart, myntra, etc.

Decision-support systems are built to analyze the data stored in data warehouse. It deals with historical data and helps in extracting the information easily and quickly.

Decision Support System Examples – Shopping outlets analyze which product is to be kept with which product so as to gain more sales and generate maximum revenue, Information for marketing, Claiming Insurance, LIC policies.

Decision makers in Data warehousing

Problem with Data warehousing

You must have read a line from a great poet, William Shakespeare – “Water, water, everywhere nor any drop to drink”. It means in sea there is water all around but you cannot drink that saline water. In the same way, Data warehouse contains volume of data but part of it is important to you that you have to look.

  • I can’t find the data I need – Data is scattered over the network.
  • I can’t get the data I need – Need an expert to get the data.
  • I can’t understand the data I found – Available data is poorly documented.
  • I can’t use the data I found – Data need to be transformed from one form to another.

Apart from these issues, Cost is the major drawback because Data warehousing is a very costly process and requires huge amount of data for Analysis and Reporting processes.

Benefits of Data warehousing

Data warehousing is a concept of merging data from heterogeneous data sources into one database. Organizations spend vast resources in terms of human resource as well as financial resource for implementing Data warehouse. Why the companies invest so much into this? What is the benefit of spending resources on storing data? Let’s see the benefits provided by Data warehousing to the organization.

Benefits and Advantages of Datawarehousing

  • It facilitates Strategic Planning and considered as a backbone of organizations. Business People get to know market’s trend and can make better decisions.
  • It provides an option of Better Data Analysis as Data from varied sources is grouped into Data marts. Companies can analyze data in a better way by querying on multiple dimensions (Multi Dimensional Query).
  • It provides flexibility in Operation Systems as it operates separately from other operation processes, Data retrieval can be done with ease without affecting other operation requirements.
  • It helps in identifying and resolving dirty data, with dirty data I mean to say Inconsistent data. All the dirty/inconsistent data is resolved and a homogenous data is then grouped into Database. As a result, Data Retrieval and Data Analysis process gets simplified.
  • It helps to improve Decision making process as it provides market trends and forecast.
  • It is used to enhance Customer service and provide better enterprise intelligence.

Free e-books and Pdf’s :-

Please Subscribe or Like PhpRinG Tutorials for free e-books and pdf’s.

  • Launching Very Soon free e-books and pdf’s on Data warehousing.

I hope you like our post on Data warehousing Concepts. Please provide feedback in the form of comments below. Stay tune to PhpRinG Tutorials for more tutorials on Data warehousing in future!!!

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.6/10 (32 votes cast)
VN:F [1.9.22_1171]
Rating: +26 (from 28 votes)
Data warehousing Concepts and Best practices, 9.6 out of 10 based on 32 ratings
  • wow what an article it is really superb very very simple to understand
    great bro keep it up..

    • Thanks Avinash!!! I am glad to know you learned something new on Data warehousing from this site.

  • venkat

    nice bro so many doubts clarified by your post

  • Beena sharma

    Thanks, very succinctly written. Could you please share more on data warehouse. What are the pros and cons of data warehouse and what all things to consider to improve its performance.

    • Hi Beena,
      Thanks a lot for the appreciation.
      Yes, I am working on the suggestions you provided and you will see them in the upcoming Posts. So, Stick with PhpRinG for future posts 🙂

  • Pankaj Goyal

    Hi Chander,
    Great article on Data warehousing !!! I would like to learn more from you. Very well written.

    • Hey Pankaj,
      I am a Learner like you only. Will try my level Best to share as much information as i can.
      Thanks a ton bro for your feedback.
      Praise makes me happy. 🙂

  • charan

    Hii chander,
    I am charan,I am beginner of MSBI.the way of approach you started data warehousing is excellent.I am excited about Data warehousing now!!..
    I would love to learn MSBI from you…

    • Hi Charan,
      Actually i read many articles on Data warehousing but the problem with them is that they concentrate more on Data warehouse rather providing information on Data warehousing.
      “Data warehousing is a concept of merging data from heterogeneous data sources into one database” whereas “Data warehouse is a place where we store all the data and performs Analysis/Reporting on to it”.

      • karthik

        Nice one Mr. Sharma

        • Thanks Karthik.
          Do Follow us on Google plus
          Like our page on Facebook for latest updates.

  • sanjiv

    nice article..

  • Hi Sanjiv,

    Thanks bro. 🙂


    Its Awesome. It makes curiosity to learn further deeply. Will follow your posts. Thanks.

    • Hello Padmanesh NC,
      Thanks a ton bro for the appreciation !!! You can browse SSIS, SSRS, SQL, SPring, Java and BI/DW sections for more articles. If you have any issues than contact us on our website or Facebook Page named ” PhpRing”. We will try our best to assist you.

      Chander Sharma

  • Rajesh

    Thanks for the explanation, could you please provide me some explanation with example of all ETL tools available in BIDS.

    Rajesh Patil.