Is there a way to achieve nested joins in Pega Insights

General
,
1

While configuring Pega Insights , when we need to fetch data from multiple tables, we generally configure associations and use it.

In the scenario, Any idea how to configure the below query in pega insights.

<-----

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, OrderDetails.ProductID, Products.ProductName

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID;

----->

So far, we have created an insight on Customers Data Object and created association from Customers to Orders.

However, we are not able to configure for the nested join in associations.

Please let us know if there is any way to do it.

2

@ROHITN26 Pega Platform supports nested joins in Insights through the use of associations. You can create both simple and advanced associations for joining multiple classes. For nested joins, you would typically use advanced associations, which allow you to specify multiple join conditions. Once these associations are defined, they can be used in Insights to fetch data from multiple related tables. However, please note that the configuration of these associations should be done carefully to ensure that they accurately represent the relationships between your data objects.

:warning: This is a GenAI-powered tool. All generated answers require validation against the provided references.

What’s new in reporting '23 > Support for custom associations in Insights

Associations > Association types

I have asked our SME’s to comment further if they can.

3

Using an advanced association is the way to go to address this problem. We added support for advanced associations in the Infinity 23’ release. You can find documentation on how to configure an Advanced Association here:

Once you have your advanced association you need to configure the Explore Data landing page by going to “Channels and Interfaces”, then you need to add the association for the specific class you want to expose it for. Once you do that, you should see the Association in your Explore Data experience. Documentation can be found here on how to add an Association to your Explore Data landing page:

Hope that helps!

4

@MeghanAtkins , Thanks for the response. However, the configuration of advanced associations wouldn’t fetch the columns related to the secondary class in the insights. It is merely for the filtering. Please let me know if it’s otherwise

5

@ROHITN26 - The above mentioned joins and showing fields in the secondary join classes are possible through Advanced associations. Need to consider the join conditions carefully.

Refer the join conditions in the OOTB association rules pxAssignedWorkGroupInfo, pxAssignedUnitInfo and try the similar in your advanced associations. it will work in Insights.

6

@choky Thanks for your response. I’ll make the query more clear.

Class A is related to B, Class B is related to Class C
I created an association between A and B. Using advanced associations I configured this association between A and (B and C)
similar to OOTB association “pxAssignedWorkGroupInfo”.

Now, If I have to create an insight in the context of Class A and refer properties from class C. Now i can only refer properties form Class B and not Class C. Is there any way possible ? Thanks again !!

7

@ROHITN26 - Its possible to refer the properties in Class C.

You need to create another association to refer C. Create advanced association in the class A and refer class C in the class name of the Class association.

In the join conditions - Add first join for class B and next Join for class C.

Now you can refer the properties of class C by using this new association.

We can refer any joined class fields, only thing is we need to create advanced association for each joined class to refer it.

Please let me know if this helps.