Optimize Data load from customized DataSource to Cube

[Former Member]

Dear Gurus,

We have created a customized datasource to load Currency/Exchange Rates to Cube. to load the data we have created a chain for the same. flow is as below.

1. Process type delete Data target content

2. Info package for customized data source

3. delete Index process type for cube

4. DTP with full update in process PSA -> CUBE

5. create Index process type for cube

6. PSA deletion process type

Business requirement is that we load the fresh data all the time. Source Data is very flat and so the transformation. DTP take forever to load the data.

Please advise what to do inorder to optimize this... basically how can we optimize it at the DTP Level.

Hoping for an early response.

Thanks,

Dhruv

[Former Member · Accepted solution]

Hi Dhruv,

Go to DTP request 'MONITOR' screen and expand all the nodes within 'Data Package 1' and analyze the 'Duration' details against each of the extraction step under this Data Package. You can then come to conclusion which step is taking time and what can be done to reduce the processing time of that step.

Regards,

Ravi Poluru

Assigning Points = Saying Thanks

[Former Member · Accepted solution]

Dear Ravi,

That's the tricky part.

Few of the DTPs Data Package ( 20000 Data Records ) are taking 11-15 secs to complete and few of them are taking 1:30hrs+.

How can I optimize the DTP.

Thanks,

Dhruv

[Former Member · Accepted solution]

There must be start routine or end rotuine or field level routines...that's the reason its taking time..

what's the total no. of records you are loading to cube in one go?

Regards

[Former Member]

ANALYSIS

first of all pls check the dimension table to fact table measures .. using SE38 : SAP_INFOCUBE_DESIGNS

The ratio of size of fact table to dimension table is ideally should be 10:1. That means the size of the dimension tables should be 10% of the size of the fact table. This serves better in terms of performance.

Whenever you load any cube, you can easily find out the number of records in fact table (E and F table) and the dimension tables. Based on that statistics you can determine the ratio of size in fact and dimension tables.

FIX

if U see the dimenion table is more than 10% then split that single dimenion into 2 or more based on the data relationship, if more cardinality then use HIGH CARDINALITY, and if only one char. in dimension also check LINE ITEM DIM.

but it should be done by taking the whole data backup and if it's full load not needed.

Regards,

VJ

[Former Member]

The DTP, which picks up data from PSA and loads to Cube, is behaving very unusual. It remains stuck suddenly for a load for hours and works perfectly sometimes (just for one request from PSA picked).May be, because of system load at times, Data-packages in DTP get stuck. Surprisingly if a stuck DTP is repeated after cancelling the stuck job, it gets loaded in 2-3 minutes. DTP parameters look good to me. Is there anything specific which could be reason of performance issue?

How can I improve the performance?

Thanks in anticipation.

[Former Member]

If it could be a FULL, then delete the previous requests from PSA

OR

If it is delta then u need to DO INIT and next loads as delta...

then it will improve the DTP performance.

[Former Member]

Which step in the DTP is taking long when it does take a long time? Is it Conversion to SID?

If so I'd recommend a couple things.

a) Instead of 1 Custom Dimension. I'd do 3 Custom dimensions each marked as "Line Item Dimensions"

b) If the 3 InfoObjects within those 3 new dimensions get master data loaded every night you're okay. (If you load master data it generates SID's for this InfoObjects at that time)

If no master date, then you may need to add Number Range Buffering to those 3 InfoObjects. Which can be changed using T-Code SNRO

If it's the Rules that take a long time then you'd have to trace it to see what it's doing for so long. Maybe look at SM50 or use some other trace to see what table it's reading/writing to.

[former_member188080]

Hi Dhruv,

Try to use following steps in DTP

1. Try to use delta DTP instead of full DTP.

2. Try to use semantic keys

3. Try to make serial extraction and parallel processing in DTP

4. Try to see data packet setting in DTP

5. See if certain year data can be made fix load and again should not be loaded.

Thanks and regards

[Former Member]

Hi Druv,

1) how much data you get daily for this?

2) In Cube how many fields are there?

3) How many diamensions are created?

4) What is a use of this Cube for reporting or refered somewhere?

Please check this question, as DTP is taking time then issue can be at target and DID and SID generation is taking time i.e. Cube.

Regards,

Ganesh

[Former Member]

Dear Ganesh,

Below are the answers to your questions:

1) how much data you get daily for this?

approx - 778,000 records daily

2) In Cube how many fields are there?

3 under Data Package Dim

9 under Time Dim

3 under custom Dim

3) How many diamensions are created?

1 custom dim

4) What is a use of this Cube for reporting or refered somewhere?

This is referred for reporting

Thanks,

Dhruv

[Former Member]

Hi dhruv,

First check you can make this as delta instead of full.

If you can do this delta then don't delete psa data daily.

If you can not make this delta, try to change your data flow as below.

1) delete psa step

2) load the data from r3 to psa as full.

3) delete the cube indexes.

4) drop cube.

5) load the dtp from psa to cube as full

6) create the index step

Regards,

Venkatesh.