InfoCube Partition ...

[Former Member]

Hello Folks,

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.

1.

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 couldn’t see any information indicating the FACT table records

and the available partitions? Any guide?

2.

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?

3.

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.

3.

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, shouldn’t 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?

4.

…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?

Thanks Caud.

[Former Member]

I can answer few of your questions..

1. No Idea..

2. The query need not be changed. The userss need not know abt what partition the query will be running against. The BW Experts based on the query statistics decide the Partition..which can be either on Fiscal year, or on regions etc depending upon the data..

3. I am not aware of any way to copy a query based on a cube to base it on the multiprovider..In the update rules we could put a start routine based on Fiscal year to populate the records belonging to a particular FY only..

4. If you have a query which can run on a single cube you won`t build it on a multiprovider..

5. I am not sure abt transactional cubes and ODS ..but normally the queryruns against the data which was last loaded in the cube/ods and is active for reporting..its not the real time R/3 data..

Hope this helps..

Ashish.

[Former Member]

Caud,

just a suggestion from a fellow SDNer...avoid to post too many questions in only one post since:

• when I opened your post I said: "Wow, I don't have enough time to read everything..sorry!"

• our BW experts want to help and to be rewarded...if you post 10 questions in only one thread, who solves your problem will receive 10 points...but if you post 10 threads with only one question inside, who solves your problems will receive 100 points !!!

and other strange behaviours, but I think that here are the main !

Bye,

Roberto

[Former Member]

lol Roberto,

I guess recongnition is always nice. BTW Congrats for having more than 2x the points then the next persion on the BW list. Having just broken the 100 barrier earlier today, it is a lot of work to answer these question lol.

Normally I am only logging in once a week if that but with the push back of the start of a new project by a couple of weeks, I am on SDN downloading or looking at recent presentations...

Cheers, Mary

[Former Member]

Hi Mary !

Just to say that I'm really appreciating your answers in these forums...you show a great authority on these subjects (and expert like you make this place the best place)...and, sincerely, I'm on the top of the contributors not because I'm so insurmountable, but simply because I've the possibility to stay on-line during my daily job (that is from 9am to 10pm from mon to fri)...this summer I will get married, I will stop my contribution for a 5 weeks period...so, a new top contributor will come !

Thanks for your hospitality, Caud!

Bye,

Roberto

[Former Member]

Hi Robert,

Congrats in Advance ...

Just wanted to take this opportunity to thank you and to let you know that I have really learnt a lot from your posts since I started on BW..

Ashish.

[Former Member]

HI Roberto,

I am more on the BPS side of the fence these days lol. But I still keep my hand in BW and perfectly willing to pitch in if the BW team is overloaded and they are okay with me building my/our own BW cubes, transformations, etc.

I have to admit I am more used to participating on e-mail based forums, including an SEM one I co-moderate, since they show up in my mailbox and there is less work involved. I just figured out today how to get e-mail notification from SDN forums so might activate that for both BW forums and keep a more active eye on the forums going forward.

This is a nice community, like the early days of SAPFAQ-BW and SAP-R3-BW... To be the top contributor, you invested a lot of time into the community so it is recognized and appreciated :). Althought if you are getting married this summer, you might decide to have more of a life and keep your hours more contained... lol

Ciao,

Mary

I had notice Marc is not around last week and this (maybe on vacation?) so pitched in for the BPS questions. With the updates and download new stuff off SDN, I can download in one or more screens and participate in the forums at the same time lol.

[Former Member]

Thanks,

I will re-post the parts which have not yet been answered in smller chunks.

[Former Member]

Partioning is basically done to improve the Query Performance.

In the scenarios discussed below.....

Partitioning can be done on Fiscal year or Posting date range.

Partition the large cube building different cubes for each year and multi providing on each of them was not that effective till BW 3.0.

With SAP BW 3.5 , queries created on this multi Providers run very fast.

As such its better to partition the mail cube by fiscal year and report against the cube using proper selection of keys ...and the OLAP engine will hit the right area.

Example you partition the cube by posting date and run the query on material posting date ...OLAP engine has to do a full table scan anyway's ....this should not help ....

Partition should be done on the right fields and would definitely improve the performance.....

Let me know

[Former Member]

Thanks and you shed more light on the topic of partition and I can always use that, thanks.

On the other hand, I was looking for specific reponses to a number of questions in my post but none of them was addressed.

I look forward to the specific responses.

Thanks.

[Former Member]

Please help.... still not answered....