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:

• 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.

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