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.
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.
More on data modeling and data warehouses
Data modeling: Entity relationship (E-R) vs. dimensional data models
A guide to conceptual data models for IT managers
This was first published in January 2008