Dimensional Data Modeling - Grain

Star Schema


The grain is the combination of all lower hierarchy level of the descriptif attribute of a relation (table or query).

At the top level, there are two main options in choosing the level of granularity:

  • Unsummarized/Atomic (transaction level granularity): this is the highest level of granularity where each fact table row corresponds to a single transaction or line item
  • Summarized: transactions may be summarized by a subset of dimensions or dimensional attributes. In this case, each row in the fact table corresponds to multiple transactions

The most granular or atomic data (atomic as an indivisible unit of work) has the most dimensionality. Atomic data is highly dimensional. Preferably, you should develop dimensional models for the most atomic information captured by a Event (Timed Measure|Action). Atomic data is the most detailled information collected: such data cannot be subdivided further.

Example: If a high grain is the month whereas a low or detail grain can be the day

A data warehouse almost always demands data to the lowest possible grain of each dimension not because queries want to see individual low level rows but because queries need to cut through the details in very precise ways.

The lower the level of granularity (or conversely, the higher the level of summarization), the less storage space required and the faster queries will be executed.

Example of grain

  • An individual line item on a customer's retail sales ticket as measured by a scanner device
  • A line item on a bill received from a doctor
  • An individual boarding pass to get on a flight
  • A daily snapshot of the inventory levels for each product in a warehouse
  • A monthly snapshot for each bank account

Discover More
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...
Star Schema
Dimensional Data Modeling - Descriptif Attribute (Dimensional Attribute)

A descriptif attribute is class attribute that describe a property or characteristic of a dimension. They are used to label, filter and/or group on. measures Typical attributes for a product dimension...
Star Schema
Dimensional Data Modeling - Dimensional Schemas

This section is dimensional data modeling, That is the building of a cube (hypercube) A dimensional schema is a schema based on dimension. The dimensional schema can modeled: in a relational database...
Star Schema
Dimensional Data Modeling - Fact Table

A fact table is a table that stores fact where a fact can be seen as the representation of an event. It's also known as: Transactional history Unalterable fact. (The content of this kind of table...
Star Schema
Dimensional Data Modeling - Level

A level in a dimensional hierarchy is a set of attributes that has: an mandatory identifier attribute (known also as the business identifier) and descriptive attributes (optional) A level is...
Data System Architecture
Logical Data Modeling - (Atomic|Indivisible)

Atomic means indivisible. Atomic data is the most detailed information collected. Such data cannot be subdivided further and is said to be an the lowest grain. In data analysis, this is in general data...
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....
Obiee Fragmentation Content One Column
OBIEE - Grain - Level of (summarization|aggregation)

Grain definition in the context of OBIEE. The following list describes the different grains in navigating a query: Query grain. The grain of the request. Aggregation grain. The grain of the aggregate...
Toad Lock
Oracle Database - Locks

Lock Mechanism in Oracle Oracle Database provides: data concurrency, consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished. and...
Data System Architecture
Relational Data Modeling - Aggregate Table (Summary Table)

An Aggregate table is a persistence unit of aggregate data. Aggregate tables are also known as summary table (from summary) materialized view A aggregate is a table that contains the result of a query...

Share this page:
Follow us:
Task Runner