07 CREATING DIMENSIONAL HIERARCHIES

                                                    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.

       









              

                Step 2: Calculating quarter column
                        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.

06 CALCULATIONS

                                                        06 calculations
           In OBIEE we can do calculations in 3 ways.
              1. Creating calculation based on logical columns.
              2. Creating calculation based on physical columns.
              3. Creating calculations based on by using calculation wizard.

         0.6.1 Creating calculation based on logical columns
               1. Open RPD in online mode.
               2. Right click on BMM layer sales fact table ->New object -> logical column.
               3. Name it as cuts.
               4. Click on column source tab.
               5. Select radio button Derived from existing columns using an expression.
               6. Click on edit expression
                           Select category -> logical tables.
                           Logical table -> sales
                           Columns -> double click on unitord column ->type Minus(-) ->
                            double click on unitship columns.
               7. Click on ok ->again ok

   




             8. Drag and drop into presentation layer sales presentation table.

          0.6.2 Creating calculation based on physical columns
                      1. Expand sales logical table
                      2. Double click on D1_orders2 LTS.
                      3. Click on add new column.
                      4. Name it as cutsp.
                      5. Click on ok.
                      6. Click on edit expression.
                      7. Double click on unitord -> type minus (-) -> double click on unitship.
                      8. Click on ok-> again ok.

              9. Double click on cutsp ->aggregation tab ->sum
              10. Click on ok .
              11. Drag and drop onto presentation layer sales table.

          0.6.3 calculation by using calculation wizard
              1. Right click on unitord -> click on calculation wizard ->next.
              2. Select unitship ->next.
              3. Disable percentage change.
              4. Rename it as Cutsw.

 












              5. Click on next -> click on Finish.
              6. Drag and drop into presentation layer sales table.
              7. Check in changes.
              8. Reload server metadata.

           Testing :
                            Develop a report with year, month code, unitord, unitship, cuts,
                             cutsp ,cutsw & observethe output.
          Notes:
                     1. In logical calculations pre aggregation will take place
                     2. In Physical calculations post aggregation will take place
                     3. As a best practice use logical columns in calculations
                     4. Calculation Wizard is useful to handle NULL values and 
                          DIVIDE WITH ZEROerror
                     5. Calculation wizard is useful to calculate percentages easily.

05 ADDING MULTIPLE SOURCES

                                                   05  Adding multiple sources
                     1. Add physical tables to an LTS or LT.
                     2. If data is not duplicated then that physical table can add to LTS or LT
                        (As a best practice  add to LTS).
                     3. If data is duplicated then we need to add physical table to LT. In this
                         case we need to define content logical level. (It is used in aggregate
                          tables and partition tables chapters )
                          Note: To add Physical table to LTS, the adding physical table 
                          and existing  physical table of LTS must have direct join.

               Process:
               Step 1 : Import below tables into physical layer.
                        1. D1_PRICELIST.
                        2. D1_PROD_DIET_TYPES.
                        3. D1_PROD_SUBTYPE.
                        4. D1_PRODUCT_TYPE.
                        5. D1_SUPPLIERS.

             Step2 : Create joins as per below.
                       1. D1_products.suppliercode = D1_suppliers.suppliercode.
                       2. D1_products.productkey = D1_pricelist.productkey.
                       3. D1_products.dietcode = D1_productype.dietcode.
                       4. D1_products.subtypecode = D1_productsubtype.subtypecode.
                       5. D1_product_subtype.typecode = D1_productype.typecode.

             Step 3 : Mapping multiple sources to an LTS.
                       1. Drag and drop D1_PROD_SUBTYPE physical table onto D1_Product LTS.
                       2. Similarly drag and drop
                           D1_PRICELIST,D1_PROD_DIET_TYPES,D1_PROD_SUBTYPE,
                           D1_PRODUCT_TYPE,D1_SUPPLIERS.

             Step 4 :
                                 Drag and drop newly added 5 columns onto products 
                                  presentation table.