cancel
Showing results for 
Search instead for 
Did you mean: 

What is Referential Join

former_member213277
Active Participant
0 Kudos

Hi Experts,

As per the posts/Blogs in SDN, Referential Join will act as INNER JOIN when we access the fields from both joined tables.

It will act as LEFT OUTER JOIN if no fields from Right table are accessed and it will not check whether corresponding value is exist in Right table or not assuming referential Integrity is maintained

To test Referential Join I have created a Attribute View with below mentioned tables which have one column Country

TAB1:                                                 TAB2:

Country                                                Country 

INDIA                                                    UK  

SRILANKA                                            US

NEPAL                                                 GERMANY

I have created a Attribute View with Referential Join and I am fetching field only from TAB1 (i.e.TAB1.Country) i.e. I have selected  TAB1.Country as output of the Attribute View.  Since there is no referential integrity maintained and only left table is queried hence Left Outer Join should be applied and all the records from Left table should be displayed.

But there are no records are fetched and displayed (its working as Inner Join even though only Left table is queried)

I am confused in understanding the Referential Join, Could you please explain me how does it work

Regards,

Nag

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Nagaraj,

  I hope you are getting correct results as per the SAP implementation doc  .I tried your scenario, referential join behaving as a inner join for the Attribute views.

" Referential join is feature is available only OLAP engine,when testing attribute views outside context of a Analytic View then the join engine will perform a inner join."

Let us wait for Ravi comments

Regards

Basha.

former_member213277
Active Participant
0 Kudos

Hi Basha,

I have not used this Attribute view in any analytical view/CV. I just created to test how Referential Join will work. When I do the data preview of this view fetching field only from left table i am getting the output as Inner Join not as outer Join.. Can you please tell what output did u get when you tried same scenario? Mean while I will upload the screen shot of the View as  suggested by Ravi

Regards,

Nagaraj

Former Member
0 Kudos

Hi Nagaraj,

To test Referential Join I have created a Attribute View with below mentioned tables which have one column Country

(Tab1-.> left and  TAB2 -Right table)

TAB1:                                                 TAB2:

Country                                                Country

INDIA                                                    UK 

SRILANKA                                            US

NEPAL                                                 GERMAN

Here Referential join working as a Inner join.  It did not display any records because there is no common records from above tables.

Regards

Basha.

Answers (6)

Answers (6)

former_member213277
Active Participant
0 Kudos

Hi All,

When we are using Attribute View outside the context of Analytic View( Data Preview of the Attribute View) then referential Join Used in Attribute View will always works as inner Join even though we are fetching fields only from Right Table

But when we use the Attribute view in context of an Analytic View then Referential Join will work as Left Join when no right table fields accessed.

Thanks and Regards,

Nagaraj

justin_molenaur2
Contributor
0 Kudos

I tried testing this a number of ways (regarding the referential join within an Attribute View), and could not validate that the referential join is meaningful within the context of an attribute view, even when executed from an analytic view.

If the idea is that the attribute view referential join will be a left only when a value from the right table is selected, I don't really see a scenario where this would be beneficial in an attribute view as most of the time the left table (which would hold the keys) in an attribute view will be the most granular level - ie KNVV (left) joined to KNA1 (right) N:1. The join to KNA1 in the attribute view may be avoided if no right fields are selected, but then again it may be cheaper to wrap KNA1 in an attribute view itself and join to the data foundation instead.

That being said, the referential join from a data foundation to an Attribute view within the Analytic View acting as a Left Outer if none of the right table fields are selected (all attribute views would be considered as a right table), is certainly of high value to eliminate unnecessary joins with the star.

In my testing, essentially no matter what I did, the attribute view still acted as an inner. However, the fact to attribute behaved as expected - it was a left outer when no right table fields were selected (skip to last 2 statements for that example).

Just wanted to share findings to further the discussion.

Thanks,

Justin

CREATE COLUMN TABLE "SYSTEM"."TEST_LEFT" ("MATNR" VARCHAR(18))

CREATE COLUMN TABLE "SYSTEM"."TEST_RIGHT" ("MATNR" VARCHAR(18),
  "MAT_TEXT" VARCHAR(20))

insert into "SYSTEM"."TEST_LEFT" values('12345')
insert into "SYSTEM"."TEST_LEFT" values('67891')
insert into "SYSTEM"."TEST_LEFT" values('55667')

insert into "SYSTEM"."TEST_RIGHT" values('12345','TEST_12345')
insert into "SYSTEM"."TEST_RIGHT" values('67891','TEST_67891')

CREATE Attribute view, TEST_LEFT.MATNR as key attribute, referential join 1:N
Resulting XML
<join cardinality="C1_N" joinType="referential" leftInput="#//Data Foundation/"SYSTEM".TEST_LEFT" rightInput="#//Data Foundation/"SYSTEM".TEST_RIGHT" textJoin="false">
      <leftElementName>MATNR</leftElementName>
      <rightElementName>MATNR</rightElementName>
    </join>

SELECT COUNT(*) FROM "_SYS_BIC"."copa-poc/AT_TEST_REFERENTIAL"
RESULT: 2

SELECT COUNT(MATNR) FROM "_SYS_BIC"."copa-poc/AT_TEST_REFERENTIAL"
RESULT: 2

SELECT MATNR FROM "_SYS_BIC"."copa-poc/AT_TEST_REFERENTIAL"
RESULT
MATNR
12345
67891

create column table "SYSTEM"."TEST_FACT"( "MATNR" VARCHAR (18) null,
  "SALES_VAL" DECIMAL (15,
2) not null)

insert into "SYSTEM"."TEST_FACT" values('12345',500.00)
insert into "SYSTEM"."TEST_FACT" values('11111',500.00)

insert into "SYSTEM"."TEST_FACT" values('67891',500.00)

insert into "SYSTEM"."TEST_FACT" values('55667', 500.00)

insert into "SYSTEM"."TEST_FACT" values('67891',500.00)

CREATE ANALYTIC view, AT_TEST_REFERENTIAL, TEST_FACT
  <join cardinality="CN_1" joinType="referential" leftInput="#//Logical Join/Data Foundation" rightInput="#//Logical Join/copa-poc::AT_TEST_REFERENTIAL" textJoin="false">
      <leftElementName>MATNR_1</leftElementName>
      <rightElementName>MATNR</rightElementName>
    </join>

SELECT COUNT(*) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
RESULT: 2

SELECT COUNT(MATNR) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
RESULT: 2

SELECT COUNT(MATNR) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
RESULT: 4

SELECT COUNT(MAT_TEXT) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
RESULT: 4

SELECT SUM(SALES_VAL) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
SUM(SALES_VAL)
2,500

SELECT MATNR, SUM(SALES_VAL) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
GROUP BY MATNR
MATNR;SUM(SALES_VAL)
12345;500
55667;500
67891;1,000

former_member184768
Active Contributor
0 Kudos

Hi Justin,

Couple of points, can you please post the graphical screenshot of your Analytic View. Just want to confirm which MATNR is being exposed in the output. If it is from the Attribute view and not from the Fact table, then the join will always work as INNER JOIN.

Can you also try the following:

SELECT SUM(SALES_VAL) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL";

This is to ensure that nothing from the right table is selected for sure.

Regards,

Ravi

justin_molenaur2
Contributor
0 Kudos

Hi Ravi, thanks for reading.

Indeed in the analytic view, MATNR from the attribute view is exposed, so I would expect that to work as an inner join as already discussed.

You can see in my script above I had executed the same statement as you mention and it was clear to me that the referential join within the analytic view was behaving as expected (Left Outer) when nothing from the right table was selected.

SELECT SUM(SALES_VAL) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"

SUM(SALES_VAL)

2,500

SELECT MATNR, SUM(SALES_VAL) FROM "_SYS_BIC"."copa-poc/AN_TEST_REFERENTIAL"
GROUP BY MATNR
MATNR;SUM(SALES_VAL)
12345;500
55667;500
67891;1,000

My only point of the long rant (thanks for reading ), is that the referential join within an attribute view - from within the context of an analytic view - is somewhat irrelevant. The base table (left) in an attribute view is typically going to be the most detailed and hold all the keys for the attribute view itself used for joining to a fact. So the only benefit I can see for a referential join from within an attribute view is if you have a right table that is 1:1 or n:1 (kind of like text), and you don't select anything from the right - it doesn't seem logical to have a 1:N within an attribute view.

Thanks,

Justin

former_member184768
Active Contributor
0 Kudos

, one comment:

The MATNR in the above case, should be exposed from the fact table and not from the Attribute view. Following the above rule, mostly all the attributes should be used from the Fact table rather than from the attribute view. In case additional attributes (similar to Navigational attributes in case of SAP BW) are required, then they should be exposed from the attribute view.

With such design principle, you would prefer to join attribute views to the fact table as most of the analysis can be done based on the fact table itself. Only in cases of the  analysis on additional attributes related to master data, the join the attribute view should happen. And this is what exactly provided by the Referential join.

And, this should have been a new discussion thread.

Regards,

Ravi

justin_molenaur2
Contributor
0 Kudos

Ravindra, that's a great approach for really exposing the value of referential join, however I am not sure how this is achieved.

- An attribute view must have a key attribute defined, which must be exposed in the attribute view in order to join to a data foundation in an analytic view.

- Once the data foundation is connected to the attribute view on the same key, the option to expose from the data foundation is removed and instead you can only set the attribute key field for output.

In my example, I have a simple view on MARA with MATNR as the key field. When it is added to an analytic view and joined to the data foundation, I am no longer able to use the DataFoundation-MATNR as output, instead you are forced to use the MARA-MATNR. Therefore the MATNR exposed in the view is sourced from the attribute view and the inner join function of the referential join would kick in.

How are you suggesting you actually implement your suggestion as I don't see how it's possible.

justin_molenaur2
Contributor
0 Kudos

Ravi - not sure you got the update to my question. Eager to know the answer when you get a chance.

Thanks,

Justin

former_member184768
Active Contributor
0 Kudos

Hi Justin,

Can you use the workaround as follows:

In the data foundation layer, please add MATNR column twice. Use one of the field (MATNR_JOIN) for the join and another column (MATNR) for reporting.

This way you be able to use the referential join as well as the column in case the join is not required.

Regards,

Ravi

justin_molenaur2
Contributor
0 Kudos

Hi Ravi, I was able to build the AV as mentioned, thanks for that. The additional step would be that you would need to hide the MATNR coming from the attribute view and also re-alias the field name.

However, what I am seeing from the reporting tools (Excel and Lumira at least), is that even though the MATNR from the attribute view is hidden (and the fact MATNR is used instead), in the tool the attribute view version of MATNR is the only option available.

MATNR from data foundation is being exposed

MATNR from attribute view is being hidden, re-aliased due to multiple MATNR references.

The MATNR from the attribute view is being displayed in Lumira, no option for the data foundation based MATNR.

I can't comment on any other tools, but this is my first observation. What front end tools have you observed the behavior with this technique?

Thanks,

Justin

malyavantham
Participant
0 Kudos

I was looking for "referential Join" and found below blog from Ms. Gemma Durany useful for me!!

reposting, might be useful for you.

http://scn.sap.com/people/gemma.durany/blog/2012/08/12/sap-hana-engine-optimization-for-bizy-geeks-e...

former_member213277
Active Participant
0 Kudos

Hi Basha,

Let me check this, I will use this Attribute View in an Analytic View and will see how data will be fetched. As you mentioned in this case Ref Join should work as Outer join when Attri View is used in an Analytic View.

Because of restriction I am not able to upload images.

Thanks and Regards,

Nagaraj

former_member213277
Active Participant
0 Kudos

This message was moderated.

former_member213277
Active Participant
0 Kudos

Hi Basha,

But if we are selecting fields only from Left table then Referential Join should work as Left Outer Join right ? in that case we should get the all the records from Left table even though there is no corresponding entry in Right table

Regards,

Nagaraj

Former Member
0 Kudos

Hi Nagaraj,

But if we are selecting fields only from Left table then Referential Join should work as Left Outer Join right ?

-> No. It works as a Inner Join when we use Referential join in attribute views.

Referential join is feature is available only OLAP engine,when testing attribute views outside context of a Analytic View then the join engine will perform a inner join."

Regards

Basha.

former_member184768
Active Contributor
0 Kudos

Hi Nagaraj,

Can you please check if there is any filter applied on the right hand side table (Attribute view). In case there is a filter applied, then referential join works like inner join EVEN IF no attributes from the right hand side table are selected.

Regards,

Ravi

former_member213277
Active Participant
0 Kudos

Hi Ravi,

I have not defined any filter on both tables.

I have just joined tables as Attribute View and outputting field only from one table.

Regards,

Nagaraj

former_member184768
Active Contributor
0 Kudos

Hi Nagaraj,

Can you please post the screenshots of the Analytic and Attribute views, if possible. Can you also confirm that there is NO filter applied on any of the tables in the Attribute view.

Regards,

Ravi

Jonathan_Haun
Participant
0 Kudos

Ravindra,

With Analytic Views, I have also noticed that a referential join acts like an inner join when there are filters within the attribute view foundation. I am assuming that no columns were queried from the attribute view with this statement. I was a bit surprised when I discovered that. I expected the referential join to be pruned from the execution because I used a "referential join". To further test, I then changed the logical analytic view join to a left outer. To my surprise, the join was pruned from the execution even if I had a filter in the attribute view foundation. I am sure that this is a matter of semantics but I would have expected this to work the otherway around.

Thanks,

justin_molenaur2
Contributor
0 Kudos

I have observed the same. For this reason, I set almost all joins in a analytic view to left outer to take advantage of the pruning.

Regards,

Justin