Building data warehouse is not different than executing other development project such as front-end application. You need to be technical and business person who understand technical details along with organizations business to successfully design and implement data warehouse project. In this article, we will check what the data warehouse project life cycle is and different steps in designing data warehouse project!
Steps of Data Warehouse Project Life Cycle Design
Following are steps generally followed in any data warehouse projects you can consider these steps as data warehouse lifecycle:
- Requirements gathering
- Requirements analysis
- High level design (HLD)
- Low level design (LLD)
- Development – Automating scripts
- Testing
- User Acceptance Testing (UAT)
- Project release or deployment
- Warranty support
Requirements Gathering
This is one of the tough phase and is very critical to the success of your data warehouse project development. Complete and clear requirement is must for success of any data warehouse project.
Requirement gathering can happen as one-to-one meetings with end-users or client. The meeting will include the business analyst and may include development team members such as architects. In this process, you will be preparing Business requirement document (BRD). The requirements gathering includes but not limited to following steps:
- Understand the existing models and prepare questionnaire for new data warehouse users
- Ask specific questions regarding project rather than high level
- Get information on the different data sources for data warehouse
- Collect information on the frequency of data loading and incremental load details
- Get information about list of reports that are needs to be built as part of this data warehouse projects.
Requirements gathering in tough process as end users may not be clear with their requirements. You have to put your expertise to build better data warehouse that suits client’s requirements.
The overall requirement process will take around 2-8 weeks.
Requirements Analysis
User requirement analysis is another crucial part of the data warehouse project along with user requirement gathering. This process is one of the toughest because it affects almost every decision throughout design and implementation of data warehouse project.
Basically, user requirement analysis in data warehouse approach can fall within four categories:
- Data-driven
- User-driven
- Goal-driven
- Mixed-driven approaches
For more information on different approach of Data warehouse design process read:
Prepare High Level Design (HLD) Documents
Once the user requirements are analyzed, next process is building high level design (HLD) documents based on the user requirement understanding. This process involves diving business requirement document into high level design (HLD) document that includes various modules in the data warehouse project. Generally, HLD is prepared by the architects.
Prepare Low Level Design (LLD) Documents
The next step in the data warehouse project is to prepare low level design (LLD) document. This process involves diving HLD to give the detailed information about each entities in the data warehouse project. Generally, developers will prepare the LLD based on HLD.
Data Warehouse Development Process
Once Low level design is implemented, the next step is the building data warehouse modules i.e. actual development.
There are two steps in the development phase:
ETL (Extract, Transform, Load) Development
This process involves building ETL process for data warehouse. The ETL developer prepare data model with all dimension and fact tables. This step also integrates the data warehouse from various heterogeneous data sources.
Read:
- Types of Fact Tables in a Data Warehouse
- Database ACID Properties and Explanation
- Data Warehouse Fact Constellation Schema and Design
- Types of Dimension Tables in a Data Warehouse
- Data Warehouse Three-tier Architecture in Details
Report Development
This step is to develop the user reports. Once data warehouse is built, reports are configured to specific repository to generate user data as per requirements.
This phase also involves automating the process to minimize the manual intervene.
Data Warehouse and Report Testing
Testing is very import process of building data warehouse systems to make them work correctly and efficiently. There are three basic levels of testing performed on a data warehouse:
- Unit testing
- Integration testing
- System testing
Data Warehouse User Acceptance Testing (UAT)
Data warehousing user acceptance testing focuses on confirming that the pre-defined reports and queries are working correctly. This step involves end users along with testing team.
End-users will verify the data and reports that are built on specific repository.
Data Warehouse Release or Deployment
Once the UAT is performed on the data warehouse the final step is to deploy the warehouse to production environment. There are two parts in data warehouse deployment process:
- Deploy scripts related to data warehouse
- Deploy reports
Deployment process involves educating the deployment and support team on the deployment process and data warehouse components.
Data Warehouse Warranty Support
Development team has to support the data warehouse system for one month from the date of deployment, this period is called ‘warranty support’.
Development team has to fix the bugs that may come up during this phase.
Along with above steps, there is the separate step to set up the environments that are required to start data warehouse development process.