Hierarchy

Hierarchy

89.Explain Hierarchies in data modeling with its types.

Hierarchies is series of many to one relationships which has different levels.

There are 2 types of Hierarchies :
1.Level Based Hierarchies
2.Parent Child Hierarchies
 90.What is Data warehousing Hierarchy?
  • Hierarchies Hierarchies are logical structures that use ordered levels as a means of organizing data.
  • A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level.
  • A hierarchy can also be used to define a navigational drill path and to establish a family structure.Within a hierarchy, each level is logically connected to the levels above and below it.
  • Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies–one for product categories and one for product suppliers
  • Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity.
  • This is one of the key benefits of a datawarehouse. When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization.Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly
A level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Levels range from general to specific, with the root level as the highest or most general level. The levels in a dimension are organized into one or more hierarchies.Level Relationships Level relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy.Hierarchies are also essential components in enabling more complex rewrites. For example, the database can aggregate an existing sales revenue on a quarterly base to a yearly aggregation when the dimensional dependencies between quarter and year are known.

91.Storing hierarchical information in dimension tables

https://dwbi.org/data-modelling/dimensional-model/17-dimensional-modeling-schema

MEASURES

92.What are additive, semi-additive and non-additive measures?

Non-additive Measures

  • Non-additive measures are those which can not be used inside any numeric aggregation function (e.g. SUM(), AVG() etc.).
  • One example of non-additive fact is any kind of ratio or percentage.
  • Example, 5% profit margin, revenue to asset ratio etc.
  • A non-numerical data can also be a non-additive measure when that data is stored in fact tables,
  • e.g. some kind of varchar flags in the fact table.
  •  For example, a ratio or a percentage column; a flag or an indicator column present in fact table holding values like Y/N, etc. is a non-additive measure.

Semi Additive Measures

  • Semi-additive measures are those where only a subset of aggregation function can be applied.
  • Let’s say account balance. A sum() function on balance does not give a useful result but max() or min() balance might be useful.
  • Consider price rate or currency rate. Sum is meaningless on rate; however, average function might be useful.

Additive Measures

  • Additive measures can be used with any aggregation function like Sum(), Avg() etc. Example is Sales Quantity etc.