1. Decision Support Systems

Applications that serve the management, operations, and planning levels of an organization to help people make decisions about future issues and problems by analyzing historical data stored in a data warehouse.

1.1 Bifurcated Environment

For a read-only OLAP database, it is common to have a bifurcated environment, where there are multiple instances of OLTP databases that ingest information from the outside world which is then fed into the backend OLAP database, sometimes called a data warehouse.

There is an intermediate step called ETL, or Extract, Transform, and Load, which combines the OLTP databases into a universal schema for the data warehouse.

1.2 Star Schema

Star schemas contain two types of tables:

1. Fact table → contains multiple “events” that occur in the application.
   1. It will contain the minimal unique information per event
   2. the rest of the attributes will be foreign key references to outer dimension tables.
2. Dimension tables → contain redundant information that is reused across multiple events.

In a star schema, there can only be one dimension-level out from the fact table.

Since the data can only have one level of dimension tables, it can have redundant information. Denormalized data models may incur integrity and consistency violations, so replication must be handled accordingly.

Queries on star schemas will (usually) be faster than a snowflake schema because there are fewer joins.

1.3 Snowflake Schema

Snowflake schemas are similar to star schemas except that:

They allow for more than one dimension out from the fact table.