Question Re: Datasphere performance issue in Technology Q&A

Datasphere performance issue

Charlielin — Wed, 28 Aug 2024 11:35:58 GMT

Dear Experts,

I am facing some performance issues with datasphere modeling. Here is my design structures:

Background: We have to create SAC report based on COPA Actual/Plan Data and some other External Data as well.

Modeling Steps refer to above:

1. Preload COPA Actual Data(20 M Records), Budget Data(1 M Records), External Data(10 K Records) to local Table and store table data in memory. Then create views based on local tables and make projections to reduce the no. of columns. Filters and Aggregations are also applied to reduce the total no. of records.

2. Use Input parameters to limit amount of data access.

3. Create UNION for Actual and Plan data

4. Create UNION for External data and above(3)

5. Aggregation based on final data structure

6. Various Calculated column(simple calculation) created

7. Fact source view created(Measures: 110 / Attribute: 45) and used by Analytic Model. Till this point, no. of records have reduced to 2M

SAC then use this Analytic Model for Reporting

Now here comes the trouble:

1. When I Preview data in the Analytic Model, it takes 30~40 seconds to show the result, the acceptable response time is within 10 secs.

2. While the model is consumed by SAC, as we create more calculations(YTD, Last year YTD...etc) SAC crashes.

Is there any suggestion for performance improvement ?

Thanks a lot

Re: Datasphere performance issue

RajKosnam8087 — Wed, 28 Aug 2024 11:23:19 GMT

Hi,

I would suggest please refer note 2511489 for troubleshooting the performance issues in SAC.

Thanks,

Raj

Re: Datasphere performance issue

JulianJuraske — Mon, 02 Sep 2024 04:59:36 GMT

Hello Charlielin,

i would recommend you to have a look at his Blog https://community.sap.com/t5/technology-blogs-by-members/performance-analysis-in-the-sap-datasphere/ba-p/13548256.

After Reading this Blog you should be familiar on how to analyse the Performance on each step done by the SAP HANA Database thats underneath the SAP Datasphere.

Best Regards
Julian

Re: Datasphere performance issue

Martin_Kuma — Tue, 10 Sep 2024 11:05:59 GMT

Hi,

# SAC crashes - OoM?

# In DSP you can get PlanViz file, which you can analyze in Studio. Could help, but not for the SAC Crash.

# For SAC there is the the Performance Analysis Tool. But before you deep dive there, go to DSP. The 40 seconds runtime is quite a lot for the 20M entries. First you need to get the runtime in DSP down - can bypass by persisting the view and consuming in SAC via analytic model.

I would:

1. lower the complexity. Remove the calculations in step#6 / remove the step #6 . Check the runtime. I assume the calculations between step #2 and #3 are necessary for the union in stepp #3, if not, remove them as well. Check if you go for runtime < 10 seconds.

2. persist this view. In the same task chain, where you have the COPA source tables, add step to persist this view and then re-use it in analytic model, which you consume in SAC.

3. How do you connect DSP to SAC? What do you consume? Try analytic model - Worked for me the best way.

Martin

Re: Datasphere performance issue

Charlielin — Tue, 10 Sep 2024 11:12:14 GMT

Thanks for your reply. I've analyzed the PlanViz file and change the model and now it's down to 2 secs in the preview of analytical model. But when it's directly consumed by SAC, it takes almost 2 mins to bring out the result. Guess i have to work with the analysis tools with SAC

Re: Datasphere performance issue

JulianJuraske — Tue, 10 Sep 2024 12:03:49 GMT

https://community.sap.com/t5/technology-blogs-by-sap/troubleshooting-of-performance-issues-between-sap-analytics-cloud-sac-and/ba-p/13801417

Re: Datasphere performance issue

ravikiran_sap — Mon, 07 Apr 2025 03:28:35 GMT

Charlielin - did you fix the performance issue ? if yes let us know

Re: Datasphere performance issue

Charlielin — Mon, 07 Apr 2025 09:53:14 GMT

We finally cut the SAC reporting time from 2 mins to around 10 sec.

The root cause is the complex logic on SAC that generated a huge amount of MDS statement and Datasphere is unable to consume it efficiently.

We overcome this issue by taking the complex logic prior in ERP using ABAP and stored the pre-processed data in add-on table for Datasphere.

For direct connection(SAC to Datasphere), it's better to avoid too much logic design on SAC.

Re: Datasphere performance issue

Martin_Kuma — Tue, 08 Apr 2025 15:01:05 GMT

Hey there :). Just wondering, did you try to persist the final COPA View?

Re: Datasphere performance issue

Charlielin — Wed, 09 Apr 2025 01:19:41 GMT

Yes, we did persist the final view and that helped quite a lot. But until that point, reporting time is still over 2 mins so we went for antoher approach