Hierarchies

In the Business Model and Mapping layer, a dimension object represents a hierarchical organization of logical columns (attributes). One or more logical dimension tables can be associated with at most one dimension object.

Hierarchies are 3 three types.
1. Level based hierarchies.
2. Parent child hierarchies (11g new features)
3. Unbalanced hierarchies (11g new features)

1. Level based hierarchies:

One to many relationship among columns is called as level based hierarchies. Level-based hierarchies are those in which members are of several types, and members of the same type occur only at a single level.

Each business model can have one or more dimensions, each dimension can have one or more logical levels, and each logical level has one or more attributes (columns) associated with it.

Eg:
1. Time hierarchies
Year --> quarter --> month --> day.
2. Customer hierarchies
Region --> district --> sales rep --> customer

A dimension contains two or more logical levels. The recommended sequence for creating logical levels is to create a Grand Total level and then create child levels, working down to the lowest level. The following are the parts of a dimension:

• Grand Total level. A special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A Grand Total level does not contain dimensional attributes and does not have a level key. However, you can associate measures with a Grand Total level. The aggregation level for those measures will always be the grand total for the dimension.
• Level. All levels, except the Grand Total level, need to have at least one column. However, it is not necessary to explicitly associate all of the columns from a table with logical levels. Any column that you do not associate with a logical level is automatically associated with the lowest level in the dimension that corresponds to that dimension table. All logical columns in the same dimension table have to be associated with the same dimension.
• Hierarchy. Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level and a common root (all) level.
  For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. Day has two named parent levels called Fiscal Year and Calendar Year, which are both children of the All root level.
  
• Level keys. Each logical level (except the topmost level defined as a Grand Total level) must have one or more attributes that compose a level key. The level key defines the unique elements in each logical level. The dimension table logical key has to be associated with the lowest level of a dimension and has to be the level key for that level.
  
• Time dimensions and chronological keys. You can identify a dimension as a time dimension. At least one level of a time dimension must have a chronological key. The following is a list of some guidelines you should use when setting up and using time dimensions:
• At least one level of a time dimension must have a chronological key. 
• All time series measures using the AGO, TODATE, and PERIODROLLING functions must be on time levels. AGO, TODATE, and PERIODROLLING aggregates are created as derived logical columns. 
• AGO, TODATE, and PERIODROLLING functionality is not supported either on fragmented dimensional logical table sources, or on fact sources fragmented on the same time dimension. Fact sources may be fragmented on other dimensions.

2. Unbalanced (or ragged) hierarchy:

An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) do not necessarily have the same depth. For example, a site can choose to have data for the current month at the day level, previous months data at the month level, and the previous 5 years data at the quarter level.

3. Skip-level hierarchy:

A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city 'Washington, D.C.' does not belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington, D.C.) and below.

Hierarchies are useful to

1. Drill down.

2. Drill up.

3. Drill across (or) drill by.

4. Level based measures.

5. Aggregate navigation.

6. Time series measures.

1. Drill down:

1. Navigating from high level to low level is called as drilldown.

2. Above testing process we can consider as drill down.(year --> quarter --> month --> day)

2. Drill up:

1. Navigating from low level to high level is called as drill up(Day --> Month --> Quarter --> Year)

Process:

1. Open RPD in online mode --> double click on day level --> preferred drill path --> click on add --> select month level --> click on ok.

2. Check in changes & reload server metadata.

3. Develop a report with yymmdd , dollars.

4. Click on yymmdd --> column properties --> interaction --> under value --> primary interaction as drill --> click on ok.

5. In results click on any one no. and observe month data.

3. Drill across:

1. Navigating from one hierarchy to another hierarchy is called as drill across.

Eg: Day --> Region (drill across)

Process:

1. Go to Rpd --> double click on day level remove month column --> click on add --> select region --> click on select --> check in.

2. Reload server metadata.

3. Develop a report with yymmdd , dollars.

4. Click on yymmdd --> column properties --> interaction  --> primary interaction as drill --> click on ok --> click on results.

5. Click on any value & observe the output.

4. Making two or more columns as target:

1. Go to Rpd --> double click on day level --> keys -->select primary key --> edit -->

select two columns(Day and YYYYMMDD) --> enable use for display --> click on ok again ok --> check in --> reload server metadata --> test your work.