Relational Data Modeling - Aggregate Table (Summary Table)

Data System Architecture

About

An Aggregate table is a persistence unit of aggregate data.

Aggregate tables are also known as summary table (from summary)

A materialized view can be used to implement aggregate data

A aggregate is a table that contains the result of a query (generally an aggregated one but not always …).

Usage

Query Performance

Data - Cache - Pre-aggregating and storing additive information is the standard practice for improving the query performance of databases.

An aggregate table is for an analytics system what an index is for an OLTP system.

When users request information at a high grain of aggregation, Pre-aggregating and storing additive information is the standard practice for improving the query performance of relational databases.

Query Rewriting

When a user or an application asks for a column in a fact table, a sql engine that is aware of the aggregate table is capable of rewrite the query against/using the aggregate table. See SQL Engine - Query Rewrite

When data becomes bigger

Parallel

To improve the throughput, you can run the process in parallel by adding more

  • hardware (more cpu, memory, disk)
  • node (ie computer) - in distributed computing such as Hadoop.

Incremental Processing

Stream - How to capture change of state - Change Data Capture (CDC)

Implementation

You can implement an aggregate table with:

Documentation / Reference





Discover More
Card Puncher Data Processing
BOBJ - Derived tables

Derived tables are similar to database views, with the advantage that the SQL for a derived table can include prompts. Derived tables appear in your Designer schema in exactly the same way as normal database...
Card Puncher Data Processing
BOBJ Getting Started

The most problem with Business Object is to find good tutorial to start. The key word to find tutorial on BOBJ is “Getting Started”. You can find this kind of document : on the web : Try...
Data System Architecture
Data Management - (Transaction|Request|Commit|Redo) Log

(Transaction|Request|commit) logs are structured log file store all changes made to the data as they occur. They permits the implementation of : transaction isolation undoable operation. recovery...
Data System Architecture
Data Warehouse

A data warehouse is a large central data repository of current, history and summarised data coming from operational and external sources used primarily for analysis. s is large historical databases for...
Data System Architecture
Data Warehousing - 34 Kimball Subsytems

This page takes back the Kimball Datawarehouse 34 Subsystem as a table of content and links them to a page on this website....
Star Schema
Dimensional Data Modeling - Dimension (Perspective)

A dimension is a part of a dimensional schema and provide the basis for analyzing data through slicing and dicing A dimension is just a set of descriptif attribute that are generally grouped: in a...
Star Schema
Dimensional Data Modeling - Hierarchy

in dimensional data modeling A hierarchy is a set of levels having many-to-one relationships between each other, and the set of levels collectively makes up a dimension in the form of a tree (A tree...
Kafka Commit Log Messaging Process
Kafka - Ksql

Without the LIMIT keyword, the SELECT query would run indefinitely until you stop it by pressing ==== Persistent Query ==== Unlike the non-persistent query above, Results from this query are written...
Obiee Fragmentation Content One Column
OBIEE - Aggregate (Navigation|Table|Content)

The aggregate navigation capability of the Oracle BI Server allows queries to use the information stored in aggregate tables automatically. When users request information at a high “grain” of aggregation,...
Obiee Aggregate Lts
OBIEE - Aggregate Navigation with level-based fragmentation

OBI Server features have the ability to retrieve queries from an aggregate table defined through level of the hierarchy instead of the fact table. This ability is a part of the aggregate navigation feature....



Share this page:
Follow us:
Task Runner