EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Data Warehouse Tutorial Data Warehouse Design
Secondary Sidebar
Data Warehouse Tutorial
  • Basic
    • What is Data Warehouse
    • Data Warehouse tools
    • Career in Data Warehousing
    • Benefits of Data Warehouse
    • Data Warehouse Architecture
    • Data Warehouse Design
    • Data Warehouse Implementation
    • Data Warehouse Features
    • Data Warehouse Modeling
    • Data Warehouse Software
    • Data Warehousing
    • Types of Data Warehouse
    • 10 Popular Data Warehouse Tools
    • Data Lake Architecture
    • Three Tier Data Warehouse Architecture
    • Data Warehouse Process
    • Database Parallelism
    • What is OLTP
    • What is OLAP
    • OLAP Tools
    • Types of OLAP
    • Operations in OLAP
    • MOLAP
    • HOLAP
    • Data Warehouse Schema
    • Data Warehouse Components
    • Snowflake Schema
    • Snowflake Architecture
    • What is Star Schema
    • Galaxy Schema
    • What is Fact Table
    • Kimball Methodology
    • Data Warehouse Testing
    • Operational Data Stores
  • ETL
    • What is Data Mart
    • What is Data Cube
    • What is a Data Lake
    • What is Data Integration
    • What is ETL
    • What is ETL Testing
    • ETL Testing Tools
    • ETL architecture
    • Dimension Table
    • Multidimensional Data Model
    • Fact Constellation Schema
    • ETL Process
  • Interview Questions
    • Data Warehouse Interview Questions
    • ETL Interview Questions
    • ETL Testing Interview Questions
    • Data Warehousing Interview Questions

Data Warehouse Design

By Priya PedamkarPriya Pedamkar

Data Warehouse Design

Introduction to Data Warehouse Design

Data Warehousing practice has its own Development Life Cycle flow for designing and implementing the Data Warehouse systems. It involves the basic steps like Requirement Analysis, Data Source Identification, ETL Processing, Data Modeling for to elect the data model based on the requirement and data sources, and design approach for selecting the design approach based on which the Data Warehouse is to be implemented, that is, either ‘top-down approach’ or ‘bottom-up approach.’

Data warehouse has two major concepts:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • OLAP: Online Analytical Processing
  • OLTP: Online Transactional Processing

Both are online processing systems but have some differences. OLTP manages the transactional application like ATM, OLAP uses for analytical processing like reporting, forecasting, etc.

Requirement Gathering

  • Requirement gathering is one phase in data warehouse design. It needs to determine the criteria and implement them successfully. Thus, there will be two strategies used for data warehouse design: the business, and the technical.
  • The business strategy focuses on the long-term business view and helps to increase the profit for growth. The technical strategy requirement is based on user reporting, analysis, hardware selection, development method, testing technique, implementation environment, and user training.
  • When we determined the business and technical strategy, we also need to design the BCP (Disaster Recovery) plan. When there is a disaster happen by human or natural, we need to have a plan to recover the data quickly and ensure no data lose. Developing the disaster recovery plan is one of the challenges and makes a trust for the organization.

Environment Setup

  • Once we gathered the data for data warehouse design, we need to make a proper environment setup for development, testing, and production. Preferably there should be a separate system for application, database, and separate for reporting/ETL as well.
  • When we are building a separate environment for each, it ensures all the changes can be developed/ tested and then move on to production.
  • If we have a single environment which designed for all these activities, it could be end-up with issue and data lose. For example, when there is an incident that occurred in the system, we couldn’t able to navigate and find out the way to fix it, and it makes it more complex.

Data Modelling

  • Once the requirement gathering and environment are set up, the next is to design how to connect the data source, process, and store in the data warehouse. This technique is called data modelling. It can be an analysis of the object and the relationship between the others.
  • When the design of the data warehouse, the engineers designed how and where the data needs to be stored. On the same occasion, we should also define the possible way to retrieve the data from the data warehouse. Once the source is identified, the team can build the logic and create a structure schema view.

Types of Data Model

There are three types:

  • Conceptual
  • Logical
  • Physical

The Three Types of Data Model are mentioned below:

1. Conceptual: It says WHAT the system contains, and it’s designed by business Architects to define the scope for business strategy.

2. Logical: This define HOW the logical can be created in DBMS; it will be designed by a Business Analyst and Data Architect to create a set of rules to store/retrieve the data

3. Physical: This defines HOW the system can be implemented.

Physical

Use of Data Warehouse Design

Being a good data warehouse design can be time-consuming when retrieving the data. Each step has to follow effectively to make the system a good one. It will help the organization to handle complex types of data and improve productivity based on the trend analysis. So each step in DWH architecture design is important and more conscious in the selection method. The organization steps into each flow subsequently and leads to successfully implementing the data warehouse.

There are few important applications uses of Data Warehouse:

Use of Data Warehouse design

1. Banking Industry: Most of the banks are using the data warehouse for storing a large amount of transaction data and the ability to retrieve the query data much faster. It can be managed like customer data, market trends, reports, analysis, etc.

2. Finance Industry: It is similar to banking, but the only focus is to improve financial changes by analyzing the customer data.

3. Government: Nowadays Government managing a lot of data online and stores it in the relational database. Each data have a relationship with each other like Aadhaar; PAN is linked to many sources.

4. Healthcare: Healthcare managers and services so much information. It maintains the clinical details, customer records and helps them to predict the outcomes, analyse the feedback and generate the reports.

5. Insurance: Insurance company primarily used for data patterns, customer trends and maintaining records.

6. Manufacturing and Distribution Industry: It is most widely used in all industries for storing item information and helps them to predict the demand item for manufacture and sales. Analyzing the sold item which gives better decision-making techniques.

7. Retailers Services: Retailers are the middleman between the producer and the customer. Data warehouse helps them with promotions and item buying trends.

8. Telephone Industry: Telephone industries manage a lot of historical data, which helps for making the customer data trend and target to push advertising campaigns.

Advantages and Disadvantages of Data Warehouse

Given below are the advantages and disadvantages mentioned:

Advantages:

  • Delivers Enhanced Business Intelligence.
  • Ensures Data Quality and Consistency.
  • Saves Time and Money.
  • Tracks Historically Intelligent Data.
  • Generates high ROI.

Disadvantages:

  • Extra Report Work.
  • Inflexibility and homogenization of Data.
  • Ownership Concerns.
  • Demands for Large Amounts of Resources.
  • Hidden Issues Consume Time.

Recommended Articles

This is a guide to Data Warehouse Design. Here we discuss the data warehouse design technique, requirement gathering, environment set-up, types, uses, with advantages and disadvantages. You may also look at the following article to learn more –

  1. Benefits of Data Warehouse
  2. Data Warehouse Implementation
  3. Data Warehouse Modeling
  4. Data Warehouse tools
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course

Related Courses

Business Intelligence Training (12 Courses, 6+ Projects)4.9
Data Visualization Training (15 Courses, 5+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more