2 HANA Modeling Questions (urgent - appreciate it)

[Former Member]

Hello experts,

Need your assistance.

1) In Attribute View, we can't have a table having joins coming from 2 different sources/tables in Attribute View.

For example, table MARC has join coming in from AUFK and AFKO. (even though before this would work at attribute level, it would fail at calculation view level).

I've tried this now and it seems to work at calculation level = confused But concerned it may not be consistent or cause other issues.

Is it still incorrect to have joins coming into a table from 2 or more different tables in Attribute View?

OR

If it is allowed, anyone have documentation from SAP, maybe a patch resolved it - so documentation of that will help? I have to show the client.

------------------------------------

2) I have a simple scenario where I only want data for the last 60 days. Unfortunately, in Attribute view you can only use a hard value. I can't put system date nor a hard coded date because the date is dynamic.

So At the calculation level I created a filter expression at projection node. But now() is not recognized here either.

So I'm stuck with getting all the data from the Attribute view. Then filtering. But that's effecting the performance significantly.

Is there a way to dynamically retrieve data only for the last 60 days from the Attribute View?

Thanks in advance.

[Former Member]

My Key attributes are MANDT and AUFNR from table AUFK (order header).

And I do need a field from Table MARC. But I have to satisfy the 2 primary keys in table MARC (plant and material) to avoid duplicates. I can satisy the 2 primary keys using AUFK and AFKO.

That's my dilemma.

[Former Member]

can you explain why you need MARC to be used as right table for both the join from AUFK and AFKO ?

Any business reason that will break if you use one of the join as inner?

Also you can create a restriction using a Join between both the MARC tables that might help get rid of duplicates..

[Former Member]

I'm using MARC as a right table because I only need to retrieve data coming from AFKO/AUFK. MARC is more of a look up table to retrieve a field.

I am using inner join with 1...1 cardinality.

[Former Member]

Did you tried using a Join between both the MARC tables (may be on  IDs) which might help get rid of duplicates?

Defining cardinality will not make any difference here as its a outer table itself..

[Former Member]

Thank you Durgha, that's what I was thinking. But I'm confised why it's working, before I used to get an error...I don't know why it's not giving an error. Regardless I don't feel comfortable moving forward this way.

Any graphical workaround for this type of scenario?

---------

Siddharth - HANA didn't propose a synonym(?) even thought MARC was used as a right table twice.

[Former Member]

If you are looking for the restriction criteria (using this model you are trying to do that) then  inner join is the only join that should be used for restriction...

But if you want addtional info from the MARC table based on join you can just use this table twice with different names and have right outer join on them in attribute view and use each MARC table for separate purposed...

Check the screenshot where I am having Z_Corporate_Parts more than once(Alias) in the same attribute view

[Former Member]

Durgamadhab, in your scenario you have only one source table. I have two source tables (AFKO & AUFK) joining MARC. I have to use a field from AUFK and a field from AFKO to join table MARC to eliminate duplicates.

If I try alias then it would look like below (diagram), but it's not working, it's giving duplicates. I did inner join and 1 to 1 cardinality.

I'm looking for a graphical work around.

[Former Member]

No it won't propose a synonym in this case. If you have a table field used in two different attribute views and you are using both this attribute views in an analytical view then it proposes a synonym. Anyways it should not concern you, yours is a totally different problem. Do you want data from MARC and are you using AUFK and AFKO to restrict the data in MARC. Or is it the other way around. Which table contains your key attributes i.e., which is your central table. The logic is not a bit clear to me.

[Former Member]

Agree with Siddharth,

You will need to define your key attribute properly based on your requirement to get the distinct records..

Basically the attribute view is like master data where you define a record based on a defined key attribute..

[Former Member]

Hey, here's a diagram. Basically MARC is receiving 1..many join from two tables. This is like a cross join/circular join....I don't know the term.

Basically data is flowing into MARC from 2 sources.

Let me know if the model below is consistent.

[Former Member]

THis appears to be a loop scenario to restrict data... You can not have MARC used as right table for both the joins..

[Former Member]

Durghamadab - I mean that a table cannot be identified as the right table on a join more than once. Basically a table can be a "right table" only once. But a table can be a "left table" many times. If you click on the join and look at properties you'll see left element and right element.

So basically, Table A cannot have a multiple joins coming in (this will make table A right table). It used to give error before at the calculation level but it's not anymore.

Nidhi - I don't have documentation but was getting an before, but now I'm now. I actually want to see documentation myself.

Siddharth - That scenario makes sense. But I'm concerned within Attribute View only. Can a table be a right table more than once? (it is receiving a join from many sources).

[Former Member]

This restriction is valid in any relation database even in Oracle as it will cause inconsistent results if you use it as right table for more than one ( which similar to full outer join)...

So this restriction will still be there in HANA

[Former Member]

All the key characteristics should come from a single table. There is nothing exclusively written about number of  right tables per se, as far as I remember.

[Former Member]

Siddharth Harsh,

Just to clarify in any RDBMS let alone HANA for a query you can have as many right tables but a single table can not be used as right table more than once..

[Former Member]

Not even by using synonyms?

[Former Member]

Synonyms will not cause any issue as database will treat them as separate table internally..

[Former Member]

So we can have a "work around" where we can use the same table as right table more than once; infact the moment you add a table as right table more than once, the HANA studio proposes the synonyms automatically. So I don't think that this should be an issue. I can only remember the two points i.e. having a table only once in a join path and all Key characteristics coming from a single table that is mentioned in the documentation which can have an impact in this scenario.

[Former Member]

There is no rule restricting multiple tables/joins ; however you can not have the same table twice in a join path.

An example would be, say you are using table "X" in an attribute view. Now you use this attribute view in an anlaytical/calculation view whose fact table also contains table "X" such that you can trace out a single path between the two instances of Table "X" ; then this would result in error.

Please check if your scenario is violating this condition.

[Former Member]

I don't see any issue having data from 2 or more tables with more than one join in attribute view. In fact we are using attribute view with more than one join and more than 2 tables without any issues. I will suggest to check the calculation view definition of what is going wrong...

Unfortunately with SPS6 there is no way to create a filter on a dynamic date using now.This is very much needed now and I hope SAP includes this in SP7. As of now there is a dynamic filter concept that only work in script .. Please check the below link if that might help

http://scn.sap.com/community/developer-center/hana/blog/2013/07/01/using-dynamic-filters-in-sap-hana

[Former Member]

Thanks for your reply Durgamadhab.

But two joins coming in from two different tables caused an error before in the calculation view. Hence I'm concerned. 

The rule was that a table can be joined to many tables (going out), but a table cannot receive joins from multiple tables. (coming in)

Any documentation would be helpful to confirm it's allowed or not allowed. (maybe a recent patch allows it).

[Former Member]

I can not see the diagram.. please update with the diagram..

[Former Member]

I'm getting an error trying to attach it. But it's a simple diagram.

Table 1

Plant----------|

                       |             Table 3

                        ---------->Plant

                         ---------->Material

                        |

Table 2           |

Material--------|

Hope this helps...just a simple scenario.

[Former Member]

Can you explain what exactly you mean by going out vs coming in?

You can just paste the SQL of the scenario if you are facing issue with diagrams..

[Former Member]

Hi Zain,

Just out of curiosity, do you have any documentation of that rule?

Thanks,

Nidhi