EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Data Warehouse Tutorial What is Star Schema?
Secondary Sidebar
Data Warehouse Tutorial
  • Basic
    • What is Data Warehouse
    • Data Warehouse tools
    • Career in Data Warehousing
    • Benefits of Data Warehouse
    • Data Warehouse Architecture
    • Data Warehouse Design
    • Data Warehouse Implementation
    • Data Warehouse Features
    • Data Warehouse Modeling
    • Data Warehouse Software
    • Data Warehousing
    • Types of Data Warehouse
    • 10 Popular Data Warehouse Tools
    • Data Lake Architecture
    • Three Tier Data Warehouse Architecture
    • Data Warehouse Process
    • Database Parallelism
    • What is OLTP
    • What is OLAP
    • OLAP Tools
    • Types of OLAP
    • Operations in OLAP
    • MOLAP
    • HOLAP
    • Data Warehouse Schema
    • Data Warehouse Components
    • Snowflake Schema
    • Snowflake Architecture
    • What is Star Schema
    • Galaxy Schema
    • What is Fact Table
    • Kimball Methodology
    • Data Warehouse Testing
    • Operational Data Stores
  • ETL
    • What is Data Mart
    • What is Data Cube
    • What is a Data Lake
    • What is Data Integration
    • What is ETL
    • What is ETL Testing
    • ETL Testing Tools
    • ETL architecture
    • Dimension Table
    • Multidimensional Data Model
    • Fact Constellation Schema
    • ETL Process
  • Interview Questions
    • Data Warehouse Interview Questions
    • ETL Interview Questions
    • ETL Testing Interview Questions
    • Data Warehousing Interview Questions

Related Courses

Business Intelligence Course

All in One Data Science Course

Data Visualization Certification Courses

What is Star Schema?

By Priya PedamkarPriya Pedamkar

What is Star Schema?

Introduction to Star Schema

A Star Schema is a schema Architectural structure used to create and implement the Data Warehouse systems, where there is only one fact table and multiple dimension tables connected to it. It is structured like a star in the shape of its appearance. This is one of the efficient data warehouse schema types, which can use simple querying to access the data from the system to derive logical contents for analytical and report generation purposes.

How to Create a Star Schema?

Here the user is going to create Star Schema by conversion of the entity-relationship model. Entity-relationship models are too complex to explain the functional quantities and attributes, so it is simplified to dimensional star schema as follows:

 Star Schema

  • Find the enterprise procedure from the entity-relationship view and understand the model, which can be split into several dimensional models. An entity-relationship consists of business data.
  • Find many to many tables in entity-relationship which explain the company procedure and convert them into dimensional model reality tables. This table contains data comprised of the fact table and a dimensional table with numeric values and unique key attributes.
  • The idea behind this process is to differentiate the exchange-based information tables or the information-erased tables. So it is necessary to design many to numerous relationships. For example, in the ERP database, there are invoice details that are in the exchange table. Details that are updated and refreshed are exchange-based tables. Now comparing both tables, it’s derived that the data is genuinely static.
  • The reality table is a representation of a dimensional model that shows many to numerous networks between finite measurements. This results that foreign keys in reality tables share many to numerous that is a countable relationship. most of this table falls under exchange based tables
  • The last step in designing the star schema is to de-normalize the residing tables into measurement tables. The mandatory key is to make a duplicate key. This key relies on the reality table, which helps in better understanding. Find the date and time from the entity-relationship design and fill the dimension table. Dates are saved as the date and time stamps. A date dimension column represents the year, month or date or time.

Example: The time dimensional table has TIMEID, Quartername, QuarterNo, MonthName, MonthNo, DayName, DayofMonth, DayOfWeek, which can be important criteria of dimensional tables. Similarly, all tables have Unique id and attributes. Query languages such as SQL can be applied to data mining, data warehouse, and data analytics.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of Cube Definition:

Define cube (cube-name)(dimension-list): (measure-list)

Cubes are deployed to address the alerts at various levels, and response time to answer the query is minimum. It is available as a pre-built design and applicable in required situations. Creating of Star schema is very easy and efficient to apply and is adaptable too. Completing the fact table and the dimensional table is mandatory, which forms as star and can be formed using SQL queries or running code. This design is made for better understanding and easy fetching of data.

Characteristics of Star Schema

1. Star schema provides fast aggregations and calculations such as total items sold and revenue of income gained at the end of every month. These details and process can be filtered according to the requirements by framing suitable queries.

2. It has the capacity of filtering the data from normalized data and provide Data warehousing needs. The associated information of the normalized table is stacked in the multiple dimensions tab. A unique key is generated for each fact table to identify each row.

3. Fact Table is the measurement of specific events, including finite number values, consisting of foreign keys related to dimensional tables. This table is framed with facts values at the atomic level and permits to store of multiple records at a time. There are three different types of the fact table.

4. Transaction fact tables consist of data about specific events such as holiday events, sales events.

5. Recording facts for given periods like account information at the end of every quarter.

6. Tables with rapid aggregation for a certain period is called as Accumulating Snapshot tables.

7. Dimensional tables provide detailed attribute data, records found in the fact table. The dimension table can have varied features. Dimensional tables are used mainly as Time and date Dimension table, Product and purchase order Dimensional table, Employee and account details Dimensional table, Geography and locations dimensional table. These tables are assigned with a single integer data type which is the duplicate primary key.

8. The user can design his table according to requirements. For example, if he needs a sales dimensional table with product and customer key, date and time key, the revenue of income generated key. Suppose the businessman frames a product dimensional table with key attributes such as color, date of the purchased item, promotion key and client key.

Advantages

  • It is formed with simple logic and queries easy to extract the data from the transactional process.
  • It has a common reporting logic that is implied dynamically.
  • Star schema can offer an increase in performance for reporting applications.
  • Star schema designed by feeding cubes applied by the Online Transaction Process to build and make the cubes work effectively.

Disadvantages

  • It has high integrity and a high de-normalized state. If the user fails to update the values, the complete process will be collapsed. The protections and security are not reliable up to the limit. It is not as flexible as an analytical model and does not extend its efficient support to many relationships.
  • Star schema is deployed in the database to control the faster recovery of data. The query is employed to select the need rather than searching the whole database. The filtered and selected data can be applied in different cases. Hence this star schema is a simple model that is adopted easily.

Recommended Article

This is a guide to What is Star Schema? Here we discuss the Introduction to Star Schema and its Characteristics along with advantages and disadvantages. You can also go through our other suggested articles to learn more –

  1. Overview of Fact Table
  2. Star Schema vs Snowflake Schema | Top differences
  3. Data Warehouse Modeling with Types
  4. AWS Data Pipeline
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course

Related Courses

Business Intelligence Training (12 Courses, 6+ Projects)4.9
Data Visualization Training (15 Courses, 5+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more