This is Part Two of a blog series on Ariba Analytics using SAP Analytics Cloud, Data Intelligence Cloud and HANA DocStore. If you would like to start with Part One, please click here

Recap

SAP Analytics Cloud makes it easy for businesses to understand their data through its stories, dashboards and analytical applications. Our worked example is using SAP Ariba Data to create an SAC Story that lets you know how much spend has been approved within Ariba Requisitions created in the last thirty days

A simple SAC Story tracking Approved Requisitions

In Part One of our blog series we discussed how we can retrieve data from SAP Ariba’s APIs using SAP Data Intelligence Cloud. We stored this data as JSON Documents in the SAP HANA Document Store

In this blog post, we’re going to build SQL and Calculation Views on top of our JSON Document Collection

In the third (and final) blog post we’ll consume that Calculation View in SAP Analytics Cloud as a Live Data Model, which will provide the data to our SAP Analytics Cloud Story

Viewing our HANA DocStore Collection data in an SAP Analytics Cloud Story

Design-Time Artifacts in Business Application Studio

As we discussed in our last blog post, objects within HANA usually have both a design-time and runtime artifact. Design-time artifacts are useful because they fully describe the object and can be deployed consistently across multiple HDI Containers or even HANA instances

When we deploy our design-time artifacts, they will be created as runtime artifacts inside our HDI Container

Our JSON Document Collection has already been created, and is already storing our Ariba JSON Documents. From here, it’s time to model our other artifacts

Creating our SQL View

JSON Documents are useful in a variety of situations where you don’t have strict, predefined schemas. When we retrieve our data from the Ariba APIs, we may retrieve data that doesn’t map cleanly to a table schema (for example, data that is nested). Putting this data in the HANA DocStore Collection allows us to store the complete document, ensuring nothing is lost

In order for us to use this data for analytics, we’ll need to map it to some sort of schema. We can create a logical schema on top of our Collection using a SQL View. This allows us to access a predefined subset of our data for analytics while leaving the full data untouched in our Collection

We’ll create the SQL View in Business Application Studio

Click on View, then Find Command or press Ctrl+Shift+P

Use Find Command to find Create SAP HANA Database Artifact, then click on it

Select SQL View as the artifact type, and enter the artifact name then click on Create

SQL Views use the following format:

VIEW "aribaRequisitionSQLView"
AS SELECT "UniqueName", "Name", [...]
FROM "aribaRequisition"

If you’re familiar with SQL, you may recognise this as the same syntax that you would use to create a standard SQL View, just missing the word “CREATE”

The SQL View doesn’t duplicate any data, just provides a schema that we can use to access the underlying data

Our data in JSON Documents are Key-Value pairs

"Status":"Complete"

To retrieve the value “Complete”, we would SELECT “Status”

JSON Documents may also have nested data

"Content":{"ItemId":"3728754507"}

To retrieve the value “3728754507”, we would SELECT “Content”.”ItemId”, with the full stop marking nested keys

Our example will use the following SQL View:

VIEW "aribaRequisitionSQLView"
AS SELECT "UniqueName", "Name", 
"TotalCost"."AmountInReportingCurrency" AS "AmountInReportingCurrency", 
"ReportingCurrency"."UniqueName" AS "ReportingCurrency", 
"ApprovedState", "Preparer"."UniqueName" AS "Preparer",
"Requester"."UniqueName" AS "Requester", "StatusString", 
"CreateDate", "SubmitDate", "ApprovedDate", "LastModified", 
"ProcurementUnit"."UniqueName" AS "ProcurementUnit"
FROM "aribaRequisition"

The fields we’re using are only a fraction of the fields available in the Documents within our Collection – if we want to customize the scenario later, there are plenty more to choose from

We want to make sure this SQL View is deployed and ready for use, so click on the Deploy rocket

We can deploy our SQL View under SAP HANA Projects on the left

Creating our Calculation View

While we’re in Business Application Studio, we’re going to create our Calculation View. This Calculation View is what we’ll be consuming in SAP Analytics Cloud

As before, we’re using View->Find Command then Create SAP HANA Database Artifact

Choose Calculation View, enter a Name then click on Create

Business Application Studio has an inbuilt editor for Calculation Views, which we’ll use to create ours

Click on Aggregation, then click the Plus symbol

Search for our SQL View, select it, then click Finish

Now that our SQL View is available as a Data Source, we want to make sure its columns end up in our Calculation View

Click on Aggregation, then click on Expand Details

Click on our SQL View on the left then drag and drop to Output Columns on the right

Our SQL view columns will now be available in our Calculation View

Because this is a Calculation View of type Cube (rather than Dimension), we’ll need to make sure it includes at least one Measure

The columns in our SQL View all have the default data type NVARCHAR(5000). If we try to mark this column as a Measure directly, it will treat it as a string – giving us the Aggregation options COUNT, MIN and MAX

We want to treat this column as the number it is – as a workaround, we’ll need to create a Calculated Column

Creating our Calculated Column

A Calculated Columnis an output column that we create within the Calculation View itself. Rather than being persisted, the values are calculated at runtime based on the result of an expression

For our example, we’re using a very simple expression. First, we have to make our way to the Expression Editor

Click on Calculated Columns

Create a Calculated Column using the Plus symbol, then Calculated Column

Click on the Arrow

Next we’re going to give our Calculated Column a name and data type. Because the granularity of our example is the Requisition-level and not the item-level, the decimal points won’t meaningfully change the results. Given that, we’re going to use the Integer data type

Give the Calculated Column a Name, and choose the Data Type Integer

Choose Measure as the Column Type

Click on Expression Editor

The Expression Editor is where we’ll define how the column is calculated. Select our AmountInReportingCurrency Column

Select our Column from the left

Our Column is in the Expression

Our Created Column will take the value of AmountInReportingCurrency and convert it to an Integer

Now we want to validate the syntax of our Expression

Click on Validate Syntax

Our Expression is valid

We have one last thing to do inside our Calculation View – we want to filter the data to only include Approved Requisitions. If we want to use the Value Help to set our Filter, we’ll need to Deploy the Calculation View

Deploy our Calculation View

Click on Filter Expression

Click on ApprovedState under Columns

Add an Equals Sign (=) then click on the Value Help

Select Approved then click OK

Now we can check the syntax of our Filter

Click on Validate Syntax

Our Filter is valid

Before we Deploy our Calculation View, we want to make sure that we’re only sending our integer Calculated Column and not the string version. To do this, we go the Semantics Node

Click on Semantics, then Columns

Check Hidden for our AmountInReportingCurrency Column to exclude it from our Calculation View

All of the Columns we need, including our new Calculated Column are available within the Calculation View. Now we’re ready to Deploy it one last time

Once again, click on the Deploy Rocket under SAP HANA Projects

Checking our Runtime Artifacts

Now that we’ve finished deploying our Design-time artifacts, we’ll have the corresponding Runtime artifacts inside of our HDI Container.  We can check these by going to SAP HANA Database Explorer from within Business Application Studio

Click on Open HDI Container on the left under SAP HANA Projects

In the Database Explorer, we want to first check on our SQL View

Click Views on the left, then click on our SQL View

Our SQL View

We can see all of the Columns in our created SQL View. If we want to check out some of the data returned by our SQL View, we can click on Open Data

Click on Open Data

Data from our SQL View is displayed

Next it’s time to check on our Calculation View

Click Column Views on the left, then click on our Calculation View

Our Calculation View

Click on Open Data

Database Explorer will open our Calculation View for Analysis. We’re going to do our analysis in SAP Analytics Cloud, so for now we just want to verify the Raw Data

Click on Raw Data

Data from our Calculation View is displayed

Wrap-Up

During this blog post we’ve built a SQL View and Calculation View on top of our HANA DocStore Collection. We’ve also made sure that our Calculation View only contains Approved Requisitions

In the third and final blog post we’ll consume our Calculation View as a Live Data Model before visualizing it in an SAP Analytics Cloud Story. We’ll also schedule the Data Intelligence Pipeline we created in our first blog post so that the data in our HANA DocStore Collection is updated on a regular basis automatically

Other Resources

SQL Views

Calculation Views

SAP HANA Database Modeling Guide for SAP Business Application Studio

Special Thanks

This blog series has had a lot of input from my colleagues – any errors are mine not theirs. In particular, thanks go to the Cross Product Management – SAP HANA Database & Analytics team, Antonio Maradiaga, Bengt Mertens, Andrei Tipoe, Melanie de Wit and Shabana Samsudheen

Note: While I am an employee of SAP, any views/thoughts are my own, and do not necessarily reflect those of my employer