Create Hana Views automatically

[former_member207903]

Hello colleagues,

I’m starting in a project where one of the requirements states that we need automate the process of creation of Hana Views. Basically, the idea is that a new user would create a new schema and would give us its URL. With this input, the application would use a script to generate the default Hana Views for the newly created database. With this, we would not need to spend much effort configuring always the same views for any new customer. I searched for this operation but I didn’t find an API or any concrete examples.

Anyone know if this is feasible? Sap Hana supports this kind of operation now?

Thanks in advance,

Ígor Lauxen

[lbreddemann]

Hi Igor

I've thought a bit about this requirement and my understanding is this:

Your database wants to give every user a virtually private database by the means of user specific schemas.

Yet, all schemas should be primed with the same HANA views.

Now, I don't fully understand the scenario here.

Typically, you would development an application and transport it via a delivery unit. Repository objects get created in the _SYS_BIC schema or the schema designated and that would be the same for every target database.

As there is the option for schema mapping upon DU import, you could think of using this to map to different schemas, even in the same system - but that wouldn't help with what you called 'HANA views' (information views: calculation, analytic and attribute views) as those still get generated into _SYS_BIC and are not user schema dependent.

Of course you could do something simple as loading a table with the DDL commands via DU into a seeding schema and then have a procedure execute the DDL for each new target schema.

Still doesn't help with activated repository objects, though.

It comes back to the unclear scenario: do you want to roll out just the views or also table definitions and data? What should users be able to do with those objects? And if tables are also part of this, should the delivered views point to the tables of the same schema?

As schemas are a naming concept and not a security object, there's also the question: how do you want to handle access privileges in this scenario? If the objects generated into the schema are generated by a different user, then this different user owns the objects - not your target schema user.

What about shared data structures? Are all users meant to be completely independent or should they share any data (which would be the actual point of having a database in the first place)?

Looks like you opened a can of worms here ...

Cheers,

Lars

[former_member207903]

Hi Lars,

Actually we are not going to provide a private database from our database. The scenario is that we are going to provide a new Hana Cloud Platform instance with a new Database and schema along with the Hana views. Whenever an user changes a specific configuration from the application, the Hana views must be dinamically created/updated/deleted.

I saw that we can create views directly by Java applications (I tested this in a demo project and it works) or by integrating with a CI build (with Jenkins, etc-- I didn't test it yet); however, I did not find a documentation explaining on how we can create Hana views (Calculation, Analytic and Attribute) by a SQL query or anything else.

Thanks for your support and time.
Kind regards,
Ígor

[lbreddemann]

Hi Igor

the API for creating views and other repository objects programatically is not publicly available.

You might want to reach out to the internal resources (e.g. SAP HANA product management) to get your hands on the available tools for this.

Cheers,

Lars