How does SID generation on DSO level affect InfoCube load?

[Former Member]

Hi experts,

I am having big performance issue with data load to InfoCube. In the DTP monitor, the step "Conversion of Characteristic Values to SID" takes about 7-8 mins for each data package of 50000 records. I need to load about 20 millions data & this would take ages.

Below illustrates part of the data flow in my scenario:

DSO A   
DSO B   ======>  DSO X =====> Cube 
DSO C   

In the entry layer, I have DSO A, B, and C which contain header, line item records as well as master data. The option "Generate SID upon Activation" is checked for DSO A, B, & C.

Records from these 3 DSOs are consolidated & enriched into DSO X. In order to reduce load time I have unchecked "Generate SID upon Activation" for DSO X.

Data from DSO X will be written to the Cube 1:1 without any additional transformation logic.

My questions:

1. Since I have generated SIDs for the chars in entry layer, why it still takes so much time to for the SID conversion during Cube load?

2. In this case, is it necessary to generated SIDs in DSO X?

Thanks in advance for any advice. I am in desperate need for a solution to this load problem

Regards,

Meng

[Former Member]

Hi All,

Simillar to the above topic, i have one doubt.

In my case we uncheck the SID generatin upon activation option for a DSO to reduce to activation time, since there is no report developed on it.

After one yr we got one new requirement which leads to devloped a report on this DSO. Now we planned to check the SID generation upon activation option.

Here my doubt is after the changes moved to production system data reload is required.

[RamanKorrapati]

Hi JL,

Data load is not mandatory but during bex report time it may take more time to get old data as there is no SID's. chance to get SID issue at bex level.

You need to maintain SIDs then only you can do reporting on DSO other wise it will be problematic.

Thanks

[Charan-V]

Hi All,

I have an issue similar to above. we have load from DSO to Cube. Normally this DTP takes very less time (58min), but some times it takes 2h 30min and when I checked at detailed level most of the killing time is at "Conversion of Characteristic Values to SIDs"(but records are less only). As suggested above I have ran the report SAP_INFOCUBE_DESIGNS and there is no problem at DIM level below screen shot FR.

at this case can we go for Deleting dimension table before cube loading..?, or is there any other solution for DTP performance improvement.

[RamanKorrapati]

Hi,

if possible try to do RSRV test for your SID consistency check.

Thanks

[Former Member]

Hi,

Load Master Data and activate it before before loading Transaction data... Reverse SID creation will degrade load performance.

-

---

Thanks

BVR

[Former Member]

Hi BVR,

thanks for the reply. What is a "reverse SID creation"?

Thanks,

Meng

[Former Member]

Hi Merg,

The moment when you load master data and activate the same , Surrogate ID will be generated in SID Table. If you load fail to load master data before loading transaction data 'based on setting' system will create reverse sid's. While loading transaction if system is generating SID for missing master data will degrade your data loading performance. It is better practice to load MD before loading TD.

http://help.sap.com/bp_bw370/documentation/Multi_Dimensional_Modeling.pdf

-

---

Thanks

BVR

[former_member188080]

Hi,

There are following things which you can do

1. Remove the setting of SID generation in DSO if you are not reporting on DSO

2.Check whether you have got multiple processes running at the time of cube loading.

3. See SM50 got sufficient free processes

4. Try to put some parallel loads if possible

5. If it is 3.x system load the data without PSA.

6. Check with basis in case there is any memory issue

I hope above helps.

Thanks and regards

[Former Member]

Hi experts,

first of all thanks for your reply.

I have carried out following test:

1. Deleted request in DSO X
2. Checked "SID Generation upon Activation"
3. Loaded data to DSO X. Load time increased from 12min to 17min
4. Activated data in DSO X. Activation time increased from 22min to 1h 11min
5. Loaded data from DSO X to Cube. The average time for the process step "Conversion of Characteristic Values to SID" dropped from about 7-8min to 2-3min

My questions:

1. It seems that the option "Generate SID upon Activation" on DSO X have positive effect on the Cube load. Why?

2. Please correct my understanding: If I check for SID on DSO, new SIDs will be created for new char values. So, for further data load to Cube less SIDs are to be generated. Is this correct? I am confused about the effect of SID generation on DSO level on Cube load...

Thanks,

Meng

[Former Member]

Hi Kiran,

thanks for your reply. Since I am quite new to BI, I would like to ask the following questions

1. Remove the setting of SID generation in DSO if you are not reporting on DSO

Is it correct to say SID generation in DSO helps create new SIDs for new char values? I thought this would improve Cube load as well since less SIDs need to be generated during Cube load. Please kindly rectify my assumption.

2.Check whether you have got multiple processes running at the time of cube loading.

which tcode should I use?

3. See SM50 got sufficient free processes 

I checked in SM50, only 3 running BTC processes from me. There are another 5 BTC processes but with the status "waiting". Could you please explain what does that mean?

4. Try to put some parallel loads if possible

I have changed the processes to 5 but I still only see 3 processes. What could be the reason?

Looking forward to your reply. Thanks!

Regards,

Meng

[Former Member]

By Mistake posted it here..sorry

Edited by: Lexmark AMS BI Support AMS BI TEAM ID on Aug 30, 2011 9:47 AM

[anindya_bose]

Hi Meng

> 1. It seems that the option "Generate SID upon Activation" on DSO X have positive effect on the Cube load. Why?

Before running the DTP once again I assume you have deleted the data from cube. Did you delete the request ?

You can choose delete data from the context menu of cube and there after delete DIM Ids. Run the DTP again and check how much time its is taking in "Conversion of Charac......."

Most probably as you already had SIDs and DIM Ids created during the first load , 2nd time when you are running DTP without deleting the DIM ids that particular steps take much less time

Regards

Anindya

[Former Member]

Hi Anindya，

did you mean that the performance improvement was because the DIM ids were not deleted?

I have aborted (change status to red) and deleted the request. So does this imply that some of the DIM IDs were created and not removed?

I think your assumption might be correct because the drop in time for "conversion of char..." only applied for the first few data packages. After that the time increased to 5-6mins again...

However, could you please validate my following understanding?

-

---

Cube load program will check for the combination of char values in DIM table. If a combination already exists (= DIM ID exists), the program will do nothing and continue with the next record. If this is a new combination (= DIM ID does not exist), the program will need to fetch new DIM ID from database.

Consequence: By keeping the old DIM IDs, the time needed for "Conversion of char.." reduces..

-

---

Thanks again.

Regards

Meng

[anindya_bose]

Yes Meng..

If you look at my first post I said the same thing .

Let's say we have one dimension Material and we have 2 characteristics 0Material 0Prodh4 ( Product Line) under Material dimension.

Now to have a dimension id you need combination of SIDs of Material and Product Line . During loading to cube system checks the SIDs of a characteristics from master data table in the "Conversion of Characteristics to SID" steps and generate the Dimension ids. This is the steps which is taking time in your case.

Basically there can be two reasons.

1. DIM ids are not present ( If you Right Click on the cube---->Delete Data , it will pop up another window to confirm whether you want to keep dimension ids or not , if you select 'No" Dimension ids will be deleted. )

2. Your dimension table is very large , in that case to read the dimension table and fill accordingly will take much time.

You can run report SAP_INFOCUBE_DESIGNS from SE38/SA38. This will give you the information whether you have bad dimension modeling.

Generating SIDs for DSO is only relevant if you are reporting on that DSO.

If you want to know why do we generate SIDs for DSO while its a flat structure you can look at the below thread

Regards

Anindya

[Former Member]

Hi,

Generation of SID upon Activation" from DSO is required if you have to do reporting on DSO if not remove it it will improve perfromance.

Thanks,

[anindya_bose]

Hi Joon

Some questions..

1. Do you delete and reload this cube every time? If yes, do you delete Dimension Ids as well when delete the cube data ? [ Check in Process Chain settings]

Deleting dimension table before cube loading increase the time in in step "Conversion of Characteristic Values to SID" as to create dimension ids system needs to read all the SIDs and with a combination of SIDs dimension ids get generated.

2. Are your Cube dimensions more than 10% of the Fact Table? Or do you have very big dimension table? Please run report SAP_INFOCUBE_DESIGNS to check if you have problem with dimension size. System will show 'bad' dimension in red for a particular cube

You can also think of Number Range Buffering so create DIM and SIDs faster.

Generating and not generating SIDs for your DSO X should not have any impact on the DTP load to cube.

Regards

Anindya

Edited by: Anindya Bose on Aug 30, 2011 3:01 AM

[former_member199691]

Remove "Generation of SID upon Activation" from DSO A, B & C and then see if it improves the performance.

Regards,

Gaurav

[Former Member]

Hi Gaurav,

could you please explain briefly why the removal of "Generation of SID upon Activation" from DSO A, B & C could improve performance of data load from DSO X to Cube?

Thanks very much.

Regards,

Meng