Script calculation view and schema mapping

[patrickbachmann]

Hi folks,

I have a calculation view with a bunch of SQL Script in it.  In the script it references tables with something like this;  select from "HANADEV"."BSEG" where...etc

Now lets say my hana development schema is called HANADEV, on my testing/qa server it's called HANAQAS and my production server it's called HANAPRD.

Next I want to export my view from my development server to my testing server however it has the schema "HANADEV" hardcoded. 

OPTION1:

If I try to change the script to read "HANAQAS"."BSEG" (while still on HANADEV server and before I create an export) I get error that table does not exist even if I have schema mapping that points HANAQAS authoring schema to HANADEV physical schema. 

OPTION2:

I leave my script as "HANADEV"."BSEG" and export from my development server and import into my test server and set the schema mapping on the test server so that HANADEV authoring schema points to HANAQAS physical schema.  When the imported object is activated it fails saying the table does not exist in schema HANADEV.  (seems schema mapping does not work)

OPTION3:

I import the view into my test environment, the activation step fails, and I manually go into the view and EDIT NATIVELY.  This is the only thing that seems to work but obviously not ideal.

I'm sure I am missing something obvious ( and I'm scared of what Lars will comment here ) but alas I am not sure so I will ask how are some of you handling this and/or what is another option that I'm overlooking?  Perhaps I should use the same generic schema name throughout my landscape?  Also I could have sworn I have gotten schema mapping to work the way I wanted to before but I have recently updated to 47 so wondering if something is wrong with schema mapping.

Thanks!

-Patrick

[patrickbachmann · Accepted solution]

Example error below when importing from HANADEV into HANAQAS (with schema mapping in HANAQAS having an entry like this: HANADEV authoring schema equals HANAQAS physical schema)

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: invalid table name:  Could not find table/view BSEG in schema HANADEV: line 27 col 16 (at pos 912)nSet Schema DDL statement: set schema "HANAQAS"nType DDL: create type

etc

[Ravi_Channe · Accepted solution]

Hi Patrick,

I was trying to look into the options. Schema mapping with SP05 may not be helpful here as the schema name is to be written in the script.

When you create a script based view, the system asks you to select a default schema. In your case, you may choose to select HANADEV as the default schema and you may not need to add schema name before the table name. Which means the table BSEG can be used just as BSEG and not HANADEV.BSEG.

When you compile the view, it expects the table in the same schema in that case. If the table is not found in the current schema, HANA tries to locate it in SYSTEM schema.

Unfortunately I don't have multiple schema or multiple environment (Dev / QA / Prod) scenario, hence I cannot test it myself.

You may try to export the script view with the SQL statements without Schema name prefix and import it in another schema. If the second schema also has the same table, then the view should work there also.

This will not work if you access tables from multiple schemas in your Calc view. Because in that case, you will have to prefix the schema name to the table name.

Alternatively you can also try the schema mapping in case all your tables reside in the same schema and you don't prefix them with the schema name.

As mentioned, I don't have the multiple schema environment hence cannot test it myself.

Secondly because of the exact same issue, we have decided to keep the schema names / user names SAME across all the environments when we go for the implementation..

Regards,

Ravi