Multidimensional model in the data warehouse.

Multidimensional model in the data warehouse.

A multidimensional model is a technique, structure for data warehousing tools. Ralph Kimball firstly introduce the three-dimension model. A multidimensional model display data in the form of a data-cube. This data cube able data to be designed and display in multiple dimensions. This cube is defined through dimensions and facts.

The dimensions are the parts or entities regards which an organization saves data or records. The Dimensional model is basically developed to read, analyze numeric information like values, counts, weights, balances, summarize the data, etc. in a data warehouse. This model has a unique way of stored data for different advantages.

This model used in the relational model to minimize redundancy and also used to normalize ER models. The data is stored in three dimensions in an easy way, it is easily retrieved and generate reports.

Example of Multi-Dimensional Data Model

A shop keeper may build a sales data warehouse in which he wants to keep a record of the stores’ sales for the dimension product, time, and location. These three dimensions keep the record of these things. For example, it keeps records of how many sales are made in one month. Each dimension allows keeping the records.

Each dimension has a table, this table is called dimension tablets table also specifies the further dimensions. The dimension table for products may contain attributes like product name, brand, price, and type.

The whole model is built around a specific theme. For example, we create it for the sales data warehouse. The theme is represented by the fact table.

The fact table consists of numerical measures. This may contain the name of fact and all measurements of related dimensions.

Elements of the dimensional data model

  1. Dimension
  2. Facts
  3. Attribute
  4. Dimension table
  5. Fact table
  6. Dimension

Dimension

Dimension provides information about a business process event. In other words, they specify who, what, whereof a fact. For example In the Sales dimension or business process, for the fact quarterly sales number, dimensions may contain

  • Who –  Names of customer
  • Where – Location/place
  • What – Product Name

Fact

Facts are the numerical measurements or facts from your business process. For a Sales business process, a measurement may be sales number

Attribute

The characteristics of dimension are called attributes. For the location dimension, the attributes would be

  1. Country
  2. City
  3. Zipcode
  4. State

Dimension table

A dimension table consists of dimensions of a fact. The dimension table is used to join the fact table with a foreign key.
Dimension tables are de-normalized table .In a dimension table the Dimension Attributes as different columns.
It describes the characteristics of the facts with the help of their attributes

Fact table

It is known as the primary table in a dimensional model. This table consists of

  1. Foreign key to the dimension table
  2. Measurements/facts