Using derived attribute view in SAP HANA

Using derived attribute view in SAP HANA

Let us assume a simple business example.
Below is a Sales Data Table : We have Sales Doc no, value,customer, date of Order and Payment(YYYYMMDD format).

Sales Doc No Net Value Cust.no Date of Order Date of Payment
0001 150 C1 20000105 20000111
0002 458 C2 20120602 20120615
0003 563 C3 20040925 20040927
0004 855 C4 20140318 20140319

Now as you can see, we have 2 fields having date (order and payment).

How do we do this in analytic view?????….

Had it been just a single date column, we could have simply created an attribute view connecting the a custom day table with M_TIME_DIMENSION_TABLE to derive the day for one column. Something like this:

Using derived attribute view in SAP HANA

But in our example , we have two date columns and we need to derive the day for both the columns.

Using derived attribute view in SAP HANA

As you can see above, we can see that date 2 is also supposed to be connected to some attribute view containing day data like the left one.
(Please note: We cannot use a given attribute view twice in the same analytic view)
Here, we can
a) Create another attribute view to generate same day data….
OR
b) Clone of the 1st attribute view and use it (derived attribute view) – A better option

Hence we create a Derived attribute view ( Attribute view 2) from the base attribute view ( Attribute view 1):

Using derived attribute view in SAP HANA

Let us see this process step by step:

1) Create Base view:

Here I have created a custom table with Day and Day Name.

Using derived attribute view in SAP HANA

and connected it to the M_TIME_DIMENSION table to to create attribute view that derives day for a given day number.

Using derived attribute view in SAP HANA

(Here you can also hide Day_of_week field):
This completes my base attribute view design where I have the date and the day fields selected.

2) Create a derived attribute view with reference to the above base view:

Using derived attribute view in SAP HANA

As you can see above, while creating attribute view I have used Derived as the subtype. The view is derived from the Base view as seen in the option.
(Please ignore the naming convention).
a)The derived attribute view will be similar in all the characterietics as the base view(except the name)
b) We will not be able to edit any semantic/data foundation design in the derived attribute view.
c) In case these is any change in the base view, the changes will be reflected in the derived attribute view too.This helps reduce extra efforts of replicating the changes in the derived view.

3) Consume both the attribute views in the Analytic view to create the required data:

Using derived attribute view in SAP HANA

4) Save and Activate the Analytic view. See the data preview:

Using derived attribute view in SAP HANA

As you can see above, we are now able to see the day for both Payment and Order dates.
Hence, we can use derived attribute views to replicate base attribute views for such requirements.

Leave a Reply

Your email address will not be published.