Introduction to Data Warehouse Design
A warehouse in common word means, storing something in one place and similar cases in industries to store the complex amount of data in one location. Business Intelligence (BI) allows you to query data from the data sources and trust can be made only when there is good data warehouse design.
The data warehouse integrates multiple data sources and will make a good support for analysis and analytical reporting. If you have a poor data warehouse design, it will impact your organization’s growth by having inaccurate query data.
Take a popularity example of Amazon web store by ordering the item and it can be delivered at our doorstep. When the customer login to the e-commerce site and searching for the available product in the store. Then we selected and ordered item, as soon as the supplier accepts and immediately dispatched. Here we can save our time to buy the required item.
A similar case to this data warehouse also, the data can be stored and procured from the transaction system. The data warehouse as a two major concept
- OLAP – Online Analytical Processing
- OLTP – Online Transactional Processing
- Requirement gathering is one phase in data warehouse design. It needs to determine the criteria and implement them successfully. There will be two strategies used for data warehouse design one is called the business and another one is called 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 challenging and makes a trust for the organization.
- 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 building a separate environment for each 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 occurred in the system, we couldn’t able to navigate and find out the way to fix and it makes it more complex.
- Once the requirement gathering and environmental is set-up, the next is to design how to connect the data source, process, and store in the data warehouse. This technique is called as data modeling. It can be an analysis of the object and 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 identified, the team can build the logic and create a structure schema view.
Types of Data Model
There are three types
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.
4.7 (3,220 ratings)
2. Logical: This define HOW the logical can be created in DBMS, it will be designed by 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.
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 the 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
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 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, analysis the feedback and generate the reports.
5. Insurance: Insurance company primarily used for data patterns, customer trend 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 for 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 of Data Warehouse
- Delivers Enhanced Business Intelligence
- Ensures Data Quality and Consistency
- Saves Time and Money
- Tracks Historically Intelligent Data
- Generates high ROI
Dis-advantage of Data Warehouse
- Extra Report Work
- Inflexibility and homogenization of data
- Ownership Concerns
- Demands for large amounts of resources
- Hidden issues consume time
This is a guide to Data Warehouse Design. Here we discuss the Data warehouse design technique, requirement gathering, environment set-up, uses, advantage / Dis-advantage. You may also look at the following article to learn more –