Data Warehouse Design
100+
Satisfied and Happy Clients we have Served all over the World.
Data Warehouse Design: the Essence
Data warehouse design is creating the DWH architecture that would enable the integration, structuring, and storing of business data for analytical querying and reporting. This process also implies ensuring data quality through defining reliable data transformation and cleansing procedures.
devstudio360 considers data warehouse design a crucial step in implementing a data warehouse solution, as at this stage we lay the foundation of the software-to-be.
- Engineer requirements.
- Discover data needs.
- Conceptualize data warehouse.
- Plan the project.
- Select data warehouse technologies.
Data Warehouse Solution Architecture
A typical data warehouse architecture includes:
Data source layer
– internal and external data sources (ERP, CRM, sensor devices, social media, public databases, etc.) providing data fed into the data warehouse.
Staging area
– a temporary repository where records from data source systems undergo consolidation and processing before loading into the storage area. The staging area may be absent when data transformation goes in the target database (data warehouse/data marts).
Data storage layer
– hosting a data warehouse database – permanent data storage that keeps slightly and highly structured data, and data marts – data warehouse subsets providing information for reporting and analysis for a company’s specific business line, department, or team.
Analytics and BI
– the data in the data warehouse database and data marts can be queried via OLAP tools, data mining tools, reporting and visualization tools.
Data Warehouse Design Steps
A data warehouse design process and its duration depend on:
- Source system complexity and quality. Data analytics complexity. Data security complexity, etc.
Based on devstudio360 ample experience in designing and implementing data warehousing solutions, we list core steps needed to design a data warehouse solution.
1. Data warehouse requirements engineering
- Determining current and future business needs to meet with a data warehouse project.
- Identifying data warehouse users’ goals and expectations.
- Determining security and compliance needs, etc.
2. Discovery
- Preliminary data source analysis (number of data source systems to be integrated, source data volume and complexity, etc.)
- Identifying a number of potential users and their location.
- High-level review of necessary security and compliance requirements, etc.
3. Data warehouse conceptualization
- Describing the main components of the data warehouse solution.
- Choosing between on-premises and in-cloud data warehouse deployment and outlining cloud deployment options (public, private, hybrid cloud, multi-cloud).
- Choosing an optimal option for the data warehouse solution architecture (Inmon vs. Kimball approach).
- Defining the potential of the selected architecture in solving business problems.
4. Data warehouse design project planning
- Defining data warehouse design project scope, deliverables and timeline.
- Data warehouse design project resource and budget planning.
- Data warehouse design project risk management and risk mitigation strategies development.
5. Data warehouse technologies selection
Selecting data warehouse solution technologies for each of the data warehouse solution components (data integration tools, a database, etc.), taking into account:
- Current analytics infrastructure environment (if any).
- Data source systems.
- In-house data warehouse experts’ competencies.
- Data security strategy, etc.
6. Data warehouse system analysis and data governance design
Detailed analysis of each data source:
- Data type and structure, data volume generated daily.
- Degree of data sensitivity and applied data access approach.
- Data quality, missing/poor data, the possibility to perform data cleansing in the data source system.
- Relation to other data sources, etc.
Setting up data governance framework by creating:
- Data quality criteria and data cleansing policies.
- Data access and usage policies, data security policies (data access policies, data encryption policies, data backup strategy, etc.).
7. Data warehouse data modeling and ETL/ELT design
Designing data models for the data warehouse and data marts:
- Identifying entities, key attributes of each entity, relationships between entities.
- Mapping attributes to entities.
- Converting the logical data model into the tables, columns, indexes, keys of the database.
- Validating data models.
The common data models to choose from are:
- Star schema – the center of the star is a fact table surrounded by a number of associated dimension tables.
- Snowflake schema – an extension of the star schema (each dimension table is surrounded by additional dimension tables).
- Galaxy schema – contains two fact tables sharing dimension tables between them.
Designing ETL/ELT processes for data integration and data flow control.
Consider Professional Data Warehouse Design and Implementation Services
devstudio360 helps you design and implement a cost-effective data warehouse solution meeting your tactical and strategic business needs.
Data warehouse design
- DWH requirements engineering.
- DWH design project planning.
- DWH solution conceptualization and architecture design.
- DWH software selection.
- DWH system analysis and data governance design.
- Design of data models and ETL/ELT process.
Data warehouse implementation
- DWH requirements engineering
- DWH solution conceptualization and platform selection.
- DWH architecture design.
- DWH solution development.
- DWH quality assurance and launch.
- DWH support and evolution.
Typical Roles in devstudio360 Data Warehouse Design Projects
Project manager
End-to-end data warehouse design project management:
- Defines data warehouse design project scope, goals and deliverables.
- Develops the data warehouse design project plan and communication approach.
- Communicates data warehouse design project purpose and expectations to stakeholders.
- Estimates and coordinates the efforts of data warehouse design project team members.
- Ensures timelines and quality of the data warehouse design project deliverables within the set budget frames.
Business analyst
- Analyzes the needs of key stakeholders and end users and translates the needs into the data warehouse requirements affecting design (e.g., the data warehouse solution should support operational analytics).
- Describes the scope of the data warehouse system, its modules, and integrations with other software.
Solution architect
- Designs a data warehouse architecture based on business and technology requirements.
- Ensures the architectural requirements (availability, scalability, performance, reliability, etc.) are implemented in the data warehouse design.
- Suggests a technology stack.
Data warehouse system analyst
- Examines data sources and data analytics software (if any) to be integrated into the data warehouse solution.
- Draws up a system requirements specification for creating data models, designing ETL/ELT processes, etc.
- Defines data integrity and data cleansing rules, etc.
Data engineer
- Designs a data model and its structures and draws up the data flows.
- Designs ETL/ELT processes.
