Dimensional Data Model

Data Hierarchy

Data Structure is represents a Systematic Organization of Data that can form through collection of interrelated Columns, Records and Tables in the Relational Database Management System. Data Hierarchy is represents a Logical Top-To-Bottom Ordered Structure of Record(s).

Dimension Table structure must be design with relevant data hierarchy that helps to find out data aggregation from Fact Table. Dimension table may design with one or many interrelated hierarchy. Each hierarchy must be integrates with Lower Level or Higher Level of Hierarchy.

An instance, a Geographical Dimension table may design with one or three interrelated hierarchies like {City}, {City - State} and {City - State - Country}. The State hierarchy can relates with Lower Level City hierarchy or Higher Level Country hierarchy information. These hierarchies are helps to generates City Wise, State Wise and Country Wise Analytical Report with Aggregation Data.

Granularity of Dimension

Granularity is the extent to which a system is broken down into small parts, either the system itself or its description or observation. The granularity of data refers to the fineness with which data fields are sub-divided. For example, a postal address can be recorded, with low granularity, as a single field:

Address = D.123 6th Avenue. East Gate 6, Madurai, 625 009, Tamil Nadu, India

(or) with high granularity, as multiple fields:

01. Address = D.123 6th Avenue.

02. Street Name = East Gate 6

03. City Name = Madurai

04. Postal Code = 625 009

05. State Name = Tamil Nadu

06. Country = India

Higher granularity has overheads for data input and storage that affects database operation. The higher granularity of address attribute can form like that

01. Address Street = D.123 6th Avenue. East Gate 6

02. City Name = Madurai

03. Postal Code = 625 009

04. State Name = Tamil Nadu

05. Country = India

Dimension Table must be design with higher level granularity and then try to re-design based on system requirement to optimize database operation.

* * * * *

Email Your Comment To AUTHOR