Skip to Content
Author's profile photo Former Member

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

Student code

Student Name

Mother’s Name

meas1

B1

Roger

Jeanne

1

B2

Nadia

Maria

1

B3

Cecili

Nadia

1

DP2

Student code

Student Name

Course Code

meas2

B1

Roger

Course1

1

B1

Roger

Course2

1

B1

Roger

Course3

1

B2

Nadia

Course1

1

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

Student code

Student Name

Course Code

Mother’s Name

B1

Roger

Course1

Jeanne

B1

Roger

Course2

Jeanne

B1

Roger

Course3

Jeanne

B2

Nadia

Course1

Maria

B3

Cecilia

Nadia

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./wp-content/uploads/2014/12/tables_605316.png

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

mothers name detail.png

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 :

table with mothers detail.png

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 :

course code.png

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

merged final.png

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

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Hi Rogerio Plank ,

      Your blog is very good 😎 and informative ℹ . I just loved the way you have demonstrated with sample data. I have got better understanding on Merging concept now.

      Expecting more blogs from you!!

      Best Regards,

      Suman

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      HI Suman,

      thank you so much for your comment!

      ROgerio

      Author's profile photo Former Member
      Former Member

      Hi Rogerio Plank,

      thank you very much, I will try this and maybe it is the answer of a problem I struggle with for weeks, now.

      Best thing is that the student taking 3 courses at once (while Cecil takes none) is also named Rogerio, what a coincidence! 😉

      BR

      Michael

      Author's profile photo Former Member
      Former Member

      Thank you very much Rogerio 🙂

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Thanks for your example for using merging dimensions and 1-N relations

      I have a problem with merged dimensions and N-N relations

      I will modifiy your example to explain. Your colum mother name, will become sisters name, in order to have many sisters for a student

      DP1

      Student code        Student Name     Sister’s Name      meas1
      B1                         Roger                         Jeanne                  1
      B1                         Roger                         Chanel                  1
      B2                         Nadia                          Maria                    1
      B2                         Nadia                          Linda                    1
      B3                         Cecili                           Nadia                   1

      DP2(no change)
      Student code        Student Name             Course Code       meas2
      B1                         Roger                          Course1              1
      B1                         Roger                          Course2              1
      B1                         Roger                          Course3              1
      B2                         Nadia                           Course1              1

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

      Student code        Student Name              Course Code       Sisters’s Name
      B1                         Roger                           Course1              Jeanne
      B1                         Roger                           Course2              Jeanne
      B1                         Roger                           Course3              Jeanne
      B1                         Roger                           Course1              Chanel
      B1                         Roger                           Course2              Chanel
      B1                         Roger                           Course3              Chanel
      B2                         Nadia                            Course1             Maria
      B2                         Nadia                            Course1             Linda
      B3                         Cecilia                                                     Nadia

      how can a display thi table?