How to get the exact Open sql query for a Composit Provider when execued?

[soumyabrata_kaity]

How to get the exact Open SQL query a Composite Provider generating when executed ?

[soumyabrata_kaity]

Hi Andrei ,

Thanks !!for your feedback.

I am looking for cases without "External SAP HANA View".

I have tried ST05 and HANA SQL traces.

Could not find the exact SQL statement.In ST05 i can see that its using column view for the HCPR-.

In HANA also I cant see any related SQL statement on column view for the HCPR.

But in case for HANA Queries directly executed in HANA SQL Console i can see them in HANA SQL trace file.

[yi]

I don't have access to a system now, but if you dig into that column view it might turn out as an XML calculation scenario, that doesn't have any underlying SQL statements. Waiting for someone with deeper understanding...

[soumyabrata_kaity]

Hi,

Thanks for the reply.

From RSRT I can only get Python trace and TREX API as below for a Query built on a HCPR , But no Proper Open SQL.But I am wondering from the performance improvement perspective that if I Need to use any HINT for the Composite Provider to work efficiently;Without the SQL i will not be able to find out the Explain Plan of the query to Optimize it further.

Any thoughts?

[yi]

1. Ok, it looks like I was more or less right - after checking this and this page it is now clear that BW providers use Column Views (AKA Calculation Scenarios) to provide data. Column views look like this:

So probably you won't be able to see SQL, because there is probably no SQL as such:

In RSRT it is possible to switch off the usage of the trex API and force the query to use SQL (but this is e.g. possible for an Infocube but not e.g. for a HCPR which always uses the trex API and column views):

The closest you can get to SQL is probably this diagram that actually looks pretty informative:

So how does that look for you now?

[yi]

Hi,

Good question,

There are a number of ways that you can trace SQL statements, but what comes to my mind is that composite providers could be represented by "analytical scenarios", which are in fact XML files that represent the analytical "query" underneath the provider (basically it's a "tree" of analytical calculations, something like calculation view). I don't remember exactly whether these scenarios result in SQL queries or not, so I think it worth trying below methods to find the SQL:

* SQL Trace - the most complex, but the most reliable way. Set the trace on, query the data, and set it off.

* Set the "External SAP HANA View" flag on the CP, then analyze the view generated - not the most accurate way, not an SQL query, but it's fairly easy to do and might help you.

* There is a view that looks like "_SYS_BIC"."system-local.bw.bw2hana/<COMPOSITE_PROVIDER_NAME>" - worth checking! Also, if you open a new query and write a simple SELECT statement from the above view, you could profile it and then hopefully see a beautiful graphical analysis of your statement with all source tables and their performance metrics, so you could see what tables or operators are the most "heavy". Just look for the "Profiler" icon or menu item/function.

* HANA Studio / Web IDE admin tools - there are a lot of tabs and somewhere there you can see active processes and statements, this is a bit tricky to find.

* If the above admin tools are not available, it is worth trying transaction DB02, if applicable.

* Also, there is an SAP note with powerful HANA "admin" SQL queries where you can find all sorts of "basis" queries. It's basically just dosens of text files with very complex SQL queries to analyze your HANA database. I'm sure there is something for you there, but it could take a while to find it.

* Not sure if the RSRT trace tools will work, but also worth trying.

* If none of the above work, I would try ST01 or equivalents and then LISTCUBE.

I hope this helps,

Andrei Dorfman