Data modeling for data warehouse projects

Learn how to begin a data warehouse project and why creating a data model is an important step.

My company (in the aviation industry) has attempted to implement a new data warehouse initiative, delivering safety data to support its users' analytic and reporting needs. Before moving forward with yet another design effort (two have already failed), what would you recommend we do? We need a blueprint to build on.

The first question to ask at the beginning of a data warehouse initiative is what happened with the previous initiatives? A common problem is having too large a scope – trying to "boil the ocean" rather than delivering value to the users in iterative steps.

Building a data warehouse is also significantly different than developing a traditional online transaction processing (OLTP) application – sometimes teams try to follow the same software development lifecycle (SDLC) formula as for an OLTP application, which often doesn't work due to the nature of analytics. For example, a traditional OLTP application has a clearly defined end point (e.g. capture data via a screen), whereas data warehouses have to be more flexible to support the needs of analytics users that will vary over time – they often don't know what types of analysis they will need to perform down the road and so the data warehouse needs to accommodate rapid change.

As part of the requirements definition process, I strongly recommend developing conceptual data models in order to understand the business and to help scope the project. Too often, data warehouse modeling starts with the design models for the data warehouse itself, instead of modeling the business first in an entitry relationship (ER) diagram. Conceptual data models are business models -- not solution models -- and help the development team understand the breadth of the subject area being chosen for the data warehouse iteration project. It is also a tool to help validate your dimensional models (star schemas) that the business will query against.

I strongly recommend that you engage the services of a consulting company that specializes in data warehousing and has a proven track record, at least to help determine the roadmap and to establish a framework for building the data warehouse. Data warehouse projects typically have high exposure within the organization, and can deliver tremendous benefits – but are highly complex in nature.

Next Steps

Data warehouse development: Four strategic steps
What are the benefits of a conceptual data model?
A guide to conceptual data models for IT managers

Dig Deeper on Data modeling tools and techniques