An Analysis of Star Schema: Components, Implementation, and Benefits

This report provides an in-depth analysis of the star schema, a fundamental data modeling technique used in data warehousing and business intelligence. The report begins with an introduction to star schema, emphasizing its role in organizing data into dimensions and facts for efficient querying and analysis. It then delves into the core components of the star schema: the fact table, which stores quantitative data and metrics, and the dimension tables, which provide context and attributes to the facts. The report explains the relationship between the fact and dimension tables, including the use of foreign keys for joining data. It also discusses the requirements and benefits of using a star schema, such as supporting OLAP cubes and enabling rapid calculations and filtering of data. Finally, the report concludes by summarizing the key aspects of the star schema, highlighting its importance in handling large datasets and providing valuable insights for business decision-making.

Running head: STAR SCHEMA, ITS PARTS AND USE
STAR SCHEMA, ITS PARTS AND USE
Name of the Student
Name of the University
Author Note

1STAR SCHEMA, ITS PARTS AND USE
Executive Summary
The prime focus of this report is on one of the schema of data warehouse known as star schema.
Later in the discussion, the integral parts of star schema is discussed in detail which are
dimension table and fact table. The dependency between the tables is also discussed followed by
a graphical depiction of the tables. The report concludes with the concept of the easy use of star
schema and the level of dependency among the fact and dimension table.

2STAR SCHEMA, ITS PARTS AND USE
Table of Contents
Introduction......................................................................................................................................3
Discussion........................................................................................................................................3
Star Schema.................................................................................................................................3
Fact Table....................................................................................................................................4
Dimension Table..........................................................................................................................4
Requirement of the Star Schema.................................................................................................5
Fact Table and Dimension Table.................................................................................................5
Conclusion.......................................................................................................................................6
References........................................................................................................................................7

3STAR SCHEMA, ITS PARTS AND USE
Introduction
In the field of data warehouse some of the integral parts include Star schema, which is
considered as the simplest forms of data mart schema, Fact table, which stores the metrics and
facts of the process related a business and Dimension tables, a structure used for creating
categories in measures and facts enabling to answer the business questions by the users. This
report aims to describe these three instances of data warehousing and requirement of star
schema.
Discussion
Star Schema
Star Schema is considered to be a dimensional model of simplest form in the field of data
warehousing and Business Intelligence, where the organisation of data is done into dimension
and facts (Sidi et al. 2016). Fact being the event, while dimension is the relative information
about the event taking place. The diagram of a star schema is done by associating all the facts
with its respective dimensions resulting into the shape resembling a star. Querying large sets of
data is the main task for which the star schema is optimized. Usage of Star Schema is done in the
data warehouses resulting in the support to OLAP (Online Analytical Processing) cubes,
analytical applications and business intelligence and queries related to ad hoc (Chevalier et al.
2015). Rapid calculations of fact records are supported by the star schema and the grouping and
filtration of these calculations could be easily done by the dimensions.

4STAR SCHEMA, ITS PARTS AND USE
Fact Table
The central table for a data warehouse is the fact table for a star schema. The main
components of the fact table is quantitative data used for the analysis and fact table
denomarlization is often done. Regarding the fact table to work it is necessary to have it
collaborated with the dimension table (Darmont, Bentayeb and Boussaid 2017). The data to be
analysed are stored in the fact table and the dimension table on the other hand is comprised with
the data that decides the way, the data from the fact table is to be analysed. Therefore, the fact
table includes two columns, foreign keys column and measures column. The former one
consisting of the data that performs the joining between the two tables and the later column
consists of the data that are to be analysed. There are limitations during the construction of fact
tables regarding physical and data aspects. Indexing can help with the consideration of both of
the size of the object and the access path (Azez, Khafagy and Omara 2015). However, the logical
approach towards the construction of the table should not result into any restrictions. The
construction of the table should be done keeping the current and future requirements under
consideration. The construction of the tables should be done in such a way that there remains
maximum flexibility so that future editing do not lead into the entire reconstruction of the data.
Dimension Table
In the star schema of a warehouse of data there is another table known as Dimension
table which holds all the attributes that will support the entire data set of the fact table to be
analysed (Salaki, Waworuntu and Tangkawarow 2016). It consists of the data that provide the
support to the data of the fact table in order to provide a way in which the data could be
analysed. The basic functionality of the dimension table is supporting in nature, as referential
data is something that the table is entirely built up with. Dimensional table forms the core of the

5STAR SCHEMA, ITS PARTS AND USE
dimensional model as it helps to analyse the facts and provides suitable answers to the business
questions.
Requirement of the Star Schema
The usage of Star Schema is done as a basic form of implementation of Online Analytical
Processing Cube. When the fact table holds a 1-to-many relationship with every other dimension
of the same schema then application of Star Schema is the most appropriate one (Aziz and Sabri
2017). The example for the following kind of relationship could be stated as a fact table
consisting of sales data and that of the dimension table is list of stores. In this given situation,
one store can have many sales but each sale has to be done from one particular store. Hence, this
results into the inference that one row of the dimension table could be equivalent to many rows
of the fact table establishing the relationship of one-to-many between the tables.
Fact table and Dimension table
Time
ID
Product
ID
Customer
ID
Unit
Sold
3 15 3 2
7 20 2 1
7 5 1 2
Fig.1 – Fact Table
(Source: Created by author)

6STAR SCHEMA, ITS PARTS AND USE
Customer ID Name Sex Qualification Earning Area
2 Matthew Perry M 8 3 3
1 Emilia Clarke F 4 2 4
3 Arya Stark F 5 1 1
Fig.2 – Dimension Table
(Source: Created by author)
Conclusion
The above discussion comes to the conclusion that the star schema is the data mart of
simplest form for a data warehouse and fact and dimension tables are integral part of it. The
dependency among the tables is established and how the star schema is beneficial for handling of
large sets of data is also discussed in brief. Finally the relation between the fact and dimension
table is also described.

7STAR SCHEMA, ITS PARTS AND USE
References
Azez, H.S.A., Khafagy, M.H. and Omara, F.A., 2015. JOUM: an indexing methodology for
improving join in hive star schema. Int. J. Sci. Eng. Res, 6, pp.111-119.
Aziz, A. and Sabri, P.S.U., 2017. Realistic Approach to Design Data Warehouse
Schema. IJCSIS, 15(1).
Chevalier, M., El Malki, M., Kopliku, A., Teste, O. and Tournier, R., 2015, May. Benchmark for
OLAP on NoSQL technologies. In 9th IEEE International Conference on Research Challenges
in Information Science (IEEE RCIS 2015) (pp. pp-480).
Darmont, J., Bentayeb, F. and Boussaid, O., 2017. Benchmarking data warehouses. arXiv
preprint arXiv:1701.00399.
Salaki, R.J., Waworuntu, J. and Tangkawarow, I.R.H.T., 2016, April. Extract transformation
loading from OLTP to OLAP data using pentaho data integration. In IOP Conference Series:
Materials Science and Engineering (Vol. 128, No. 1, p. 012020). IOP Publishing.
Sidi, E., El Merouani, M., Amin, E. and Abdelouarit, A., 2016. Star Schema Advantages on Data
Warehouse: Using Bitmap Index and Partitioned Fact Tables. International Journal of Computer
Applications, 975, p.8887.