Learn the basic steps to set up a new flavor of SAP HANA information views, the star join graphical calculation view. Understand why it is a valuable new addition in the HANA Studio modeling tool chest.
Key Concept
A new graphical calculation view option offered in HANA Support Package 7, called star join, allows for easy modeling of a star schema. Although an analytic view previously allowed the creation of a star schema, it had limitations involving sourcing of measures from more than one fact table. These limitations are not an issue for the new star join graphical calculation view.
Star joins are a new option in your SAP HANA modeling tool chest. They overcome the constraints that limit your options when modeling complex analytical views with measures from more than one table. To illustrate my points with a real-world twist, I’m using a scenario revolving around the analysis of airline performance data.
I show how to create a star join calculation view using dimension calculation views and explain the pros and cons of this new method and the older analytic view/attribute view methods. Before I delve deeper into this new star-join concept in SAP HANA, however, first let me review the basics of the existing modeling method for complex star schemas in SAP HANA.
Note
This article enables an SAP HANA modeling professional to create new star join models in SAP HANA. I do not, however, provide the details needed for the basics of modeling in SAP HANA. For that, attend the HA100 or HA300 classes offered by SAP or find online e-learning courses on the web.
The Basics of SAP HANA Modeling
SAP HANA, like other more traditional disk-based databases, provides for the creation of database views. These views are run-time collections (joins and unions) of database tables. Unlike many databases, in addition to the pure coding option to create database views, SAP HANA has graphical tools (such as Microsoft Access) to make for a more user-friendly process for creating these views. When the SAP HANA graphical modeling toolset is used and views are activated, the system creates the needed code. In addition, these modeled content views are, in most cases, available for use with various reporting tools.
When modelling views in SAP HANA you end up with objects analogous to those in SAP BW, because, in fact, the process of building BW objects also builds views. Modeling in BW is easier than modeling in SAP HANA, but I think SAP HANA is more flexible. The best option is to have both toolsets available by running SAP BW on HANA. Then, in addition to the SAP BW modeling tools (which are in some ways better than HANA’s), SAP BW has a transformation engine and built-in automation tools for scheduling. (The BW362 class is about how to run SAP BW on HANA and how both toolsets work together. For many, the SAP BW reference helps them understand HANA modeling.) Table 1 highlights the analogous objects between SAP BW and HANA.
| BW object |
SAP HANA information view |
| InfoObject |
Attribute view |
| Cube (star schema) |
Analytic view or new star join calculation view |
| MultiProvider |
Calculation view |
| InfoSet |
Calculation view |
| Dimension table (as part of an InfoCube) |
New dimension calculation view |
Table 1
SAP BW versus SAP HANA analogous objects
Modeling Cubes: The Existing Solution
These new dimension and star join calculation views are the main topic of this article, but to get an appreciation for the advantage of this newer way for modeling a star schema in SAP HANA, you first need to understand the existing (the pre-Support Package 7) way of modeling a star schema.
The primary earlier modeling option for building a star schema was to first build attribute views, and then combine them with facts in an analytical view. Attribute views (not covered or shown in detail in this article) model master data by joining language-specific text and attribute tables (for example, a complete customer master with language specific text. Using one or more of these attribute views you can join them to a single table containing the measures (some refer to them as facts or key figures) of the business process you’re trying to model.
The single table constraint refers to the prohibition that forces the modeler from using more than one measure table in an analytic view. Unlike SAP BW’s fact table, the analogous foundation of an SAP HANA analytic view can include attributes in addition to the attributes contained in the attribute views. These are added later. However, to be clear, an SAP HANA analytic view’s foundation cannot contain measures coming from more than one table.
Figure 1 shows an example of an analytic view that highlights two tables in the foundation. Notice that the fields (highlighted with orange icons) that are included in the output only include measures from the table on the left. The airlines table used in the foundation does not provide measures to the model. Additionally, notice that the logical join node of the analytic view (not highlighted) is where the attribute views are joined to the foundation (facts) to make a star schema model.
Figure 1
Analytic view with two tables in the foundation
In SAP HANA before Support Package 7, if you wanted to combine measures from more than one table, you needed to use a calculation view. Figure 2 shows the graphical calculation view where the join depicts measures from more than one analytic view.
Figure 2
Two analytical views joined in a calculation view
One negative about the modeling of complex star schemas using attribute, analytic, and calculation views is the number of objects you are maintaining. Another is the fact that these views drive SAP HANA to use different software engines in the ultimate calculation. The system manages this processing but it is less efficient than having the logic done in just one engine. One slight advantage to this method is the clean reusability of the attribute views, but this might not be worth the tradeoff in speed.
The New Star Join Calculation View
The new way to model these complex star schemas is to use a combination of dimension calculation views and cube (with star join) calculation views, especially when measures are coming from more than one table. The use of the Graphical Calculation View modeling tool is basically the same as it was when building these types of views in the older release of SAP HANA. After the introduction of Support Package 7.0, however, there are more options.
The first step in creating a star schema with the newer star join calculation view is to create dimensions. Like a traditional star schema (but unlike the SAP BW version), the dimensions hold master data attributes such as customer master (city, state, and postal code) or material master data (size, color, and manufacturer code). Like the attribute views, these dimensions are reusable. However, in this case the only place you can consume dimension calculation views is within a star join node of a cube calculation view.
To build a dimension calculation view, you simply need to change the default option in the initial screen of the calculation view user interface (UI). The menu path needed to create the views is the normal one, starting from the context menu on your package: > New > Calculation view, which results in the screen shown in Figure 3.
Figure 3
Choose the Dimension Data Category option for the calculation view type
After accessing this build screen for calculation views, the magic begins. First, change the Data Category option from the Cube default to Dimension. This is because only calculation views with this option subsequently can be used to create the star join calculation view.
After choosing Dimension from the drop-down options, click the Finish button. Figure 4 shows the resulting normal looking UI. Here you build your dimension with attributes from one or more tables using joins and unions as you normally do in the UI of calculation views. This is shown in my example for airport data, including the extra table to hold the description of the country code where the airport is located.
Figure 4
The airport dimension with joined tables representing airport master data
The next step is to activate this dimension view. Most star schemas have more than one dimension, so my example (Figure 5) duplicates the process, but this time for the airlines dimension instead of airports.
Figure 5
The airlines dimension calculation view with the dimension data category
After you model all the dimensions of your star schema (representing the points on the star), you need to connect the points (dimensions) around the core of the star—the measures table(s).
To do this return again to the calculation view creation UI and this time choose a different, option. The Data Category defaults to Cube, but you need to tell the system you want to build a star joined cube. You do this by selecting the With Star Join check box, as shown in Figure 6.
Figure 6
Build the Air_Cube as a star joined cube
With this option checked, click the Finish button and you now get the defaulted node of the star join as shown in Figure 7. You need this later in a subsequent step, but for this step you need to model (in this case, join) tables that hold the measures (facts). Specifically, in this case, I used more than one table and I joined them together. In addition, to show that the star joined cube does not limit the sources of the measures (as does the analytic view, as discussed above) I grab measures from more than one table. The only difference here is the defaulted star join node that you are forced to use later. The join process for the objects is identical to that of other nodes you have used previously in the construction of calculation views.
Figure 7
A Join node of a star join calculation view (measures from more than one table)
After you finish the modeling for the sources of the measures, you need to assemble your star schema. First point your measures table collection (e.g., Join_1 in the Scenario section of Figure 7) to the star node. Then, in the traditional way (shown in Figure 8), add the dimensions to the star node. You should be aware that only the sources of the measures (in this case, the join) and the dimension calculation views can be joined in the star join node. After you pick the pre-existing airports and airlines dimensions, Figure 8 shows the join needed in the star join node. Make your selections for the output fields, and then move on to the final step.
Figure 8
Join dimensions with the measures in the star join node (along with the ability to preview the data via the context menu on the any node)
As with all views in SAP HANA, the last step is to define the semantics. After selecting the semantics node, you have the option to tell the system a revised field name and, more importantly, determine if the field should be treated as a measure or as an attribute. The semantics node is highlighted as shown in Figure 9.
Figure 9
Finish the calculation view by defining the semantics
Once you finish defining the semantics fields (Figure 9) you’re done modeling. To use your view containing all the semantics fields just activate it as you would normally and then deploy your view into the BusinessObjects Web Intelligence Reports, BusinessObjects Explorer, or the newer BusinessObjects Analysis, Design Studio, or SAP Lumira visualization tools.
Ned Falk
Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.
You may contact the author at ned.falk@sap.com.
If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.
