Join cardinality in Analytical View: EKBE & EKET tables

[Former Member]

Dear All,

    I need to join 2 fact tables EKBE and EKET, with EBELN & EBELP fields. The other measures I would be interested would be MENGE from both and EKBE-BUDAT and EKET-EINDT. So,

1. What should be the cardinality for join EKET:EKBE ; 1:n for the combination of EBELN & EBELP keys? or m:n ?

or You suggest me to create 2 Anay Views for each fact tables and then join them in calc view(again what cardinality) or use union?

2. I need the vendor(LIFNR) information for the PO, but I dont find these field in neither of tables so I should join EKKO to EKET ? or any other work around?

Thanks again for your help!

Best Regards,

[Former Member · Accepted solution]

Hi Vittall,

To determine the cardinality you can look in source system SD11 as see the current model relationship I think. However how you model depends on the output you are trying to achieve. EG I recommend you open up source SAP system and use transaction SE16 to view the tables. From here choose a test case and use it to enter into the tables. EG if you are only joining on EBELN and EBELP fields of the 2 tables then you get a M:N relationship as both tables contain more keys and any combination you choose of these 2 fields will return multi values. If you are able to you should map to as many keys as you are able to when joining tables.

Please do not join these tables in one analytic view as you need 2 measures from 2 different tables. You cannot do this in one view. You are only able to select measures from one table within the analytic view so you will not be able to achieve your results.

Do a Union in the Calculation view as this is best practice and do not do a join. If vendor is not in your fact table then you will need to create an attribute view on a table which has this and join it to one of your analytic views.

Views in HANA are essentially new tables you are creating complete with keys and attributes. Best practice is to join attribute view keys to the primary keys of the analytic view. This allows the analytic view to inherit the output of the attribute view.

Kind regards,

Danielle

[Former Member · Accepted solution]

Hello Danielle,

   Thanks for your detailed response, this is what I was looking for.

In the mean time I was playing around, I joined 3 tables EKKO -(1:n)- EKET -(m:n)- EKBE, and defined only MENGE as measure and rest of others as attributes.

To my surprise, this works and in the data preview. I can use aggregate functions(SUM in my case) on these attributes. This is really cool (possible with HANA Studio 1.0.36)

To my surprise, I was also able to join the attribute views with this analytical view, and the join is with attributes from different physical tables(EKKO & EKBE) ?

But I should get error message as "The joins all need to point to the same physical table" ??

Would you know the reason ?

Best Regards,

[Former Member · Accepted solution]

Ive not seen that error before but it might have something to do with the fact you have created a analytic view with EKKO and EKBE and then also joined it to the same tables. Best way to understand what HANA is doing is to test each level and put all fields for testing into the output so you can see whats going on. Do a filter so you can do a case by case unit test and compare with the source system data base tables. Start with the attribute view testing first. Review the output. Then test your data foundation makeing sure to again put all relevant fields into the output for testing and filter for the same case bases. Remember an attribute/analytical output is effectively a new table you have structures with new keys. Then join you attribute view to your analytic view one join at a time and test the output. You will see all attribute output fields automatically (EG navi attributes).

Doing this should help you to better understand, improve performance, avoid repetitive data, and debug your error.

Kind regards,

Danielle

[Former Member · Accepted solution]

Hi Vittali,

I know you had posted this question sometime back now, but i could not resist to reply here as we had faced similar issue with EKBE and EKET.

I do not see any reason why you should get error when you select on MENGE from one table as your measure.

Also, you can join attribute views with the data foundation only if the joining columns, for one attribute view, are from the same table of data foundation also. if your join is based on 2 columns (Col1 and Col2) and if these 2 are coming from different tables in DF then you will definitely get error. But if both these columns are present in one single table of DF then there should not be any error.

Regards,

Piyush

[rama_shankar3]

Please create two analytical views and then create a calculation view. This is the best practice recommended.

Regards,

Rama