As many of you BusinessObjects experts understand, stored procedures need to be leveraged from time to time. This gives you that added flexibility to leverage complex logic that you may not be able to do directly in the universe or HANA view effectively / efficiently. I am only focusing on the SAP HANA perspective when I speak to stored procedures but there are instances that a normal SQL Script Calculation View can’t accommodate all of the complex logic that you need to integrate. The main instance that I have found is the case where you need to create internal tables that can’t easily be created using multiple select statements. One example of this is when you are going through a while loop or a For loop and you want to populate an internal record after you process each individual record and execute specific logic against it.
There may be someone that can do the complex select statements but in the instance that I was trying, I was unable to. With this said, I created a read/write stored procedure within SAP HANA that handled all of the logic that I needed. Unfortunately, SAP HANA SQL Calculation Views will not let you consume (CALL) a read/write Stored Procedure directly within it. Since this is the case, you have to do it within the universe layer. Unfortunately, the IDT tool, which is the successor to the UDT tool, does not allow you to consume stored procedures (or I am unaware on how to do this). With this stated, I needed to create a unx within the UDT that used the stored procedure as a source.
Within the WEBI, I then had a portion of my source coming from a unv universe and a portion of my source coming from a unx universe. These were both consumed by the WEBI front end and all attributes were merged accordingly.
The question that I ask you all are the following:
- Can IDT leverage Stored Procedures?
- If IDT can not, when is SAP going to allow this? You would think that a successor of a product would include this and many more options?
- Has anyone else run into this issue in the past?
Happy HANA! and please provide comments!
Please follow me on twitter @tim_korba