Flatten Parent-Child Hierarchy into Level Hierarchy using HANA (2.0 & above) Hierarchy Functions in SQL

Knock knock! Anyone else also looking for handy illustrations of the hierarchy functions introduced with HANA 2.0? Well count me in then.

While trying hard not to write SQLs with recursive self joins to flatten a hierarchical data format presented in parent-child relationship, the hierarchy functions in HANA can be a saviour for sure. Let’s look into something easy to implement using pre-defined hierarchy functions available with HANA 2.0 & above.

As a starter, let’s assume we have a miniature article hierarchy structured as below:

Top 10 Insights from the openSAP course “A First Step Towards SAP HANA Query Optimization”

I’m hoping that the title starting with ‘A First Step…’ implies that we will be continuing the journey with them with a second more detailed course.

Here are my top 10 interesting insights from the course, I hope this will entice you to go do the course yourselves. Here is the link to the course:

https://open.sap.com/courses/hanasql1

Transpose Row-set into Column Name – Dynamic Table Creation in SAP HANA 2.0 SPS04

Introduction

During BI implementation of a project, there could be a reporting requirement where row data need to be transposed as column name. In various BI landscapes, usually data is ingested from sources on which data cleansing/massaging is performed and then data get loaded into table which feeds to reporting layer.

How to support both MySQL and SAP HANA during development phase

Have you ever experienced a project that require support both MySQL and SAP HANA during the development phase.

In this blog post, I’d like to share my experience of how to support MySQL and SAP HANA during the development phase.

If We want to develop applications that support both MySQL and SAP HANA at the same time. We have to find the difference between them.

Playing with HANA parameters

Note: This blog is the recreation of an old blog on an old profile as part of the process of pulling all my content together on a consolidated profile.

The purpose of this blog was to share two end to end examples of passing parameters within HANA and using placeholders in SELECT statements.

I have created two scenarios:

1. HANA Table –> Table Function (with parameters) –> Calculation View (with parameters)

2. HANA Table –> Calculation View (with parameters) –> Procedure (with parameters)

What is the difference CalcView versus SQL View?

Recently I was asked what the difference between a Hana Calculation View and a SQL View is. To provide reading material, I did a quick search via google and was shocked by the answers I found. Some wrong, some missing the point, many outdated.

How to pivot/unpivot in SAP HANA

Introduction

Currently there is no built-in pivot/unpivot function in HANA. In this blog you will find a workaround how to implement this in SQLScript.

Create Time-Related Tables and Generate Time Data in SAP HANA XSA Platform

Introduction

Time data is necessary almost in any kind of data manipulation process, as well as adding time-dimension to the calculation views. So, ways to go for the time-dimension has changed a bit with the release of SAP HANA XSA Platform. In this blog post, you’ll see how to create time-related tables if required, and generate time data through the steps on SAP WEB IDE.

Table Functions in SAP HANA – step by step guide

SAP HANA Table Functions

Recently I spoke to multiple developers working on SAP HANA Native system for developing reports. What really surprised me was the fact, that most of them are still using calculation views of a SQL Script type instead of Table Functions. What is more, some of them were even not aware that these type of views are deprecated and replaced by Table Functions. I also haven’t found any step by step tutorial describing when to create Table Functions, how to do that, what are the benefits and how to consume them in calculation views.

Execute SQL Statement in HANA Studio using Literals or Bind variables

There are two variants of SQL statement execution defined in SAP note 2000002 – FAQ: SAP HANA SQL Optimization. The difference is how the where condition is specified in SQL Statement.

Sometimes I prefer to call “Literals” as “Hard code”.

CDS Associations and Propagation with SQL in SAP HANA

As Part of HANA 1.0 SPS11, the New feature called CDS Associations implementation with the help of SQL instead having the dependency of CDS to create association among Database objects like tables, views.

Later as part of HANA 2.0 SPS00, an interesting new feature and an enhancement of the above one where the user is allowed to propagate the association while creating views.

Creating CDS/EDM views from RAW HANA SQL TABLES

If you need to create a CDS/EDM views from raw SQL HANA tables..here is a script. There seems to be no automatic utility in HANA studio.

The below assumes you know SQL and how to create a CDS/EDM view.

A definition of a SQL table, which need to be converted.

HANA Views & SQL – Technical Performance Optimization

BW on HANA can easily enable SQL data access for third party tools, using the “External SAP HANA View” flag to automatically generate Calculation Views out of BW objects. However, Consuming multidimensional BW scenarios through relational SQL paradigm can lead to quite some issues with performance. This blog is about a few basic technical tips, which help to enhance runtime performance. Tested with BW 7.5 on HANA SPS 10, 11 and 12.

Core Data Services [CDS] in SAP S/4 HANA

A data model represents framework of what relationships are in a database. Data models are the foundation of software development. They provide a standardized method for defining and formatting database contents consistently across systems, enabling different applications to share the same data.
Professional data modeling tools provide a way to generate SQL from a modeled ER diagram although some developers often prefer writing SQL directly. Below is a look of a data model.
​

SQL Clients and SAP HANA 2.0

So recently I’ve been playing around a lot with our “Server Only” version of the SAP HANA, express edition (HXE). Now with that server only version I am mainly focused on just using more of the Database features than anything else. Trying SQL and working with the PAL libraries.

With our activities and ability to quickly load HXE into the Google Cloud Platform I also thought how else could I speed of some of my time. I mean I am only working with SQL on some of these things so could I use the JDBC driver and find a tool that would do nicely for it?

I’ve now tried a good half a dozen or so and decided that the current one I will play with the next few weeks would be DBeaver.

Paddling Upstream: How to find all HANA views that use a particular table/column

Requirement:

Find all HANA views that use a particular table/column.

If a field is dropped/modified from a table, identify the upstream model impacts.

Problem Faced:

Our team’s analytics was based on tables from multiple source systems.

There were instances where fields were being dropped from source tables as a part of continuous changes.

Using Synonyms in SAP HANA

In this blog post I try to explain what synonyms are and how they work in HANA SQL, but most of it will apply to other databases as well.

Use Case, Users and Schemas

Lets consider the following use case:

• One user (SYN_PROV) creates/owns DB objects like tables, and provides access to its objects via synonyms. That could be e.g. a replicated ERP Schema.

The Parent Child hierarchy in HANA

There are two types of hierarchies supported in HANA:

• Parent-Child hierarchy
• Level hierarchy

In this document, I would like to take the readers through the Parent child hierarchy: to create a very simple Parent Child hierarchy and to use it in a reporting tool that supports multidimensional reporting, like A-Office.

SAP HANA: Using SAX Parser for Loading XML response from outbound HTTP into table

In this blog , I would like to share my thoughts around using XS destinations for internet connectivity and capturing the XML response and loading the same into HANA with the help of SAX XML Parser.

Scenario :

We shall take the familiar Google Maps API and let us see how can we connect from SAP HANA and capture the XML response. We would need to define xshttpdest for outbound connectivity.

New Hierarchy SQL enablement with Calculation Views in SAP HANA 1.0 SPS 10

SQL enabled hierarchies with SAP HANA Calculation Views

Modeling SAP HANA Calculation Views is the key approach to successfully exploit the power of the SAP HANA Platform and leverage key SAP HANA capabilities. With SAP HANA SPS 10 (find enhancement overview here), calculation views provide a deeper integration of hierarchy objects and their exposure for usage within SQL. By leveraging the SQL integration of hierarchy objects, hierarchy-based filters, aggregations and hierarchy-driven analytic privileges are enabled.