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.
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:
A surrogate key (unique key) is assigned to a fact table to identify each row uniquely.
Looking for top jobs in Business Intelligence ? This blog post gives you all the information you need!
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
Benefits of using Star Schema
Download Interview Questions asked by top MNCs in 2019?