I was trying to understand more about infoCube partition when I came across a discussion on this site, which almost made me a Pro right away but I realized that I was left with more questions than answers.
I will try not to go into details on the discussion(on this site) with title "Very Large InfoCube - To partition of not?" by Zubin Limbuvala, but a few lines from those discussions and my questions are low:
In the discussion that ensued, a large InfoCube was to be
a) either portioned or,
b) to create smaller InfoCubes and join them using MultiProvider.
There was a discussion about running the transaction RSRV for this reason:
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
In testing, I logged on to BW 3.1 but from here, I couldnt see any information indicating the FACT table records
and the available partitions? Any guide?
So when you have a Cube that queries depend on, and you partition it, how will users queries be impacted? Will queries have to be modified in any way? How will the queries access these smaller Cubes and will users have to know that their queries depend on a particular partition?
Also, in the discussions there was an alternate suggestion, i.e. instead of partitioning the large Cube, to create smaller similar Cubes and join them using a MultiProvider.
One of the participants noted that
. 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.
--Can someone clarify the line 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. ..that is where I got lost.
You may also touch a bit on the update rules although I seem to have a rough idea.
Also, another person argued that
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 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.
--Can you explain the multiprovider parallel processing vs. serial processing a bit? And is this taking place on the BW or R/3 side? ... and if R/3, shouldnt all the transaction data already be loaded into the infocube on the BW side already?
-- and if on BW side, how do you do you set this processing type in BW?
--Can you explain also . You could restrict the actual cubes you want to hit in the query built over the multiprovider ?
If you are to restrict the query to one of the cubes then why the need for the MultiProvider?
By the way, are BW queries pulling real time data? I.e. do they pull data from R/3 as the query run or only data from the loaded infoproviders on the BW side?