It is a logical design technique that seeks to present the data in a standard, intuitive framework that allows for high-performance access.
Dimensions that are changing over time are referred to as slowly changing dimensions (SCD).
Type 1: override the existing value
Type 2: create a new record with effective dates
Type 3: create a new and old columns, no new rows
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been pre-computed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
- Entity Relationship modeling.
- Dimensional modelling.
- Hierarchal modelling
- Network modelling
Star schema is preferred
- A Centralized fact table surrounded by n number of dimensions is known as fact table.
- Its Normalized model.
- Easy to use and understand, because of fewer joins contains.
- More time for execution, because of more joins.