Warehouse Structure for Your Logical Data Model

Warehouse Structure for Your Logical Data Model

Physical Warehouse Schema

As discussed in the previous chapter, the logical data model can help you think about the logical structure of your business data and the many relationships that exist within that information.

The physical warehouse schema is based on the logical data model. It is a detailed graphic representation of your business data as it is stored in the data warehouse. The physical warehouse schema organizes the logical data model in a method that makes sense from a database perspective.

In contrast, the logical data model is a logical arrangement of data from the perspective of the general user or business analyst. For more information on what a logical data model is and how to create one, see The Logical Data Model.

The logical data model is only concerned with logical objects of the business model, such as Day, Item, Store, or Account. Several physical warehouse schemas can be derived from the same logical data model. The structure of the schema depends on how the data representing those logical objects are to be stored in the warehouse. For example you can store logical objects in the same table, in separate tables, duplicated across several tables, or in some other arrangement.

While the logical data model tells you what facts and attributes to create, the physical warehouse schema tells you where the underlying data for those objects is stored. The physical warehouse schema describes how your data is stored in the data warehouse and how it can be retrieved for analysis.

Creating a physical warehouse schema is the next step in organizing your business data before you create a project, as shown below:

The key components that make up the physical warehouse schema are columns and tables.

Columns and tables in the physical warehouse schema represent facts and attributes from the logical data model. The rows in a table represent attribute elements and fact data.