There are three types of views in data modeling in SAP HANA: attribute views, analytic views, and calculation views. Understanding how each of these different views provides additional value to the raw data in the database will help you figure out how to model your data for maximum flexibility and performance.
1. Attribute views provide descriptive data about the characteristics of data in your database. This is “master data” that defines things like hierarchies that describe relationships between data elements. By constructing attribute views, you create dimensions from which subsequent views can be constructed.
2. Analytic views in SAP HANA are optimized for aggregating mass data. Because the database is so fast, it’s not necessary to store aggregates in the database; rather, you aggregate on the fly in memory. Analytic views construct a central master “fact table” with key figures. You can use expressions, operators, and functions to analyze this data.
3. Calculation views provide a way to do flexible, complex logic in the database. They are built on top of one or more analytic or attribute views and allow you to do calculations after aggregation and grouping. Calculations are generally done after grouping at the attribute level and after aggregation at the analytic level.
Figure 6-2 shows a summary of the three types of views in SAP HANA.
Figure 6-2. SAP HANA Modeling Views
In order to work with data more effectively, Accounting may need a different transformation on its data than Sales does. Traditionally transformation comes before loading data into the database (ETL). With HANA, it makes sense to load data, and then to do transformation (ELT). Because of the power of HANA, you can store (load) one version of the truth in the database and then use views to support transformations for different lines of business. This eliminates redundancy as well as the potential for data getting out of sync.
Suppose your transaction database has a customer ID that links to all the information about a customer who purchased a given product. By itself, the customer record allows you to compute simple measures, like total sales by customer.
Attribute views allow you to join relationships together that further describe the data that you’re working with. By joining your “customer ID” to data in your customer database, you can further subdivide and analyze sales data according to conditions and relationships which are not present in the original transaction. Because these joins run in memory and are not limited by disk speeds, you can explore even more complex relationships than you could in traditional database models and still maintain performance.
Figure 6-3. SAP HANA Attribute View
Using attribute views allows you to bring data analysis down to size before running complex calculations. In HANA, it makes sense to apply attribute views as filters, which pull out just the data you need before handing it off to a complex calculation.
Analytic views operate on key figures in your database. They are used to model data that includes measures and to compute operations based on those measures.
An SAP HANA database may have a very large number of records in it, corresponding to individual transactions. In a typical disk-based database, you’d compute and store a separate table for aggregated data, so that something like “total sales by day” would be updated periodically and stored separately on disk. In HANA, this is not necessary since the database is very fast and aggregates are best computed on the fly. Because these aggregates are computed very rapidly, you can explore a much broader set of queries than if you had to decide upfront which aggregation approach you would be using and commit those records to disk.
[Related Article: SAP HANA Interview Questions and Answers]
The end result of this for modeling is that the analytic views that you build on top of these measures are also key for reducing the amount of data passed along to subsequent views. Do the aggregation in your database before you pass that data up to the next level so that the computations happen at the right level. You’ll gain a lot of performance when the BI tool or the application can deal with data that’s already been aggregated rather than giving it a firehose dump of data to aggregate.
Figure 6-4. SAP HANA Analytic View
One of the exceptional features of SAP HANA is the ability to do calculation views in the database. These views offer a level of programming flexibility that goes beyond the aggregations found in the analytic views, and they can bring in data from multiple analytic and attribute views to express complex logic. Calculation views can have layers of calculation logic, can include measures sourced from multiple source tables, and can include advanced SQL logic, R code, and more.
Calculation views are visible within HANA as virtual tables, and applications and BI tools can access them in the same way that they consume other views. Like other views in HANA they are computed as needed, and intermediate values and aggregates are built on the fly rather than being stored on disk and updated periodically.
To obtain the best results, the modeler should use the full power of the attribute and analytic views before passing data into the calculation view. Calculations will work best with data that has already been reduced in size. If your database has a billion records in it, but you only need the calculations to run on a few thousands of them, you should build your models so that the aggregation and grouping has already occurred before your calculations run.
Figure 6-5. SAP HANA Calculation View
Explore SAP HANA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!
As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.