Strategy to handle Historical data in SAP BW

[aluri_hemanth]

Hello Gurus,

I have a question @ strategy level what is the approach to handle Historical data in SAP BW.

1. How do we plan to store historical data in BW. For example do we keep 1 year data in each DSO and 10 years in Inforcube?
2. How do we will achieve the data for more than 3 year?
3. How easy is to retrieve the data for reporting?

Could you please help me to provide some Best practices and Guidelines what is the approach and steps to be followed ?

Thanks,

HA

[sander_vanwilligen · Accepted solution]

Hi,

Starting point for Information Lifecycle Management are the business users' requirements for storing the data for a certain time period. This will depend on the type of information. So it will be wise to categorize the data and define data retention periods per category.

The technical solution is partitioning. Logical partitioning on a Time Characteristic like Calendar Year or Fiscal Year is very useful. This can be combined with table partitioning on Calendar Month or Fiscal Period depending on your database.

This will give you a scalable solution and easy to handle from a data management point-of-view. You can for example store certain logical partitions on Near-line Storage (NLS) if you have that in place. Also dropping the oldest partition is quite easy to manage.

For reporting a MultiProvider can be used as a virtual layer between persistent InfoProviders and BEx Queries.

A note on data volumes per InfoProvider (PartProvider): it does not make sense to use logical partitioning with very small data sets. It will depend on your database but be aware that data volumes of 200 or 300 million records per InfoProvider are possible for databases like Oracle and DB2. I would suggest to have a lower limit per InfoProvider and also take the advantages of "yearly" partitions into account. But if you have less than e.g. 10 million records, I would not recommend using logical partitioning.

I would recommend to have use SPO (Semantically Partitioned Object) to facilitate the logical partitioning. Please have a look at my blog for more information and an idea how you can introduce partitioning patterns using the SPO BAdI.

Best regards,

Sander

[aluri_hemanth · Accepted solution]

Thanks All,

If  my Client would like to bring 3 years of history data into SAP BW, please provide below details.

1. what are the advantage and disadvantages
2. High level solution / options

Could you please share me these details

Thanks,

HA

[sander_vanwilligen · Accepted solution]

Hi,

Please review my reply of Feb 4, 2015 11:43 PM. Here I tried to highlight the advantages/disadvantages as well as a high level solution and options.

If you have further questions, please be more specific.

Best regards,

Sander

[former_member214415]

Hi H A,

It would be good if you dont keep all data in one infocube. so better to create more infocubes based on year and create multiprovider on top of it.By doing this, performance will be improved because it will hit that particular infocube only with data.

And from the reporting aspect, you should create multiprovider on top of the infocubes and restrict particular year infocube by 0infoprovider in multiprovider.So your reporting performance would be improved.

Thanks,

Swapna Jain

[former_member194898]

Hi,

Instead o keeping data in one datacube you can divide it into more than one and build a multiprovider on them.

eg.

You have a datacube 0coom_c02 and a huge amount of data in it.

1. copy cube to eg. zcoom_c13,zcoom_c14,zcoom_c15 for keeping data recorded in years 2013,2014,2015.

2. built a dataflows to those cubes.

3. filter on dtp level on eg. 0fiscper to load exact year's data to given cube. 

4. build a multiprovider zcoom_m01 on those cubes

5. move querries which were built on 0coom_c02 to zcoom_m01.

Hope this helps,

Leszek