The DWH design includes services for integration, structuring and storing business data for analytics and reporting. A DWH design is the first step in implementing a DWH solution. It creates architecture and other consideration for a successful DWH system. Project team includes Project Manager, Business Analyst, Solution Architect, Data Warehouse System Analyst, Data Engineer and Programmer.

​

The typical architecture will include

​

• ​Data source: internal or external data sources – ERP, CRM, sensor, social media, public data etc).

​

• Staging area: a temporary repository of records from data source to consolidation and correlation before loading to storage area. The staging area is not needed when data transformation goes directly into target database. (DWH/Data Marts)

​​

• Data Storage: hosting of DWH database. Data storage for structured data and data marts. Datawarehouse subsets in providing information for reporting and analysis for business lines.

​

• Analytics and BI: the data in the data warehouse database dan data marts can be queried via any OLAP tools, data mining tools and visualizations tools.

 

DWH Design will have the following

• DWH requirements engineering

  • Determining the current and future business needs to meet the data warehouse project.

  • Identify DWH user goals and expectations.

  • Security and compliance needs.

​

• DWH Discovery

  • Data source analysis.

  • Number of data sources to be integrated.

  • Data source volume.

  • Data source complexity.

  • Identify number of potential users and their location.

  • High level review for the necessary security and compliance requirements.

​

• DWH Conceptualization

  • Describing the main components of the DWH solution.

  • Choosing between on-prem and in-cloud DWH deployment.

  • Outlining the deployment option (Public, Hybrid Cloud, Multi-Cloud) and choose optimum approach.

​

• ​Defining the potential of selected architecture in solving business problems.

​

• DWH Project Plan

  • Define DWH project scope, deliverables, and timeline.

  • DWH project resource and budget planning.

  • DWH design, project risk and risk mitigation strategies.

​

• DWH technology selection

  • Current infrastructure.

  • Data source systems.

  • In house competencies.

  • Data security strategy.

​

• DWH system analysis and data governance

  • Detailed analysis of each source.

  • Data type and structure, data volume generated daily.

  • Data sensitivity and data access approach.

  • Data quality, missing/poor data. Data cleansing in data source systems.

  • Relationship to other data sources.

  • Data quality criteria and data cleansing policies.

  • Data access and usage policies.

​

• Data security policy – access, encryption, data backup, and etc.)

​

• DWH Data modelling and ELT/ETL Design

  • Designing the data models for DWH and data marts

  • Identify entities, key attributes of each entity, relationships between entities.

  • Mapping attributes to entities.

  • Converting logical data model into tables, columns, indexes, keys of database.

  • Validating data models – star schema, snowflake schema, galaxy schema.

  • Design ELT/ETL process for data integration and data flow control.