Aster Database uses a dimensional model known as a star schema to provide high performance for large, data analytics workloads. The star schema consists of fact and dimension tables.
When you define an Aster table, you must specify whether it is a FACT or DIMENSION table. Your decision affects how Aster Database stores the table data and optimizes it for the types of operations that use the table, such as joins between tables.
- Fact Table
- A fact table is a single, large table that records business process data as facts, such as measurements or metrics for a specific event. Fact tables typically contain numeric values and foreign keys to the dimensional data stored in other tables.
- Fact tables contain two types of columns: fact columns and foreign key columns. In Aster Database, you must declare a distribution key column for each fact table using the Distribute by (DISTRIBUTED BY HASH) field in the Storage tab of the Table object form in Object List Viewer. The selection you make tells Aster Database how to physically distribute the table data across v-workers. A fact table is always a distributed table.
- Dimension Table
- A dimension table is a set of tables that contain the domains of values that describe attributes for the events and factual data recorded in a fact table. Each dimension table contains a set of values for a specific dimension. For example, a customer contact table would contain detailed information about each customer such as customer _id, name, phone, address, and other information. A dimension table can be a distributed table or a replicated table.
Most dimension tables are replicated in Aster Database with a copy on every node in the cluster. Optionally, you can specify your dimension table as distributed by (DISTRIBUTE BY HASH) by declaring a distribution key column in the Distribute by field in the Storage tab of the Table object form in Object List Viewer. This option distributes the table across nodes using the distribution key you specify, instead of replicating the table on every node. In Aster Database a replicated table is by definition a dimension table.