Creating multiple joins to one table in dataset

User_0PDV4

Hi,

I don't understand how could I create the red join in the picture. I can do all the greens but if I try to connect table 2 and 4, it breaks the other connections, either to table 1 or intersection table, depending on which way I drag the tables. Weird thing is that I have similar joins in table 3 and they work perfectly. There is now something that I'm missing, I just can't see it. :D Is this even possible to do this in datasets or do I have to do it in RPD?

Thanks in advance :)

Federico Venturin - Oracle

Hi @User_0PDV4 ,

It's not possible to create that join directly. Given a table A in the Join Diagram, you can join as many tables as you want on the right-side, but only one on the left-side.

You could either a) drag and drop the CX_RESERVATION_V table one more time to the Join Diagram (with a different name) and join it with CX_PRSP_CONTACT_V, or b) click on the Edit Definition button in the CX_PRSP_CONTACT_V tab, select the Enter SQL option, and manually update the query to include the join.

Federico Venturin - Oracle

Hi @User_0PDV4 ,

You could add Table 2 (or Table 4) twice to the Join Diagram with a different name to create the required joins.

Can you please share a screenshot of the Join Diagram tab from the Dataset Editor? This will allow us to provide better recommendations.

User_0PDV4

So I'd need a join between contact and reservation and looks like I really need to multiply probably the reservation, which is pretty stupid thing in end users point of view.

User_0PDV4

Thanks, I'll test which one works best :)

[Deleted User]

Expanding on what Federico said it's good to think about "why" you can't do that. Very simply because such a circular relationship does not make sense. This is why proper modeling approaches like using the semantic layer through either the Administration Tool or via the web based Semantic Modeler will go the route of aliasing tables for precisely this use case.

A direct relationship as drawn in the original post is methodologically wrong when it comes to data modeling.

User_0PDV4

True, unfortunately sometimes you just have to model 1:1 what you are given, no matter is it wise or not. :D

[Deleted User]

That is a very dangerous approach to say the least. At the end of the day it's about SQL code generation by model. If you supply the model with objectively wrong instructions you end up with wrong results as soon as that circular relationship kicks in especially with an intersection table included in the model. Result row multiplication is almost guaranteed. This kind of erroneous "model" is a dangerous thing to give to self-service users or even just users who do more than a hard-coded and static report.

There's a reason why there are different levels of modeling with different capabilities. Just because self-service multi-table data models exist doesn't mean they are the answer to all questions nor the right tool to use nor a justification for creating wrong models. A Cessna 172 is the most produced airplane on the planet but that doesn't mean it's the correct choice for crossing the Pacific unless the pilot is a really good swimmer.

George_Samaan

There is another approach, you can create CX_RESERVATION_V table in another dataset, then in the dataflow you can join both dataset using multiple columns on different tables(columns) then you will have a new dataset which you can use for your dashboards