How to capture SQL tracing in HANA from AAO 2.0+ clients?

[justin_molenaur2]

In previous versions of AAO, the connection for client machines was direct to SAP HANA through ODBC connections. In AAO 2.0+ that changes to the HTTP INA technique from what I gather.

So in the past, when there were various issues or performance problems with AAO queries, we could easily run a SQL trace on the user from within HANA and capture the actual activity/statement that was executing on the database. Now, it seems that there is a process that runs (Multidimensional Services) that executes a given plan, but a SQL trace does not return any hits at all.

Has anyone hit this issue and solved it?

Regards,

Justin

[justin_molenaur2]

I am just circling back on this as I got a response from SAP in the past month. Apparently the only known methods for capture the details behind the AAO request is either a python trace or a performance trace, neither of which can be readily analyzed by a non-core HANA support resource (as far as I know).

Just wondering if anyone else has hit this problem and how you approached it. I am getting multiple requests now to be able to do this and I find it hard to believe that there is not a solution.

Regards,

Justin

[filipe_zeuch]

Hi Justin,

I can confirm that from Analysis for Office side, currently there is no way to see the SQL statements that AO sends to HANA database in AO 2.x version. In AO 1.4 version we could see the SQL statements in the log files but now the communication with HANA has been changed (as you also mentioned) and the requests are in a different format.

In this case I believe that the only option is by looking at the traces created in HANA side.

As this is an important topic for you I recommend to request this feature (some way to check the SQL statements) in our Idea Place, referring to SAP note 1879237.

Regards,

Filipe

[justin_molenaur2]

Hey Filipe, thanks for the note. In parallel I am also working on the trace functions from the HANA side.

Unfortunately, there is also a big gap on that side. Due to the change in communication protocol, the requests are now in a different format and there is no obvious solution to be able to troubleshoot performance through simulations.

Long story short - looks like there is no way to be able to analyze query issues with AAO 2.x and HANA.

Regards,

Justin

[filipe_zeuch]

Hi Justin,

I agree with you. This is why I suggested to request such functionality in Idea Place of Analysis for Office.

Since now the connection to HANA is via HTTP, I know that Fiddler traces can also help with some performance analysis, but this is not exactly what you are looking for.

However, since more tools are using the same protocol for the communication with HANA, maybe a good place to get more hints about how to debug these requests that the front-end tools send to HANA database is the HANA space in SCN.

Regards,

Filipe

[justin_molenaur2]

Fiddler will not give the inner workings of the database as you mentioned - that was one of the first responses I received from development.

This is cross posted in HANA space here

Additionally I have opened 2 separate incidents to engage HANA support.

So far, no meaningful information has been surfaced. I'll be sure to respond back here with any new information.

Regards,

Justin

[Former Member]

Hi Justin,

Did you make any progress on this?

I got a query that works fine in AO 1.4 but not in 2.3; I would be very interested in comparing the 2 generated SQL statements...

Thanks,

Sylvain

[justin_molenaur2]

No Sylvain, I pretty much hit a brick wall with a couple of OSS incidents and even an onsite AO expert. There apparently is no "tool for the masses" to be able to do this type of analysis. All that can be done is send the traces to SAP for analysis. There is a way to capture the XML/InA requests, but you really don't have a way to "replay" them in something like a VizPlan would offer.

I can't really see how this wouldn't be required on a go-forward basis. I really hope there is a solution in the pipeline somewhere.

Cheers,

Justin

[Former Member]

OK, thanks for the feedback, at least I won't waste time looking for something that is simply not there! I guess I'll have SAP support look into my issue then...