How-to: Bridge Tables and Many to Many Relationships Demystified in OBIEE 11g

Bridge tables - entire books have been devoted to this concept, countless blogs write about it, and organizations offer entire classes dedicated to demystifying this idea. Ralph Kimball, creator of Kimball Dimensional Modeling and founder of the Kimball Group has written quite a few great articles discussing the theory of bridge tables.

Yet when researching for comprehensive guides on how to actually implement a bridge table in OBIEE 11g, the documentation available is either:

• Out of date
• Contains implementation steps for OBIEE 10g which has since been deprecated
• Does not contain adequate detail 
• e.g. missing key steps

This guide is going to outline the basic use case of a many to many relationship, how OBIEE 11g resolves this dilemma and how to successfully implement a bridge table model within the 11g platform.

First thing's first - what is a bridge table and why do we need it?

At its core, bridge table solve the many to many relationship we encounter in many datasets. Many to many relationships in itself are not "bad", but when attempting to conform a data set to a star schema - many to many relationships just do not work. Star schemas assume a one to many (1:N) cardinality from the dimension to the fact. This means "one attribute of a dimension row can be found in many rows of the fact table".

For Example:

• One job (job dimension) can be performed by many people
• You would see the same JOB_WID repeating in the fact table
• One employee (employee dimension) can have many jobs
• You would see the same EMPLOYEE_WID  repeating in the fact table
• One call at a call center(ticket dimension) can have many ticket types
• You would see the same CALL_WID repeating in the fact table
• One patient (patient dimension) can have many diagnosis
• You would see the same PATIENT_WID repeating in the fact table

This 1:N cardinality is represented in OBIEE as (using call center/employee example) :

"Cardinality of '1' applied to the dimension and cardinality of 'N' applied to the fact'

But what happens when in the above examples, the cardinality is actually N:N? 

For Example:

• Many employees can have multiple jobs and each job can be performed by multiple employees
• Many patients can have multiple diagnosis and each diagnosis can be 'assigned' to many patients
• Many calls can have multiple call ticket types and each ticket type can belong to multiple calls

This many to many relationship is initially (and incorrectly) represented in OBIEE 11g as:

'Cardinality of '1' is applied to the two dimension tables and cardinality of 'N' is applied to the fact'

Any BI Architect should recognize the above model - it's a traditional star schema! If you stop here and decided to ignore the issue with your dataset and 'hope' OBIEE aggregates the model correctly, you're about to be disappointed.

Why star schemas dont work for N:N cardinality

Consider the following scenario: You're a call center manager and you want to capture the number of calls with positive feedback per employee. You also want to capture the type of calls an employee answers in any given day.

Upon analysis of the requirements you conclude that "each call received by an employee can have many call types and each call type can be answered by multiple employees".

For example:

• I answer a take a call that is classified as a 'new call', 'urgent', and 'out of state transfer' (three different call types) - this is the "each call received by an employee can have many call types".
• A colleague also received a phone call that is classified as 'out of state transfer' - this is the 'each call type can be answered by multiple employees"

Now let's put this data in a traditional star schema fact table as modeled below:

ID	EMPLOYEE_WID	CALL_TYPE_WID	NUMBER_OF_GOOD_CALLS
1	1	1	300
2	1	2	300
3	1	3	300
4	2	2	500
5	2	3	500
6	3	1	200

You can see in the above data set that:

• EMPLOYEE 1:
• Has 3 different call types
• Has 300 positive reviews (NUMBER_OF_GOOD_CALLS) 
• This metric is at the EMPLOYEE level and not the call type level!
• EMPLOYEE 2:
• Has 2 different call types
• Has 500 positive reviews (NUMBER_OF_GOOD_CALLS)
• This metric is at the EMPLOYEE level and not the call type level
• EMPLOYEE 3:
• Has 1 different call type
• Has 200 positive reviews (NUMBER_OF_GOOD_CALLS)

Now you receive a requirement to create a KPI that displays the Number of Good Calls as a stand alone widget.

PROBLEM 1 - Aggregation :

The number of good calls you received based on the above fact table is not 2100 - it's 300 + 500 + 200 = 1000

• Employee 1 received 300 good calls
• Employee 2 received 500 good calls
• Employee 3 received 200 good calls

but due to the many to many cardinality of the data, the star schema duplicates the measures because each employee can take calls of many call types and each call type can be assigned to many employees!

PROBLEM 2 - Grand Totaling:

What if you don't care about aggregates? What if you just want a report that contains the employee, call type and a summation/grand total?

Notice how NUMBER_OF_GOOD_CALLS is repeated across multiple call types and the grand total is still incorrect. It's being duplicated due to the many to many relationship that exists between call type and employee. Furthermore, it paints an incorrect picture that NUMBER_OF_GOOD_CALLS is some how related to CALL_TYPE

How do we resolve this many to many cardinality with a bridge table?

When all is said and done, the incorrectly built star schema:

should be modified to:

Let's break this down:

The bridge table:

This the purpose of the bridge table is to resolve the many to many relationship between the call type and employee. It will contain, at a minimum, the following four columns:

1. The primary key of the table
2. The EMPLOYEE_WID
3. The CALLTYPE_WID
4. The weight factor

The weight factor is what's going to resolve the issue of double counting. 

• If an employee has 3 call types, there would be 3 rows and the weight factor of each row would be .33
• If an employee has 10 call types, there would be 10 rows and the weight factor of each row would be .1

In our bridge table data set, we're going to use the same 3 EMPLOYEE_WIDs and create the following:

ID	CALL_TYPE_WID	EMPLOYEE_WID	WEIGHT
11	1	1	0.33
12	2	1	0.33
13	3	1	0.33
23	2	2	0.5
24	3	2	0.5
31	1	3	1

You can see from this example that we've taken the N:N dataset in the fact table and moved it into this bridge.

The dimension that is joined to both the fact and bridge

This is a generic dimension that contains the unique EMPLOYEE IDs in your organization's dataset.

For example:

ID	EMPLOYEE_ID
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10

The dimension that is joined to only the bridge table

This dimension contains all of the possible call types. Note how this table is not physically joined to the fact. This is because this specific dimension (CALL_TYPE) is what's causing the N:N cardinality

For example:

ID	DESC
1	Call Type 1
2	Call Type 2
3	Call Type 3
4	Call Type 4
5	Call Type 5
6	Call Type 6
7	Call Type 7
8	Call Type 8
9	Call Type 9
10	Call Type 10

The Fact Table

We've moved the N:N cardinality from the original fact table to the bridge table so the new fact table now contains exactly one row per employee and does not have the CALL_TYPE_WID.

ID	EMPLOYEE_WID	NUMBER_OF_GOOD_CALLS
1	1	300
2	2	500
3	3	200

How do we implement this model in OBIEE 11g?

Step 1: Import Tables into Physical Layer

This is always the first step performed when creating a model regardless of its type. In the above example i'm importing four tables:

Step 2: Create the Physical Data Model

Based on our data set above the join conditions would be implemented as follows:

• 1:N relationship from employee dimension to fact table
• 1:N relationship from employee dimension to bridge
• 1:N relationship from call type dimension to bridge

Notice how employee_demo_d is the only dimension that is joined to the fact. w_call_type_d is not joined to the fact because that is the dimension that is causing the many to many relationship issue.

Step 3:  Create the Logical Data Model
The creation of the BMM is where we deviate from our standard build steps of a traditional star schema:

1. All associated dimension tables referencing the bridge table will be stored in a single BMM table
2. The single BMM table will have two logical table source

Step 3.1 : Drag the fact table and dimension table that is connected to the fact table into the BMM. 

In our example, we are dragging w_calls_f and w_employee_demo_d into the BMM:

Step 3.2: Create a 2nd LTS in the existing dimension table

1. Right click W_EMPLOYEE_DEMO_D -> New Object -> New Logical Table Source
2. Name it 'Bridge'
3. Add W_BRIDGE_D and W_CALLTYPE_DEMO_D (the two dimensions not directly joined to the fact table) under the 'Map to these tables' section

1. Next add the remaining dimension columns from W_CALLTYPE_DEMO_D and W_BRIDGE_DEMO_D to the Dimension table in the BMM

Step 3.3: Create a level-based dimension hierarchy for the dimension BMM

1. This step should be completed whether or not the schema is a star or bridge

Step 3.4: Confirm the BMM model has a 1:N relationship from the dimension to fact

Step 3.5: Set aggregation rule of NUMBER_OF_GOOD_CALLS to sum 

All measures in the BMM must have a mathematical operation applied to the column

Step 3.5: Set the Content level of the dimension table to 'detail' in within the LTS of the fact table

Again, this is something that should always take place regardless of the type of model

Step 4: Create the Presentation Layer

This part is straight forward, just drag the folders from the BMM into the new subject area:

The moment of truth

So why did we go through this elaborate exercise again? To fix the aggregation issues we were having with NUMBER_OF_GOOD_CALLS due to the N:N cardinality of the data set. Let's create that 'standalone KPI' Number of Good Calls:

Notice how the metric correctly sums to 1000. Let's check the back end physical query to confirm:

Notice how it's hitting the fact table and not the bridge or the call type dimension. 

But what about the weight factor?

Let's go back to the scenario where we want to compare across dimensions joined via the bridge table (EMPLOYEE and CALL_TYPE):

• 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:

• Notice column is using the the NUMBER_OF_GOOD_CALLS multiplied by the WEIGHT factor in column 2
• Each row in column 1 correctly represents the NUMBER_OF_GOOD_CALLS in the fact table despite having the repeated values of multiple call types
• Note the aggregation of grand total sums to 997. This is because the weight factor is rounded to the 2nd decimal for EMPLOYEE_WID = 1 (.33%)

In order for grand totaling to work correctly with bridge table measures that use weight facts you must set the aggregation rule of the column (in this case column 1) to sum within Answers:

So what did we accomplish in this guide?

• A basic understanding of many to many (N:N) cardinality
• A basic understanding of why the star schema won't work for N:N cardinality
• How to resolve the cardinality issue with a bridge table
• How to implement a bridge table in OBIEE 11g

OBIEE - Selecting columns dynamically - part2, simple Prompt

 OBIEE - Selecting columns dynamically - part2, simple Prompt

In previous post I talked about column selectors. They are not enough in complex cases, for example: 

• Single selection of columns for several reports.
• Using the same column selection in several columns of the same analysis.
• Dependency between selection of columns (for example, I can select "Target Revenue" in measures only if "Year" selected in one of the dimensions.)

In this post I will create an example where selecting a column from prompt would influence 2 columns in analysis.

First I create a Dashboard Prompt:

All I want is to create a presentation variable, I will name P_col1, so I add a new Variable prompt (and not a Column Prompt).

There I will select Presentation Variable and it's name (P_col1) the Label and the user input format. Here you can select whatever you like, just make sure user can select only one value. I selected a "Radio Buttons" option. 

Next I add Custom Values. It is important that those Custom Values will be exactly the same way those columns appear in the column Formula. For example this is the Formula of the "Offices"."Department" column:

and this is the Prompt definition (I selected the Year, Product Type and Department columns):

 

The last thing I did was to select a specific value as default selection (this is important).

This is the resulting prompt:

Now i will create an Analysis that uses the above described presentation Variable and place them both in a Dashboard.

First I will create an equivalent to a column selector:

In the formula I create a presentation variable with a default value:

the result in my case is: @{P_col1}{"Time"."Per Name Year"}

I'll add few more columns:

And this is the result:

I could do exactly the same with column selector. Now, for fun,  lets add something I couldn't do with column selector: I'll add a measure that will show "Discount Amount" if the "Per Name Year" was selected and "Billed Quantity" otherwise. 

So my extra column will have the formula:

 CASE WHEN '@{P_col1}{"Time"."Per Name Year"}'='"Time"."Per Name Year"' THEN "Base Facts"."Discount Amount" ELSE "Base Facts"."Billed Quantity"  END

This part I couldn't do with column selectors.

Now I'll place both in a dashboard:

 You might have noticed the first column name didn't change after I selected the "Product Type". We didn't have this issue with column selectors. 

To fix it I'll place the Presentation Variable in the Column Heading:

Now the result header is dynamic:

While developers might be happy with the described above, users and UI / UX people might not share my enthusiasm with headers and Prompt Values such as "Time"."Per Name Year" and column header such as Measure2.

They tend to express their feelings:

How to make them happier? That would be covered in the next post.

    

OBIEE 11G Creating Column Selector and View Selector Views

 OBIEE 11G Creating Column Selector and View Selector Views

A Column Selector view adds a column selector to the results. A column selector is a drop-down list from which users can dynamically change the columns that display in results. This will allow you to analyze data along several dimensions. By changing the measure columns, you can dynamically alter the content of the analyses you have created.
To create a Column Selector and View Selector views, perform the following steps:

1 .	a. Open the Regional Revenue analysis in the Analysis Editor. The Results tabbed page appears. b. Click the New View icon and select Other Views > Column Selector.
2 .	The Column Selector view appears. Drag the Column Selector view above the Title view. Click the Edit View icon for the Column Selector view. The Column Selector editor appears.
3 .	a. Select the Include Selector C50 Region check box. b. In the Label (optional) Choices text box, enter Choose a column:. c. With Column still selected, double-click the following columns to add to the selector: P4 Brand, P3 LOB, and P2 Product Type. d. Click Done. The Compound Layout appears:
4 .	a. Click the Column Selector drop-down list and select P3 LOB: b. The values change appropriately. Note, however, that because you set a custom heading for the C50 Region column earlier, the custom heading is still displayed for the column. c. Save the analysis.
5 .	Now you will add the View Selector view. A View Selector view provides a drop-down list from which users can select a specific view of analysis results from among saved views. A View Selector view is analogous to a storage container, because it holds other views that have been selected in the editor for display. a. Perform these steps before adding the View Selector view: Delete the Title view from the Compound Layout. Set the Column Selector to display the C50 Region column, which is the default. Then delete the Column Selector view from the Compound Layout. Add a Graph view - Vertical Bar graph. These changes will allow you to showcase the analytic data-driven views. Regional Revenue should look like this:
6 .	a. Click the New View icon on the toolbar and select Other Views > View Selector. b. Drag the View Selector view to the right of the Table view. c. Click the Edit View icon for the View Selector view.
7 .	a. The View Selector editor appears.In the Caption text box, enter Choose a view:. b. In the Available Views list, select the Table and Graph views and click the shuttle icon to move them to the Views Included list. A preview appears at the bottom of the editor. Note that these views are data-driven views, unlike the Column Selector and Title views, which were deleted from the Compound Layout. c. Click Done.
8 .	The Compound Layout should look like this when the Graph view is selected: Do not save your changes to the analysis.