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.
Dig Deeper on Data modeling tools and techniques
Related Q&A from Pete Stiglich
Find out how semantic modeling is changing data modeling and what the future holds for the use of semantic technologies in data modeling. Continue Reading
Learn about the roles of data architects when it comes to making data management project decisions. Continue Reading
Should you build custom data modeling applications or buy data modeling software? Get an expert's take, plus see how using a conceptual data model ... Continue Reading