Datasphere performance issue

[Charlielin]

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

[ravikiran_sap]

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

[Charlielin]

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.

[Martin_Kuma]

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

[Charlielin]

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