01 Physical Layer

STEP 1: Creating RPD file and importing metadata

1.    Start à All Programs à oracle business intelligence à BI administration

2.    Go to file menu à new repository.

3.    Name : RRITEC_EBA

4.    Repository password: RRitec123

5.    Retype password:RRitec123(minimum length should be 8 characters)

6.    Click on next

1.    Select Connection type: OCI 10g/11g (native connection of oracle data base)

2.    Data source name: ORCL

3.    User name: supplier2

4.    Password: supplier2

5.    Click on next

1.    Select tables, keys, foreign keys

1. Click on next
2. Expand supplier2 select D1_CALENDER2,D1_CUSTOMER2,D1_ORDERS2,D1_PRODUCTS.
3. Click on import selected (>).
4. Name the connection pool as RRITEC_CP
5. Click on ok
6. Click On finish.

Step2 : Creating keys and joins

·         Constraints: mainly we have 3 types of constraints.

                                      I.        Unique constraints à eg: emp no.

                                    II.        Not null constraints à eg: salary.

                                   III.        Check constraints à eg: phone number.

·         Keys: we have three types of keys

1.    Primary key

2.    Foreign key

3.    Composite key

1.    Primary key: unique + not null constraint is called as primary key.

Eg: Empno in emp table, deptno in dept table.

2.    Foreign key: reference of primary key is called as foreign key.

             Eg: deptno in emp table

3.    Composite key: if we need two or more columns to uniquely identify record then it is called as composite keys.

Eg: product no + product color.

 Joins:

A relationship is called as join. In database we have mainly 5 joins.

1.    Equi join >>>>>>>Foreign Key

2.    Non equi join>>>>>>Complex

3.    Outer join(left, right, full) >>>>>>  Logical join in BMM layer

4.    Self join.   >>>>>>   Alias (in Physical Layer)

5.    Cross join. >>>>> not supported by any BI tool

OBIEE (11G) Joins:

In OBIEE (11G) we have three types of joins.

1.    Foreign Key join

a.    Equivalent to Equi join

b.    Accepts only = operator

c.    Most used join in physical layer

2.    Complex Join

a.    Equivalent to Non Equi join

b.    Accepts all operators like (=,<>,=>,,=…….etc)

c.    Less used join in Physical layer

3.    Logical join : BMM layer Join is called as logical join (in OBIEE 10G it is also called as complex join)

Process to create joins:

In physical layer select supplier2 schema object click on physical diagram àobjects and all joins àclick on new join

·         Drag and drop from D1_orders table to calendar select yyyymmdd and period key click on ok.

·         Drag and drop from Orders table to customers. Select new key from customers and select custkey from orders.

·         Drag and drop orders table to products. Select product key from products and select prod key from orders.

·         Click on collapse all. Click on auto layout notice that we created star schema. Close physical diagram window.

Note1: Is OBIEE supports multiple databases?

Yes OBIEE has no limitation, to prove this import excel meta data into rpd

Step 1: Creating MS EXCEL Data base

1.    Open ms-excel.

2.    Create a file

Product	Price
Lux	10
Rin	20

·         Select all the cells name it as products. Similarly create one more table with the name of Sales .

  

Year	Sales
2001	100
2002	200

·         Save it into E drive with the name of RRITEC.

Step 2: Creating ODBC connection

·         Go to start à control panel àadministration tools à data source (ODBC)   à system DSN à Add

·         Select Microsoft excel driver à finish.

·         Give data source name : RRITEC_excel

·         Click on select workbook à select E drive , select required file à double click

·         Click on ok. Again ok. Again ok.

Step 3: Importing Meta data

·         Open administration tool.

·         Go to file menu à open à offline

·         Select RRITEC.rpd à open

·         Provide pwd as RRitec123

·         Go to file menu à import metadata

·         select connection type: ODBC 3.5

·         DSN :RRITEC_excel

·         Click on next again next

·         Select two tables(products, sales)

·         Click on import

·         Click on finish

Note2: Can we access all the tables of a database using one connection pool.

Yes. but we need to take care below points

1.    To access all the schemas of DB we should have all schemas permissions to connection pool user.

2.    In connection pool properties we need to enable Required fully qualified names.

3.    In connection pool select shared logon

Note3: To create automatic joins we need to have

1) In DB level foreign key relationship

2) While you are importing you should enable foreign key options.

Note4: Update Physical Layer

It is useful to synchronize RPD metadata with DB metadata

ToolsàUtilitiesàUpdate Physical Layer

Note5: Remove unused objects from physical layer

It will remove objects, which we did not use in BMM layer

ToolsàUtilitiesà Remove unused objects from physical layer

This utility cannot track columns