The Star Schema

A good dimensional data warehouse has a simple database structure. Technically, a simple structure means faster queries. In a dimensional data warehouse, the implementation relational database has two types of tables, the fact table and the dimension table. The fact table consists of the facts (or measures) of the business. Dimension tables contain descriptions for querying the database.

The tables in a data warehouse are related such that the schema looks like a star, hence the term star schema


A star consists of a fact table surrounded by two or more dimension tables. A one-star structure has one fact table only. A multi-star structure has multiple fact tables, one for each star

dimension tables may be shared among multiple fact tables.

In Data warehouse the star structure, the snowflake structure could also be used However, it is more difficult to model than the star structure. As well, the snowflake structure is not that easy to understand and implement and the performance of its queries is slower than that of the star structure. These drawbacks make the snowflake structure unsuitable for dimensional data warehousing.

The Dimension, Fact table used term to suffix the table _dim, _fact.

Every Dimension table has one or more primary key which is reference to fact table. so based on this we can build query to retrieve the date what we required.

Above figure..

The customer_dim (customer dimensional table) is reference the Sales_order_fact table so that we can trace customer information. By using the Product_id in sales_order_fact table, we can retrieve the product information in production_dim table. By joining the sales_order_fact table and the date_dim table on the date_id column, you’ll get the order date. And by joining the fact table and the order_dim table on the order_id column, you obtain the order number.