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
DP2
|
|
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
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
HI Suman,
thank you so much for your comment!
ROgerio
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
Thank you very much Rogerio 🙂
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?