Importance of Views in SAP HANA Studio
In ECC, data is stored in tables with foreign key relationships. This data needs to be analyzed based on the end user requirements. For this purpose, Business warehouse used to store data in star schema format and transformations were defined to modify the data.
However, this prevented the user to see the real time data and increased complexity of the data model, led to data redundancy and there was no access to real time data.
These limitations are not there in SAP HANA, where the transformations can be performed on the fly. For this purpose, views are created which are joins on multiple tables along with calculated columns which serve the purpose of read access on the transaction tables.
SQL Views and Column Views
There are views that can be defined in every database system. These are called as SQL views and are also available in SAP HANA. However, the performance and functionality of these views are limited. SQL Views can be created using the ‘Create View’ statement.
Some functionality like currency conversion cannot be done in SQL Views, these can only be done via column views which use the engines while performing a query.
The prerequisite for using column views is that all the base tables should be columnar tables.
In SAP HANA Studio, both the SQL Views and the Column Views are visible in the database catalog.
Using Open SQL, we can use simple operations like summation and existence checks. In real business scenario however, the key figures are much more complex including currencies and unit of measures.
These play an important role in conversions. Time stamps are also very important like fiscal year and calendar year.
These operations cannot be dealt with standard SQL. This is where SAP HANA has its greatest advantage.
The integrated engines provide reusable functions tailored for business processes which are utilized by column views. The data from these views can be accessed using standard SQL.
Thus, normal database views can also be defined as column views to enhance its functionality.
Following types of column views are available in SAP HANA;
- Attribute views – These are created as master data views which can also have derived columns and hierarchies.
- Analytical views – These have attribute views as dimensions and a fact table, all connected via joins to create a star schema type of structure. Analytical views can have restricted and calculated key figures. These can also be used for currency conversions and unit conversions.
- Calculation views – These are used to combine views along with data operations like union, rank and aggregation. SQLScript based calculation views are also used to add more flexibility.
These views can be externally accessed to display the data like for example via Microsoft Excel.
Keep looking for the next posts for more details on views.
To learn more, go for complete course in SAP HANA Modeling.