Downsizing a fat ODS to optimaze the processing time!

[Former Member]

Hello all,

Working with SAP NetWeaver BI 7.0, a customizing FI_CO OPC ODS with 300.000.000 records extracted from Sap R/3, was enhanced with 2 new fields and since then it has generated big processing time – about 20 hrs and same times basis team needs to kill it! This ODS has been loading a Cube and the new enhanced fields will be loaded to another Cube.

As a newbie and Sap BI also newbie at this project, I’ve been requested to analyze the model and give a better option to optimize the loading process, options such as should this ODS be deleted and data be extracted straight to the Cubes, or downsizing this ODS by removing the olds records of it and saving it in other new ODS?

Anyone could come up with a bright idea?

Thanks in advanced

[Former Member]

Hi Bia

ODS runtime is increased to 20 hrs after new field addition. Yes the real problem is reindexing for all the records for this 300.000.000 values if your new fields are loaded for retrospective data.

If the new fields are for only new records, this problem may not be that.

You can avoid this by 2 ways.

1. Build a new ODS and split up the load between 2 ODS and finally take the data to a secondary staging ODS layer. Ensure both the ODS have same primary keys to upload in the second ODS consistent

Your will extract data to both the ODS from the source and datamart to secondary staging ODS

2. If your sure reindexing is the cause,the best way you can go for new ODS . But this is not the apt design in future if further more fields needs to be added. In FI reports the change of requirements are much in vogue

Regards

N Ganesh

[Former Member]

Hi all,

Thanks for all replies (Alex & Jorge)…

But I've just learned today, the problem above is not exactly what is going on! In reality the problem is not about processing overtime while loading the COPS ODS, but after the 2 new attributes were added to the structure COPS ODS the "Transport" of it is taking big overtime, caused by the needy of BW to re-index the ODS since the 300.0000.000 records are already there! As I said before, I need to redesign the model and suggest some good options to solve the problem. Some options could be deleting data from ODS and keeping the InfoCubes; or deleting data prior to current fiscal year from COPS ODS and keeping historical InfoCubes; or updating everything then perform logical partitioning then keep details in the ODS and aggregate data in Infocubes, also could be moving all COPS ODS data to a temp, apply OPC changes to it and then move data back!

I need to show the advantage or disadvantage of my options and “must choose” the better solution!

Please, any good suggestion?

Many thanks in advance

Bia

[Former Member]

Hi Bia,

If you are having 3,000,000,000 records in one DSO, then there is a serious concern in your design..Do you know how much history you have in the DSO? You can consult your business and arhive your history or delete it based on the business decision. You can also physically and logically partition your DSO into more than one and split the data loads across different DSO and cubes and later combine them in your query using multi providers.

If you dont wanna do any of this and if your DSO is not used for reporting, then you can remove the SID generation from the DSO settings which will improve the activation time.

Also check the runtime settings of the DSO and make sure you have enough parallel processing and wait time. Keep the package size at a medium size instead of having it really big...its again system and your load dependent.

Check that in transaction RSODSO_SETTINGS or you can do that through the administration - current settings - dso settings.

Let us know what solution you are opting to solve this issue.

Regards,

Kadriks

[Former Member]

It could be because you add these fields to the "key fields". If you can add or move field from "key fields" to "data fields", the performance should improve.

[Former Member]

Bia,

I understand the issue. But could you explain a bit more about the further targets where it gets loaded? and what is the volumne of daily load? and what is exactly your requirement.

Thanks,

Alex(Arthur Samson)