Analytic View with calculated attributes

[Former Member]

Hello.

I'm trying to create an analytic view.

I have the following 3 tables:

INOB:

CUOBJ          OBJEK

100               1G1          G01

101               2D2          D01

102               3K3          K01

103               1G1          G02

MSKA:

MATNR          CHARG          KALAB

1G1               G01               3

3K3                 K01               1

MCHB:

MATNR          CHARG          CLABS

2D2               D01               8

1G1               G02               6

I am interested in a following result set:

CUOBJ          MATNR          CHARG          KALAB          CLABS

100               1G1               G01                       3                     ?

101               2D2                 D01                   ?                    8

102               3K3               K01                         1                ?

103               1G1               G02                     ?                    6

As you can see the field OBJEK (of the table INOB) is a combination of the fields MATNR and CHARG.I thought of creating an attribute view from the table INOB with two calculated attributes (MATNR and CHARG). So I can easily join in the next step (analytic View) the other two tables with the attribute view (join operates should be MATNR and CHARG). The problem ist that it is not possible to join in an analytic view with an calculated attribute as far as i see. Why? Is there another possibility. I don't want to use a calculation view in this example....

[Former Member]

The best way to do this is to use a GENERATED ALWAYS statement. You can join on these and they are awesome!

ALTER TABLE MSKA ADD (OBJEK CHAR(100) GENERATED ALWAYS AS concat(MATNR, CHARG));

Or you can do substr to do the other way around and join on both MATNR and CHARG.

May have the syntax slightly off, I'm not in front of a HANA system.

John

[Former Member]

Vitalij,

There are two important things to note.

1. All calculated attributes are actually calculated in a calculation view that is generated at runtime. This is as good as you manulaly creating a calculation view on top of an analytic view and defining these calculated attributes in that calculation view.

2. It seems to me that analytic view ultimately is a map between source and target fields. Each individual element in the semantic layer of the analytic view is mapped either directly or through series of joins to a source field in one of the underlying tables. The joins have to be simple as well. You cannot join a table by mapping  fields from two left side tables to fields in one right side  table.  The analytic engine traverses these paths and brings the data out. We cannot design any analytic view that requireds an intermediate calculation or logic or alternate paths.

-Ramana Krothapalli

[henrique_pinto]

Hi Vitalij,

just for the sake of testing ;-), I tried to create the model as you had requested, to verify the error you were getting. And you were right, I could not join an Attribute View with the fact table of an Analytic View through Calculated Attributes.

I'm not sure what's the reason for that. Maybe the joins are processed before calculations? I don't know...

So, what I did instead to achieve your desired output was the following:

1. I created the attribute view as mentioned (2 attributes out of INOB + 2 calc attributes, MATNR & CHARG);
2. I created two Analytical Views out of MSKA & MCHB, pretty straightforward, no joins, just the two attributes and the measure from each table;
3. I then created a calc view with:
   1. a Union between the two ANs (used automap)
   2. a Join between the Union result and the AT (Join on MATNR and CHARG attributes)
   3. Output with 3 attributes (CUOBJ, MATNR, CHARG) and two Measures (KALAB, CLABS)

It gave me the desired output.

Now, some comments:

1. This is definitely not the optimal scenario. Joins are usually not very performatic on Calc Views, the best results are usually achieved by Joining tables on Analytic View level, as you had tried to. For several million records of data, it might get a bit (to say the least) slow...
2. Another option you could have is to materialize both MATNR and CHARG columns in the INOB table. Just add two columsn to the table and fill them with the values read out of OBJEK. Should be pretty simple and you will then be able to perform the joins on AN level. A simple code like this should suffice:

   ALTER TABLE "TEST"."INOB" ADD (MATNR VARCHAR(10), CHARG VARCHAR(10));
   UPDATE "TEST"."INOB" SET "MATNR" = SUBSTRING("OBJEK", 1, 3), "CHARG" = SUBSTRING("OBJEK", 4);

3. If you use SLT or Data Services for replicating data from ERP to HANA, the logic of these two new materialized columns could be incorporated in the replication jobs, hence when new data is inserted on INOB, it would already have the MATNR & CHARG columns properly populated;
4. Lastly, I'd use 0's instead of nulls in the empty measures in the Union, so that you can properly aggregate if necessary. On the calc view's Union Step, this is pretty simple, just right click on both measures in the Union target mapping, go to option "Manage Mapping" and instead of having the flag "isNull" for the empty sources, write '0' (without the quotes) in the "Constant Value" column.

Best regards,

Henrique.

[Former Member]

Thank you for your comments.

Actually I did it as you said (joining the tables inside the calculation view). But the performance is really bad. I have several millions of records. I think I will try your idea with the materialized columns.

Thank you very much!

[henrique_pinto]

Just so I understand: OBJEK is a concatenation of both of these values, is that it?

With these spaces in the middle??? It's kinda strange...

Also, this is how it looks after your extraction, in ERP it isn't really like this, is it?

Anyway, here are a couple of comments:

a) what you're trying to achieve between MSKA and MCHB is not a Join, but a Union. Notice that the number of rows in the output is not equal the sources, but their sum. So, each row in the source tables becomes one row in the output.

I'd suggest going directly to a calc view (you could create AN for both tables, if you wish).

b) I was able to achieve what're you trying to do with a simple SQL Query on top of the tables directly:

SELECT A."CUOBJ", B."MATNR", B."CHARG", B."KALAB", B."CLABS"
FROM "TEST"."INOB" A
INNER JOIN
(SELECT "MATNR", "CHARG", "KALAB", null AS "CLABS" FROM "TEST"."MSKA"
UNION ALL
SELECT "MATNR", "CHARG", null AS "KALAB", "CLABS" FROM "TEST"."MCHB") B
ON B."MATNR" = SUBSTRING(A."OBJEK", 1, 3) AND B."CHARG" = SUBSTRING(A."OBJEK", 4);

(in my case, "INOB"."OBJEK" was a mere concatenation of both values, without the blankspaces in the middle, but you could adapt easily according to how the data is in your table)

If you really wish to, I suppose you could create Analytical Views for MSKA & MCHB, Union them in a calc and then Join with INOB.

Or even with an Attribute View out of INOB. If you were having trouble joining them graphically, you could always use SQLSCript.

Best regards,

Henrique.