02 BMM LAYER

                                                              02 BMM LAYER



      · It is also called as logical layer (or) business layer.
      · In this layer we will convert Data base technical terminology into business
        terminology
             Eg: ACTLEXTND column into Dollars.
     · BMM layer contains ‘n’ number of business models.
     · Each business model called as subject area or data mart.
     · In development of BMM layer we have below steps.
               1. Creating business model
               2. Creating logical tables and logical columns
               3. Creating logical joins.
              4. Renaming logical tables and logical columns
              5. Defining measures.
Step 1: Creating Business Model
            1. Right click in BMM layer -><- new business model
            2. Name it as suppliersales -><- click on ok

Step 2: Creating logical tables and Logical columns
          · Right click on suppliersales business model -><- new object -><- logical table.
          · Name it as periods.
          · Click on ok.
          · Drag and drop D1_calendar2 physical table onto periods.
       Similarly create below three tables and map it
                Customers -><- D1_customers2
                Sales -><- D1_orders2
                Products -><- D1_products

Note: Wherever you drag and drop physical table then automatically logical column will
be created. More we will discuss in hands on 6 calculations
Step 3: Creating logical joins
        1. Logical join is useful to define driving table.
        2. Logical join is useful to define Inner Join, Left Outer Join,Right Outer Join and Full Outer                               Join
       3. Logical join is useful to define cardinality. more used cardinality is one to many and never                              used cardinality is Many to Many
      4. Logical join is useful to identify dimension tables and fact tables.
      5. Logical join allows Oracle BI Server to make the best decision about the exact physical SQL                       to generate based on the logical query path

Process:
        · Right click on BMM layer-><- business model diagram -><- whole diagram.
        · Click on new join.
       ·  Drag and drop sales onto periods
       ·  Click on ok.
       ·  Similarly create below joins
                   supplier sales to customers
                   supplier sales to products.
      · Close business model diagram.

Step 5: Renaming columns:
      · Go to tools -><- Utilities -><-Rename Wizard -><-Execute -><- select Sales Business Model -><-              Click on Add hierarchy -><- Click on next -><- again next.
      · select All text lower case -><- add
      · Select First letter of each word capital -><- add
      · Select Change each occurrence of ‘-‘into a space -><- add.
      · Click on next and click on finish.
      · Save it and click on No.

Step 6: Defining measures
       Each and every measure should associated with one aggregation rule
       Table Source Data :

Deptno	Empno	Sal
10	101	1000
10	102	2000
20	103	500
20	104	1000

If we are not mentioning the sal as measure column the output is like this.

Deptno	Sal
10	1000
20	2000
20	500
20	1000

  

If we mention the Sal column as Measure (Sal is measure and is associated with
aggregate rule sum) then output is

Deptno	Sal
10	3000
20	1500

Process:
In the BMM layer expand sales fact table double click on dollars column-><- select
aggregation tab -><-select aggregation rule as sum -><- Click on ok.
Note1: one business model may map to multiple data bases.
Note2: one logical Table may map to multiple LTS
Note3: One logical tables source may map to multiple physical tables
Note4: one logical column may map to ‘n’ number of physical columns.