Richtlinien und deren Prüfung

für HANA native Entwicklung (Views / SQLScript)

DSAG Technologietage

Stuttgart, 21. Februar 2018

Jonatan Skopek (MunichRe), Christian Pfaller (CQSE)

About Munich Re

21. Februar 2018 2DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

▪ Munich Re Group (MR, ERGO, MEAG, …) is one of the market leaders in reinsurance business

▪ Since April 1880

▪ Key figures (10yr average):

▪ Revenue: 44.5 bn €

▪ Profit: 2.7 bn €

▪ Key figures (2016):

▪ Employees: about 43.000

▪ Equity capital: 31.8 bn €

About Us

Business Informatics, Technikum Wien

Engineer - Industrial Engineering

Since 2017: Munich Re – IT Architect

Before: SAP BI Consultant and Developer

(BW, HANA, BODS, BO Frontends)

Master’s in Computer Science, TU München

PhD in Software Engineering (Testing)

Since 2011: CQSE GmbH

Munich Re: Software Quality Control

Static Code Analysis for ABAP, HANA

Integration in Teamscale

Jonatan Skopek Dr. Christian Pfaller

21. Februar 2018DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller 3

SAP HANA @ Munich Re

21. Februar 2018 4DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Pure data base replacement

Non-SAP DB

BW on HANA

HANA native

Sparq / Hadoop / Datahub

(PoCs)

Suite on HANA

Hybrid Development - BW on HANA / HANA Native

GKDMDTD

HANA

MD

vv

FS-RI

AfO/DS

UWPF MDTD

SDI

BWcombined data

logics calculator

interface logics

SLT

Software Quality – More than „it Works“

21. Februar 2018 6DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Outsourcing / Offshoring Watch out for Maintainability!

You can Detect a Lot in the Code

21. Februar 2018 7DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Maintainability

Performance

Security

Bugs

Munich Re TQE Process

Developers Application Continious Code

Analysis

Fast Personal Feedback

by continuous, automatic

code analysis

Tasks TQE

Erfergsdgo

Ewrggsdgo

Ewrggsdgo

Ewsdgo

Ewrggsdgo

Ewrgo

Ewrggsdgo

Ewrgfhgfdgo

Ewrggsdgo Detailed Improvements

Suggestions by »TQE«

Project / Service

Managers

Quality Report

Sdfg

Sdfg

Fg

Fggbbortrgrg

rtgrethtjgrhkui

Fthjghf

Tzjjjzr

trzjrtzjrjrthzhzt

Rtzhrtzjrtzj

Sdfg

Sdfg

Fg

Fggbbortrgrg

rtgrethtjgrhkui

Fthjghf

Tzjjjzr

trzjrtzjrjrthzhzt

Rtzhrtzj

Sdfg

SdfgFgFggbbrtz

htjtzzukkh

High Transparancy

»Features vs. Quality«

Decisions

Technical

Quality

Engineering

9

Established for

ABAP / C#

since 2011, aprox. 50 applications

HANA: New Development Technology

Sdf

g

Sdf

g

Fg

?HANA

Coding Guidelines

21. Februar 2018DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller 11

Guidelines HANA Views

▪ Readability

▪ Naming of Views

▪ Naming of Identifiers

▪ Robustness

▪ No Reserved Words

▪ Performance

▪ No Joins on Calculated Attributes

▪ Avoid Column Engine Expressions

▪ Architecture

▪ Ensure Layer Conformance

21. Februar 2018 12DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Performance: Avoid Column Engine

21. Februar 2018 14DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Performance: Avoid Joins on Calculated Attributes

21. Februar 2018 15DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Architecture Layers for Views

21. Februar 2018 16DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Guidelines HANA SQLScript

▪ Readability

▪ Avoid Nested Subqueries

▪ Nested Control Blocks

▪ Long Procedures

▪ Robustness

▪ Missing LANGUAGE definition

▪ Missing Quotes at Identifiers

▪ Performance

▪ Detect Imperative Control Statements

▪ Duplicated Code

21. Februar 2018 17DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Nested Subqueries in SQL Statements

21. Februar 2018 18DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Duplicated Code

21. Februar 2018 19DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Inconsistent Changes: Risk of Bugs

21. Februar 2018 20DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Scientific Study

• More than 100 bugs in production

• 52% of all unintended inconsistencies

Juergens, Deissenboeck et al: Do Code Clones Matter? ICSE 2009

critical visible to user not visible to user

▪ ABAP / C#

▪ Extend for

▪ SQLScript

▪ HANA Views (XML)

▪ Access HANA Development

Objects?

Needed: Automated Checks

21. Februar 2018 25DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

HANA

Coding Guidelines

How to Get the Code?

21. Februar 2018 26DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Continuous

Results

Incremental Analysis

HANACode Repository

?

How to Get the Code?

21. Februar 2018 27DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

pull

Code

Changes

Continuous

Results

Incremental Analysis

HANACode Repository

regi.exe

10 min

Changed

*.hdbprocedure (SQLSscript)

*.analyticview (XML)

*.attributeview (XML)

*.calculationview (XML)

Guidelines Evolve …

21. Februar 2018 28DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

HANA

Coding Guidelines

v0.1

HANA

Coding Guidelines

v0.2

HANA

Coding Guidelines

v1.0…

HANA

Coding Guidelines

v2.0…

Evolving Guidlines: Keep on with the Checks

21. Februar 2018 29DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

DECLARE

CURSOR cur …

Architecture Conformance

21. Februar 2018 30DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Views Inside:

Conform with Layer Architecture?

21. Februar 2018 31DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Modelling Architecture: Components

Modelling Intended Architecture – Dependencies

32

Modelling Intended Architecture – Dependencies

33

Showing Violations

21. Februar 2018 34

TQE Process,

Static Code Analysis:

Observed Effect

Sdfg

Sdfg

Fg

Amount of Code

(Lines of C#)

Num. of Findings

TQE Process

D. Steidl et al: Continuous Software Quality Control in Practice.

2014 IEEE International Conference on Software Maintenance and Evolution (ICSME’14)

Summary

▪ Important to define design guidelines

▪ Continuous update due to technology changes and growing experience

▪ Two major purposes for code quality checks:

▪ Self service tool for developers to fulfill design guidelines already during initial development

▪ Code quality reports of already implemented code

▪ Checks should be part of development process (quality gates, etc.)

▪ Automated checks beneficial especially in large development projects

21. Februar 2018 39DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller

Questions welcome!

Contacts / Further Reading

Jonatan Skopek, JSkopek@munichre.com, +49 89 3891-3569

Dr. Christian Pfaller, pfaller@cqse.eu, +49 176 10090694

Blog-Post on SQLScript Checks: https://www.cqse.eu/en/blog/sqlscript-analysis/

21. Februar 2018DSAG Technologietage - HANA Richtlinien und Checks, J. Skopek / Ch. Pfaller 40