To design a datawarehouse one need to design dimensional data model. The dimensional data model provides a method for making databases simple and understandable by denormalizing it and removing extra join which are generally required to fetch the data. In general you can imagine dimensional database as a cube of three or more dimensions using which users can access a data in all possible way by slicing it.
Lets first understand the terms and terminology used in Dimensional Model
Dimension:
is a an data element against which data or performance is measured. A data warehouse dimension provides the means to slice and dice the data in a data warehouse.
e.g. Customer, Day, Month are dimension
Fact:
is generally a numerical value representing measurement.
e.g. Revenue, count of users
Hierarchy:
represent the navigation path of dimension which can be used to drill-up or drill-down data in reports in order to see data at different level.
e.g.
Year -> Month -> Week -> Day
As opposed to relational data model which uses ER Model, dimensional modeling uses either star schema, snowflake schema.
Subscribe to Email to receive all the FREE updates and Business Objects Interview Questions and Answers FREE!!
Related posts:
{ 1 comment… read it below or add one }
wrong Hierarchy Year -> Month -> Week -> Day.
correct Hierarchy is Year -> Month -> Day
because number of weeks in a month are not constant mean a week contain 4.2 or 4.3 that why you can not use week as a normal Hierarchy part.