Improve Third Party Apps Data Consumption from Datasphere

[rebeca_elvetia]

Hello experts,

We have been experimenting long data retrieval durations when consuming views created in SAP Datasphere in Third Party apps like Excel and Power BI.

We consume data via ODBC Connection (changing this is not an option for us), so we use the Database user from each space we wish to consume data in order to make a successful connection.

Every graphical view or SQL view created, uses as data sources remote tables. We work through data federation as we cannot store data on the cloud due to security politics. The remote tables have as main source On-premise SAP HANA Warehouse, connected to Datasphere through a DPA (Data Provisioning Agent).

We have various cases, some of them, we identified the SQL query is complex and that's why it takes a long time.

But other cases, when the SQL Query is simple, but the number of rows is high (600,000) we suppose we should have a better time when retrieving that data (as with other tools the time is considerable smaller).

Is there any parameter we could modify in the DPA configuration, HANA configuration or in Datasphere, so we can improve the data retrieval times?

Thank you.

[klorn_pallier]

We haven't tried this yet ourselves but have a look at the SAP note below:https://userapps.support.sap.com/sap/support/knowledge/en/2737656

[Martin_Kuma]

Hi Rebeca, if the note 2737656 does not help, I hope it will :), you will need to deep dive into the SQLs.

For example, is it the first time where you have slow performance in complex SQLs? Or every time you start the very same SQL? Compilation / HANA creates exec plan - this takes time.

Variables? Or Parameters used? Variables - HANA will create exec plan for each different variable, whereas parameters only once, where the parameters are not part of the exec / hash.  

HANA Warehouse - I assume you mean BW. BW 7.5, or BW/4? Do you consume the exposed models - HCPRs or create own VDM-likes? If exposed views, check the complexity via PlanViz (what you have to do anyway...), you can copy the exposed VDM and remove all texts f.e. reducing the complexity of the exposed view self. Check just how long the exposed view loads in studio - if it takes like 15-20+ Seconds, it is complex 🙂

Also HANA Hints are good to try out. Unfolding f.e. helps a lot. 

Complex views, complex issues 🙂

Let us know the progress please. I am sure performance issues during federation will popup very often 🙂

Cheers

Martin