More Data Warehouse Schemas

Previously I had written about the different types of schemas for data warehousing. Now I want to write about some more details. The schemas are made up of fact tables and dimension tables. Fact tables contain a bunch of numeric values called measures. They have foreign keys into the dimension tables. In fact, their primary keys are usually a composite of all the foreign keys.

The dimension table categorizes data. It has less rows than a fact table. But it will probably have a lot of attributes. These attributes are descriptive text values that help answer business questions. They can contain data collected at the lowest level. The data can be aggregated into hierarchies. These tables might take along time to load.

Here are some misc terms. A level is a position within a dimension table hierarchy. A stars query is a join between a fact table and a dimension table. A centipede schema is a star schema with a lot of dimensions.

An entity is a chunk of information. That are things of importance. Entities usually map to a database table. Attributes are components of an entity. They define the unique of the entity. Logical design is the process of identifying entities and attributes. You could use a tool such as Oracle Warehouse Builder or Oracle Designer to conduct logical design.