07 creating dimensional hierarchies
Hierarchies are 3 three types.
1. Level based hierarchies.
2. Parent child hierarchies (11g new features)
3. Unbalanced hierarchies (11g new features)
Level based hierarchies:
One to many relationship among columns is called as level based
hierarchies.
Eg:
1. Time hierarchies
Year -> quarter -> month -> day.
2. Customer hierarchies
Region -> district -> sales rep -> customer
3. Product hierarchies
Type -> subtype -> generic -> specific.
Step 1: Creating time hierarchy Levels
1. Right click on period dimension table -> create logical
dimension -> dimension with level based hierarchy -> expand periods
dimension -> rename period detail as day level.
2. Right click on day level -> new object -> parent level -> type: month
- click on ok.
3. Right click on month level -> new object -> parent level -> quarter
click on ok.
4. Right click on quarter -> new object -> parent level -> year -> ok.
1. Right click on period dimension table -> new object -> logical column
-> name it as quarter.
2. Click on columns source tab ->select derived from existing
column using an expression.
3. Click on edit expression .
4. Develop below expression.
Case when “sales”, ”periods” , “month in year” < 4 then 1.
When “sales”, “periods”, “month in year” < 7 then 2
When “sales” ,”periods” ,”month in year” < 10 then 3
Else
4
End.
5. Click on ok ->again ok.
Step 3: Mapping columns to hierarchy Levels
1. From day level drag and drop year column onto year level.
2. From periods dimension table drag and drop quarter onto
quarter level.
3. From day level drag and drop month code onto month level.
4. In day level delete all columns except yyyymmdd .
Step 4: Defining Logical Keys
Each and every level must contains a key column,it is required
to identify unique values in that level
1. Right click on year level -> year column ->new
logical level key -> click on ok.
2. Right click on quarter column ->new logical level key -> click on ok.
3. Right click on month code -> new logical level key -> click on ok.
4. Click on save.
Step 5: Defining Content level
1. Expand periods
2. Double click on D1_calendar logical source -> Click on content ->
set logical level as day level
3. Click on ok.
4. Expand sales -> double click on D1_orders2 logical table source
-> Click on content ->logical level as day level -> ok.
5. Drag and drop quarter column into presentation layer
periods presentation table
Step 6: Testing
1. Load RPD into oracle BI server.
2. Develop a report with year , dollars column.
3. Click on year ->column properties -> then interaction
-> select primary interaction as drill ->: click on ok -> click on results
-> click on 1998 -> Click On Quarter -> click on month.
Note : Similarly create product & customer hierarchy with below
levels as shown in screen shot and test by developing reports .
Customer hierarchies
Region -> district -> sales rep -> customer
Product hierarchies
Type -> subtype -> generic -> specific.
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 drillup(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.
main difference between level based hierarchy and parent chaild hierarchy?
ReplyDeletewhich scenarios we have to use level based hierarchy and which scenario's we have to use parent chaild hierarchy??