I faced a problem related to Parent-Child hierarchy in HANA Attribute view. I searched for the solution, of course, and the only trace in Internet that I found was a SCN thread with no replies, which suggested that the problem was not documented so far. So I started a research on my own.

Very often, I found that “classical” examples with real-life-like data (such as tables in SAP_HANA_DEMO schema which comes along with trial cloud solutions) overwhelmed my attention with too much information and too large data sets to analyze. I was not able to really understand what was going on until I created the most simple examples, far too simple for any real-life scenario, but “naked” to the essence of my problem. Inspired by my wife’s favorite chef Jamie Oliver (who called himself “Naked Chef” because his cooking was “naked” to its essence), I created this blog with intention to make it a series. I will try to elaborate problems that I had and simplest possible test-cases which enabled me to find solutions.

So let’s move on.

Create Test Data

Let’s create two simple tables: COMPANIES with company names and countries, and OUTGOING_INVOICES, payments each company receives, to be analyzed later. In order to create tables and fill them with data, we run this code in SQL console. Please replace my schema name and table name prefix with something that suits your environment.


-- create tables
CREATE COLUMN TABLE "HA300_160115"."D107_COMPANIES"
  (COMP_CODE varchar(10) primary key,
   COUNTRY   varchar(10),
   NAME      varchar(30) )  ;
CREATE COLUMN TABLE "HA300_160115"."D107_OUTGOING_INVOICES"
  (DOC_ID    varchar(10) primary key,
   COMP_CODE varchar(10),
   AMOUNT    decimal(15,2)    )  ;
-- fill test data
-- companies
DELETE FROM "HA300_160115"."D107_COMPANIES";
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '1', 'DE',  'SAP AG' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '2', 'DE',  'Mercedes' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '3', 'DE',  'Bosch' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '4', 'CH',  'Nestle' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '5', 'SA',  'Saudi Aramco' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '6', 'HR',  'Erpicon' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '7', 'HR',  'INA' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( '8', 'US',  'Boeing' );
-- incoming invoices
DELETE FROM "HA300_160115"."D107_OUTGOING_INVOICES";
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '0', '0', '2000000' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '1', '1', '2000000' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '2', '1', '24352' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '3', '2', '6746' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '4', '2', '33333' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '5', '3', '2000000' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '6', '3', '24352' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '7', '4', '33667' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '8', '4', '34324' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '9', '5', '24352' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '10', '6', '6746' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '11', '7', '33333' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '12', '8', '2000000' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '13', '5', '24352' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '14', '4', '33667' );
INSERT INTO "HA300_160115"."D107_OUTGOING_INVOICES" VALUES ( '15', '8', '34324' );





Attribute View with Hierarchy

Data Foundation

Now is the time for Attribute view with parent-child hierarchy. Let’s call it after the table: AT_COMPANIES. The data foundation is the table COMPANIES and we will take all the fields into output:

Semantics

In Semantics, the COMP_CODE is the key.

The hierarchy has only one line: COMP_CODE is a child of COUNTRY.

Don’t forget to assign the NAME field as a Label Column to COMP_CODE. This is not necessary for our example in technical terms, but it will help the output to look better.

Analytic View

The Analytic View AN_COMPANIES_OUTGOING_INVOICES has just one table as Data Foundation: OUTGOING_INVOICES. The Logical Join (Star Join) uses the attribute view which we have just created: AT_COMPANIES. There are only two output columns (from Data Foundation): attribute DOC_ID and measure AMOUNT.

Testing: Excel and MDX

Let’s try to consume this view in a hierarchical way. We shall use Microsoft Excel with SAP HANA MDX Provider connection. A prerequisite for this is installing SAP HANA Client on your workstation, which you can download here (if SAP removes this link, please inform me so I can update/remove it).

After starting Excel, we need to add data From Other Sources for the first run (later, provided that you save the newly created connection, you can use Existing Connections).

->

Now we need to choose Data Link SAP HANA MDX Provider. If you haven’t installed SAP HANA Client on your PC, you won’t see it and won’t be able to continue from here. Supply host name (or IP), instance number and your account credentials.

->

Pick a package from the dropdown box and select your Analytic View. You can save the connection file (with password) so that you don’t have to go through this process next time you want to run the same Analytic View.

->

We are almost there, just one more step:

and…

Error

Like in the above mentioned SCN thread, the engine complains: Each predecessor (except the root node) must also appear as a successor, with country names listed. Trying this with standard real-life-like tables gave me headache. There was just too much data to analyze and play with, and I didn’t want to destroy it. But with my own nano-size custom data set I could do whatever I wanted.

Problem Analysis

The key characteristic of Parent-Child hierarchy is the fact that it is Recursive.

This means that every parent can be a child at the same time and there must be no orphans, except the roots. If I say that my company code “1” (that’s SAP AG) belongs to country with code “DE”, and given recursive nature of the relation (every child must have a parent), it means that the company with code “DE” must exist too. Since it does not, my company code “1” is an orphan and so are all the companies. Now, semantically this is meaningless, but technically that’s how Parent-Child hierarchy works.

What could I do about it?

Expected Solution

I would expect that setting the hierarchy properties would fix the problem: Setting COUNTRY as a Root Node and deciding what happens with orphans – they become Root Nodes – should do the trick. Unfortunately it didn’t help.

I plaid with other variations of these properties. If I chose Orphan nodes: Ignore, my Excel worked, but with no data in it. Naturally, all my companies belong to countries and they are therefore orphans. Since all the facts (outgoing invoices) belong to them, there were no facts to analyze.

This is where I hit the wall when it comes to view adjustments. If there is better solution to this, please share. Meanwhile, I tried another thing which made things much more clear and helped me to understand the Parent-Child hierarchy better.

Solution (or at least explanation)

Since this failed, I tried making the data set technically consistent. Let’s pretend that countries are companies at the topmost level of the recursive hierarchy and they have no parents (their COUNTRY field is filled by null and therefore they should be root). I added the following data to my COMPANIES table:


-- countries as companies
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( 'DE', null, 'Germany' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( 'CH', null, 'Switzerland' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( 'HR', null, 'Croatia' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( 'US', null, 'United States' );
INSERT INTO "HA300_160115"."D107_COMPANIES" VALUES ( 'SA', null, 'Kingdom of Saudi Arabia' );

And finally, after the long battle, sunshine on the horizon:

So technically we reached the satisfactory result, but not semantically, as countries are not companies. In real life, I believe that this example calls for Level Hierarchy rather than Parent-Child.

Parent-Child recursive hierarchy would be more suitable for truly recursive relations, such as company organization with departments and job positions, or folders and files structure.

I hope that my blog helped to share more light on Parent-Child hierarchy.

Igor

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply