EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

What is Star Schema?

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Data Warehouse Tutorial » What is Star Schema?

What is Star Schema?

Introduction to Star Schema

A Star Schema is a schema Architectural structure used for creation and implementation of 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 shape of appearance. This is one of the efficient data warehouse schema types, which can use simple querying for accessing the data from the system, in order 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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

 Star Schema

  • Find the enterprise procedure from 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 explains the company procedure and convert them into dimensional model reality tables. This table contains data comprises 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 which are the exchange table. Details that are updated and refreshed are exchange based tables. Now comparing both tables, it’s derived that the data in 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 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 entity-relationship design and fil 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.

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. Completion of the fact table and the dimensional table is mandatory which in turn 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 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 and consists of foreign keys related to dimensional tables. This table is framed with facts values at the atomic level and permits to store multiple records at a time. There are three different types of 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 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. If 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 which 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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary 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 Modeling
    • Data Warehouse Software
    • 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
    • Snowflake Schema
    • 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
    • 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

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • 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

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

EDUCBA Login

Forgot Password?

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
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
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

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More