Multidimensional Data Model (Data cubes)😎

Let’s take a look at what is a multidimensional data model.

Data cube figure — 1

Multi-dimensional data model is a data storage a schema that has more than two dimensions, meaning it contains rows and columns it’s a data structure and that data structure is repeated and extended with another category or multiple categories.

Let’s take this following example and discuss how data cube is formed and why data cube is formed. As you can see following table includes time, Ent, comp. phone, sec Fields and each state includes following data fields. So that this data can be in a form of tree. the lowest data level includes items. those items can be stored in a 2D array or any 2D data structure but there is another data level that is broken down by the state. Therefore, we can get a third dimension of the data as location that the lowest data level is being represented so we can form a data cube out of those three data levels.

figure 2 — 2D view of sales

figure 3–3D view of sales

A data cube allows data to be modeled and viewed in multiple dimensions

• It is defined by dimensions and facts

• dimensions — perspectives or entities with respect to which an organization wants to keep records

• facts — quantities by which we want to analyze relationships between dimensions

• data cube is often referred to as a cuboid

👉🏻 What are the characteristics of data cube ?

• A data cube allows data to be modeled and viewed in multiple dimensions

• It is defined by dimensions and facts

• dimensions — perspectives or entities with respect to which an organization wants to keep records

• facts — quantities by which we want to analyze relationships between dimensions

Now let’s see how to store data in a data cube. there are several ways we can use to store data and relate each data 2D array in a data cube. Those are called the sachems for data cubes and those are used to show the entire logical description of the data cube much like a general database. schema can be used to show the association between different tables in the data cube. Those associations can be either aggregations or compositions.

👉🏻 Sachems for Multidimensional Data Models

1) Star schema

Star schema contains one-dimensional tables that are combined together by adding a one-dimensional table which contains keys to all the other connected tables. These tables are arranged in the form of a star. So in this following example you see Anastasia schema which shows the sales of a company. Sales represents the cuboid and time, item, branch and location are the dimensions(attributes) of that cuboid.

Figure — 4 (Star schema)

• the large central table containing the bulk of the data we call as fact table, With some amount of redundancy.

• a set of smaller attendant tables (dimension tables), one for each dimension

The problem with this is schema is that if there was subcategories that are related to a dimension then there will be data redundancy occur regards to that subcategory. for example if location dimension has a subcategories like districts then there will be and another dimention attached to the sales namely district and district we’ll have repeated data for the location.

this issue is addressed in the snowflake schema and that was solved by snowflake schema by adding sub dimensions to another dimension.

2) Snowflake schema

In his North-lake schema the dimension are normalized. Therefore the dimensions splatted attributes. hence the data redundancy is reduced greatly.

Figure — 5 (snow flake schema)

• a variant of the star schema-model

• some dimension tables are normalized (splitting the data into additional tables)

3) Fact Constellation schema

In fact constellation schema there can be multiple fact tables. This is also called Galaxy schema. this is same as start schema but there are multiple fact tables.

Figure — 5 (Fact Constellation schema)

• multiple fact tables to share dimension tables

• can be viewed as a collection of stars, and hence is called a galaxy schema or a fact Constellation

It becomes a little bit harder to visualize multiple dimensions. Hence cuboids are represented as follows. There are multi dimensions involved into this and it becomes quite complected when it comes to a larger schema. This is called a “Lattice of cuboids”.

Figure — 6 (Lattice of cuboids)

Schema Definition

Multidimensional schema is defined using Data Mining Query Language (DMQL). The two primitives, cube definition and dimension definition, can be used for defining the data warehouses and data marts.

Different operations can be applied to data cubes. Most common operations are listed as follows.

👉🏻 OLAP Operations

Figure — 7 (OLAP Operations)

1. Roll-up (drill-up):

Figure — 7 (Drill — up OLAP Operation)

performs aggregation on a data cube, either by climbing up a concept hierarchy for a dimension or by dimension reduction

• Drill-down:

Figure — 8 (Drill — down OLAP Operation)

can be realized by either stepping down a concept hierarchy for a dimension or introducing additional dimensions

• Slice and dice:

Figure — 9 (Slice and Dice OLAP Operation)

The slice operation performs a selection on one dimension of the given cube, resulting in a sub cube and the dice operation defines a sub cube by performing a selection on two or more dimensions

• Pivot (rotate):

Figure — 9 (Pivot OLAP Operation)

a visualization operation that rotates the data axes in view to provide an alternative data presentation

Thanks for reading…🥳👏🏻