|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
The UNRAVEL function is used with an assignment statement to copy the values of an expression into the cells of a variable when the dimensions of the expression are different from the dimensions of the variable.
An assignment statement created using an assignment statement assigns the values obtained from UNRAVEL by looping over the status of the dimensions of the target variable. The first dimension listed in the variable's definition varies the fastest. UNRAVEL obtains the values of the expression in the same way, looping over the status of the dimensions of the expression with the first dimension varying the fastest. You can alter the order in which UNRAVEL obtains its values by specifying the dimensions over which to loop.
The data type returned by UNRAVEL is the data type of the values specified by the expression.
The expression whose values are to be copied.
Specifies one or more dimensions over which to loop; the dimension specified first varies fastest as the data is unraveled.
Specifying dimensions in UNRAVEL overrides the default looping order and the extent to which the expression is unraveled. By default, unraveling extends through all the dimensions of the expression. However, when you specify some but not all the dimensions of the expression, any dimensions you have not specified do not unravel. Instead, the unraveled values include only the first value of each of the omitted dimensions.
Performance Tip for Unraveling Variables Dimensioned by Composites
By default, when UNRAVEL loops over a composite, it sorts the composite values according to the current order of the values in the composite's base dimensions. The task of sorting requires some processing time, so when variables are large, performance can be affected. When your variable is very large, and you are more concerned about performance than about the order in which UNRAVEL output is produced, you can set the SORTCOMPOSITE option to
Moving Worksheet Data
One common use of UNRAVEL is to move data from a worksheet to a variable, because the worksheet usually does not have the same dimensions as the variable. See Example 8-157, "Copying Data from a Worksheet to a Variable".
Filling Extra Target Cells
When there are still more cells in the target for the assignment statement (created using an assignment statement) to fill after it has used the last value from the expression, UNRAVEL starts over at the first value again.
Since the order in which unraveled values are assigned depends on the current status of the dimensions of both the variable and the expression, ensure that the appropriate LIMIT commands have been given so that the cells match up correctly.
Assigning Data Values
See SET for information on how data values are assigned.
Example 8-157 Copying Data from a Worksheet to a Variable
In an analytic workspace, you have imported some product price data from a spreadsheet into a worksheet. You now want to transfer that data to a variable called
newprice. You can produce a report of a worksheet, called
pricedata, with these statements.
LIMIT wksrow TO 1 TO 6 LIMIT wkscol TO 1 2 3 REPORT pricedata
This is the report.
-----------PRICEDATA------------ -------------WKSCOL------------- WKSROW 1 2 3 -------------- ---------- ---------- ---------- 1 Jan95 Jan96 2 Tents 191.39 194.00 3 Canoes 279.92 300.00 4 Racquets 83.34 85.00 5 Sportswear 107.90 110.00 6 Footwear 183.18 195.00
As you can see, row 1 contains month labels, while column 1 contains product labels. The variable
newprice is dimensioned by
product, as shown in its definition.
DEFINE newprice VARIABLE DECIMAL <month product> LD Wholesale Unit Selling Price
Even though the worksheet has different dimensions (
newprice, the data contained in it is well organized for transferring to the variable.
However, you do not want to take data from all the rows and columns in the worksheet, so limit
wksrow to the rows and columns that contain the price data itself.
LIMIT wkscol TO 2 3 LIMIT wksrow TO 2 TO 6
Also, you only want to set values into the variable
newprice for January 1995 and January 1996. So first limit
month to these values, then type an assignment statement using UNRAVEL to move the values from the worksheet to the variable.
LIMIT month TO 'Jan95' 'Jan96' newprice = UNRAVEL(pricedata)
You do not have to specify dimensions in the UNRAVEL function because
wkscol is the fastest varying dimension. Consequently, both months unravel for the first product, then both months for the second product. Since the fastest-varying dimension of the variable is
month, SET assigns values to the variable in the same order.
A report of
newprice looks like this.
------NEWPRICE------- --------MONTH-------- PRODUCT Jan95 Jan96 -------------- ---------- ---------- Tents 191.39 194.00 Canoes 279.92 300.00 Racquets 83.34 85.00 Sportswear 107.90 110.00 Footwear 183.18 195.00