Merging queries with a 1 to N relationship

Today I would like to show a way to merge two queries with a 1xN  relationship.

WEBI is good on merging queries ( DP1 and DP2 ) in which for one row of DP1 there’s, at most, one row in DP2 and vice versa.

When you come to a situation in which for each row in DP1 you can have more than one row in DP2 it’s a problem.

Let’s take an example. Suppose you have a query with students I will call it DP1. In DP1 you got: Student code, Student Name, Mother’s name and a measure (meas1). A second query DP 2 contains the course in which students are enrolled, DP2 has Student code, Student Name, Course code and a measure meas2.

Each student can be enrolled in zero or more courses

I will populate this queries with the following data :

DP1

My task here is to bring all data in a single table, like :

The data is on two MsExcel sheets , so, firstly, I need to import it to WEBI Rich Client.

After importing the sheets, that’s how my data looks like.

I will merge the two queries on Student name and Student code, which are the common objects.

A table with the merged Student name and Student code will present the data from both data providers.

To show the not merged dimensions coming from DP1 and DP2, I must create detail variables. Let’s do it with Mother’s name

For Mother’s name detail I choose , for the associated dimension the merged Student code. Dropping it on the table containing merged Student code and merged Student name :

The challenge now is to bring course code into the table. If I create a detail  variable over course code , this will generate a #MULTIVALUE error since , for example, Student “Rogerio” is enrolled in 3 courses.

So, we need to , force the relationship to be reflected on the table. We can achieve it by dropping the Course code dimension in the table, which will result in the following table :

The problem here is that Student “Cecil” that isn’t enrolled in any course won’t show up.

In order to show “Cecil”, select the table , right click and go to “Format table” and check “Show rows with empty dimension values”

Now the table shows the value for “Cecil”shows up

As a rule of thumb , when trying to merge DP’s with a 1xN relationship :

1 – Merge the common fields;

2 – Use the dimension coming from the N side query;

3 – Create detail variables from the 1 side query for each dimension needed with associated dimension equal the merged dimension;

4 – Check “Show rows with empty dimension values”on Table formatting for each table using dimensions coming from both queries.

Any comments will be much appreciated.

Cheers,

Rogerio