Very Large InfoCube - To partition or not?

[Former Member]

Hi,

My client has a custom Infocube of Sales data with about 6 years of history. It has grown to be very large and their users are complaining about the performance of their Query workbooks.

I have noted that:

Transaction RSRV - [Database info] tells me that the FACT table has over 123 million records.

Transaction RSRV - [Database Parameters] tells me that there are 0 partitions.

We wish to achieve the following:

1. Migrate the existing cube and data to a new BW instance.

2. Improve the performance of the queries.

We should have some improvement with the new hardware but I am not sure if this will be enough. Our intention is transport the cube to the new instance and then Generate an Export DataSource to transfer the data between the two instances.

My questions are as follows:

1. When we have transported the cube should we look at changing the InfoCube to have partition defined (e.g based on Fiscal year)?

[InfoProvider>Change; Menu: Extras>Partition].

- With 6 years of data, I'm assuming we'd get roughly 6 partitions of 20 million records. I'm guessing that this would have to be done before we load the data. Would this noticeably improve the performance?

2. The other option I am considering is analysing what parameters are typically running within BEX Analyser and creating Query Views. We can then prime the OLAP cache with these Query Views.

Note: My client does not wish to create a new cube as they have hundreds of queries hanging off which they rely on for their business. Therefore, I have to work with what is there.

Many thanks for any help.

Regards

Adrian

[former_member1382010]

Hi Adrian,

We have had a similar issue and the answer was to logically partition it by having different cubes for different years and then join them up with a multiprovider as righly suggested by Jkyle and Siggi.

Even despite doing this query performance will be poor as a multiprovider parallel processing will run out after 5000 rec hits and will then proceed to serial processing.

The solution will be to deactivate parallel processing if you think the hits will be too high. This eliminates the extra processing step and helps in a small way. You could restrict the actual cubes you want to hit in the query built over the multiprovider. This will also speed it up in some way.

However the ultimate solution will be to make this existing cube as an aggregate cube and then have query jumps to the detailed cubes. I know this sounds like a major design change but till you implement this solution, you will always keep on making small tweaks and changes and just manage to keep your head above the water.

Hope this helps.

Cheers,

Zubin

[Former Member]

Hi Friends,

We have created the partitions in ODS, while loading data we have received dumps "DBIF_RSQL_SQL_ERROR" stating Database error text........: "ORA-01502: Index 'SAPBSW./BIC/B0000118000~0' or Partition of such index is in unusable state”.

On analyses we found that index BIC/B0000118000~0 is of table BIC/B0000118000 and this table is change log table of the particular ODS which was portioned.

We have recreated this table/index using SE14, then the data load was successful. We realize that before dropping there was a range partition on field PARTNO, this looks like system created one. After recreating the table this wasn’t there.

Has any one noticed such things or can any one tell tell us what is the implication of this ?.

Please let us know incase if you require more information from our side.

Best Regards,

Ananth

[Former Member]

Hi Adrian,

As a general rule, QUERY performance depends largely on Table Size. The larger the table, the less efficient the Query becomes.

Your plan is ok. Migration to a new machine and partitioning the SALES CUBE to smaller chunks. I advice you to proceed with the YEARLY Chunks for the CUBE Partition.

However, I think the system only allows 0CALMONTH as of 3.1 release. Please confirm...

Remember to do a full backup first. Its a good practice if you messed up the system...

--Jkyle

[Former Member]

Many thanks Jkyle,

We are on 3.0B and it looks like Fiscal Year is an option (along Fiscal Period and Calendar Week) - So I guess I will go with Fiscal Year. Would this be a problem if we were to later upgrade to 3.1?

I am also considering my client's current approach of extracting direct from R/3 to the custom BW Infocube. I suspect this is a little risky given the volume of data and possibility that they may need to rebuild the history.

It am considering changing the extraction process that data is loaded first in a similarly designed ODS and then load from the ODS to the cube. Any comments appreciated.

Regards

Adrian

[Former Member]

Hi Adrian,

Why not try to build separate identical SALES CUBES for each year and join them using Multiprovider? I think its better to maintain that than a single partitioned CUBE.

--Jkyle

[Former Member]

Hi Jkyle,

The main reason for keeping with the existing cube is that the users are set up to run existing queries from that cube.

I think I'll go with the partitioning plan, but am uncertain as to whether my client will be impacted if they upgrade to 3.1 or 3.5 (assuming the partition is on Fiscal Year).

Regards

Adrian

[Former Member]

Hi Adrian,

Jkyle's suggestion is quite useful. I already used this approach in some projects. If you design your cubes identical and additional your multiprovider identical to the basis cubes it is possible to just copy the queries to the multi provider and delete them from the basis cube. The only thing you need is a bit of a logic in the update rules of each cube to separate the data by the year. Each year you enhance your multi provider by an additional cube. This approach will also lead to a lot better performance of your queries. If you want you can additionally partition your basis cubes by the period (if possible).

regards

Siggi