cancel
Showing results for 
Search instead for 
Did you mean: 

Look Up Work Around Needed in Attribute View

Former Member
0 Kudos

I have the following 2 tables in attribute view:

Table 1

BUKRS

WERKS

Table 2

BUKRS

KOSTL

Lookup Table

WERKS (primary key)

KOSTL (primary key)

TEXT

If I join table 1 to lookup table using werks and table 2 to the lookup table using KOSTL and run data preview in attribute view it works fine. I get TXT displayed. But, when I use this attribute view in analytic View I get an error. Basically, I logically can't have joins coming from 2 separate tables.

The only option is to use the lookup table at calculation level but that is causing my to keep joining these little tables there which doesn't seem like a good practice. How can I get TXT field from the lookup table without getting any error when I join this attribute view in analytic view?

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Mic,

You haven't said what field you are using to join your fact/data foundation back to your attribute view in your analytic view. I am assuming its on bukrs.

To replicate the issue i created these tables in my hana system. Within my attribute view joined table1 to lookup table and table2 to lookup table. Added bukrs from table1 as a key attribute and rest of the fields including TXT as attributes. Activated and data preview worked fine.

In the analytic view, joined my fact table with attribute view on the bukrs field. Activated and data preview worked fine.

Now, not sure if I replicated your scenario exactly here. If not, please let me know what I missed.

I am on revision 37.

Thanks,

Anooj

Former Member
0 Kudos

Hey Anooj, thanks for the reply.

The above was an example I created to simplify the logic.

I'm using tables AFKO and AUFK in my attribute view. Joining them together using AUFNR (order number).

AFKO has FEVOR

AUFK has WERKS

I'm using look up table T024F. It has keys WERKS and FEVOR. I'm joining both fields to my attribute view. Activation and Data preview is fine in Attribute view, but when I link this in analytic view I get an error.

IThe data foundation is table AFVC. I'm linking my attribute view to data foundation using AUFPL (rougint number).

I get the following error message:

Error: The table "DATA_SLT_EQ100"."AFKO" (alias: <unset>) is contained twice in a join path. I.e. the table is contained in the analytic view and in the attribute view. This is only allowed if the logical join starts from table "DATA_SLT_EQ100"."AFKO" (alias: <unset>) in the analytic view and ends at table "DATA_SLT_EQ100"."AFKO" (alias: <unset>) in the attribute view. But currently there is another table in between. Therefore the model cannot be deployed.

Attached is the snapshot of the attribute view and error.

Former Member
0 Kudos

If your lookup table has values for all combination of WERKS and FEVOR, then within your attribute view can you just have a join from AFKO and AUFK to T024F. Do not join AFKO and AUFK. The order number from one of the tables (AFKO or AUFK) can be added as a key attribute. Can you test if this gives you the results you want?

Thanks,

Anooj

Former Member
0 Kudos

Hey Anooj,

I tried your approach and it's working but is giving me duplicate order even though I made AUFNR a primary key in the table AFKO, deleted the join between AFKO and AUFK.

Plus, my concern would be performance also. When I did a select count (*) for this attribute view without joining AFKO and AUFK it took forever (even though data preview runtime wasn't effected).

Any other ideas? I hear about aliases...anyway it can be used somehow?

Former Member
0 Kudos

Hi Mic,

Suspected the data wouldn't be right - apologies. Can't think of another way of doing it all with just Analytic-Attribute views. Hopefully someone else in the forum can think of one.

Thanks,

Anooj

Former Member
0 Kudos

No problem. Your creative idea actually worked in getting the Text field, just cause duplicates.

Thanks again.

henrique_pinto
Active Contributor
0 Kudos

Given the tests that were already done, I suppose the best option would be to create a materialized table as a join of AUFK and AFKO (e.g. with WERKS and FEVOR for each MANDT/AUFNR) and then join this one with the lookup table.

Former Member
0 Kudos

Hi Henrique,

You aware of a way this new "join table" that you have proposed above can get populated within HANA with delta records loaded into their base tables (AUFK & AFKO) going foward? I can think of the following:

a. If there is data services, a data flow can be created there and can be scheduled to run regularly

b. A stored procedure in HANA to do the same but scheduled to run regularly from outside HANA as there is no native scheduling capability in HANA as yet (is that correct?)

Would be great to hear other ideas especially if there is a way to do this easily within HANA itself.

Not sure why a database view cannot be used within attribute or analytic views. If that was possible then it could have been a easy solution to this requirement here.

Thanks,

Anooj

Former Member
0 Kudos

Hi Mic

   I thought of one work around ,but it is not a complete solution as it has some manual task needs to be performed when ever there is new data in underlying tables.I am thinking at one approach like this.

Step1. Careate an Attributeview A1  which is Join on tables AUFK and T024F based on MANDT,WERKS

Step 2. Create an Attributeview A2 which is join on tables AFKO and T024F based on MANDT,FEVOR

After these two steps you would have two column views create two tables each like A1 and A2 and get the data from Attribute views into physical tables,this steps needs manual intervention as when ever there is a new data you need to delete the data in teh tables and fill them with new data.

Step 3. Create an attributeview  A3 based on both tables which are replicas of A1  and A2.join them based on MANDT ,FEVOR

Step 4.Now use Attribute view A3 in your Analyticl view to join with AFVC.

but infuture if a columnviews  are permitted in Attributeviews, we can overcome the limitations I believe.

Otherwise if we are using BODS or any ETL tool for replicating , you can get the FEVOR and WERKS in a single table by using complex transfirmations. which will adress the root cause of the problem.

Thanks

Santosh Varada