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).
- 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).
- 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.
- 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
- 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.
- Select the D1 Book and F1 Book logical tables, Right Click on them and select Business Model Diagram -> Selected Tables Only.
- Join tables from F1 Book to D1 Book.
- Double click on join
- Make sure to select 0, 1 on the D1 Book Table and N on the F1 Book Table.
- Select the L1 Author table select “Lookup table” checkbox.
- 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“
- Go to the Column Source tab and select “Derived from existing columns using an expression.
- Enter the below expression and click OK
- Again Click OK
- Again Click OK.
- Click General Tab and next to the “Descriptor ID column:” click “Set…“
- Select the “AUTHORID“
- Click OK
- Drag and Drop Book Lookup from BMM to Presentation Layer.
- Check Consistency and save
Hence you are done with OBIEE Modelling Many to Many Relationships.
In next article we will learn about adding calculation to a Fact.