Dimensional Data Modeling - Star Schema

Star Schema


A star schema is a relational schema that is a dimensional:

The characteristic star-like structure is often called a star join schema. This term dates back to the earliest days of relational databases.

The main advantages of star schemas are :

  • The data is easier to understand and navigate. User agree immediately that the dimensional model is their business
  • Provide highly optimized performance for typical star queries (fewer join, very strong assumption about first constraining to attack the fact table, …)
  • extensible to accommodate change. (You don't need to change this schema for future business need)
  • Are widely supported by a large number of business intelligence tools, which may anticipate or even require that the data warehouse schema contain dimension tables.

A star join is a primary key to foreign key join :

Star schemas are used generally in the data marts to deliver business data to specific business units for drill down, analysis and other OLAP operations.

Star Schema

Dimension or Measure Attribute ?

It's some time difficult to make the choice between measure and dimensional descriptif attributes, especially when the value is a number. You must so answer to this question :

Where the value is more used ?
Measures are Descriptif Attributes are
additional used as report constraint
semi-additive used as row header
number that behaves like a textual descriptor
discrete and constant

Accommodate change

We can :

  • add completely new dimension
  • add new fact to the fact table assuming that the level of detail is consistent with the existent fact row.
  • supplement pre-existing dimension with new unanticipated attributes

Discover More
Bobj Loop Structure Pane
BOBJ - Loops

in BOBJ. “multiple stars” Depending on the nature of the loop, you can resolve the loop in Designer using either an alias to break the join path, or a context to separate the two join paths...
Thomas Bayes
Data Mining - Data (Preparation | Wrangling | Munging)

Data Preparation is a data step that prepares your data for further analyis. It's a key factor in any data project: mining, ai analytics Preparing has several steps that are explained below. ...
Data System Architecture
Data Partition - Row or Column Data Store

Most data warehouses either use a star schema (cube), the central fact table in such a schema usually has many attributes. 50 attributes is very common and 200 is not unusual. So assume a fact table with...
3nf Sales
Data Warehouse - Data Models

In the data warehousing environment, you can find several relational data models. The staging area is a temporary layer where data can persisted in order to assist the data integration in the persistence...
Dw Layers
Data Warehouse - Layer (Architecture)

Name Properties Stage Layer Real-Time, CDC, continuous refresh DataWarehouse Layer / ODS Normalized, Data History, refresh: 2-6 daily Data Mart Performance, Access layer, Star schema, refresh: 1-4...
Oltp Dwh
Data Warehousing - Contrasting OLTP and Data Warehousing Environments

One major difference between the types of system is that data warehouses are not usually in third normal form (3NF), a type of data normalization common in Online Transaction Processing (OLTP) environments....
Data Mart
Data Warehousing - Data Marts

A data mart is a subject-or application-specific multidimensional schema build on the top of an Enterprise Data Warehouse. Data marts are designed for a particular line of business and is an aggregation...
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 - 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...

Share this page:
Follow us:
Task Runner