Business requirement :
When SAP implements SAP Enterprise HANA from other landscape, huge no of SQL query need to be transform to HANA graphical VDM as BI tool like Lumira, Webi design Studio in BOBJ and Tableau. In one scenario similar request (convert SQL to HANA Graphical VDM (Virtual Data Model) raised when the landscape transform to SAP HANA.
Current Process:
The current process is to change the SQL Query into HANA graphical virtual Data model by performing manual steps. The steps includes the following steps.
1. Identifying the tables manually.
2. Identifying the selection, joins ,projections, Union manually
3. Inside the HANA studio redesign the Query graphically.
Assumption and Risk:
If the SQL queries are more complex, following are the risks –
1. It is too much time consuming. For a query it is almost takes no of day to convert it into graphical HANA VDM.
2. Data Accuracy mismatch: It is very difficult to manually convert the SQL query into HANA VDM accurately. Most of the time the data mismatch.
Solution:
After this solution is implemented, manual intervention from the business is drastically reduced. The time duration also has been reduced for this transformation drastically. The desired results can be achieved by implementing the logic within the standard HANA interface with proper accuracy. The changes are purely automated process and it will populate the accurately to the business user.
Proposed Logic and Scenarios:
Proposed logic is given below –
1. Get the SQL query from the business.
2. Format it with the proper format.
3. Open HANA Studio Interface
4. Create a Script based calculation view.
5. Add the SQL query in the Script based calculation view.
6. Create output column similar to SQL Query.
7. Activate the Script based Calculation view.
8. Go to the option –
Windows -> show view -> others -> HANA -> Quick View
9. Select the migrate option -> Select the hana system from where you want to migrate
- select Script based calculation view to graphical calculation view and table function.
10. Click next and then select the script which you want to transform.
11. Click finish.
12. Once finished button clicked one graphical calculation view and table function will be created.
13. But you will be unable to activate the graphical calculation view and table function from the modeler perspective.
14. Go to developer mode -> repository -> Open the table function-> activate it.
15. Return to modeler perspective ->Activate the Graphical calculation view.
16. Finally this graphical view represents the SQL query which we have transformed.
17. Now if you data preview, you will get the accurate result same as SQL Query.
Scenario:
We have got the following Query which needs to be transformed to HANA graphical view so that the BI tool can consume this
1.
2. Formatted into proper format – Formatted the script code properly.
3. Open the HANA Studio Interface and create the SQL based calculation view –
4 and 5. Provide the SQL inside the view –
i.e. VAR_OUT= SQL QUERY
6. Create output column similar to SQL Query :
Create the output column of the view as similar as query sequentially –
7.Activate the script based calculation view.
8.Go to the option –
Windows -> show view -> others -> HANA -> Quick View
9.Then Select the migrate option -> Select the hana system from where you want to migrate
->select Script based calculation view to graphical calculation view and table function-
10. And 11. Click next and then select the script calculation view which you want to transform and click Finish.
12. Once finished button clicked one graphical calculation view and table function will be created.
13. But you will be unable to activate the graphical calculation view and table function from the modeler perspective.
14. Go to developer mode -> repository -> Open the table function-> activate it.
15. Return to modeler perspective ->Activate the Graphical calculation view.
16. Finally this graphical view represents the SQL query which we have transformed.
17. Now if you data preview, you will get the accurate result same as SQL Query.
No comments:
Post a Comment