What is Star schema?
The implementation of a data warehouse and business intelligence model involves the concept of Star Schema as the simplest dimensional model. Similar to every other dimensional model, star schema consists of data in the form of facts and dimensions. The reason behind the name ‘Star Schema’ is that this data model resembles a star with ends radiating from the center , where the center refers to the fact table and the radiating points are dimension tables.
The fact table consists of the measurable and quantitative data while the dimension table is made up of descriptive attributes that are related to fact data.
Fact Table
Refers to measurements for specific event. Along with numeric values, the table also consists of foreign keys pointing to the dimension tables. The table is designed in a way such that the facts included can store values at atomic level , which allows the storage of large number of records at a time. There are primarily three kinds of fact tables:
- Transaction fact tables recording facts about a specific event (holiday sales event)
- Snapshot fact tables recording facts for a given period (account details at end of the month quarter)
- Accumulating snapshot tables recording rapid aggregations at a given time( total sales in a month)
A surrogate key (unique key) is assigned to a fact table to identify each row uniquely.
Dimension Table
Since dimension tables support descriptive attribute data, the number of records inserted are relatively lesser than the fact table. The dimensions in a data warehouse can explain a variety of characteristics. Some of the frequently used dimension tables are Time dimension table, Geography dimension table, Product dimension table, Employee dimension table, Range dimension table and others.
Similarly, the tables are also assigned a surrogate primary key (single-column integer data type) referring to the combination of dimension attributes forming the natural key.
For instance, you can create a Sales fact table referring to an event with product key, customer key, promotion key, date key, items sold and revenue generated. For every fact table key, there is a dimension table like Product dimension table containing reference information as product name, product type, quantity, size, color, description and others. Likewise, there is a customer dimension table with customer name, email id, phone number, birth date, gender, address and so on for all fact table foreign keys. This fact table can help business people calculate their total sales, total items sold in a month or day or total revenue generated at the end of the day and many more queries.
Features of Star Schema:
- Supports rapid aggregations and calculations like total items sold at month end, total revenue generated at end of day and others.
- These aggregates can then be filtered and grouped using various query statements and data can be accessed as per requirements.
- A star schema has the capability of getting partially normalized to cater to some explicit DWH needs. the related information of the normalized table is stored in multiple dimension tables.
Benefits of using Star Schema
- Easy to use and understand. The queries to fetch data re simple and answers most of the data questions of the users.
- Simple business logic compared to normalized schemas
- Faster aggregations and swift retrieval of data
- With smaller number of tables to be joined, star schema is effective and efficient for businesses
- Supported by most Business Intelligence Tools