Partitioning an ODS is done for two reasons -
1) to improve query performance by allowing the DB to ignore portions of the table that are not relevant, e.g. you partition an ODS on 0FISCYEAR because almost all queries only ever look at one year.
2) to ease data administration burdens when dealing with large tables, e.g. you want to archive off data that is more than 4 years old. The archive process can simply drop partitions rather than having to run delete query.
ODS design does not use partitions to improve loading as the cubes do. Instead it has a separate table that new data is loaded to, and which is then subsequently inserted/updated to the Activated table during the Activation process.
InfoCubes - the F fact table is partitioned to faciliates the loading of data. The E fact table could be partitioned on 0CALMONTH or 0FISCPER (these are currently the only options for cubes), and would be partitioned for the same reasons mentioned above.
Cubes can be partitioned from within the BW Admin Workbench (cube MUST be empty). A DBA must perform partitioning of an ODS.
Before considering partitioing an ODS, yo uwould want to now about the ODS size, what queries exist, how they are restricted/filtered, etc. Typically, an ODS is partitioned on a time characteristic, e.g 0FISCYEAR, or 0FISCPER, but could also be partitioned on something like Business Area, or could even be partitioned on 0FISCYEAR then Business Area. The queries really drive that decision. Not going to try to explain all the options, that's really something to review with your DBA.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.