How to Create and Use Script-Based Calculation Views in SAP HANA

SAP HANA is a powerful in-memory database platform that enables you to perform complex calculations and analyses on large volumes of data. One of the features of SAP HANA is the calculation view, which is a type of SAP HANA view that can be used to model complex logic and calculations on SAP HANA database.

Calculation views can be created and used in two ways: graphical or script-based. Graphical calculation views are created and edited using graphical nodes (such as Projection, Join, Union, Aggregation, etc.) or SQL script statements (such as SELECT, FROM, WHERE, GROUP BY, etc.) in the SAP HANA studio or the SAP Web IDE. Script-based calculation views are created and edited using SQL script statements only in the SAP HANA studio.

In this blog post, you will learn how to create and use script-based calculation views in SAP HANA, what are the benefits and challenges of doing so, and what are some examples of script-based calculation views for different scenarios.

Why create and use script-based calculation views?

Script-based calculation views have some advantages over graphical calculation views, such as:

They can leverage the full power and features of SQLScript language, such as user-defined functions, procedures, table types, variables, expressions, etc.
They can use advanced SQL functions and features that are not available or supported in graphical calculation views, such as window functions, predictive functions, R-Lang integration, etc.
They can provide more flexibility and control over the logic and calculations of the calculation view.

However, script-based calculation views also have some limitations and challenges, such as:

They require sufficient knowledge and skills of SQLScript language and SQL syntax and semantics.
They are less intuitive and user-friendly than graphical calculation views, especially for complex scenarios.
They are harder to maintain and debug than graphical calculation views.

Therefore, you should create and use script-based calculation views only when the existing graphical calculation views or other types of views are not sufficient or efficient for your data modeling or reporting needs. You should also consider the complexity and readability of your script-based calculation views when creating them.

How to create and use script-based calculation views?

You can create and use script-based calculation views using the SAP HANA studio. The steps are as follows:

Launch SAP HANA studio. In SAP HANA System view, expand the content node.
In the navigation pane, select a package where you want to create the new calculation view.
In the context menu of the package, select New > Calculation View.
Provide a name and a description for the calculation view. You can also choose a subtype (such as Cube or Dimension) for the calculation view.
In the Type dropdown list, select SQL Script.
Click Create.
The calculation view is created in the inactive version and is displayed in the editor. You can design your calculation view using SQL script statements only in the editor. You can also define input parameters and variables for your calculation view.
To activate the calculation view, right-click on the calculation view name and choose Activate.

How to use script-based calculation views in different scenarios?

Script-based calculation views can be used in different scenarios in SAP HANA system, such as:

As sources for reporting tools: You can use script-based calculation views as sources for reporting tools (such as SAP Analytics Cloud, SAP BusinessObjects BI Platform, SAP Lumira, etc.) to consume and visualize data from SAP HANA database.
As sources for SAP HANA native applications: You can use script-based calculation views as sources for SAP HANA native applications (such as SAP HANA XS applications, SAP Fiori applications, etc.) to access and process data from SAP HANA database.
As sources for other views: You can use script-based calculation views as sources for other types of views (such as graphical calculation views or CDS views) to implement complex logic and calculations on SAP HANA database.

Here are some examples of script-based calculation views for different scenarios:

Example 1: Script-based calculation view using window functions

In this example, a script-based calculation view is used to calculate the running total and ranking of sales amount by product category using window functions.

The SQL script code for this example is:

FUNCTION "SYSTEM"."CALC_VIEW_EXAMPLE_1" ( )
RETURNS TABLE (
    "CATEGORY" VARCHAR(100),
    "SALES_AMOUNT" DECIMAL(15,2),
    "RUNNING_TOTAL" DECIMAL(15,2),
    "RANK" INTEGER
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
    -- Define a table variable to store the sales data by category
    sales_data = SELECT "CATEGORY", SUM("SALES_AMOUNT") AS "SALES_AMOUNT"
                 FROM "SYSTEM"."SALES_DATA"
                 GROUP BY "CATEGORY";

    -- Define the output table variable to store the result of the calculation view
    var_out = SELECT "CATEGORY", "SALES_AMOUNT",
              -- Use SUM window function to calculate the running total of sales amount by category
              SUM("SALES_AMOUNT") OVER (ORDER BY "SALES_AMOUNT" DESC) AS "RUNNING_TOTAL",
              -- Use RANK window function to calculate the ranking of sales amount by category
              RANK() OVER (ORDER BY "SALES_AMOUNT" DESC) AS "RANK"
              FROM :sales_data;

    -- Return the output table variable
    RETURN :var_out;
END;

FUNCTION "SYSTEM"."CALC_VIEW_EXAMPLE_1" ( )

RETURNS TABLE (

"CATEGORY" VARCHAR(100),

"SALES_AMOUNT" DECIMAL(15,2),

"RUNNING_TOTAL" DECIMAL(15,2),

"RANK" INTEGER

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

BEGIN

-- Define a table variable to store the sales data by category

sales_data = SELECT "CATEGORY", SUM("SALES_AMOUNT") AS "SALES_AMOUNT"

FROM "SYSTEM"."SALES_DATA"

GROUP BY "CATEGORY";

-- Define the output table variable to store the result of the calculation view

var_out = SELECT "CATEGORY", "SALES_AMOUNT",

-- Use SUM window function to calculate the running total of sales amount by category

SUM("SALES_AMOUNT") OVER (ORDER BY "SALES_AMOUNT" DESC) AS "RUNNING_TOTAL",

-- Use RANK window function to calculate the ranking of sales amount by category

RANK() OVER (ORDER BY "SALES_AMOUNT" DESC) AS "RANK"

FROM :sales_data;

-- Return the output table variable

RETURN :var_out;

END;

Example 2: Script-based calculation view using predictive functions

In this example, a script-based calculation view is used to apply a linear regression model to predict the sales amount by product category using predictive functions.

The SQL script code for this example is:

FUNCTION "SYSTEM"."CALC_VIEW_EXAMPLE_2" ( )
RETURNS TABLE (
    "CATEGORY" VARCHAR(100),
    "ACTUAL_SALES" DECIMAL(15,2),
    "PREDICTED_SALES" DECIMAL(15,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
    -- Define a table variable to store the sales data by category and month
    sales_data = SELECT "CATEGORY", "MONTH", SUM("SALES_AMOUNT") AS "SALES_AMOUNT"
                 FROM "SYSTEM"."SALES_DATA"
                 GROUP BY "CATEGORY", "MONTH";

    -- Define a table variable to store the result of the linear regression model by category
    model_result = SELECT PAL_LINEAR_REGRESSION("CATEGORY", PAL_NUMERIC_ARRAY_AGG("MONTH"), PAL_NUMERIC_ARRAY_AGG("SALES_AMOUNT"))
                   FROM :sales_data
                   GROUP BY "CATEGORY";

    -- Define the output table variable to store the result of the calculation view
    var_out = SELECT A."CATEGORY", A."ACTUAL_SALES",
              -- Use PAL_PREDICT_LINEAR_REGRESSION function to predict the sales amount by category for the next month
              PAL_PREDICT_LINEAR_REGRESSION(B."CATEGORY", B."COEFFICIENTS", 13) AS "PREDICTED_SALES"
              FROM (SELECT "CATEGORY", SUM("SALES_AMOUNT") AS "ACTUAL_SALES"
                    FROM :sales_data
                    GROUP BY "CATEGORY") AS A
              INNER JOIN :model_result AS B ON A."CATEGORY" = B."CATEGORY";

    -- Return the output table variable
    RETURN :var_out;
END;

FUNCTION "SYSTEM"."CALC_VIEW_EXAMPLE_2" ( )

RETURNS TABLE (

"CATEGORY" VARCHAR(100),

"ACTUAL_SALES" DECIMAL(15,2),

"PREDICTED_SALES" DECIMAL(15,2)

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

BEGIN

-- Define a table variable to store the sales data by category and month

sales_data = SELECT "CATEGORY", "MONTH", SUM("SALES_AMOUNT") AS "SALES_AMOUNT"

FROM "SYSTEM"."SALES_DATA"

GROUP BY "CATEGORY", "MONTH";

-- Define a table variable to store the result of the linear regression model by category

model_result = SELECT PAL_LINEAR_REGRESSION("CATEGORY", PAL_NUMERIC_ARRAY_AGG("MONTH"), PAL_NUMERIC_ARRAY_AGG("SALES_AMOUNT"))

FROM :sales_data

GROUP BY "CATEGORY";

-- Define the output table variable to store the result of the calculation view

var_out = SELECT A."CATEGORY", A."ACTUAL_SALES",

-- Use PAL_PREDICT_LINEAR_REGRESSION function to predict the sales amount by category for the next month

PAL_PREDICT_LINEAR_REGRESSION(B."CATEGORY", B."COEFFICIENTS", 13) AS "PREDICTED_SALES"

FROM (SELECT "CATEGORY", SUM("SALES_AMOUNT") AS "ACTUAL_SALES"

FROM :sales_data

GROUP BY "CATEGORY") AS A

INNER JOIN :model_result AS B ON A."CATEGORY" = B."CATEGORY";

-- Return the output table variable

RETURN :var_out;

END;

Conclusion

In this blog post, you have learned how to create and use script-based calculation views in SAP HANA, what are the benefits and challenges of doing so, and what are some examples of script-based calculation views for different scenarios. Script-based calculation views are created and edited using SQL script statements only in the SAP HANA studio.

Disclaimer: This content is generated by AI.