Tool for Quick Creation of Virtual Table in HANA

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.
A. User will pass SOURCE_SCHEMA_NAME and TARGET_SCHEMA_NAME along with the SOURCE_TABLE_NAME
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. 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.

Source: scn.sap.com