OBIEE Tutorial

OBIEE Modelling Many to Many Relationships

OBIEE Modelling Many to Many Relationships


Few cases where you need Many-to-Many Relationships:

  • A author has contributed to many book, a book is written by many authors
  • In a Sales database, each product belongs to one or more groups, and each group contains multiple products
  • In banking, a customer can have different accounts, and an account can belong to different customers.
  • In insurance, a customer (or household) can have different policies, but a policy can support multiple customers (or households). In many businesses, a parent customer can have subsidiaries.

When you need to model a many-to-many relationship in a Dimensional Modelling – Dimensional Schemas, you fall in a snowflake schema.


In this case we have tables:

  • BRD_BOOK which contains books written by many authors and also authors who wrote many books
  • BRD_AUTHOR which contains unique AuthorID, Author Name and Total No. of books written by that Author.

We are going to create the BRD_AUTHOR as a lookup table.

  • Right Click on BRD_BOOK physical table and select “New Object” -> “Alias…” and enter “D1 Book” for Name and click OK (to close the dialog box).

OBIEE Modelling Many to Many Relationships

  • Right Click on BRD_AUTHOR physical table and select “New Object” -> “Alias…” and enter “L1 Author” for Name and click OK (to close the dialog box).

OBIEE Modelling Many to Many Relationships

  • Create a new Business Model and Mapping section called “Book Lookup” and drag the “D1 Book” physical table to the “Book Lookup” in the BMM layer.
  • Drag the “D1 Book” physical table to the “Book Lookup” again a second time to the BMM layer and rename it to “F1 Book
  • Drag the “L1 Author” physical table to the “Book Lookup” in BMM layer.

OBIEE Modelling Many to Many Relationships

  • Double Click the D1 Book logical table and under the Keys tab create a Key name: AuthorID and select the Columns: AUTHORID and set AuthorID and the Primary Key

OBIEE Modelling Many to Many Relationships

  • Double Click the L1 Author logical table and under the Keys tab create a Key name: AuthorID and select the Columns: AUTHID and set AuthorID and the Primary Key.

OBIEE Modelling Many to Many Relationships

  • Select the D1 Book and F1 Book logical tables, Right Click on them and select Business Model Diagram -> Selected Tables Only.

OBIEE Modelling Many to Many Relationships

  • Join tables from F1 Book to D1 Book.
  • Double click on join

OBIEE Modelling Many to Many Relationships

  • Make sure to select 0, 1 on the D1 Book Table and N on the F1 Book Table.

OBIEE Modelling Many to Many Relationships

  • Select the L1 Author table select “Lookup table” checkbox.

OBIEE Modelling Many to Many Relationships

  • In the BMM layer right click on the F1 Book logical table and select “New Object” -> “Logical Column” and enter Name: as “Dense Books By Author

OBIEE Modelling Many to Many Relationships

  • Go to the Column Source tab and select “Derived from existing columns using an expression.

OBIEE Modelling Many to Many Relationships

  • Enter the below expression and click OK

OBIEE Modelling Many to Many Relationships

  • Again Click OK

OBIEE Modelling Many to Many Relationships

  • Again Click OK.

OBIEE Modelling Many to Many Relationships

  • Click General Tab and next to the “Descriptor ID column:” click “Set…

OBIEE Modelling Many to Many Relationships

  • Select the “AUTHORID

OBIEE Modelling Many to Many Relationships

  • Click OK

OBIEE Modelling Many to Many Relationships

  • Drag and Drop Book Lookup from BMM to Presentation Layer.

OBIEE Modelling Many to Many Relationships

  • Check Consistency and save

OBIEE Modelling Many to Many Relationships

Hence you are done with OBIEE Modelling Many to Many Relationships.

In next article we will learn about adding calculation to a Fact.

Leave a Reply