Data Warehouse Architecture – Meet Your Criteria

The term "architecture" refers to the structural design of an object. We usually think of this in the context of buildings , but it is also used to refer to more abstract objects like databases. A data warehouse is a database designed to meet certain criteria, and the database architecture is the structure that is employed to do that.

Since a data warehouse is a relational database, it is made up of tables of data that can be joined together in a query to produce a desired output. The data within a table is organized in columns, or attributes , and the combination of attributes in each table is determined by the architecture of the database.

Two commonly employed approaches to data warehouse architecture correspond to two very different approaches to building a data warehouse. One approach is based on the notion of the entities and relationships involved in a business process . The other is based on the descriptive dimensions by which numeric facts are organized for presentation to decision makers (as in a spreadsheet, for example). The first approach begins where the data originates, while the second works backward from the final reporting objective. The second architecture can be derived from the first, but not the other way around.

Both approaches for the data warehouse architecture have significant benefits and drawbacks. The entity-relationship approach creates an integrated model of the enterprise's business processes, from which it is possible to extract quickly the dimensioned data marts at almost any level of detail. The downside is that it takes a comparatively long time to design and implement from legacy data sources and start to derive real benefits. The dimensional approach is much quicker to implement, but may not lead to a satisfactory integration of data across business processes. A data warehouse created in this way may consist of multiple, sometimes overlapping data marts, which can lead to the very kind of data inconsistencies a data warehouse is supposed to cure.

The choice of data warehouse architecture will entail certain decisions and commitments in any data warehouse project. The dimensional approach will probably be the only practical choice if the initiative for the project is coming from within a single business process, as is often the case. Many organizations taking their first stab at a data warehouse want to get all their historical sales data together in a single database to perform seasonal and trend analysis, for example. While it is possible to create entity relationship models of the sales business process, it is much quicker and more direct simply to organize a data mart of sales and derived sales statistics by product or service, geography, market segment and time.

On the other hand, if one of the objectives of the data warehouse is to facilitate strategic analysis for the business as a whole, data from many business processes must be combined. Attempting to do this by working backward from a dimensional model to the source data is a lot harder than it sounds. The entity relationship approach will give results that are more satisfactory, much sooner.

Any organization tackling the big decision of which data warehouse architecture is appropriate, and hence which approach is appropriate would be wise to work with experienced consultants who can help guide in the decision-making. The Worcester Group brings over twenty-five years of relevant experience to assist in your data warehouse decision making and implementation.

We have provided consulting services to Fortune 1000 companies and smaller organizations in many industries, building corporate data warehouses or developing stand alone decision support systems. Why not contact us today to learn more about how we can provide you with quick, workable solutions at a reasonable price.