Is it possible to change the Schema name in analytic views?

[Former Member]

I have developed some analytic views that use tables in a specific schema "SCHEMA_A" which has other table for stored procedures etc. However, I want to improve the performance of the views and need to remove a large portion of rows from those tables. I can't modify the tables in SCHEMA_A directly because they are also used by other things like procedures. Therefore, I was thinking to create a new schema called "SCHEMA_B", copy tables used in the views there, and delete rows from them. However, to make this work, I need to change the schema in the views from "SCHEMA_A" to "SCHEMA_B" but I couldn't find a way to do that. The table metadata is the same just the size is different. It looks like I have to rebuild all views to switch to another schema which doesn't make sense to me. Is there any trick to get around this? Maybe manually "search and replace" the schema name in the exported DU will work? Has anyone done this before?

Thanks,

Larry

[henrique_pinto]

In the old days, you could export the content in developer mode (external .analyticview file, which would be a well-formed XML) and then, in the file's content, replace manually all references to the old schema to the new schema.

In the latest revisions (since SP5? maybe before, not sure), they've released the schema mapping functionality, which doesn't require you to do that replacement manually anymore.

Check item "7.1.6 Mapping the Authoring Schema to the Physical Schema" of the HANA Developer Guide: http://help.sap.com/hana/hana_dev_en.pdf

[Former Member]

Hi Henrique, thanks for the quick reply. I tried this approach but it didn't work. The reason is that in schema mapping, the authoring (source) schema does NOT exist in the target HANA instance. Therefore, I think it mainly is designed for code migration between different HANA instances.

My use case is different. I'm trying to improve the performance of the analytic views which use tables in SHCEMA_A. Those tables are big and contain a lot of extra rows not needed by the views. But I can't delete data because they are needed by procedures. Therefore, I'm thinking of creating another schema SCHEMA_B which contains a small subset of data in SCHEMA_A and change the schema name in the views from SCHEMA_A to SCHEMA_B. So both SCHEMA_A and SCHEMA_B are physical schemas, and I can't create a schema mapping between them - HANA studio throws an error message saying that authoring schema SCHEMA_A already exists as a physical schema.

I guess I can still manually modify the exported XML files, right? Is there a more elegant way to do this?

Thanks,

Larry

[henrique_pinto]

Hi Larry,

even though this was really designed for transporting between different systems, I don't see why you couldn't use it for exporting and then importing in the same system. Did you try and it didn't work?

This would be the more elegant solution.

The workaround would be to change the content of the exported .analyticview file manually, and then reimporting it under another package (make sure to change the folder of the exported files for that).

Best regards,

Henrique.

[Former Member]

Yes, I tried it but couldn't make it work in my use case. The issue for me is that I need both schemas SCHEMA_A and SCHEMA_B exist in the same HANA instance. SCHEMA_A has the full dataset which makes the analytic views running slow. I want to create another schema SCHEMA_B which is stripped down version of SCHEMA_A and has at most 1/10 of data in it. I want to make the views point to SCHEMA_B instead of SCHEMA_A to improve performance.

When I tried to create a schema mapping between SCHEMA_A and SCHEMA_B, HANA complained that the authorizing schema SCHEMA_A already exists as a physical schema and didn't allow me to proceed.

Thanks,

Larry

[henrique_pinto]

I see...

I suppose you'll need to change it manually then.

[Former Member]

I just have to ask - how many records are in your source table, and what kind of performance are you getting out of your Analytic View? What target performance are you working towards? And are you developing for training purposes, a proof of concept, or a production scenario (or something else?)

The idea of "copying and pasting" data from one table to another is a really bad idea for a production solution. If you have a reporting requirement that's definitely limited to a subset of data then I'd recommend putting filters in your models which should definitely speed up your queries without data integrity/quality issues you'd face with partial duplication of another table.