MDDM
Multi-Dimensional Data Modelling
Before going further, it is highly recommended to visit Data Cube.
Earlier, data were present in the form of relational tables having multiple joins and views. This made the data retrieval a time taking process. Also, this resulted in a slow turnaround time for complex queries and large size of the database. To overcome this, MDDM came into the picture.
In Multi-Dimensional Data Modelling, data is present in the form of data cube which enables data to be viewed in multiple dimensions. This data model design optimizes and addresses the analysis and reporting needs of an enterprise. It helps with data retrieval operation and is also called a Dimensional or Dimension model. MDDM is associated with certain terms that define it :
- Fact: A transactional data or a central theme around which MDDM is organized. eg, Sales, Profit, Loss
- Dimension: A descriptive information that is used to group transactional data. This provides context to analyze transactional information. eg, Customer, Product, Date, Time, Location
- Grain: An attribute of hierarchy at which data is collected or stored in the fact table.
- Hierarchy: A set of related dimensional attributes (columns) is known as a hierarchy. eg, Calendar hierarchy [ Day → Month → Quarter → Semester → Year ]
- Schema: An arrangement of fact and dimension table is called schema. eg, a fact table sales are connected to dimensions customer, date, product, and geography is an example of the simplest schema (star)
Let's understand each of these terms with the help of an example. Suppose, MDDM is organized around a central theme, Sales. Now, Sales have various tables associated with it such as CustomerDetails, ProductDetails, ProductCategory, ProductSubcategory, Date, etc. The details regarding Products are stored in a hierarchical manner [ ProductDetails → ProductSubcategory → ProductCategory ]. Since the theme revolves around Sales, there will be a daily purchase and daily updation will be made regarding the same, based on OrderDate and ProductKey. From this scenario, we can conclude the below:
- Fact: Sales
- Dimension: CustomerDetails, ProductDetails, ProductCategory, ProductSubcategory
- Grain: OrderDate, ProductKey
- Hierarchy: ProductDetails →ProductSubcategory →ProductCategory
There are various kinds of Dimensions and Schema which will be discussed in later articles.
