Monday, 8 July 2019

Improve the quality of your data with SAP Data Hub

Data drives the business processes we all rely upon, yet in many organizations poor quality data causes inefficiencies that could be avoided. SAP Data Hub includes various data quality operators that allow data pipelines to be created that can improve the quality of data in motion, or at rest.

In Data Hub 2.5.1 the set of data quality operators includes anonymization, data masking, location services (address cleansing, geocoding, and reverse geocoding), and validation.

This article shows how to use the validation operator to apply a data quality rule and to trigger a business process for data that fails the rule. It demonstrates how to use the SAP HANA Monitor and Validation Rule operators, tracing data using the Wiretap and Terminal operators, and extending a blank JavaScript operator.

Creating the data source

We will use a HANA table as the data source for this example, although the data could be read from anywhere that Data Hub supports, which includes databases, cloud storage, streams, applications and APIs.

To create our source HANA table, we executed the following DDL via HANA Studio.

CREATE COLUMN TABLE SVCROPRODUCT.JOURNEY (
ID INT PRIMARY KEY,
SOURCE NVARCHAR(10),
CUSTOMER NVARCHAR(30),
TIME_START TIMESTAMP,
TIME_END TIMESTAMP,
DISTANCE INT
);

In this example, new rows inserted into this table will be checked to ensure that the journey start time is before the end time, and if it is not, start a remediation process to correct the data.

Configure a connection from Data Hub to HANA


To logon to the Data Hub Launch Pad, you will require your tenant name, username and password.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Once logged in, the Launch Pad displays tiles for the tenant applications.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

In this demonstration we will use Connection Management to configure a connection to the source HANA system.  While this is not necessary as connections can be configured in the modeler, connections created via Connection Management are reusable in the modeler and can also be used in the Metadata Explorer to browse and catalog metadata, and to view and profile data.

Click on the Connection Management tile to open the application, then click Create.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Enter your HANA system connection information.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Once saved, click the ellipsis against the new connection and select Check Status to ensure Data Hub can connect.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Creating the Graph

Switch back to the Launch Pad and click on the Modeler tile to launch the Modeler.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Click the + (in the top left) to create a new graph.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

A new graph will be created, and rather conveniently the operators tab is selected.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Operators can be added to a graph by double-clicking on them or dragging them into the graph editor. If you know the operator name you can use the search box. Locate the SAP HANA Monitor operator and add it to the graph.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

The HANA Monitor operator continuously captures new data inserted into a table. It works by creating a trigger on the source table which inserts data into a shadow table, and polling the shadow table, we’ll look at those later.

Open the operator configuration panel by clicking on the Open Configuration button.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

We must configure the connection, schema name, table columns and table name. We can reuse the connection we created in the Configuration Management application. Click on the Connection property to open the property editor.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Select Configuration Manager as the Configuration Type and the Connection ID of the previously created connection.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Enter the schema name and table name of the source table.  Table columns is a comma separated list of column name and data type of the source table columns you want to monitor, so for the source table we created we will use

ID INT,SOURCE NVARCHAR(10),CUSTOMER NVARCHAR(30),TIME_START TIMESTAMP, TIME_END TIMESTAMP,DISTANCE INT

The completed monitor configuration.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

A great feature of Data Hub is that you can execute a graph before it is complete, this allows for incremental development and instant gratification! Save the graph using the Save button on the Editor toolbar.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

You must provide a name, a Description is optional, Category allows graphs to be grouped in the navigation pane.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Now that the graph is saved, it can be run using the run button on the editor toolbar.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

The bottom pane shows that the graph is running.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Switching to HANA Studio, we can see that a shadow table and a trigger have been created to capture new rows. These are created and deleted by the operator during startup and shutdown of the graph.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

If we now inserted new data into the source table, the trigger would copy it to the shadow table and the graph would process the new data. However, as our graph is very simple we have no way of seeing what’s happening, that’s not gratifying at all!

Tracing and Debugging


When testing and debugging graphs it is often useful to trace the data output by operators. There are 2 operators that can be used for this, Wiretap and Terminal. We’ll use Wiretap to display the data output by the HANA Monitor operator.

Add the Wiretap operator to the graph and connect the output port of the HANA Monitor to the input port of the Wiretap.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Save and run the graph. Once the graph is running another option appears for the wiretap instance, an Open UI button.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Click on the Open UI button, a new browser window will open.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Back in HANA Studio, we insert a new row into the source table.

INSERT INTO JOURNEY VALUES (1, 'openCAB', 'Michael', 
TO_TIMESTAMP('18-02-2019 10:00:00', 'DD-MM-YYYY HH24:MI:SS'),
TO_TIMESTAMP('18-02-2019 11:00:00', 'DD-MM-YYYY HH24:MI:SS'), 10);

The wiretap window now shows the inserted data, gratification at last!

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

The data output is

[{"CUSTOMER":"Michael","DISTANCE":10,"ID":1,"SOURCE":"openCAB","TIME_END":"2019-02-18 11:00:00","TIME_START":"2019-02-18 10:00:00"}]

The {} notation shows that the operator output format is JSON, and the [] hints that multiple objects may be output. Let’s insert multiple rows to see what happens.

INSERT INTO JOURNEY VALUES (2, 'openCAB', 'Matt', 
TO_TIMESTAMP('18-02-2019 10:12:00', 'DD-MM-YYYY HH24:MI:SS'),
TO_TIMESTAMP('18-02-2019 11:11:00', 'DD-MM-YYYY HH24:MI:SS'), 10);
INSERT INTO JOURNEY VALUES (3, 'openCAB', 'Tyler', 
TO_TIMESTAMP('18-02-2019 12:00:00', 'DD-MM-YYYY HH24:MI:SS'),
TO_TIMESTAMP('18-02-2019 12:30:00', 'DD-MM-YYYY HH24:MI:SS'), 10);

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

The data output is

[{"CUSTOMER":"Matt","DISTANCE":10,"ID":2,"SOURCE":"openCAB","TIME_END":"2019-02-18 11:11:00","TIME_START":"2019-02-18 10:12:00"},{"CUSTOMER":"Tyler","DISTANCE":10,"ID":3,"SOURCE":"openCAB","TIME_END":"2019-02-18 12:30:00","TIME_START":"2019-02-18 12:00:00"}]

While this is not easily human readable, its an array of JSON objects, showing that the HANA Monitor will capture multiple rows during each poll and the output of the operator is a single message containing multiple rows.

Using JavaScript to format data


The JSON output from the HANA Monitor must be converted to CSV before being sent to the Validation Rule operator. The Format Converter operator can be used in some cases, but we’ll demonstrate manual conversion which would be required for complex formatting.

Stop the graph, then add a Blank JS Operator, we’ll use JavaScript, but we could use the Go or Python operators to format the data.

In general, operators are data processors. They accept data on input ports, process it, then produce data on their output ports. Operators can have multiple input and output ports, and ports support various data types. The delivered operators have predefined ports for their specific purpose, but when creating or extending an operator, we must configure its ports. In our example we’ll match the input port to the output port of the HANA Monitor, and use a string datatype for the output CSV, as this is the input port type on the Validation Rule operator.

Click on the Add Port button on the Blank JS Operator.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Add an input port called inMessage.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Add an output port called outString.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Click on the Script button to open the script editor.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Paste in the following JavaScript.

var csv = "";

$.setPortCallback("inMessage", onInput)

function onInput(ctx, s) {
    var b = s.Body;
    csv = "";
    
    b.forEach(arrFunc);
    
    $.outString(csv);
}

function arrFunc(item, index) {
    if (index > 0) 
        csv = csv + "\n";
        
    csv = csv + item.ID + ",\"" + item.SOURCE + "\",\"" + item.CUSTOMER + "\"," + 
        item.TIME_START + "," + item.TIME_END + "," + item.DISTANCE;
}

The script registers a function to be called when data arrives on the input port. The function formats the data and sends it to the output port.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

In the graph editor, edit the graph so that the output port of the HANA Monitor is connected to the input port of the JavaScript operator, and the wiretap is connected to the JavaScript output.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Save and run the graph, then insert some data into the source table. The wiretap output should now show that the JSON data has been formatted as CSV data.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Applying a Data Quality rule


The Validation Rule operator allows us to specify multiple rules that route data to pass or fail output ports. The rule we’ll apply is simple, a journey start time must be before the journey end time.

Stop the graph and remove the Wiretap operator.  Add a Validation Rule operator, and 2 Terminal operators. Connect the Validation operator to the output of the JavaScript operator, and the Terminals to the pass and fail output ports on the validation operator. An operator’s label can be edited to be more descriptive, so we’ll do that as well.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

In the validation operator configuration we must specify the structure of the incoming CSV data. This can be done using a form to enter each element or as JSON. The JSON to describe the input schema is shown below.

[
{
"name": "ID",
"type": "Integer"
},
{
"name": "SOURCE",
"type": "String",
"length": 10
},
{
"name": "CUSTOMER",
"type": "String",
"length": 30
},
{
"name": "TIME_START",
"type": "String",
"length": 20
},
{
"name": "TIME_END",
"type": "String",
"length": 20
},
{
"name": "DISTANCE",
"type": "Integer"
}
]

We can simply paste that into the property editor.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

The Rules property allows us to specify simple rules. The column names used in the rules are from the previous step, where we defined the input schema.

Our basic rule is defined as shown below. TIME_START < TIME_END.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Save and run the graph. Once the graph has started open the UI for the 2 terminals, they will open new browser windows.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Now let’s insert some data into the source table that will pass the rule. TIME_START is 10AM, and TIME_END 11AM, which satisfies our rule.

INSERT INTO JOURNEY VALUES (1, 'openCAB', 'Michael', 
TO_TIMESTAMP('18-02-2019 10:00:00', 'DD-MM-YYYY HH24:MI:SS'),
TO_TIMESTAMP('18-02-2019 11:00:00', 'DD-MM-YYYY HH24:MI:SS'), 10);

The terminal connected to the pass port shows the data.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

If we enter some data that fails the rule, for example if the start and end time are the same due to an upstream mapping error where the same data is mapped to both columns, the data is displayed in the terminal connected to the fail port.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

Great, our rule works and the failed data is captured.

Completing the graph


We want to send the failed data to a separate governance platform that can control the remediation process. The third party platform has a REST API that we can use to start the governance process, so we’ll use an HTTP Client operator to perform an HTTP POST of the failed data. The REST API requires the data to be in JSON format so we can use a Format Converter operator to convert the failed data CSV to JSON. The completed graph is shown below.

SAP HANA Study Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Tutorials and Materials

No comments:

Post a Comment