Table partition?

[Former Member]

Hi BW gurus's

Anybody can explain TABLE PARTITIONING and in which situations we r going for this option?

Thanks in advance.

SS Kumar

sskumar2780@gmail.com

[Former Member]

I'd search SDN on Partitioning, there are lots of threads on the topic - always the first thing to do when you have a question.

For most purposes, Cube Partitioning and Table Partitioning are usually talking about the same thing, but you can also partition an ODS, but that is usually done by a DBA, as there is no option in the Admin Workbench to do this.

Don't confuse logical partitionin with physical partitioning. Logical refers to creating multiple cubes with different data in each, e.g. 2004 data in cube 1, 2005 data in cube 2, 2006 data in cube 3, then using a multiprovider to query across some or all the underlying cubes.

Physical partitioning (generally done with larger cubes) creates different segemtns within the same table. There are couple advantages to partitioning, when going to archive or use selective deletion based on the partitioning characteristic, the BW will drop the partition (quick deletion of the data) rather than having to run a resource intensive delete query. The other reason for partitioning is to improve query performance. The DB issmart enough to "prune" unneeded partitions from consideration, e.g. you have a cube partitioned on 0CALMONTH with five years of data in it. If it is partitioned, and a use runs a query selecting just one, or several months, the database is able to ignore the partitions that do not match the 0CALMONTH criteria being used. It won't help a query that doesn't restrict on teh partitioning characteristic.

Partitioning is <b>not</b> supported on all DBs - see the NW 2004s link.

Couple of corrections to some of the other posts -

The only two cube partitioning options are either <b>0CALMONTH or 0FISCPER</b>.

(0CALYEAR Isn't an option).

NW2004s has the same restrictions on 0CALMONTH or 0FISCPER.

An ODS can be partitioned on just about anything.

Before partitioning a production cube, I would review what you want to do with your DBA.

Check this Help page

http://help.sap.com/saphelp_nw04s/helpdata/en/33/dc2038aa3bcd23e10000009b38f8cf/content.htm

[Former Member]

Hi Kumar

Table partitioning is done for the performance improvemnt in BW. In cube you have option of defining the partitioning based on calmonth or cal year basis. As of now till BW 3.5 you had these two options but in BI 7.0 you can use all the char available in cube.

This is jus to partintion the data according to the characteristic on which you have defined the partition. When you partition on say cal year. Then when compressed the data is partitioned in E table based on cal year. Each cal year data would be seperate hence it would improve performance of query.

But partitioning cannot be done when cube has data.

More details can be read in help by SAP.

Hope this helps.

Regards,

Wrushali

[Former Member]

thank you wrushali..

Regards,

Kumar

[Former Member]

hi Wrushali

let me know one thing...

TABLE PARTIOTION and CUBE PARTITION both r same or different?

can u plz calrify my dought?

Regards,

kumar.

sskumar2780@gmail.com

[Former Member]

Cube is nothing but a set of tables joined by STAR JOIN. So cube partitioning can be interchanged with table partitioning, especially because we cannot physically partition any other database object in SAP. So when we refer to forced physical partitioning, in other words it means cube partitioning.

(Not trying to confuse you ).

[Former Member]

Try this link from help.sap.com

http://help.sap.com/saphelp_nw04/helpdata/en/33/dc2038aa3bcd23e10000009b38f8cf/content.htm

Also you can search with similar terms in SDN and I am sure that this topic has been answered many times here before.

Good luck!

[Former Member]

Hi sudheer..

Let me know one thing clearly..

TABLE PARTITIONING and CUBE PARTITIONING both are same or different?

Regards,

Kumar.

sskumar2780@gmail.com