Dimensional modelling :
Dimensional modelling is referred to as the process of organising and categorising data into
facts and dimensions. Where the dimensions are used to describe the data and the facts are
used to quantify the data.
Star schema:
A star schema is the simplest form of dimensional model diagrammed by surrounding each fact
with its associated dimension.
It is a combination of fact and dimensions.
Facts are things that can be measured or counted such as the total sales etc.
The dimensions are the things that describes or provides reference information such as date,
product name etc.
The dimension table has a primary key that uniquely identifies each record (row)
Within the data warehouse a dimension table is associated with a fact table using a foreign key.
Steps to design a star schema:
1. Identify the business process /fact table for example sales, reservation etc.
2. Identify the fact records (measurable records in the data ) such as the sales,
number_ofunitssold, net_profit etc.
3. Identify the dimension records such as the product, time, location, branch etc.
4. List of attributes or columns in each dimension table
5. Find the lowest level of aggregation in the fact table.
Snowflake schema:
1. A snowflake schema is an extension of the star schema and it adds some
additional dimensions .
2. The dimension tables are normalised which splits the data into multiple
tables.
3. Due to multiple tables, the query performance is reduced
4. The main benefit is that , it uses smaller disc space.
Fact constellation schema :
This kind of schema is possible to be built by splitting one star schema into more star
schemas .
unlike a single fact table in the star schema here , multiple fact tables are associated
with many shared dimensions tables.
The complexity rises from star schema to snowflake schema to the fact constellation
schema and the complexity of queries rises too.
OLAP (online analytical processing): OLAP plays a crucial role in analysing the
multidimensional data within the data warehouse . it's a set of software tools and
technologies specifically designed for analysing the multidimensional data stored in data
warehouses.
OLAP provides a platform for gaining insights from databases retrieved from multiple
database systems at the same time.
It is based on a multidimensional data model which helps user analyse the data in
multiple views.
Types of OLAP :
1. ROLAP: (relational online analytical processing): here data is stored in
relational database but OLAP tools are used for analysis. Here both the base
data and dimensional tables are stored in relational tables.
ROLAP servers store and manage data using RDBMS (relational database
management system) and OLAP middleware filling in the gaps.
a) It is compatible with data warehouse and OLAP systems
b) The volume of the data depends upon the underlying RDBMS .
c) Although SQL functionality is constrained.
2. MOLAP: (Multidimensional online analytical processing) : MOLAP server
stores the data on a disc in the form of specialised multidimensional array structures, MOLAP
server uses smart indexing and hashing techniques to locate the relevant data from the
multidimensional array.
3. HOLAP: (hybrid on-line analytical processing): HOLAP is a combination of
MOLAP and ROLAP . HOLAP servers can store large volumes of data as it provides more
scalability than ROLAP server and faster computation than MOLAP server.
OLAP operations :
1. Slice : the slice operation focuses on single dimension from the data cube which results
in a sub cube creation. The subset of the data is selected that it focuses on a particular
aspect of overall data. It generally selects one or more dimensions.
2. Dice : this selects a cube by selecting 2 or more dimensions from the main data cube.
It focuses on specific combination of values as it selects 2 or more dimensions from the
data cube.
3. Pivot : this OLAP operation is also known as rotation operation as the cube is rotated to
another direction to get a different view of the data and then analytics can be performed.
This operation is performed after a sub cube is obtained from the slice operation and
then rotation is performed.
4. Drilldown: in this operation the less detailed data is converted into more specifically
detailed data by diving deep into the content hierarchy and adding new dimensions .
5. Rollup: this is exactly the opposite of the drilldown operation here, climbing back is
performed in the content hierarchy and the dimensions are reduced.
Querying :
It involves retrieving a specified subset from the data warehouse based on the query
provided by the user.
The query can be written in any language such as SQL
These queries can be simple or complex, data warehouses are optimised for querying
large datasets from the warehouses.
Reporting :
This involves transforming the retrieved data into a presentable format that
communicates insights efficiently.
Reports are generally built using data visualisation techniques such as graphs, relational
tables etc.
