Introduction to Star Schema
Star schema is the easiest approach and dimensional model where the function tables, dimensions, and facts are arranged in an organized manner and it is mostly applied in Business Intelligence and Data Warehousing. A Star schema is formed by arranging each fact with its related dimensions that resemble a star. A fact is an outcome that is infinite such as sales details and login counts. A dimension is the collection of reference data including facts, such as date, details about the product and customers. Star schema is optimized for huge data queries in data warehousing, Online Analytical Processing data cubes, and also ad-hoc queries.
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 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 –
