Parallel Execution of OLAP DML

While I was teaching a workshop in Moscow recently a student asked about parallel execution of OLAP DML. Their cube processing included the usual loading and aggregation, which are automatically parallelized, but they also have some assignments into the cube which are done using an OLAP DML program. They noted that this was slow because it was single threaded on one CPU. I told the student that Oracle OLAP supports a multi-write attach mode that can be used to update multiple objects in parallel using separate sessions. I checked the documentation, but didn't find a clear explanation of how this works in the context of processing partitions of a cube in parallel.

Here's a code sample that illustrates how to process partitions of a cube in parallel. For this example, assume that the cube has four dimensions: time, product, geography and channel. The cube is partitioned on the time dimension at the retail year level. An OLAP DML program is used to assign data into the cube at the day level (it will be aggregated later).

" Attach the AW
AW ATTACH global_business multi

" Set status of time to match the partition. In this example, the partition is 'P3'
LIMIT time TO PARTITION(sales_prt_template) EQ 'P3'

" Keep data at the day level.
LIMIT time KEEP time_levelrel 'DAY'

" Set status of other dimensions.
LIMIT product TO product_levelrel 'ITEM'
LIMIT geography TO geography_levelrel 'STATE_PROVINCE'
LIMIT store TO store_levelrel 'STORE'

" Acquire the partition for write.
ACQUIRE RESYNC sales_stored_stored (PARTITION p3)

" OLAP DML assignments.
sales_stored(sales_measure_dim 'DOLLAR_SALES') = sales_stored(sales_measure_dim 'QUANTITY') * price_stored(price_measure_dim 'PRICE') across sales_stored_prt_template

" Update and commit to save work.
UPDATE multi
COMMIT

" Release the partition.
RELEASE sales_stored (partition p3)

" Detach the AW
AW DETACH global_business


The important points are:

• The attach mode is MULTI
• Use ACQUIRE to attach the appropriate partition.
• The PARTITION function can be used to set status of the partitioned dimension to the dimension members in the partition.
• Use the MULTI keyword with UPDATE.
• RELEASE the partition.
• While it doesn't have anything to do with running this in parallel, "across sales_stored_prt_template" will cause the OLAP DML to loop the local composite index of the partition rather than loop over the base dimensions.

Repeat this code (or use parameters) for each partition. To run in parallel, run these commands in seperate database sessions for each partition. The above code sample is pure OLAP DML. In practice, it makes sense to execute this in the context of PL/SQL that you can call in separate SQL Plus sessions. E.g.,

BEGIN

-- Attach the AW in multi-write model.
dbms_aw.execute('AW ATTACH global_business multi');

-- Set status of time to match the partition.
dbms_aw.execute('LIMIT time TO PARTITION sales_prt_template) EQ ''P1''');
dbms_aw.execute('LIMIT time KEEP time_levelrel ''DAY''');

-- Set status of other dimensions.
dbms_aw.execute('LIMIT product TO product_levelrel ''ITEM''');
dbms_aw.execute('LIMIT geography TO geography_levelrel ''STATE_PROVINCE''');
dbms_aw.execute('LIMIT store TO store_levelrel ''STORE''');

-- Aquire the partition for write.
dbms_aw.execute('ACQUIRE RESYNC sales_stored_stored (PARTITION p1)');

-- OLAP DML assigment.
dbms_aw.execute('sales_stored(sales_measure_dim ''DOLLAR_SALES'') = sales_stored(sales_measure_dim ''QUANTITY'') * price_stored(price_measure_dim ''PRICE'') across sales_prt_template');

-- Update to save work.
dbms_aw.execute('UPDATE multi');

-- Release the partition.
dbms_aw.execute('RELEASE sales_stored_stored (partition p1)');

-- Detach the AW
dbms_aw.execute('AW DETACH global_business');

END;
/

COMMIT;

If this is done with PL/SQL, then you can run a shell script such as this to run all partitions in parallel.

sqlplus olaptrain/***** @set_sales_cost_p1.sql &
sqlplus olaptrain/***** @set_sales_cost_p2.sql &
sqlplus olaptrain/***** @set_sales_cost_p3.sql &
sqlplus olaptrain/***** @set_sales_cost_p4.sql &