Different Types of SAP HANA Modeling Views
There are three types of views in data modeling in SAP HANA: attribute views, analytic views, and calculation views. Understanding how each of these diﬀerent views provides additional value on the raw data in the database will help you ﬁgure out how to model your data for maximum ﬂexibility and performance.
- Attribute views provide descriptive data about the characteristics of data in your database. This is “master data” that deﬁnes things like hierarchies that describe relationships between data elements. By constructing attribute views, you create dimensions from which subsequent views can be constructed.
- 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 ﬂy in memory. Analytic views construct a central master “fact table” with key ﬁgures. You can use expressions, operators, and functions to analyze this data.
- Calculation views provide a way to do ﬂexible, complex logic in the database. They are built on top of one or more analytic or attribute views and allow you 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
Views and ELT versus ETL
In order to work with data more eﬀectively, Accounting may need a diﬀerent 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 diﬀerent 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 ﬁlters, which pull out just the data you need before handing it oﬀ to a complex calculation.
Analytic views operate on key ﬁgures 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 ﬂy. Because these aggregates are computed very rapidly, you can explore a much broader set of queries than if you had to decide up front which aggregation approach you would be using and commit those records to disk.
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 oﬀer a level of programming ﬂexibility 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 ﬂy rather than being stored on disk and updated periodically.
To obtain 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