Calculation View INTERSECT and MINUS nodes

SAP HANA Calculation Views are Information View Models which are created graphically using tables as data sources. We can use Projections, Aggregations, Unions, Joins, Rank nodes for different functionality.

But when the user wants to get the data from two tables where only matching records should be the output of the view (i.e. INTERSECT) or when only not-matching records from left table (i.e. MINUS) user should use different combination of nodes mentioned above. It will be difficult to build such a view for the user and will be time consuming also.

In HANA Calculation View Model to achieve INTERSECT and MINUS set operations new nodes (like Aggregation, Projection etc ) have been provided with HANA 2.0 SP01. These options can be used in only XSA WebIDE Modeler.

What is INTERSECT and MINUS set operations?

For example, consider two tables as below.

FRUITS table

COLOUR table

INTESECT operation with FRUITS table and COLOUR table will give result as the values present in both the tables.

MINUS operation with FRUITS table and COLOUR table will give result as the values present only in FRUITS table (case 1). Similarly, MINUS operation with COLOUR table and FRUITS table will give result as the values present only in COLOUR table (case 2).

Case 1                                                                                   Case 2

How it can be achieved with Calculation View model?

With HANA 2.0 SP01, it is very easy to perform INTERSECT and MINUS operations. New nodes to perform these operations are provided with this version, available only with XSA WebIDE Modeling.

The same tables mentioned in below example has been created in the MTA project in XSA as CDS Entities and populated with same set of data.

Now let us create Calculation View for INTERSECT operation

Here I am creating a DIMENSION calculation view

In the created calculation view, a new node for INTERSECT operation can be seen

Add the INTERSECT node in calculation view, add FRUITS and COLOURS table in INTERSECT node.

Map all the columns to output from both the tables. Add the columns to output till the Semantics node. Build the Calculation View successfully. Right Click on the view and click on “Data Preview” option. Data preview of the view will be,

Similarly, we create Calculation View for MINUS operation. MINUS operation with COLOUR table and FRUITS case 2 as mentioned above

Data preview of the view will be,

With these two new options now user can achieve INTERSECT and MINUS set operations in Calculation View model very easily.