How to Convert SQL from MS SQL Server to SAP HANA - new version UI enabled!

In this blog I'll like to point you to a tool that has been recently released in SDN for converting SQL language from MS SQL Server to SAP HANA™.
The Query Migration Tool for Microsoft SQL Server to SAP HANA is a semi-automatic tool that helps convert most of the data-definition language (DDL) and data-manipulation language (DML).
It will help you to convert structured query language (SQL) in the Microsoft SQL Server database (using T-SQL grammar) to SQL that can be used in the SAP HANA™ database (using ANSI-SQL grammar).
After the conversion, you must check whether the converted version is correct according to your needs. 
This tool supports most of the official T-SQL grammar, and some well-known and widely-used undocumented feature. For more information about the official T-SQL grammar, see the MSDN Library
If SAP HANA does not support certain SQL, this tool will do the following: 
• Find equivalents in the SAP HANA database and convert the SQL.
• Delete the SQL in the input file and display relevant comments in the output file.
• Leave the SQL in the input file as it is, for example, the WITH statement.

Download links:

In order to be up to date with the SQL language supported by SAP HANA please check the following links:

Note:
If you encounter any issue when using the SQL converter, post the issue to the SAP Community Network athttp://scn.sap.com/community/business-one for general topics or at http://scn.sap.com/community/business-one/system-administrationfor system-administration topics. Please don't post them here as this is not in a discussion format easy to follow up.

We hope this tool will help you.

Troubleshooting SAP HANA Authorisation issues

Every now and again I receive questions regarding SAP authorisation issues. I thought it might be useful to create a troubleshooting walk through.

This document will deal with issues regarding analytical privilege in SAP HANA Studio

So what are Privileges some might ask?
System Privilege:
System privileges control general system activities. They are mainly used for administrative purposes, such as creating schemas, creating and changing users and roles, performing data backups, managing licenses, and so on.
Object Privilege:
Object privileges are used to allow access to and modification of database objects, such as tables and views. Depending on the object type, different actions can be authorized (for example, SELECT, CREATE ANY, ALTER, DROP, and so on).
Analytic Privilege:
Analytic privileges are used to allow read access to data in SAP HANA information models (that is, analytic views, attribute views, and calculation views) depending on certain values or combinations of values. Analytic privileges are evaluated during query processing.
In a multiple-container system, analytic privileges granted to users in a particular database authorize access to information models in that database only.
Package Privilege:
Package privileges are used to allow access to and the ability to work in packages in the repository of the SAP HANA database.
Packages contain design time versions of various objects, such as analytic views, attribute views, calculation views, and analytic privileges.
In a multiple-container system, package privileges granted to users in a particular database authorize access to and the ability to work in packages in the repository of that database only.

For more information on SAP HANA privileges please see the SAP HANA Security Guide:


So, you are trying to access a view, a table or simply trying to add roles to users in HANA Studio and you are receiving errors such as:
  • Error during Plan execution of model _SYS_BIC:onep.Queries.qnoverview/CV_QMT_OVERVIEW (-1), reason: user is not authorized
  • pop1 (rc 2950, user is not authorized)
  • insufficient privilege: search table error: [2950] user is not authorized
  • Could not execute 'SELECT * FROM"_SYS_BIC"."<>"' SAP DBTech JDBC: [258]: insufficient privilege: Not authorized.SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

These errors are just examples of  some the different authorisation issues you can see in HANA Studio, and each one is pointing towards a missing analytical privilege.

Once you have created all your models, you then have the opportunity to define your specific authorization requirements on top of the views that you have created.

So for example, we have a model in HANA Studio Schema and its called "_SYS_BIC:Overview/SAP_OVERVIEW"
We have a user, lets just say its the "SYSTEM" user, and when you query this view you get the error:

Error during Plan execution of model _SYS_BIC:Overview/SAP_OVERVIEW (-1), reason: user is not authorized.

So if you are a DBA, and you get a message from a team member informing you that they getting a authorisation issue in HANA Studio. What are you to do?
How are you supposed to know the User ID? And most importantly, how are you to find out what the missing analytical privilege is?

So this is the perfect opportunity to run an authorisation trace through the means of the SQL console on HANA Studio.
So if you follow the below instructions it will walk you through executing the authorisation trace:

1) Please run the following statement in the HANA database to set the DB  trace:
alter system alter configuration ('indexserver.ini','SYSTEM') SET
('trace','authorization')='info' with reconfigure;

2) Reproduce the issue/execute the command again/

3) When the execution finishes please turn off the trace as follows in the Hana studio:
alter system alter configuration ('indexserver.ini','SYSTEM') unset
('trace','authorization') with reconfigure;


So now that you have turned the trace on and reproduced the issue, now you must turn off the trace.

You should now see a new indexserver0000000trc file created in the Diagnosis Files Tab in HANA Studio
Capture.PNG

So once you open the trace files, scroll to the end of the file and you should see something familiar to this:

e cePlanExec      cePlanExecutor.cpp(06890) : Error during Plan execution of model _SYS_BIC:onep.Queries.qnoverview/CV_QMT_OVERVIEW (-1), reason: user is not authorized
i TraceContext    TraceContext.cpp(00718) : UserName=TABLEAU, ApplicationUserName=luben00d, ApplicationName=HDBStudio, ApplicationSource=csns.modeler.datapreview.providers.ResultSetDelegationDataProvider.<init>(ResultSetDelegationDataProvider.java:122);csns.modeler.actions.DataPreviewDelegationAction.getDataProvider(DataPreviewDelegationAction.java:310);csns.modeler.actions.DataPreviewDelegationAction.run(DataPreviewDelegationAction.java:270);csns.modeler.actions.DataPreviewDelegationAction.run(DataPreviewDelegationAction.java:130);csns.modeler.command.handlers.DataPreviewHandler.execute(DataPreviewHandler.java:70);org.eclipse.core.commands
i Authorization    XmlAnalyticalPrivilegeFacade.cpp(01250) : UserId(123456) is missing analytic privileges in order to access _SYS_BIC:onep.MasterData.qn/AT_QMT(ObjectId(15,0,oid=78787)). Current situation:
AP ObjectId(13,2,oid=3): Not granted.
i Authorization    TRexApiSearch.cpp(20566) : TRexApiSearch::analyticalPrivilegesCheck(): User TABLEAU is not authorized on _SYS_BIC:onep.MasterData.qn/AT_QMT (787878) due to XML APs
e CalcEngine      cePopDataSources.cpp(00488) : ceJoinSearchPop ($REQUEST$): Execution of search failed: user is not authorized(2950)
e Executor        PlanExecutor.cpp(00690) : plan plan558676@<> failed with rc 2950; user is not authorized
e Executor        PlanExecutor.cpp(00690) : -- returns for plan558676@<> 
e Executor        PlanExecutor.cpp(00690) : user is not authorized(2950), plan: 1 pops: ceJoinSearchPop pop1(out a)
e Executor        PlanExecutor.cpp(00690) : pop1, 09:57:41.755  +0.000, cpu 139960197732232, <> ceJoinSearchPop, rc 2950, user is not authorized
e Executor        PlanExecutor.cpp(00690) : Comm total: 0.000
e Executor        PlanExecutor.cpp(00690) : Total: <Time- Stamp>, cpu 139960197732232
e Executor        PlanExecutor.cpp(00690) : sizes a 0
e Executor        PlanExecutor.cpp(00690) : -- end executor returns
e Executor        PlanExecutor.cpp(00690) : pop1 (rc 2950, user is not authorized)

So we can see from the trace file that User who is trying to query from the view is called TABLEAU. TABLEAU is also represented by the User ID (123456)

So by looking at the lines:

i Authorization    XmlAnalyticalPrivilegeFacade.cpp(01250) : UserId(123456) is missing analytic privileges in order to access _SYS_BIC:onep.MasterData.qn/AT_QMT(ObjectId(15,0,oid=78787)).
&
i Authorization    TRexApiSearch.cpp(20566) : TRexApiSearch::analyticalPrivilegesCheck(): User TABLEAU is not authorized on _SYS_BIC:onep.MasterData.qn/AT_QMT (787878) due to XML APs

We can clearly see that TABLEAU user is missing the correct analytical privileges to access the_SYS_BIC:onep.MasterData.qn/AT_QMT which is located on Object 78787.

So now we have to find out who owns the Object 78787. We can find out this information by querying the following:

select * from objects where object_oid = '<oid>';
Select * from objects where object_oid = '78787'

Once you have found out the owner for this object, you can get the owner to Grant the TABLEAU user the necessary privileges to query the object.

Please be aware that if you find that the owner of an object is _SYS_REPO, this is not as straight forward as logging in as _SYS_REPO as this is not possible because SYS_REPO is a technical database user used by the SAP HANA repository. The repository consists of packages that contain design time versions of various objects, such as attribute views, analytic views, calculation views, procedures, analytic privileges, and roles. _SYS_REPO is the owner of all objects in the repository, as well as their activated runtime versions.
You have to create a .hdbrole file which which gives the access ( Development type of role, giving select, execute, insert etc access) on this schema. You then assign this role to the user who is trying to access the object.


Another option that is available for analyzing privileges issues was introduced as of SP9. This comes in the form of the Authorization Dependency Viewer. Man-Ted Chan has prepared an excellent blog on this new feature:




For more useful information on Privileges can be seen in the following KBA's:
KBA #2220157 - Database error 258 at EXE insufficient
KBA #1735586 – Unable to grant privileges for SYS_REPO.-objects via SAP HANA Studio authorization management.
KBA #1966219 – HANA technical database user _SYS_REPO cannot be activated.
KBA #1897236 – HANA: Error&quot; insufficient privilege: Not authorized &quot; in SM21
KBA #2092748 – Failure to activate HANA roles in Design Time.
KBA #2126689 – Insufficient privilege. Not authorized
KBA #2250445 - SAP DBTech JDBC 485 - Invalid definition of structured privilege: Invalid filter condition


For more useful Troubleshooting documentation you can visit:


Troubleshooting High CPU Utilisation

High CPU Utilisation


Whilst using HANA i.e. running reports, executing queries, etc. you can see an alert in HANA Studio that the system has consumed CPU resources and the system has reached full utilisation or hangs.

Before performing any traces, please check to see if you have Transparent HugePages enabled on your system. THP should be disabled across your landscape until SAP has recommended activating them once again. Please see the relevant notes in relation to TransparentHugesPages:

HUGEPAGES 

SAP Note 1944799 - SAP HANA Guidelines for SLES Operating System Installation
SAP Note 1824819 - SAP HANA DB: Recommended OS settings for SLES 11 / SLES for SAP Applications 11 SP2
SAP Note 2131662 - Transparent Huge Pages (THP) on SAP HANA Servers
SAP Note 1954788 - SAP HANA DB: Recommended OS settings for SLES 11 / SLES for SAP Applications 11 SP3


The THP activity could also be checked in the runtime dumps by searching “AnonHugePages”. Whilst also checking the THP, it is also recommended to check for:

Swaptotal = ??
Swapfree = ??

This will let you know if there is a reasonable amount of memory in the system.

Next you can Check the (GAL) Global allocation limit:  (search for IPM) and check the limit and ensure it is not lower than what the process/thread in question is trying to allocate.

Usually it is evident what caused the High CPU’s. In many events it is caused by the execution of large queries or running reports from HANA Studio on models.

To be able to use the kernel profile, you must have the SAP_INTERNAL_HANA_SUPPORT role. This role is intended only for SAP HANA development support.

The kernel profile collects, for example, information about frequent and/or expensive execution paths during query processing. It is recommended that you start kernel profiler tracing immediately before you execute the statements you want to analyze and stop it immediately after they have finished. This avoids the unnecessary recording of irrelevant statements. It is also advisable as this kind of tracing can negatively impact performance.

When you stop tracing, the results are saved to trace files that you can access on the Diagnosis Files tab of the Administration editor.

You cannot analyze these files meaningfully in the SAP HANA studio, but instead must use a tool capable of reading the configured output format, that is KCacheGrind or DOT (default format).

You activate and configure the kernel profile in the Administration editor on the Trace Configuration tab. Please be aware that you will also need to have run the runtime dumps also.

The Kernel Profiler Trace results reads in conjunction from the runtime dumps to pick out the relevant Stacks and Thread numbers. To see the full information on Kernel Profiler Trace’s please see Note 1804811 or follow the steps below:

Please be aware that you will also need to execute 2-3 runtime dumps also. The Kernel Profiler Trace results reads in conjunction from the runtime dumps to pick out the relevant Stacks and Thread numbers.


To see the full information on Kernel Profiler Trace’s please see Note 1804811 or follow the steps below:
     
Kernel%20Profiler.PNG

Connect to your HANA database server as user sidadm (for example via putty) and start HDBCONS by typing command "hdbcons".
To do a Kernel Profiler Trace of your query, please follow these steps:
1. "profiler clear" - Resets all information to a clear state
2. "profiler start" - Starts collecting information.
3. Execute the affected query.
4. "profiler stop" - Stops collecting information.
5. "profiler print -o /path/on/disk/cpu.dot;/path/on/disk/wait.dot" - writes the collected information into two dot files which can be sent to SAP.


Once you have this information you will see two dot files called
1: cpu.dot
2: wait.dot.

To read these .dot files you will need to download GVEdit. You can download this at the following:

Once you open the program it will look something similar to this:

Graph%20Viz.PNG
     
      The wait.dot file can be used to analyse a situation where a process is running very slowly without any reasons In such cases, a wait graph can help to identify whether the process is waiting for an IndexHandle, I/O, Savepoint lock, etc.

So once you open the graph viz tool, please open the cpu.dot file. File > open > select the dot file > open > this will open the file:
Once you open this file you will see a screen such as

graphviz%201.PNG
     

The graph might already be open and you might not see it because it is zoomed out very large. You need to use the scroll bar (horizontal and vertical to scroll).

CPU_DOT%201.PNG

From there on it will depend on what the issue is that you are processing.
Normally you will be looking for the process/step that has the highest amount on value for
E= …
Where "E" means Exclusive
There is also:
I=…
Where "I" means Inclusive
The Exclusive is of more interest because it is the exclusive value just for that particular process or step that will indicate if more memory/CPU is used in that particular step or not. In this example case we can see that __memcmp_se44_1= I =16.399% E = 16.399%. By tracing the RED colouring we can see where most of utilisation is happening and we can trace the activity, which will lead you to the stack in the runtime dump, which will also have the thread number we are looking for

CPU_DOT%202.PNG

CPU_DOT%203.PNG





By viewing the CPU.dot you have now traced the RED trail to the source of the most exclusive. It is now that you open the RTE (Runtime Dump). Working from the bottom up, we can now get an idea of what the stack will look like in the RTE (Runtime Dump).

CPU_DOT%204.PNG




By comparing the RED path, you can see that the path matches exactly with this Stack from the Runtime dump. This stack also has the Thread number at the top of the stack.

So now you have found the thread number in which this query was executed with. So by searching this thread number in the runtime dump we can check for the parent of this thread & check for the child’s related to that parent. This thread number can then be linked back to the query within the runtime dumps. The exact query can now be found, giving you the information on the exact query and also the USER that executed this query.

For more information or queries on HANA CPU please visit Note 2100040 - FAQ: SAP HANA CPU

I hope you find this instructive,

Thank you,