Configuring Many To Many Relationship

Configuring Many To Many Relationship:

1. What is a bridge table ?

A) If you want to connect two tables where there is no relationship you can use a third bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer.

2.What are the steps not valued in bridge table?

A) Import tables.

a. create the physical model.

b. map the bridge table.

c. create a calculation measure.

d. map objects to the presentation layer.

e. Verify the results.

3. How to avoid a bridge table?

A) There are several approaches:

a.Hide the many-to-many relationship. You can publish two versions of    the schema: the full one for  use by structured reporting and a handful of power users, and a version that eliminates the many-to- many relationship for use by more casual users.

b.Eliminate the many-to-many relationship by collapsing/aggregating multiple rows. Add a row to the many-to-many dimension table: “Multiple rows”. The fact table can then link directly with the dimension. As with all design decisions, the IT organization cannot choose this approach without consulting with the user community.For something which has limited information value, this approach may be quite acceptable.

c.Identify a single primary row (The top 1) It may be possible to identify a primary row, either based on some logic in the transaction system or by way of business rules.

d.Pivot out the many-to-many dimension (see below the Boolean Column Method). If the domain of the multi-choice space is small, you can eliminate the bridge table by creating a dimension table with one column for each choice.

4.what is the use of a Bridge Table?

  A) Instead of modeling the relationship table into a new lower level in the dimension as in Technique #2, the relationship table can become a separate logical table that servers as the Bridge between the dimension and the facts.  Create a new Logical table with the M:M relationship table as the source, mark the logical table as a Bridge table, and adjust the Business model to show the relationship of Facts:Bridge as 1:M and Bridge:Dimension as M:1.  The indication that the Logical Table is a Bridge table is merely an indicator to Analytics that the table is not a Fact table, which it assumes to be any lowest-level table in the data model.

5.what is a helper table?

  A) Dimensional modeling is the preferred method of organizing data in OBIEE but at times the standard configuration for a dimensional star does not represent the way data is collected in the source system

6.when we go for helper table?

A)In single dimension  between column then if have the many to many relationship then we go to helper table

7.what are the steps of helper table?

A)The steps for helper table are:

1.Create helper table.

2.Build physical model.

3.Build logical model.

4.Map logical table source.

5.Build the presentation layer.

6.Verify the results.

8.What is bridge table, help table, Weight factor and gap column?

A) Bridge Table: If you want to connect two tables where there is no relationship, you can use a third table, or bridge table for connecting them. This table will have common columns in both tables, this is used in BMM Layer. You would mainly use a bridge table to perform a many-to-many join without the bridge table being seen as the fact table. The bridge table is then joined in the Business Modelling Layer and 'tagged' as a bridge type or assignment.

1. Resolves many-to-many relationships between dimension tables and fact tables.

2. Stores multiple records corresponding to a dimension.

3. Contains a weight factor column representing the ratio of the many-to-many relationship.

For Example: Each sales representative may participate in many deals that pay commission.

1. Each deal may include many sales representatives who split the commission.

2. A Bridge table is required to model this many-to-many relationship between the commission fact table and the sales representatives dimension table.

Help Table: Are used to model many-to-many relationships for team based hierarchies in a dimension. Enable users to query for hierarchy data and get meaningful results at different levels of the hierarchy. 
Example: Modeling allows Queries at different levels in the position hierarchy and the ability to roll up results for managers.
Fact table: Contains measures by position.
Position dimension table: Contains position hierarchy data.
Position helper table: Contains position relationship data.

Weight Factor: When creating a report that uses a measure from the fact table, a dimension value from the the employee table, and a dimension value from the table that causes the N:N cardinality - you need to use the weight factor to make sure your measure isn't getting double or triple counted.