Real-time reporting solution combining Geo-spatial and Financial data

In this blog post, I describe the steps I followed to create a real-time reporting solution combining Geo spatial and Financial data. The data is federated from SAP S/4HANA®  Flexible Real Estate Management (RE-FX), SAP Geographical Enablement Framework (SAP Geo Framework) and consumed using Esri ArcGIS (Pro or Insights).

This was an iterative process between the Data provisioning and Data consumption.

Components

◉ SAP S/4HANA Flexible Real Estate Management

◉ SAP Geographical Enablement Framework

◉ Esri ArcGIS Pro and ArcGIS Insights

Simplified Architecture

I performed the data provisioning and data modelling using SAP HANA Studio.

Data Provisioning

The core data used by this project are the real estate objects from RE-FX:

◉ Business Entity (table VIBDBE)

◉ Land (table VIBDPR)

◉ Building (table VIBDBU)

◉ Rental Object (table VIBDRO)

In this implementation of RE-FX, a Business entity is a Project and multiple Land, Building and Rental units are associated with it.

Smart Data Access

In this system, cross tenant access was disabled for security reason, so we used instead Smart Data Access (SDA). I defined a remote source from the SAP HANA GFO tenant pointing the SAP HANA S4O tenant. Then I defined virtual tables pointing either to RE-FX data (SAPABAP1 schema) or the SAP Geo Framework data (SAP_GEF schema). The technical user for the connection only needed read access to the SAPABAP1 and SAP_GEF schemas, therefore preventing any data modification.

Smart Data Access

CREATE VIRTUAL TABLE SDE.VT_S4O_VIBDBE AT S4O.S4O.SAPABAP1.VIBDBE;

CREATE VIRTUAL TABLE SDE.VT_S4O_VIBDBU AT S4O.S4O.SAPABAP1.VIBDBU;

…

CREATE VIRTUAL TABLE SDE.VT_SAP_GEF_GEOMETRIE AT REFX.S4O.SAP_GEF."z_gef_geom_28992.polygon";

The shapes of interest were stored in the table z_gef_geom_28992.polygon but there are other tables with different shapes.

Data Modelling

Once the virtual tables were created, I started the modelling.

Keeping in mind that the final objective was to expose jointly the combine financial and Geospatial data to be consumed by ArcGIS, I created multiple calculation views to break down the development cycle in manageable steps.

I started by creating the generic calculation views, then a set of views specific to a business object (Project, Land, Building, Rental unit). This set is composed of the following

◉ A foundation view for an object type

◉ A view for retrieving the shape from SAP Geo Framework

◉ A view to retrieve detailed information about the business object

◉ A consolidation view that exposes all the previous

Here, I describe the steps for the business object Building, the process is the same for the other objects (Land, Rental Object,…)

So, for the object Building, we have the following Calculation views:

◉ STATUS: Read the Building Status. I developed it to replicate (partially) – in SAP HANA – the ABAP Function Module STATUS_READ. It enables you to read the status of the business object. This a generic calculation view (ie not specific to the Business object Building).

◉ BUILDING_FOUNDATION: Provide the Building associated to a project

◉ BUILDING_DETAILS: Retrieve the detailed information about a building

◉ BUILDING_GEF: Retrieve the building shapes from SAP Geo Framework

◉ BUILDING: Associated the Building shape and the detailed information. This is the view that will be consumed by ESRI.

Object Status

The Object Status in S/4HANA is retrieved by using the function module STATUS_READ. I partially replicated the logic in SAP HANA.

To build the STATUS calculation view:

1. Start from the JEST table (Individual Object Status) filtering on the Active Object (INACT not X)

2. Join on OBJNR to Table JSTO to get the Status Profile (STSMA).

3. Join on STSMA, STAT to Table TJ30 STSMA, ESTAT to get all Status Changes.

4. Join on STSMA & ESTAT values from TJ30 to table TJ30T to get Status Description.

5. Read TXT04 (Status Code) and TXT30 (Status Description

Calculated View to read the Object Status

Object Foundation

The BUILDING_FOUNDATION calculated view return all the building associated to a project. We used the VIBDBE Business Entity table (here a project) and the VIBDBU table (Building)

1. Start from the VIBDBE table

2. Join on MANDT, BUKRS (Company Code) and SWENR (Business Entity) to Table VIBDBU

3. Read INTRENO (Internal Key of Real Estate Object), OBJNR (Object Number), XGETXT (Building Description), and TXT30 (Status Description)

Building Foundation

Building Details

The BUILDING_DETAILS calculated view provides the Status of the Building and their addresses. We used the STATUS calculations view previously defined as well as the BUILDING FOUNDATION. VIBDBE Business Entity table (here a project) and the VIBDBU table (Building)

1. Start from the VZOBBJECT table (Object Data – Virtual Object)

2. Join MANDT, ADROBJNR (Internal Key for Address Objects) to MANDT, INTRENO from the BUILDING_FOUNDATION view, and read ADRNR (address Number)

3. Join MANDT, ADRNR to CLIENT, ADDRNUMBER from the table ADRC (Addresses) and read the HOUSE_NUM1 (House Number), POST_CODE1 (Post Code), CITY1 (City) and

4. Join MANDT, OBJNR from the view STATUS and retrieve the Status information

Building details

Retrieve the Geometries

The Building and Land have geometries associated. This information is stored under the SAP Geo Framework Schema (SAP_GEF). I created a Calculated view with the following Logic to read the shape of a Building.

Each shape stored in the SAP_GEF_GEOMETRIE table has an Id (GEF_OBJECTID) and an Object number (GEF_OBJKEY). The table VICAINTRENO provides the assignment between the Internal Key of Real Estate Object (INTRENO) (like Building, Land,…) and the Object number (OBJNR). 

1. Start from the Building table (VIBDBU) or in our case the Calculated view BUILDING_FOUNDATION built on top of it

2. Join on INTRENO to Table VICAINTRENO to get the Object Number (OBJNR).

3. Join OBJNR to GEF_OBJKEY from the table SAP_GEF_GEOMETRIE filtered on the active geometries (Year of GEF_DATE_TO is 9999).

4. Read GEF_SHAPE i.e. the shape of the building

Retrieving Building Geometries

Currently, ArcGIS doesn’t support BIGINT so we used a calculated column to convert the OBJECTID (Key for the shapes) into an INTEGER, so we could consume the information in ArcGIS.

Calculated Column

One consolidated view

The final step is to join the information from the BUILD_DETAILS and BUILDING_GEF views, and expose the needed data.

Consolidated view

Consumption in ArcGIS

Synonyms

All Calculation view created in Hana Studio contain by default “/” in their name. Currently the consumption of object with “/” in their name from Esri generates an error. As a workaround I defined a synonym for the Calculated views in SAP HANA Studio and then a View in ArcGIS.

 

CREATE SYNONYM  S_BUILDING_GEO FOR "_SYS_BIC"."sde/BUILDING_GEO"

 

ArcGIS Feature Class

From ArcGIS Pro using the “Create Database View” functionality I created a view in SAP HANA on top of the previously defined synonym. You could define the view directly in SAP HANA but doing this way save you from having to list manually all the columns and use a * instead.

Create Database View

Enterprise Geodatabase Feature Class

This view is defined as a feature class and is used for reporting.

Reporting

Base on the previous view, we were able to represent the asset and to display financial information as well.

Reporting example

It is also possible to extend the use of these views by processing the spatial data in SAP HANA. For example, we combined multiple shapes into one using the SAP Hana native aggregation methods.

CREATE VIEW "SDE"."VW_PROJECT_BOUNDERIES" ( "PROJECT",

"SHAPE" ) AS select

SWENR AS PROJECT,

ST_ConvexHullAggr(GEF_SHAPE) AS SHAPE

from "SDE"."S_BUILDING_GEO"

where BUKRS = '1000'

Group by SWENR

ORDER BY SWENR WITH READ ONLY

Multiple Shapes using SAP HANA aggregation