Recently, Oracle released a fantastic new calculation command in the 11.1.2.3.500 patch set – the FIXPARALLEL calculation command (click here for details of this command). Expanding the horizon of ways that Essbase developers can perform multi-threaded calculations. I recently finished a project at client that had some stellar Exalytics machines chock full of CPUs and memory and fast drives. I was able to leverage the new FIXPARALLEL command quite extensively with these powerful Exalytics servers. I especially liked the ability to perform data export calcs in parallel. I was loving FIXPARALLEL…until I wanted to use partitions. I love me some partitions and use them quite extensively. Imagine my surprise when I created a (replicated) partition between a BSO staging cube and ASO reporting cube and then tried to run one of my FIXPARALLEL enabled calculations. BAM!!
FIXPARALLEL and partitions not playing nicely together
This problem was really cramping my style. Being the rebel I am, I immediately started thinking about work-arounds. Hmmm, maybe I could drop the partition, run my calculations on the BSO staging cube (with FIXPARALLEL) and then recreate the partition from the BSO cube to my ASO reporting cube. But no, that wouldn’t work because the cube updates (including calculations and partition refreshes) were running while users were actively on the ASO cube (think slices) and the partition definition creation step will error with users actively using the ASO target cube. Think, Pete,THINK!!! That’s when I thought, “Well, how does the calculation engine know there’s a partition on the cube?” Maybe I could trick it. Hmmm. The .ddb file!!!!. Maybe that’s how the calculation engine is determining that the cube is partitioned. So, I performed the following steps:
- Shut down the BSO staging cube
- Renamed the .ddb file on the source BSO cube to .ddb.temp
- Restarted the BSO staging cube
- Ran my calculations with FIXPARALLEL with no errors
- Shut down the BSO staging cube
- Renamed the .ddb.temp back to .ddb and restarted the BSO staging cube
- Ran a partition refresh with “All data” selection and successfully refresh data from target to source
A-ha! It worked.
For reference, here’s a simple diagram of the cubes and partition flow.
Cubes and Partition
So, it appears you can trick the BSO cube into thinking its not partitioned to another cube without actually dropping the partition. Please note that I don’t expect Oracle to provide any support for a solution like this so use at your own risk. I would suggest thorough testing if you decide to try this setup. But until FIXPARALLEL explictly supports partitioned databases, this appears a viable workaround from my testing and experience.
Hyperion Essbase version tested (11.1.2.4)
