A very effective way to develop the data architecture for a data warehouse is to think about the situation from four different angles:
Data Storage - This layer is the actual physical data model for base data warehouse tables. The purpose of this model is to provide a clear and concise representation of the entities, attributes, and relationships present in the data warehouse.
Data Presentation - This layer of the data architecture is accessed by users or user tools that provide data access. It may be as simple as a series of synonyms or "select *" views, but it provides a strict layer of abstraction between data presentation and physical data representation. This abstraction layer, decoupling the presentation of data from the underlying storage of data, allows for changes to made independently on either side of that boundary.
Data Staging - Like the data presentation layer, a data staging layer creates an abstraction boundary between the processes that deliver data to the data warehouse and the way in which data is represented internally within the data storage layer. This separation allows for new and expanded data sources to feed information to the data warehouse with necessarily impacting existing data sources on the same schedule. It helps avoid the "if I add that new source, then I'll have to rewrite another 20 ETL jobs at the same time" situation.
Data Marts - The final layer is the business-problem-centric data mart. Arguably, data marts can be considered solutions that are independent of a more comprehensive data warehouse architecture, but I've included it here for the sake of contrast. In this model, a data mart is a grouping of entities and business rules that represent the information necessary to address a particular and well defined business problem. In many situations, a data mart will be a subset of data in a data warehouse that is filtered and interpreted through specific business rules. In some situations, data marts can be represented entirely through a logical abstraction layer (rather than through additional physical tables and the duplication of data). Data marts created without significantly duplicating underlying information are referred to as virtual data marts.
Word Count: 4882