Analytical databases

Analytical databases are a family of database management systems that are often contrasted with transactional databases.

Both analytical and transactional databases may be non-relational or relational – which is another way to classify database management systems.

Analytical databases are used for Online Analytics Processing (OLAP). Data warehouses may be used for this purpose, with the source data coming from the Online Transaction Processing (OLTP) done by transactional databases, or other sources.

Analytical databases do not tend to be customer-facing. However, data may be moved from customer-facing databases into analytical databases via ETL to feed management information systems and other analytical workloads used for decision making.

Compared to transactional databases, the transactions performed in analytics databases tend to be longer and involve more complex queries. That makes query optimization especially important for analytical workloads.

Analytical schemas are typically denormalized. The star and snowflake schemas used in data warehousing deliberately duplicate dimensional data across fact table rows so that aggregate queries can be answered with few joins, trading single source of truth for read speed. See denormalization for the trade in general.

Examples of relational analytical databases include Amazon Redshift, Teradata, and HP Vertica. SAP HANA is a notable in-memory analytical database. Examples of non-relational analytical databases include Amazon Elastic MapReduce (EMR) and other MapReduce databases.