on 03-20-2013 7:07 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
, 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
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.
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
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
I was looking for "referential Join" and found below blog from Ms. Gemma Durany useful for me!!
reposting, might be useful for you.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.