Dimensional Modeling

Dimensional modeling represents data with a cube operation, making more suitable
logical data representation with OLAP data management. The perception of Dimensional
Modeling was developed by Ralph Kimball and is consist
of "fact" and "dimension" tables.

Objectives of Dimensional Modeling

The purposes of dimensional modeling are:

1. To produce database architecture that is easy for end-clients to understand and write
queries.
2. To maximize the efficiency of queries. It achieves these goals by minimizing the number
of tables and relationships between them.

Advantages of Dimensional Modeling

Following are the benefits of dimensional modeling are:

Dimensional modeling is simple: Dimensional modeling methods make it possible for

warehouse designers to create database schemas that business customers can easily
hold and comprehend. There is no need for vast training on how to read diagrams, and
there is no complicated relationship between different data elements.

Dimensional modeling promotes data quality: The star schema enable warehouse
administrators to enforce referential integrity checks on the data warehouse. Since the
fact information key is a concatenation of the essentials of its associated dimensions, a
factual record is actively loaded if the corresponding dimensions records are duly
described and also exist in the database.

By enforcing foreign key constraints as a form of referential integrity check, data

warehouse DBAs add a line of defense against corrupted warehouses data.

Performance optimization is possible through aggregates: As the size of the data

warehouse increases, performance optimization develops into a pressing concern.
Customers who have to wait for hours to get a response to a query will quickly become
discouraged with the warehouses. Aggregates are one of the easiest methods by which
query performance can be optimized.

Disadvantages of Dimensional Modeling

1. To maintain the integrity of fact and dimensions, loading the data warehouses with a
record from various operational systems is complicated.
2. It is severe to modify the data warehouse operation if the organization adopting the
dimensional technique changes the method in which it does business.

Elements of Dimensional Modeling

Fact
It is a collection of associated data items, consisting of measures and context data. It
typically represents business items or business transactions.

Dimensions
It is a collection of data which describe one business dimension. Dimensions decide the
contextual background for the facts, and they are the framework over which OLAP is
performed.

Measure
It is a numeric attribute of a fact, representing the performance or behavior of the
business relative to the dimensions.

Considering the relational context, there are two basic models which are used in
dimensional modeling:

o Star Model
o Snowflake Model
The star model is the underlying structure for a dimensional model. It has one broad
central table (fact table) and a set of smaller tables (dimensions) arranged in a radial
design around the primary table. The snowflake model is the conclusion of decomposing
one or more of the dimensions.

Fact Table
Fact tables are used to data facts or measures in the business. Facts are the numeric
data elements that are of interest to the company.

Characteristics of the Fact table

The fact table includes numerical values of what we measure. For example, a fact value
of 20 might means that 20 widgets have been sold.

Each fact table includes the keys to associated dimension tables. These are known as
foreign keys in the fact table.

Fact tables typically include a small number of columns.

When it is compared to dimension tables, fact tables have a large number of rows.

Dimension Table
Dimension tables establish the context of the facts. Dimensional tables store fields that
describe the facts.
Characteristics of the Dimension table

Dimension tables contain the details about the facts. That, as an example, enables the
business analysts to understand the data and their reports better.

The dimension tables include descriptive data about the numerical values in the fact
table. That is, they contain the attributes of the facts. For example, the dimension tables
for a marketing analysis function might include attributes such as time, marketing region,
and product type.
Since the record in a dimension table is denormalized, it usually has a large number of
columns. The dimension tables include significantly fewer rows of information than the
fact table.

The attributes in a dimension table are used as row and column headings in a document
or query results display.

Example: A city and state can view a store summary in a fact table. Item summary can
be viewed by brand, color, etc. Customer information can be viewed by name and
address.
 Fact Table

Time ID Product ID Customer ID Unit Sold

4 17 2 1
8 21 3 2
8 4 1 1
In this example, Customer ID column in the facts table is the foreign keys that join with
the dimension table. By following the links, we can see that row 2 of the fact table
records the fact that customer 3, Gaurav, bought two items on day 8.

Dimension Tables

Customer Name Gender Income Education Region

ID

1 Rohan Male 2 3 4
2 Sandeep Male 3 5 1
3 Gaurav Male 1 7 3

Types of Database Schema

Star Schema

A star schema is the elementary form of a dimensional model, in which data are organized into facts
and dimensions. A fact is an event that is counted or measured, such as a sale or log in. A dimension
includes reference data about the fact, such as date, item, or customer.

A star schema is a relational schema where a relational schema whose design represents a
multidimensional data model. The star schema is the explicit data warehouse schema. It is known as
star schema because the entity-relationship diagram of this schemas simulates a star, with points,
diverge from a central table. The center of the schema consists of a large fact table, and the points
of the star are the dimension tables.

What is Star Schema

Fact Tables
A table in a star schema which contains facts and connected to dimensions. A fact table has two
types of columns: those that include fact and those that are foreign keys to the dimension table. The
primary key of the fact tables is generally a composite key that is made up of all of its foreign keys.

A fact table might involve either detail level fact or fact that have been aggregated (fact tables that
include aggregated fact are often instead called summary tables). A fact table generally contains
facts with the same level of aggregation.

Dimension Tables

A dimension is an architecture usually composed of one or more hierarchies that categorize data. If a
dimension has not got hierarchies and levels, it is called a flat dimension or list. The primary keys of
each of the dimensions table are part of the composite primary keys of the fact table. Dimensional
attributes help to define the dimensional value. They are generally descriptive, textual values.
Dimensional tables are usually small in size than fact table.

Fact tables store data about sales while dimension tables data about the geographic region
(markets, cities), clients, products, times, channels.

Characteristics of Star Schema

The star schema is intensely suitable for data warehouse database design because of the following
features:

 It creates a DE-normalized database that can quickly provide query responses.

 It provides a flexible design that can be changed easily or added to throughout the
development cycle, and as the database grows.
 It provides a parallel in design to how end-users typically think of and use the data.
 It reduces the complexity of metadata for both developers and end-users.

Advantages of Star Schema

Star Schemas are easy for end-users and application to understand and navigate. With a well-
designed schema, the customer can instantly analyze large, multidimensional data sets.

The main advantage of star schemas in a decision-support environment are:

[Link] Performance

A star schema database has a limited number of table and clear join paths, the query run faster than
they do against OLTP systems. Small single-table queries, frequently of a dimension table, are almost
instantaneous. Large join queries that contain multiple tables takes only seconds or minutes to run.

In a star schema database design, the dimension is connected only through the central fact table.
When the two-dimension table is used in a query, only one join path, intersecting the fact tables,
exist between those two tables. This design feature enforces authentic and consistent query results.

[Link] performance and administration

Structural simplicity also decreases the time required to load large batches of record into a star
schema database. By describing facts and dimensions and separating them into the various table,
the impact of a load structure is reduced. Dimension table can be populated once and occasionally
refreshed. We can add new facts regularly and selectively by appending records to a fact table.

[Link]-in referential integrity

A star schema has referential integrity built-in when information is loaded. Referential integrity is
enforced because each data in dimensional tables has a unique primary key, and all keys in the fact
table are legitimate foreign keys drawn from the dimension table. A record in the fact table which is
not related correctly to a dimension cannot be given the correct key value to be retrieved.

[Link] Understood

A star schema is simple to understand and navigate, with dimensions joined only through the fact
table. These joins are more significant to the end-user because they represent the fundamental
relationship between parts of the underlying business. Customer can also browse dimension table
attributes before constructing a query.

Disadvantage of Star Schema

There is some condition which cannot be meet by star schemas like the relationship between the
user, and bank account cannot describe as star schema as the relationship between them is many to
many.
Example: Suppose a star schema is composed of a fact table, SALES, and several dimension tables
connected to it for time, branch, item, and geographic locations.

The TIME table has a column for each day, month, quarter, and year. The ITEM table has columns for
each item_Key, item_name, brand, type, supplier_type. The BRANCH table has columns for each
branch_key, branch_name, branch_type. The LOCATION table has columns of geographic data,
including street, city, state, and country.

What is Star Schema

In this scenario, the SALES table contains only four columns with IDs from the dimension tables,
TIME, ITEM, BRANCH, and LOCATION, instead of four columns for time data, four columns for ITEM
data, three columns for BRANCH data, and four columns for LOCATION data. Thus, the size of the
fact table is significantly reduced. When we need to change an item, we need only make a single
change in the dimension table, instead of making many changes in the fact table.

We can create even more complex star schemas by normalizing a dimension table into several
tables. The normalized dimension table is called a Snowflake.
Snowflake Schema
A snowflake schema is equivalent to the star schema. "A schema is known as a
snowflake if one or more dimension tables do not connect directly to the fact table but
must join through other dimension tables."

[Link] snowflake schema is an expansion of the star schema where each point of the
star explodes into more points.

[Link] is called snowflake schema because the diagram of snowflake schema resembles a
snowflake.

[Link] is a method of normalizing the dimension tables in a STAR schemas.

[Link] is used to develop the performance of specific queries.

[Link] snowflake schema consists of one fact table which is linked to many dimension
tables, which can be linked to other dimension tables through a many-to-one
relationship.

[Link] in a snowflake schema are generally normalized to the third normal form.

The following diagram shows a snowflake schema with two dimensions, each having
three levels. A snowflake schemas can have any number of dimension, and each
dimension can have any number of levels.

Example: Figure shows a snowflake schema with a Sales fact table, with Store,
Location, Time, Product, Line, and Family dimension tables. The Market dimension has
two dimension tables with Store as the primary dimension table, and Location as the
outrigger dimension table. The product dimension has three dimension tables with
Product as the primary dimension table, and the Line and Family table are the outrigger
dimension tables.
A star schema store all attributes for a dimension into one denormalized table. This
needed more disk space than a more normalized snowflake schema. Snowflaking
normalizes the dimension by moving attributes with low cardinality into separate
dimension tables that relate to the core dimension table by using foreign keys.
Snowflaking for the sole purpose of minimizing disk space is not recommended,
because it can adversely impact query performance.

In snowflake, schema tables are normalized to delete redundancy. In snowflake

dimension tables are damaged into multiple dimension tables.

Figure shows a simple STAR schema for sales in a manufacturing company. The sales
fact table include quantity, price, and other relevant metrics. SALESREP, CUSTOMER,
PRODUCT, and TIME are the dimension tables.
The STAR schema for sales, as shown above, contains only five tables, whereas the
normalized version now extends to eleven tables. We will notice that in the snowflake
schema, the attributes with low cardinality in each original dimension tables are removed
to form separate tables. These new tables are connected back to the original dimension
table through artificial keys.
A snowflake schema is designed for flexible querying across more complex dimensions
and relationship. It is suitable for many to many and one to many relationships between
dimension levels.

Advantage of Snowflake Schema

1. The primary advantage of the snowflake schema is the development in query
performance due to minimized disk storage requirements and joining smaller lookup
tables.
2. It provides greater scalability in the interrelationship between dimension levels and
components.
3. No redundancy, so it is easier to maintain.

Disadvantage of Snowflake Schema

1. The primary disadvantage of the snowflake schema is the additional maintenance efforts
required due to the increasing number of lookup tables. It is also known as a multi fact
star schema.
2. There are more complex queries and hence, difficult to understand.
3. More tables more join so more query execution time.
Difference between Star and Snowflake
Schemas
Star Schema
o In a star schema, the fact table will be at the center and is connected to the
dimension tables.
o The tables are completely in a denormalized structure.
o SQL queries performance is good as there is less number of joins involved.
o Data redundancy is high and occupies more disk space.

Snowflake Schema
o A snowflake schema is an extension of star schema where the dimension
tables are connected to one or more dimensions.
o The tables are partially denormalized in structure.
o The performance of SQL queries is a bit less when compared to star schema
as more number of joins are involved.
o Data redundancy is low and occupies less disk space when compared to star
schema.