A Full Guide to Data Warehouse Design
ScienceSoft has been rendering data warehousing services since 2005.
Data warehouse design: the essence
A data warehouse provides for the integration, structuring and storing of business data for analytical querying and reporting. Data warehouse design is the first step in implementing a data warehouse solution, and it focuses on creating the architecture of a data warehouse system.
Project time: From 2 months.
Data warehouse planning steps: Requirements engineering, discovery, data warehouse conceptualization, project planning, data warehouse technologies selection, system analysis and data governance design, data modeling and ETL design.
Cost: Starts from $40,000.
Team: A project manager, a business analyst, a data warehouse system analyst, a solution architect, a data engineer.
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.
|
Note: The timeframes below are highly approximate, as, for example, the architecture design project for an enterprise-level data warehouse may last up to 3-6 months and even more because of the project scale and specificity. |
1
Data warehouse requirements engineering
Duration: from 3 days
- 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
Duration: from 4 days
- 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
Duration: from 2 days
- 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
Duration: from 2 days
- 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
Duration: from 2 days
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
Duration: from 10 days
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
Duration: from 10 days
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.
|
Note: The next steps would be data warehouse development and launch, which are not addressed within the framework of this guide. In case you are interested in the end-to-end data warehouse implementation process, explore our structured overview of the data warehouse implementation process. |
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.
All in-house
Pros: The company has full control over the data warehouse design project.
Caution: Risk of project delays/failure due to the shortage of resources.
Outsourcing of technical resources
The company owns the data warehouse design project management while relying on outsourced resources to perform data warehouse platform selection, data warehouse solution architecture design and data modeling, etc.
Pros: No risk of the technical resources overprovisioning after the project completion.
Caution: The model requires constant cooperation of all team members. High requirements for in-house PM and BA competencies.
Complete outsourcing (in-house project sponsor, everything else is outsourced)
The company communicates its data warehouse-related needs to a vendor, who takes on detailed data warehouse requirements engineering, business planning, systems analysis, data warehouse design, etc.
Pros: No data warehouse project delays or failures due to resource unavailability.
Caution: Increased vendor dependency.
Amazon Redshift
Best for: petabyte-scale analytics
DESCRIPTION
- Integration of all data types (structured, semi-structured, unstructured).
- SQL data querying (including big data).
- Automatic infrastructure provisioning, database backups and cluster health monitoring.
- Federated query capability.
- Deep integration with the AWS services (including S3, AWS Glue, Amazon EMR).
- Integration with third-party tools (Power BI, Tableau, Informatica, Qlik, Talend Cloud).
- Materialized views and ML-optimized performance.
- End-to-end data encryption, granular access control and network isolation.
- Separate billing for compute and storage resources.
- On-demand pricing with no upfront costs.
Pricing
- On-demand pricing: $0.25/hour (dc2.large) - $13.04/hour (ra3.16xlarge).
- Reserved instance pricing can save up to 75% over the on-demand option (in a 3-year term).
- Data storage (RA3 node types): $0.024/GB/month.
Azure Synapse Analytics
Best for: advanced data management
DESCRIPTION
- SQL querying of structured, semi-structured, unstructured data, including big data.
- Support for T-SQL, Python, Scala, Spark SQL, and .Net.
- Native integration with Azure services, including Apache Spark, Power BI, Azure ML, Azure Stream Analytics, Azure Cosmos DB, etc.
- Integration with third-party BI services (Tableau, SAS, Qlik, etc.).
- Speeding up queries with result-set caching and workload isolation.
- Automatic restore points and backups.
- Always-on data encryption, dynamic data masking and fine-grained access control.
- Separate billing for storage and compute resources.
- Cost optimization with the pay-as-you-go/reserved capacity pricing models.
Pricing
- Compute on-demand pricing: $1.20/hour (DW100c) - $360/hour (DW30000c).
- Compute reserved instance pricing can save up to 65% over the on-demand option (in a 3-year term).
- Data storage: $122.88/TB/month.
Oracle Autonomous Data Warehouse
Best for: hybrid DWH
Description
- Deployment flexibility (Oracle public cloud (shared/dedicated infrastructure) or a customer’s data center).
- Integration of all data types (structured, semi-structured, unstructured).
- Automated data warehouse provisioning, scaling, tuning, and securing.
- Native integration with Oracle Analytics Desktop.
- Connectivity to Oracle Cloud Infrastructure Object Storage, Azure Blob Storage, Amazon S3.
- Connection with custom applications and third-party products via SQL*Net, JDBC, ODBC.
- Always-on data encryption, multifactor authentication, data classification and discovery.
- Independent scaling of storage and compute resources.
Pricing
- Compute: $1.3441/CPU/hour.
- Data storage: $118.40/TB/month (in the public cloud).
Designing a 10GB data warehouse solution, which involves data transformation and data cleansing processes, may cost from $40,000 depending on the initial data quality, data transformation complexity, etc. |
|
Among the major data warehouse design cost drivers are:
- Number of data sources (ERP, CRM, SCM, etc.), data disparity across different sources (e.g., the difference in the data structure, format), data source complexity.
- Data volume to be processed and stored.
- Source data quality (low-quality data requires sophisticated data cleansing procedures).
- Required data security level.
- Data warehouse velocity, scalability, and fault tolerance requirements.
Consider Professional data warehouse Design and Implementation Services
With 16 years in data warehousing services, ScienceSoft helps you design and implement a cost-effective data warehouse solution meeting your tactical and strategic business needs.
Data warehouse design
- Data warehouse requirements engineering.
- Data warehouse design project planning.
- Data warehouse solution conceptualization and architecture design.
- Data warehouse software selection.
- Data warehouse system analysis and data governance design.
- Design of data models and ETL/ELT process.
Data warehouse implementation
- Data warehouse requirements engineering.
- Data warehouse solution conceptualization and platform selection.
- Data warehouse architecture design.
- Data warehouse solution development.
- Data warehouse quality assurance and launch.
- Data warehouse support and evolution.
About ScienceSoft
ScienceSoft is a global IT consulting and software development company headquartered in McKinney, TX, US. Since 2005, we’ve been providing d serata warehousing services, including data warehouse consulting, to help our customers build robust analytics with scalable and effective data warehouse solutions designed in accordance with their particular business needs. Being ISO 27001-certified, we guarantee cooperation with us does not pose any risks to our customers' data security.