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

[lbreddemann]

Hi Patrick,

no idea, why you could be scared ...

Anyhow, I just tried to reproduce the behavior and apparently as soon as you explicitly reference a specific schema in your code, this won't be auto-converted.

The simplest solution to me is: don't explicitly refer to the schema and use the default schema instead.

- Lars

[patrickbachmann]

Thanks guys.  I removed the explicit schema reference and exported and it worked!  Go figure.  I could have sworn several versions ago when I first created this script view that I had to explicitly put the schema name in the script to get it to work but perhaps not. 

-Patrick

[patrickbachmann]

I think I just had a revelation.  On our original test box we had an original schema name and loaded tons of data and then created a brand new schema and loaded lots of data so we had two schemas.  That's why I had to explicitly list it in the past but now we are cleaned up with one main schema.  See I'm not entirely crazy. 

[patrickbachmann]

By the way if you have more than one schema where is the DEFAULT defined exactly?

Thanks

[Ravi_Channe]

Hi Patrick,

Did not understand the question correctly, but when you create the script based calc view, the first screen itself asks for the default schema. You can also see it in the properties of the existing calc view.

Regards,

Ravi

[patrickbachmann]

Thank you Ravi, I found it now under properties.  With Analytical view it is a little more obvious when you first open the view whereas the calc view I had to open it and click on blank area of scenario screen and then look at properties.  Excellent, exactly what I was looking for.  Thanks again.

[patrickbachmann]

Hey guys, interestingly when I took the explicit schema name out of my SQL script code and exported it works BUT if I look at my view properties in the original in hanadev the default schema is HANADEV but in the imported view in HANAQAS if I look at properties the default schema shows the number '5'.  I'm baffled at how this is still working and what the 5 represents.  Any clue?