Table Transpose in SAP HANA Modeling

Approach 1:

• Analytic view will be built on each base table column which needs transposition.
• In this case 6 columns need transposition, hence 6 Analytic views will be created.
• Calculated Column (VALUE) is created in each Analytic view which derives the value of a particular month in a year.
• Create Calculation View based on Analytic Views created above and join them together using Union with Constant Value.
• No need to create Calculated Column (MONTH) in each Analytic view as this can be derived in Calculation View to improve performance.

What is SAP HANA?

SAP HANA is an in-memory data platform that is deployable as an on-premise appliance, or in the cloud.  It is a revolutionary platform that’s best suited for performing real-time analytics, and developing and deploying real-time applications. At the core of this real-time data platform is the SAP HANA database which is fundamentally different than any other database engine in the market today (figure 1).

SAP HANA Modeling - Project Learnings and Tips

Key benefits of Data Modeling in SAP HANA:

• Building analytics and data mart solutions using SAP HANA enterprise data modeling offers various benefits, compared to the traditional data warehousing solutions such as SAP BW.
• Virtual data models with on the fly calculation of results, which enables reporting accuracy and requires very limited data storage – powered by the in-memory processing, columnar storage and parallel processing etc.
• Ability to perform highly processing intensive calculations efficiently – For example identify the customers where the sales revenue is greater than the average sales revenue per customer
• Real time reporting leveraging the data replication and access techniques such as SLT, Smart data access etc.

Apart from the HANA sidecar or data mart solutions, HANA modeling also plays an essential role in the BW on HANA mixed scenarios, S/4 HANA Analytics, Predictive Analytics and Native HANA applications etc.

Move HANA data and log files to different mount point

I decided to write this because I ran into a space problem while trying to upgrade a HANA test system. Of course, it isn’t a certified appliance, but I had to move the data and log files to another mount point to free up enough space to complete the upgrade.

Moving data files to a different mount point is a common procedure for many databases, but typically isn’t an issue with HANA’s appliance model. HANA has a very specific hardware configuration with the persistent mount point being 4 times RAM. The introduction of the Tailored Datacenter Integration (TDI) may warrant this process a little more often. I’ve outlined the steps below to move the $(DIR_GLOBAL)/hdb/data and $(DIR_GLOBAL)/hdb/log directories to a different location.

Vora 1.2 installation Cheat sheet: Concepts, Requirements and Installation

SAP HANA Vora provides an in-memory processing engine which can scale up to thousands of nodes, both on premise and in cloud. Vora fits into the Hadoop Ecosystem and extends the Spark execution framework.

Concepts and Requirements:

Sap HANA VORA 1.2 consists of the two following main components:

• SAP HANA Vora Engine: 

SAP HANA Vora instances hold data in memory and boost the performance.

• SAP HANA Vora Spark Extension Library:
• Provides access to SAP HANA Vora through Spark.
• Makes available additional functionality, such as a hierarchy implementation.

Code Push Down for HANA Starts with ABAP Open SQL

What is Code Push Down?
One of the key differences for developing applications in ABAP for HANA is that you can push down data intense computations and calculations to the HANA DB layer instead bringing all the data to the ABAP layer and the processing the data to do computations. This is what is termed as Code-to-Data paradigm in the context of developing ABAP applications optimized for HANA.

Where does Code Push Down Start?
It is a general misconception that if one wants to do code push down in ABAP for HANA you always need to either use HANA native SQL or build complex HANA artefacts in order to achieve this.
But in reality the Code Push Down for HANA  from ABAP can very well start with ABAP Open SQL. Let us see How and Why?

The New Enhanced Open SQL
It has been SAP's constant endeavour to improve Open SQL with each release of ABAP Application Server in order make it the most efficient channel for accessing data  in a manner that is database agnostic.

HANA: Lost Updates

Brief Intro of what happens in Lost Updates?

A simple example which will make you understand Lost Update.
For an example:
Session 1: User A reads record 1
Session 2: User B reads record 1
Session 1: User A updates record 1
Session 2: User B updated record 1
User B have not seen the record updated by User A and updated the existing record resulting in Lost Updates.

How can we tackle it programmatically?

Installing SAP HANA SPS 7 on AWS

You should be aware that currently, Amazon only provides SAP HANA Revision 68 as a ready to go installation image. This tutorial thoroughly explains how you can reinstall SAP HANA SPS 7 by yourself in order to replace your old version.

Launching an instance

First, we will install a SUSE Linux Enterprise Server 11 Service Pack 3 64-bit, sized m2.4xlarge with 68.4 GB storage space.

RANK Function by SQL & Calculation View

RANK  Logic by SQL RANK Function & SQL Logic  & Calculation View Graphical & CE Function.

Scenario:
⦁ Consider a Non SAP load (e.g: Flat file load )  which Full load daily , gets loaded into HANA table.
⦁ Because of Full load , we get daily all transactions uploaded into HANA table, unless we implement any pseudo delta logic  in source side.
⦁ We have chance of getting same transaction multiple times from source file , if there were multiple changes on any key figures for same Transaction ID.
⦁ For Example , Order 100000, on created on date have Order Qty as 10 KG.
⦁ On same day or next subsequent day for above transaction there is  increase  in order qty from 10 KG to 20 KG.
⦁ So from NON SAP source we get these transaction multiple times with old Value & new value Order Qty with Different Time Stamp.

Introducing SAP HANA Vora1.2

SAP HANA Vora 1.2 was released recently and with this new version we have added several new features to the product. Some of the key ones I want to highlight in this blog are

• Support for MapR Hadoop distro
• Introducing new “OLAP” modeler to build hierarchical data models on Vora data
• Discovery service using open source Consul – to register Vora services automatically
• New Catalog to replace Zookeper as metadatstore
• Native persistency for metadata catalog using Distributed shared log
• Thriftserver for client access thru jdbc-spark connectivity

The new installer for Vora in ver1.2 extends the simplified installer to be able to use Hadoop Management tools like MapR Control System to deploy Vora on all the Hadoop/Spark nodes. This is an addition to what was provided in ver1.0 for Cloudera Manager and Ambari admin tools.