Data Warehousing Schemas - Data Warehousing

A schema is a collection of database objects, including tables, views, indexes, and synonyms. You can arrange schema objects in the schema models designed for data warehousing in a variety of ways. Most data warehouses use a dimensional model.

The model of your source data and the requirements of your users help you design the data warehouse schema. You can sometimes get the source model from your company's enterprise data model and reverse-engineer the logical data model for the data warehouse from this. The physical implementation of the logical data parameters—size of machine, number of users, storage capacity, type of network, and software.

Star Schemas
The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables, as shown in Figure.

Star Schemas

The most natural way to model a data warehouse is as a star schema, where only one join establishes the relationship between the fact table and any one of the dimension tables.A star schema optimizes performance by keeping queries simple and providing fast response time. All the information about each level is stored in one row.

Note: Oracle Corporation recommends that you choose a star schema unless you have a clear reason not to.

Other Schemas
Some schemas in data warehousing environments use third normal form rather than star schemas. Another schema that is sometimes useful is the snowflake schema, which is a star schema with normalized dimensions in a tree structure.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Data Warehousing Topics