How to Run HVR Refresh with Slice Options

Question

How can I run HVR Refresh with slice options?

Environment

HVR 5

Answer

To help speed up Refresh, slicing can be used to split a refresh into multiple jobs and run those jobs in parallel. Provided below are examples of configuring a sliced refresh using Modulo, Boundaries and Series slicing.

Test data

The examples were generated with an Oracle source and a SQL Server target. The test table was created on the source as follows:

CREATE TABLE refresh\_test (id number, region varchar2(10), mydate date, salary number);  
ALTER TABLE refresh\_test ADD PRIMARY KEY(id);  
BEGIN  
FOR i IN 1..1000 LOOP  
INSERT INTO refresh\_test VALUES (i, 'East', sysdate-365, 30000);  
END LOOP;  
COMMIT;  
END;  
/  
BEGIN  
FOR i IN 1..1000 LOOP  
INSERT INTO refresh\_test VALUES (i, 'West', sysdate, 50000);  
END LOOP;  
COMMIT;  
END;  
/  
BEGIN  
FOR i IN 1..1000 LOOP  
INSERT INTO refresh\_test VALUES (i, 'South', sysdate-10, 40000);  
END LOOP;  
COMMIT;  
END;  
/  
BEGIN  
FOR i IN 1..1000 LOOP  
INSERT INTO refresh\_test VALUES (i, 'North', sysdate-30, 20000);  
END LOOP;  
COMMIT;  
END;  
/

Slice column

Each mode of slicing is based on one of the table columns. The column should be one whose values do not change frequently. Each type of slicing is best defined by a column with specific range of values, as described below.

Modulo slicing

Modulo slicing requires an INTEGER type column that has a large number of unique values - preferrably every row has a unique value. To configure the table for modulo slicing select the Scheduling tab then select Schedule Classic Job, Slice Table, select the slice column, and check Modulo as in the example below. Enter the number of slices desired in the box next to the Modulo radio button.

Boundary Slicing

Boundary slicing requires a column with a known range of possible values. In our test table we will slice on Salary. Configure for boundary slicing by selecting Schedule Classic Job, Slice Table, select the slice column, and Boundaries. Then click on the three dots in the box next to the Boundaries radio button and enter the upper boundary for each slice as in the example:

Click on OK, HVR will display the boundaries as follows:

Series Slicing

Series slicing requires a column that has a small set of unique values, one value per slice. To use Series Slicing you must first create a Restrict action. In our example we are using region as the series slice column, the Restrict action would be /SliceCondition="region ={hvr_slice_value}" /Context=slice

Then start a refresh and select the Scheduling tab, select Schedule Classic Job, Slice Table, and then check the Series radio button. Click on the three dots in the box next to the Series radio button and enter the column value for each slice. In the example below we enter 'South', 'West', 'East', 'North' as the four values. Note that string values must be delimited by single quotes:

Click on OK, HVR will display the series as follows:

Select the Context tab, select the “slice” context (defined in the Restrict action above), and check OK:

Schedule Job

Now click on the Schedule button at the bottom right of the screen. HVR may display an error such as “Slicing (option -S) cannot be used with online refresh option -q rw unless 'select moment' (option -M) is used”. This can be rectified in the options tab:

Once validated, HVR will generate a job for each slice.

The jobs can all be started at once. The number of refresh jobs running at one time can be controlled by adding a quota_run attribute on the refresh job group:

NOTE: Make sure you choose a column which does not often change such as primary key and also make sure you have an index created for the column you choose to do slicing for performance reasons.