Views are integral part of data modeling in HANA. But what does View mean to a non-technical person?
In the language of relational databases, a View is a virtual table i.e., a table which actually does not store any data physically, but shows you data derived from one or more other tables.
Views allows logical cross-sections of data to be generated in response to the needs of specific applications, so that the required data can be directly and efficiently accessed.
What are the different types of Views in SAP HANA?
- Attribute View
- Analytic View
- Calculation View
Before we go into the nitty-gritty of these types of Views, let me throw a simple question. Why do we need these three types of views? Let your brain do some churning..
Have you heard the argument, from which side (USA or Canada) does the Niagara Fall look better? Oops, what relation does Niagara have with HANA Views..
Why do some folks visit Niagara from the US side while some take the trouble to go to Canada and why some visit it from both sides?
Answer is to ‘View’. Yes, View it from different angles. View it as per ones choice/ecstasy and perform certain activity along with the View.
If you want to get the gorgeous panorama of the American Falls along with the mighty Horseshoe Falls, then Canadian side of view should be your choice.
But, if you want a cheap parking area and opportunity to get close and personal with the waterfalls (including American and Bridal Veil Falls, Cave of the Winds), then American side is your bet.
If you want to spend some more buck, then you might want to be little more adventurous and take the ‘Maid of the Mist Boat’ and get the closer detailed View of the Fall, which you would not get by just standing near the edge of the Fall.
The ‘data’ is same i.e. ‘Niagara Fall’. But you view and appreciate it according to your need, accessibility, choice and preference.
Similarly, we use the Attribute, Analytic and Calculation Views, according to our need, requirement and what we want to do with the data and what we want to see, show and report.
Let’s get back to work after our excursion to Niagara
Attribute View:
If you want to see only text and numeric characters (not quantity/amount), then Attribute is your view. Usually in SAP, the material number or customer id or vendor id are present in one master table and their corresponding texts like name, address, contact information are there in different table. For example, material id is in MARA, while material description is in MAKT table. When you want to join these two master tables to display the dimensions (text attributes/characteristics) of the material number, Attribute Views are created.
Please do not confuse numeric characters to numbers like quantity, amount, currency value. Material/Vendor/Customer number may be ‘1000101’, but it is still character/text (NUMC). So, you can use them in Attribute view. Attribute view can also be made to view transactional data but it does not make sense to have an Attribute view of non quantifiable transactional fields.
Numbers are not for Attribute View.
Analytic View:
If you want to play with numbers, quantities and currencies, then Analytic View should be your choice. G/L amount 1,000,101.00$ is for Analytic View. PO quantity 10.00 EA of value 4500.00 $ is also for Analytic View.
According to SAP, Analytical views are the multidimensional views that analyze values from the single fact table (like sales, deliveries, accounting etc) which contains transactional data. In simple word, Analytic view is typically used for analyzing numerical data and figures.
But, numbers alone do not make sense. If the retails shop says, 143.00$ value was sold, it does not give any relevant info. But it they tell, 143.00$ worth of material id ‘PV10001’, material name ‘PVC Pipes 1 in’ were sold to customer id ‘900499’ customer name ‘SAPSPOT Groups’.
In the above hypothetical example, 143.00$ is the measure or fact or transactional data, while material id ‘PV10001’, material name ‘PVC Pipes 1 in’and customer id ‘900499’ and customer name ‘SAPSPOT Groups’ are dimensions, characteristics or attribute data.
Fact Table + Attribute Views and/or Tables = Analytic View
Again, Attribute View might contain material id and name. Similarly, vendor id and name can be another Attribute View.
So, we can safely say, Analytic view can be derived from a fact table along with single table or joined tables and attribute views.
Analytic views are highly optimized for aggregating mass data.
Calculation View:
If you are ready to go an extra mile and do some complicated and/or simple calculations/mathematics (usually custom), then Calculation View should be your area of play. Say, you want to calculate the net value of the PO (4500.00$) after giving a 10.00 percent discount, then you need to do some mathematics (4500 – (10/100*4500)) to get a figure of 4050.00$.
Calculation views can be referred as combination of tables, attributes views, analytical views and even other calculation views to deliver a complex business requirement. Calculative View can logically link two or more Analytic Views.
Usually, when the modeling requirement cannot be met using attribute view and analytical view, or when we need to use two or more analytical view and derive a resultant set, Calculation view comes into picture.
Just like attribute view has a limitation of using non-numeric data, similarly, one analytical view cannot consume another analytic view. When we have a complex need to use multiple analytic view, then Calculation view is the only respite.
In simple terms, Calculation View are view with SQL Script inside (with the calculation logic). It has Graphical & Script based editor.
Hope with the above explanations, you would be in a position to defend, why HANA has these three types of View.
SAP coined the Views very smartly using their literal meaning.
Attribute = Characteristic/Dimension/Trait => Master Data (Material/Customer/Vendor etc. Does not change very often)
Analytic = Fact/Measure/Quantity/Numbers => Transaction Data (Sales Orders/Purchase Orders/Delivery Quantity/Accounting Documents etc. Gets created and changed everyday. The data set grows at a faster rate)
Calculation = Mathematics/Derived Numbers => Custom Calculated Data (Find the net price after deducting the tax)
With the above fundamental, you might have some quality doubts and questions like below.
Q. Why should we link attribute view to transactional table (fact) to create Analytic view? What is the advantage? Why not just join tables directly to transactional table?
A. Yes, technically, we can join tables directly to fact tables. But creating Attribute view has more advantages and HANA adovcates modelling objects like attribute views instead of tables.
Reasons:
i) Reusability: Attribute views are reusable building blocks and would be useful in future developments. .
ii) Maintenance: Any change in the dimension or characteristic of the field in Attribute view would flow downstream to all developed objects and models. We do not need to change/update each and every development.
iii) Coherence : If we always use attribute view instead of adding the base tables, we can ensure that all our developments are coherent.
iv) Analysis: HANA does not have ‘where-used’ search to find the tables. But we can do the ‘where-used’ of HANA modeling objects. So, it is easier to find the views and then figure out the tables when we want to do some analysis or investigating something.
Q. Can there be calculative field/column in Analytical View or Attribute View?
A. Yes, we can. But any Attribute/Analytic View containing a calculation attribute would automatically become a Calculation View.
Q. Attribute Views do not store data, then how does it display the output?
A. When Attribute Views are called for output, the Join Engine takes care of processing the data and providing the output.
Q. Which engine is responsible for Analytic View?
A. OLAP (Online Analytical Processing) engine processes the Analytical View.
Q. How does Calculation View work?
A. Once the Calculation view is successfully generated, a column view is generated in _SYS_BIC Schema. This column view is available to HANA reporting tools.
Q. Is Calculation View directly available for reporting?
A. No. But, Calculation view can be made available for reporting, by enabling MultiDimensional Reporting under the Semantics section. Once it is enabled, the execution occurs using CE (Calculation Engine) functions in the Index Server at the database level.
Q. Between Calculation View and Analytic View, which view has better performance?
A. Analytic View. Calculation View is executed in CE (Calculation Engine) while Analytic View in OLAP. Calculation View is not as fast as an Analytical View.
Please note:
Analytic views with calculated attributes and Calculation views both run in Calculation Engine.
Analytic Views (without derived columns or calculated columns) use the OLAP Engine
Q. Views analogy with SAP BW terminology
A. Attribute View is like BW Dimension which can be reused throughout the system and not just one model.
Info Cubes or Info Sets in SAP BW are the closest cousins to Analytic View.
Q. Analytic view can have attribute view. So will the join engine of attribute would trigger or OLAP of analytic view?
A. During activation of the analytic views, the joins in the attribute views get ‘flattened’ and included in the analytic view run time object. Only the OLAP engine will be used thereafter.
The famous Engine diagram to conclude this post.
