Foundational Data Warehouse – Design Phase

Continuing the blog series, Foundational Data Warehouse, we will be taking a deeper dive into the next foundational building block which is the Design phase. Hopefully, by the end of this blog you will be able to understand the importance of this phase.

Often it is tempting to skip the design and jump right into development. In certain situations, it may make sense, but in more situations than not it is best to work through the design. Using the analogy of a young couple building a house, by the end of design, you should have a clear understanding of the overall foundation of the house and what each team member will be working on. The physical data model, similar to the houses blue prints, should be used as a means to communicate amongst the different contractors (i.e. plumber, cabinetry person, electrician, etc.) to help start visualizing the end result before you even break ground. Just like a house, each role (i.e. tech lead, ETL, BI, DA, etc.) have their own set of documentation required to ensure they can meet the customers needs.

This phase is referred to as the HOW? (How will I extract this field, how will I need to transform the data, how will this fit into the overall architect, etc). During this phase, design documents are complete, test plans created, the user interface tool is determined, and a physical data model should be delivered.

These documents should be used as a tool to help developers think through the process prior to development, forcing developers to think of the overall data movement strategy and how the new objects will fit into the grand scheme of things. As part of the design review process, the design documents along with the business requirements document (previous blog, Foundational Data Warehouse - Analysis Phase) will be used to continue verifying that all the requirements are being met. The documentation also lends nicely for iterative development given that the Lead can hand-off the design documentation to other Developers and proceed to the next phase or project.

When you come out of the Design phase you should have a good understanding of the data movement strategy, the data population to ensure consistency amount the data, how many tables you will be building, how you will build those tables, what your reports will look like, how you will deliver the reports to end-users, report security, etc. Most importantly the documentation should be used as a guide to ensure you are meeting all metrics defined in the requirement documentation. Below are some examples documents that may come out of the Design phase:

Physical Model: This document is usually created by the Technical Lead or some other Lead that has a good understanding of the requirements and is familiar with data warehousing modeling approach. This is generally the physical model that is used by all the roles (ETL, BI, BA) to help build their objects and ensure that all metrics are being met.

High Level Design Document: This document is usually created by the BI/ETL Lead(s) that has a good understanding of the EDW strategic direction. These document should be used as a mechanism to review the high level plan amongst the lead(s) and developers.

  • From an ETL perspective the document should contain the following: A diagram of the data movement strategy, how the new objects will fit into the current process, assumptions, data retention period, service level agreement (SLA), delete processing, items out of scope, etc.
  • From a BI perspective the document should contain the following: Report strategy indicating how the reports fit into the EDW strategy, high level report development, reports published, report accessibility, report security, service level agreement (SLA), assumptions, out of scope, data retention, etc.

Detail Design Specs: The BI/ETL Developers generally creates these document using the high level design document as a guide. The detail design documents are generally specific to interface or report, depending on the role, and should be at the field level. Each interface should also contain a unit test plan. These documents should be used as a guide along with the model to ensure you are meeting all the metrics defined in the requirement documentation.

In conclusion, the Design phase is a critical part of the project lifecycle. There should be a lot of reviews taking place amongst the team members to ensure you are meeting all the requirement and that everyone understands the end result. By the end of this phase you would likely have determined if you are missing any metrics, just like a house, it is cheaper to find issues in design then it is after constructed and implemented.

Now that you have a clear vision of what you are building, you are ready to get your hands dirty, and begin the Development phase. In my next blog we will be discussing the importance of the Development phase and possible deliverables.