Create custom filters using SAP Analytics Cloud Analytics Designer and SAP HANA

Introduction

SAP Analytics Cloud’s story mode supports various filter concepts such as story filters, page filters, widget filters, input controls and linked analysis. SAC’s Analytic Application uses a different approach to implement filter capabilities in dashboards. In fact, the Analytics Designer (where Analytic Applications are created) is all about customization and flexibility.

SAC’s Analytics Designer provides filtering capabilities through programming APIs. A full documentation of SAC Analytics Designer APIs can be found here. This enables developers to create the desired filtering logic within a dashboard. Furthermore, with Analytics Designer APIs you can create your own filtering concepts! This blog post will guide you along one specific scenario to create such a custom filter.

In this blog post , we will showcase a custom filter to search through members of multiple dimensions and subsequently filter the Analytic Application. As a benefit, the end-user does not need to know the dimension in advance.

Approach

We will use an SAP HANA connection to create a calculation view providing the necessary filter characteristics. Afterwards, we will setup a data model in SAC. This allows us to use Analytics Designer components to fetch filter characteristics from our previously created calculation view.

Terminology

◉ SAC’s Analytics Designer provides an environment to create Analytic Applications. An event-based scripting logic with standard Analytics Designer APIs can be implemented within an Analytic Application. The Analytic Application can then be run by end-users.

◉ A member is a characteristic. In this blog post, every member (of a dimension) is a potential filter value and hence a filter characteristic.

◉ A calculation view a modeling object used by SAP HANA database modules for data preparation, data wrangling and data provisioning.

Step-by-Step Implementation

Step 1 – Fetch all required members

Firstly, we need to define which members should appear later in our custom filter. For that, we need to populate the required ‘filter characteristics’ using a calculation view in HANA. As we described in the introduction, we want to search members through multiple dimensions. Thus, we must create a calculation view delivering all dimensions and members to SAC. We decided to model through a .hdbfunction using the following SQL statement. As one can inspect, we return a table with two columns: ‘Dimension‘ and ‘Member‘. If your scenario requires a different data to be displayed by SAC’ Filter, you can implement different logic.

FUNCTION "blog.db::filter"( )

       RETURNS  TABLE

       (

        "DIMENSION" nvarchar(50),

        "MEMBER" nvarchar(100)

       )

       LANGUAGE SQLSCRIPT 

       SQL SECURITY INVOKER AS

BEGIN 

return 

select distinct 'CUSTOMER_NAME' as "DIMENSION", "CUSTOMER_NAME" as "MEMBER" FROM "blog.db::test_cds.CUSTOMERS"

union

select distinct 'PRODUCT_NAME' as "DIMENSION", "PRODUCT_NAME" as "MEMBER" FROM "blog.db::test_cds.PRODUCTS";

END;

Step 2 – Model HANA calculation view

In this step, we need to model a calculation view (which will later be consumed by SAC). The calculation has to be of type cube to be accessible and visible by SAC. The modeled calculation view mainly calls the previously build .hdbfunction of step one. The result can be inspected in the picture below.

To make it clear, the .hdbfunction populates the HANA calculation view with dimension names and their related dimension members. The column ‘DIMENSION‘ (green rectangle) delivers all dimension names and the column ‘MEMBER‘ (orange rectangle) delivers all members (of a specific dimension). The SAP HANA terminology would be: The attribute ‘DIMENSION‘ delivers all dimensions as members and the attribute ‘MEMBER‘ delivers all members of the corresponding dimension in attribute ‘DIMENSION‘.

Step 3 – Model a hierarchy in the calculation view

Afterwards, model a (level-based) hierarchy within the HANA calculation view. The dimension ‘DIMENSION‘ (green rectangle) must be one level above the dimension ‘MEMBER‘ (orange rectangle). This can be achieved by clicking on the semantics node of the calculation view and switch to tab ‘Hierarchies’. The result should look like the following picture.

Step 4 – Create an SAC data model

Now it’s time to switch to SAC. We want to consume our modeled calculation view of step three. Next to the usual SAC data model (which is used for general purpose BI and not covered by this blog post), create a second data model in SAC based on the modeled calculation view of step three. This can be done by navigating through Home ➝ + Create ➝ Model ➝ Get data from a data source ➝ SAP HANA. One should be able to see the attributes ‘DIMENSION‘, ‘MEMBER‘ and the hiearchy ‘SEARCH_HIERARCHY‘ of the modeled calculation view. Make sure to save the data model by clicking the save icon.

Step 5 – Create the Analytic Application

Create a (hidden) table widget. This table will later be used to fetch member information and create subsequent filters based on coding in Analytics Designer. As one can see, members and their corresponding dimensions appear in the ‘SEARCH_HIERARCHY‘ outline. We will use the hierarchy information to query which member belongs to which dimension. Using the information we can subsequently implement filtering logic in step six.

The remaining Analytic Application can be designed the way you want. The following picture shows a sample Analytic Application for the designed scenario. The brown rectangle shows the hidden table (which will not be visible to end-users). Additionally, we created a filter line widget (black rectangle) that uses the ‘SEARCH_HIERARCHY‘ to let end-users filter desired characteristics. The yellow rectangle showcases a basic chart. In this case, the chart uses the two dimensions ‘Product‘ and ‘Customer‘ split by a measure ‘Net Sales‘. (Note, this chart is based on the general purpose data model.)

Also, the filter line is connected to the hidden table in the Designer Panel – Once end-users filter anything using the filter line widget, the hidden table filters accordingly. This is important, because we will reuse this functionality in step six.

Step 6 – Program the filtering logic using JavaScript

Open the “onResultsChanged” event of the hidden table in the Analytics Designer. This allows us to perform custom filtering logic once the result set of the hidden table changes. This occurs once end-users filter anything using the filter line widget.

The programming code will parse the filter information of the hidden table and reuses the information to query the dimension-member relationship. This enables us to filter the chart displayed in the yellow rectangle. This concept can be handled as follows:

Firstly, we use API endpoints to fetch the selection the user performed using the filter line through the hidden table (Table_1). Afterwards, we query the member info for that specific member id. This will deliver us the parent id. The parent ID is the corresponding dimension of the selected member.

//fetch the filter of the hidden table. Table_1 is the hidden table of the analytic application

var filter = Table_1.getDataSource().getDimensionFilters("MEMBER")[0];

//cast the filter value to the corresponding object type

var member_id = cast(Type.SingleFilterValue, filter);

//create a selection object, this is needed to query member info

var selection = { 'SEARCH_HIERARCHY': member_id.value};

//query member info

var member_info = Table_1.getDataSource().getResultMember("SEARCH_HIERARCHY", selection);

//access member info - parentId is the dimension

var dim = member_info.parentId;

var member_name = member_info.id;

//Chart_1 is the chart in the yellow rectangle of the analytic application

Chart_1.getDataSource().setDimensionFilter(dim, member_name);

Step 7 – Enjoy the working scenario