Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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.

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

5 Comments
Labels in this area