Multiple default schemas

[justin_molenaur2]

In calc views and stored procedures, we of course have the option to select a default schema. This allows tables to be referenced without specifically mentioning the schema (DEV, QA, PRD for example). During import, schema mapping will handle the conversion from the logical to the physical schema.

With the above in mind, it seems that there is only support for one default schema, say ECC DEV/QA/PRD. What happens when another schema is actually required, say from a CRM system? Is it possible to have dynamic logical schemas that refer to two different schemas?

Thanks,

Justin

[sagarjoshi · Accepted solution]

Hi Justin,

As per my understanding this is not possible in Scripted calc view or procedures to have multiple logical schemas.

However as per my understanding this should not be a real problem if you can separate your coding in separate procedures/calc views based on logical system boundary and use their own default schemas (e.g. separate calc view/procedure code for CRM and ERP) and use the SELECT statements for calc views or nested call procedure statements.

Even if you have to JOIN tables from ERP and CRM it would be possible based on local OUT table parameters.

There is an interesting article here but it is applicable for Graphical calc views.

http://www.saphana.com/servlet/JiveServlet/previewBody/3885-102-1-8185/How%20to%20Realize%20Cross%20...

Thanks

Sagar

[justin_molenaur2 · Accepted solution]

Lars - agree that it may cause confusion to implement functionality like this natively. Your statement about putting multisource data into one schema confuses me a bit, HANA has to support mixing schema data somehow - I think Sagar has the right idea.

The requirement would be to mix operational data from two systems, which is fairly common. In my current project, this is accomplished by having the second schema name remain the same across each system, ie "ERP2". In this way, it can be hard coded into any code and be used that way. However, since SLT naming typically will have the source system included in the name, that might not be an option as the model moves through the landscape.

Sagar - I like the approach you mention, it will meet the requirement just fine. Having separate views that address components from their respective source systems and then merging at a later point in a parent view should work.

Thanks guys,

Justin

[lbreddemann · Accepted solution]

Hi Justin

agreed - this approach sounds good. That will keep the schema local modelling at the model and would allow to reference the views via a separate package then.

Thinking about it - that's how the SAP HANA live contents works as well...

Cheers, Lars    

[justin_molenaur2 · Accepted solution]

Yes, that is true. That is partially why I was confused on your initial response about mixing data too

What I don't know at this point is - if/what scenarios of reusability may impact performance when the cascading model is instantiated.

Happy HANA,

Justin

[justin_molenaur2 · Accepted solution]

So I finally got around to actually starting on this project, which requires mixing data from two seperate source system schemas.

What I can say is this (so far) - seperating views out by their respective source system schemas will work. However, what I am seeing is some AWFUL performance. What I see happening through VizPlan is that essentially the entire result from each view needs to be materialized and THEN the join occurs. So instead of the various reduction phases being able to work their magic and optimize the joins, we have to wait until afterwards to do a massive join.

To make things ultra simple, consider the following example. Testing on a rev 74 box.

Required end result query (Super Simplified), running in Studio

SELECT A.COL1, B.COL2

FROM SCHEMA1.TABLE1 A

INNER JOIN

SCHEMA2.TABLE2 B

ON (A.COL1 = B.COL1)

WHERE A.TYPE = TEST_TYPE

We would see a VizPlan that resembles somehing like this, with execution time of around 8 seconds

To accomplish the same thing, we would have to create two views, one that encapsulates each of the schema views.

VIEW1 = SELECT COL1 FROM SCHEMA1.TABLE1 WHERE TYPE = 'TEST_TYPE"

VIEW2 = SELECT COL1, COL2 FROM SCHEMA2.TABLE2

End View

SELECT A.COL1, B.COL2

FROM VIEW1 A

INNER JOIN

VIEW2 B

ON (A.COL1 = B.COL1)

So here, when we execute we get a VizPlan like this, and the total runtime is over 2m30s. We can see that the entire results of both views are materialized first, and THEN the join is happening. Obviously this requires a huge chunk of temporary processing, whereas a query that is able to optimize knowing the tables "natively" has a huge advantage to reduce the data size before the join ever occurs.

Keep in mind this is using scripted calc views, I am not sure how (or if) this behavior can change using graphical calc views - but my guess is that it won't really since similar operations need to be performed.

Another idea to retain the first style of SQL syntax (directly referencing tables and not views), is to use synonyms for these tables across all systems that the content may be migrated to. Obviously, this would impose some additional work, but would allow the tables that exist in different schemas to be referenced and joined in the same view, achieving ideal optimization.

I'm sure someone has already hit this issue - so opening the thread back up

Happy HANA,

Justin