Using derived attribute view in SAP HANA
With regards to HANA modeling, freshers often have a doubt regarding why do we have the ‘derived attirbute view’.
To answer this question, let us see a simple example.
— 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|
Now as you can see, we have 2 fields having date (order and payment).
Here the requirement is such that we have to also see the ‘day’ on which these dates fall (As in Sunday, Monday…).
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:
But in our example , we have two date columns and we need to derive the day for both the columns.
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….
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):
— Let us see this process step by step:
1) Create Base view:
Here I have created a custom table with Day and Day Name.
and connected it to the M_TIME_DIMENSION table to to create attribute view that derives day for a given day number.
(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:
As you can see above, while creating attribute view I have used Derived as the subtype. The view is derived from the Baseview 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:
4) Save and Activate the Analytic view. See the data preview:
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.
Thanks for taking your time out and reading this document.
I hope this simple example helped you understand the application of Derived attribute views.
Hoping for your feedback and pointers…