Tool for Quick Creation of Virtual Table in HANA

[chandan_praharaj]

Introduction:

I have seen there is redundant work involved in creating Virtual table after setting up connection in HANA. So here is a tool which you can create in your schema to quickly create the tables.

My Scenario:

I have a scenario where I have to move an application from old SP09 landscape to SP11 landscape where most the Calculation views have virtual table.  In my scenario we have both the SCHEMA_NAME and the source is also HDB. So I have 2 scenario to cover to make it more generic.

1. A. User will pass SOURCE_SCHEMA_NAME and TARGET_SCHEMA_NAME along with the SOURCE_TABLE_NAME
2. B. User will pass SOURCE_SCHEMA_NAME and the SOURCE_TABLE_NAME

We can also replicate the scenario with minor modification to my case.

Problems faced while moving the Calculation View:

1. 1. If I would have moved the package directly it would have shown error in activation after import.

So I need to create all the virtual tables in the new system. So manually creating VT is a time consuming and repetitive task. So I have written a HDB Procedure which will create the VT in one click.

The HDB Procedure code is as follows:

I have created the Procedure in my local Schema, as this is one time activity and doesn’t required to be used across landscape or some other user.

PROCEDURE "CHANDAN"."users.CHANDAN_PK::VT_AUTO_CREATE"(

  IN v_schema NVARCHAR(20),

 -- IN v_targ_schema NVARCHAR(20),

  IN v_table NVARCHAR(50),

  OUT v_message NVARCHAR(100)

 )

 LANGUAGE SQLSCRIPT

 SQL SECURITY INVOKER

AS

BEGIN

 DECLARE v_remote_src NVARCHAR(20) := null;

 DECLARE v_remote_src_sch NVARCHAR(20) := null;

 DECLARE v_table_exists NVARCHAR(20) := null;

 DECLARE v_statement NVARCHAR(200) := null;

 SELECT TOP 1

    REMOTE_SOURCE_NAME

  INTO v_remote_src

  FROM "SYS"."REMOTE_SOURCES";

 SELECT TOP 1

    REMOTE_OWNER_NAME

  INTO v_remote_src_sch

  FROM "SYS"."VIRTUAL_TABLES"

  WHERE SCHEMA_NAME = :v_schema;

 SELECT count(*)

  INTO v_table_exists

  FROM "SYS"."VIRTUAL_TABLES"

  WHERE TABLE_NAME = :v_table;

 IF (v_table_exists = 0) THEN

  v_statement := 'CREATE VIRTUAL TABLE ' || :v_schema || '.' ||:v_remote_src||'_'|| :v_table || ' AT ' || :v_remote_src || '."<NULL>".' || :v_remote_src_sch || '.' || :v_table;

 -- v_statement := 'CREATE VIRTUAL TABLE ' || :v_schema || '.' || :v_table || ' AT ' || :v_remote_src || '."<NULL>".' || :v_targ_schema || '.' || :v_table;

  EXEC :v_statement;

  SELECT 'Table Created in Schema ' || :v_schema

   INTO v_message

   FROM dummy;

 ELSE

  SELECT 'Table already Exist in Schema ' || :v_schema

   INTO v_message

   FROM dummy;

 END IF;

END;

Tested the procedure by Invoking the Procedure once in SQL Console. If it executes fine. Copy the call for other Tables in the SQL Console.

Better to use Concatenate function in excel to prepare the call statement when you have all the table ready in one excel. If table list is not ready, you can check the SYS.VIRTUAL_TABLES and copy the table list from there.

You can create this procedure and call this in UI via XSJS services too, which I will try adding in my next blog.

Hope this will help other projects. This blog is based on my personal experience, and any suggestion or correction is most welcome to improve this further.

Chandan Praharaj