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…
Simple and Informative Sameer, Good Stuff. I will try a few more examples myself 🙂
And why wouldn't you make a calculation view - Dimension type, with 2 inner joins to the same BASE_VIEW ? Seems to me simpler, and more aligned with SAP latest recommandations on not using attribute views anymore.
Can you please explain with screenshots how we can do this using calculation view. Derived attribute views will change according to the base view.. This cannot be achieved through Calculation views.. My example is simply a hypothetical example.. there must be many real time examples where using derived attribute views will be most recommended 🙂 ..
We can achieve using graphical calculation view as well. I just tried.
Table Date: YDATE
1.Initially taken projection of YDATE and M_TIME_DIMENSION table.
2. Join1(DATE1<->DATE_SAP, get DAY_OF_WEEK) and Join2(DATE2<->DATE_SAP,get DAY_OF_WEEK).
3.Join3 just used as intersection (to avoid null if we use UNION)
4.Last node projection/aggregation.
5.We need to a tlest 1 column as measure(INTEGER,DOUBLE,BIGINT etc). In this case column 'HIDDEN' we have created.
Created calculated column to get weekday based on weekday no.
Yes for this specific example you can .. but again.. in case there are some changes you want to do in the base view.. you will have to do it in all the locations corresponding locations..in this case I understand its date and that will never change.. but there will be requirements where we need to make change in the base view and want that to replicate in all the places the base view has been used..
And also.. we never generally replicate the dates in another table that is not advised and not efficient either..we simply use the time_dimension tables for date references..
Perfect Explanations. Thanks to sharing this in details and step wise step.
Day_name is showing as a question mark when displayed in the atrribute view.Any idea as to why it is not getting displayed?
Join type selected is left outer join.Referential join doesn't show any data for attribute view.
Thanks for sharing the very useful information.