DEFINE VARIABLE |
The DEFINE command with the VARIABLE keyword adds a new variable object to an analytic workspace. Variables store one type of data, which can be numeric, text, Boolean, or dates. Beside the data type of a variable, the definition that you create for a variable also determines the following characteristics of the variable:
The number of elements that are actually created in the array that is the variable.
The logical order of the variable's elements.
Whether the variable's data is stored permanently or is only available for the session.
The number of LOBs that Oracle OLAP creates for the variable's data.
You can also define local variables in programs using a VARIABLE statement. These variables exist only as long as the program is running.
Syntax
DEFINE name [VARIABLE] datatype [<dims...>] [WITH AGGCOUNT] [PERMANENT | TEMP ] -
[(partition-instance...)] [WIDTH n] [AW workspace] [SESSION]
where:
dims are the dimensions of the object separated by commas. For a dimension of a variable you can specify a dimension object, a partition template object, a named uncompressed composite, a compressed composite, or an unnamed uncompressed composite using one of the following:
dimension_name
partition_template_name<dims>
uncompressed_composite_name <[basedims...]>
compressed_composite_name <[basedims...]>
SPARSE <basedims...>
|
Note: The order in which you list the dims of a variable is the default order of the dimensions and behavior of a variety of statements (such as REPORT and UNRAVEL) and affects how the data for the variable is stored (as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping". Also, when you define more than one object with the same dimensions, most operations will work much more efficiently when you list the dimensions in the same order in each definition. |
partition-instance are the partitions of the variable separated by commas. Use the following syntax to specify a partition.
PARTITION partition-name [ {INTERNAL [TEMP | PERMANENT] } | {EXTERNAL target} ]
Arguments
The name of the variable you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a variable. You do not have to include the word VARIABLE, because it is the default.
The kind of data to be stored in the variable. The data types, their abbreviations, and the range of acceptable values are shown in Table: Valid Data Types for Variables.
Valid Data Types for Variables
| Data Type | Abbreviation | Values |
|---|---|---|
| INTEGER | INT | Whole numbers in the range of (-2**31) to (2**31)-1
|
| LONGINTEGER | LONGINT | Whole numbers in the range of (-2**63) to (2**63)-1
|
| SHORTINTEGER | SHORTINT | Whole numbers in the range of (-2**15) to (2**15)-1
|
| NUMBER [(p,[s])] | None | Decimal numbers with up to 38 significant digits, as defined by the precision and scale where precision (p) is a whole number between 1 and 38 and scale (s) is a whole number between -84 and 127 |
| DECIMAL | DEC | Decimal numbers with up to 15 significant digits |
| SHORTDECIMAL | SHORT | Decimal numbers with up to seven significant digits |
| TEXT | None | TEXT data type values (with no WIDTH setting) of one or more lines with no more than 4000 bytes for each line. Text values, with WIDTH set, of 1 line with no more than 4000 bytes. The TEXT data type corresponds to CHAR and VARCHAR2 data types in the Oracle relational database. TEXT characters are encoded in the database character set |
| NTEXT | None | NTEXT data type values with the same restrictions as TEXT data type values. The NTEXT data type corresponds to NCHAR and NVARCHAR2 data types in the Oracle relational database. However, an NTEXT character is always encoded in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16. (See "Text Data Types".) |
| ID | None | ID data type values of one line with no more than eight characters |
| BOOLEAN | BOOL | Logical YES/NO values (valid synonyms are ON/OFF and TRUE/FALSE)
|
| DATE | None | Dates between Jan 1, 1000 A.D. and Dec 31, 9999 A.D. |
| DATETIME | None | Dates between Jan 1, 4712 B.C. and Dec 31, 9999 A.D., and times in hours, minutes, and seconds |
The name of a simple, concat, conjoint, or alias dimension that you have previously defined using a DEFINE DIMENSION statement. In this case, you specify the name of the dimension.
The name of a partition template object that you have previously defined using a DEFINE PARTITION TEMPLATE statement. For dims, specify the names of the dimensions of the partition template object. These dimensions must be the same dimensions as those used to define the partition template object.
The name of an uncompressed composite previously defined using a DEFINE COMPOSITE statement. For the optional basedims argument, specify the names, separated by commas, of the dimensions used to define the composite.
The name of a compressed composite previously defined using a DEFINE COMPOSITE statement. For the optional basedims argument, specify the names, separated by commas, of the dimensions used to define the composite.
When defining a variable that is dimensioned by a compressed composite, keep the following points in mind:
A compressed composite can dimension only one variable or one partition of a variable. A compressed composite cannot be a shared composite.
The compressed composite must be the last dimension in the variable's dimension list of the DEFINE VARIABLE statement that defines the variable.
Indicates that you want Oracle OLAP to create an unnamed composite and use it when dimensioning the variable. For the basedims argument, specify the names of the dimensions, separated by commas, for which the unnamed composite is created.
Specifies that Oracle OLAP automatically creates an INTEGER variable in which it stores the non-NA counts of the number of leaf nodes that contributed to aggregate values calculated for RELATION (for aggregation) statements that have an AVERAGE, HWAVERAGE, or WAVERAGE operator. You must include this phrase to calculate average aggregations for a variable dimensioned by a compressed composite. For more information on Aggcount variables, see "Aggcount Variables".
Specifies that a variable or a partition of a variable is either permanent or temporary. After you update and commit, the definition of both permanent and temporary variables and partitions is always saved between sessions. Specifying permanent or temporary determines whether or not the values of a variable or partition of a variable are saved or discarded, after you update and commit, when you leave end your session or switch to another workspace:
Permanent variables and partitions—Oracle OLAP saves the data values or a permanent variable or permanent partitions. When you start the workspace, the data values or a permanent variable or permanent partitions are the same as they were at the last commit.
Temporary variables and partitions—Oracle OLAP discards the data values of a temporary variable or temporary partition. Each time you start the workspace, the values of a temporary variable or temporary partition are NA.
Keep the following points in mind when specifying the PERMANENT and TEMP keywords:
By default, a variable is permanent.
Temporary variables can be dimensioned by partition template objects or by temporary dimensions.
External partitions of a variable have the permanence of the variable that they represent.
By default, a top-level internal partition of a variable has the same permanence as the variable that contains it. Specifically, an internal partition of a temporary variable is a temporary partition unless you use the PEMANENT keyword to make it a permanent partition, and an internal partition of a permanent variable is a permanent partition unless you use the TEMPORARY keyword to make it a temporary partition. To indicate different behavior, use either the PERMANENT or TEMP keyword.
By default, an internal subpartition has the same permanence as its parent partition. To indicate different behavior, use either the PERMANENT or TEMP keyword.
(You can abbreviate WIDTH as W.) The width, in bytes, of the storage area for each value of a variable. When you are using a multibyte character set, be sure to specify the number of bytes, not characters.
You specify fixed widths to create faster and more compact data storage formats. You can specify fixed widths for dimensioned TEXT, NTEXT, and INTEGER variables only, as described in the following list:
For dimensioned TEXT and NTEXT variables, you can specify a width from 1 byte through 4000 bytes. Specify a fixed width for such variables only when you are certain that the values of a particular variable are of similar size. You cannot assign a width to a scalar variable.
For dimensioned INTEGER variables, you can specify a width of 1 byte only. Define a fixed width INTEGER variable only when you are certain that all the values for that variable are between -128 and 127.
The default widths for variables are: 2 bytes for SHORTINTEGER, 4 bytes for DATE, INTEGER, and SHORTDECIMAL, and 8 bytes for DECIMAL and ID. TEXT and NTEXT variables that do not have fixed widths are stored on two sets of pages. The first set contains 4-byte cells, each of which points to the actual text value that is stored in the other set of pages. The default width of 4 bytes for TEXT and NTEXT variables is for these 4-byte cells.
The name of the partition.
When defining the partitions of a variable dimensioned by a compressed composite, keep the following points in mind:
A compressed composite can dimension only one partition.
The partitions of a variable dimensioned by a compressed composite must respect the parent-child relationships of the hierarchical dimensions. When an AGGREGATE command executes, data cannot be aggregated across partitions. To check to see if a variable is partitioned correctly, use the PARTITIONCHECK function.
(Default) Indicates that this partition is not a previously defined variable. For an example of a variable with internal partitions, see Example: Defining a Variable with Internal Partitions.
You can only use this keyword when variable is dimensioned by a partition template object that was defined using a DEFINE PARTITION TEMPLATE statement that included the PARTITION BY CONCAT clause. Indicates that this partition is a previously defined variable that is a base dimension of concat dimension by which the variable is partitioned. For an example of a variable with external partitions, see Example: Defining a Variable with External Partitions.
The name of the variable that is the external partition.
The name of an attached workspace in which you wish to define the variable. When the variable is dimensioned, it must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.
Notes
Aggcount Variables
When you include the WITH AGGCOUNT phrase in a DEFINE VARIABLE statement, Oracle OLAP automatically creates the variable specified in the DEFINE statement and a secondary variable (often called the Aggcount variable). The Aggcount variable is an INTEGER variable that Oracle OLAP uses when performing average aggregations for the defined variable. When resolving RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE operator and that do not have a NOCOUNT phrase, Oracle OLAP stores the non-NA counts of the number of leaf nodes that contribute to the average aggregate values in the Aggcount variable.
Most statements that maintain a variable also automatically maintain an associated Aggcount variable. For example, an EXPORT statement exports both a variable and its associated Aggcount variable, and a CLEAR statement clears both the variable and the related portions of the associated Aggcount variable. Additionally, some OLAP DML statements are specific to the use of Aggcount objects. Table: OLAP DML Statements for Aggcount Variables lists these statements.
OLAP DML Statements for Aggcount Variables
| Statement | Keywords | Description |
|---|---|---|
| DEFINE VARIABLE | WITH AGGCOUNT | Defines a variable and an associated Aggcount variable. |
| AGGCOUNT
|
|
Retrieves the values of the Aggcount variable associated with the specified variable. |
| CHGDFN
|
Add|REMOVE AGGCOUNT | Adds or removes an Aggcount variable to the specified variable. |
| OBJ
|
HASAGGCOUNT | Returns a BOOLEAN value that indicates whether or not a specified variable has an Aggcount variable associated with it.
|
Defining Very Large Variables
Theoretically, a variable can contain up to 2**63 cells and a TEXT or NTEXT variable can contain up to 2 billion bytes. However, the page size determines if a variable can be stored entirely on a page or how many variables can be stored on a page. To calculate the maximum number of values for a variable of a given width that will fit on one page, use the VALSPERPAGE program.
Effect of Dimension Order on Variable Storage and Statement Looping
The order in which you list the dimensions of a variable definition determines the order in which the elements of the variable are stored and, consequently, how the data is accessed. The first dimension in the variable definition is the fastest-varying dimension, and the last dimension is the slowest-varying dimension.
For example, assume your analytic workspace has an opcosts variable that contains the operating costs, by month, of each city in which you have offices. In the following definition for the opcosts variable, month is the fastest-varying dimension and city is the slowest-varying dimension.
DEFINE opcosts VARIABLE DECIMAL <month city>
The data for a multidimensional variable is stored as a linear stream of values, in which the values of the fastest-varying dimension are clustered together. For example, for the opcosts variable, the values for Boston for all the months are stored in a sequence, and then it stores the values for Chicago for all the months in a sequence, and so on. Thus the month values vary fastest in the opcosts variable, as shown in the following table.
When you define variables and other dimensioned objects, and when you write programs that loop over multidimensional expressions in nested loops, you should always try to maximize performance by matching the fastest-varying dimension with the inner loop.
Unnamed Composites
Oracle OLAP automatically defines an unnamed composite when a DEFINE VARIABLE statement with a SPARSE <dimlist> phrase executes. An unnamed composite can have either a b-tree or hash index. The type of index is determined by the value of the SPARSEINDEX option at the time that Oracle OLAP defines an unnamed composite.
Once Oracle OLAP has created a definition for an unnamed composite for a certain dimension list, it uses that composite any time you define a variable with the same SPARSE <dimlist> phrase. Thus all variables that are defined with the same SPARSE <dimlist> phrase share the same unnamed composite. For more information on sharing composites, see "Shared Composites".
Variable Segments
Within a partition, variable data is stored in analytic workspace segments. An analytic workspace segment is a group of logically contiguous analytic workspace pages. By default, the segment sizes of a variable are automatically determined by Oracle OLAP. Each segment is the exactly the number of analytic workspace pages needed to store the values assigned by the one OLAP DML statement. You can explicitly specify a segment size for a variable using the SEGWIDTH keyword of the CHGDFN command. In this case, when you assign values to a variable, Oracle OLAP stores the data assigned by multiple OLAP DML statements into a segment until the segment is full.
Examples
Defining an INTEGER Variable with One Regular Dimension
This example adds the variable population to a workspace. It is dimensioned by city, which has already been defined in the workspace. The LD Statement attaches a description to the object. The statements
DEFINE population INTEGER <city> LD Population in each city DESCRIBE population
produce the following description.
DEFINE POPULATION VARIABLE INTEGER <CITY> LD Population in each city
Defining a Single-Cell Variable
The following is a definition for a variable named newdata which is a single Boolean value. It has no dimensions. An application might set it to YES when new data is added to the workspace and to NO after a user views the data.
DEFINE newdata BOOLEAN newdata = YES
Defining NUMBER Variables
The following statement defines a NUMBER variable named sales and dimensioned by product and geography with a precision of 16 digits and a scale of 4 digits.
DEFINE sales VARIABLE NUMBER (16,4) <product, geography>
The following statements define a NUMBER variable named numvar with 5 significant digits and 2 decimal places. The number 1234567 is out of its range.
DEFINE numvar VARIABLE NUMBER (5, 2) numvar = 1234567 SHOW numvar NA
A negative scale defines a NUMBER variable named numnegvar with 5 significant digits and 2 rounded digits to the left of the decimal point. The number 1,234,567 is rounded up.
DEFINE numnegvar VARIABLE NUMBER (5, -2) numnegvar = 1234567 SHOW numnegvar 1,234,600.00
Defining a Variable Dimensioned by Two Regular Dimensions
Assume that you have an analytic workspace that contains the following definitions for dimensions, relations, and aggmaps.
DEFINE GEOG_CITY DIMENSION TEXT DEFINE GEOG_STATE DIMENSION TEXT DEFINE GEOG_AREA DIMENSION TEXT DEFINE GEOG_CONT DIMENSION TEXT DEFINE GEOG DIMENSION CONCAT (GEOG_CITY GEOG_STATE GEOG_AREA GEOG_CONT) DEFINE PROD_UPC DIMENSION TEXT DEFINE PROD_FAMILY DIMENSION TEXT DEFINE PROD_DIV DIMENSION TEXT DEFINE PROD_TOP DIMENSION TEXT DEFINE PROD DIMENSION CONCAT (PROD_UPC PROD_FAMILY PROD_DIV PROD_TOP) DEFINE GEOGLEVEL DIMENSION TEXT DEFINE PRODLEVEL DIMENSION TEXT DEFINE GEOG.PARENT RELATION GEOG <GEOG> DEFINE PROD.PARENT RELATION PROD <PROD> DEFINE GEOG.LEVELREL RELATION GEOGLEVEL <GEOG> DEFINE PROD.LEVELREL RELATION PRODLEVEL <PROD> DEFINE GEOG.FAMILYREL RELATION GEOG <GEOG GEOGLEVEL> DEFINE PROD.FAMILYREL RELATION PROD <PROD PRODLEVEL> DEFINE SALES_DIMS_REG VARIABLE NUMBER (12,0) <PROD GEOG> DEFINE SALES_AGGMAP AGGMAP AGGMAP RELATION geog.parent RELATION prod.parent END
The two parent relations (prod.parent and geog.parent) have the following values.
PROD PROD.PARENT ------------------------- ------------------------- <PROD_UPC: ColorTV> <PROD_FAMILY: TV> <PROD_UPC: BWTV> <PROD_FAMILY: TV> <PROD_UPC: StndVCR> <PROD_FAMILY: VCR> <PROD_UPC: StrVCR> <PROD_FAMILY: VCR> <PROD_FAMILY: VCR> <PROD_DIV: VideoDiv> <PROD_FAMILY: TV> <PROD_DIV: VideoDiv> <PROD_DIV: VideoDiv> <PROD_TOP: Total Prod> <PROD_TOP: Total Prod> NA GEOG GEOG.PARENT ------------------------- ------------------------- <GEOG_CITY: Canberra> <GEOG_STATE: ACT> <GEOG_CITY: Sydney> <GEOG_STATE: NSW> <GEOG_CITY: Darwin> <GEOG_STATE: NT> <GEOG_CITY: Brisbane> <GEOG_STATE: QLD> <GEOG_CITY: Adelaide> <GEOG_STATE: SA> <GEOG_CITY: Hobart> <GEOG_STATE: TAS> <GEOG_CITY: Melbourne> <GEOG_STATE: VIC> <GEOG_CITY: Perth> <GEOG_STATE: WA> <GEOG_STATE: ACT> <GEOG_AREA: Aust Terr> <GEOG_STATE: NSW> <GEOG_AREA: Aust State> <GEOG_STATE: NT> <GEOG_AREA: Aust Terr> <GEOG_STATE: QLD> <GEOG_AREA: Aust State> <GEOG_STATE: SA> <GEOG_AREA: Aust State> <GEOG_STATE: TAS> <GEOG_AREA: Aust State> <GEOG_STATE: VIC> <GEOG_AREA: Aust State> <GEOG_STATE: WA> <GEOG_AREA: Aust State> <GEOG_AREA: Aust State> <GEOG_CONT: Australia> <GEOG_AREA: Aust Terr> <GEOG_CONT: Australia> <GEOG_CONT: Australia> NA
Assume that you aggregate sales_dims_reg using sales_aggmap). Now assume that you issue the following REPORT statement for a report of the sales_dims_reg variable.
REPORT sales_dims_reg->REPORT sales_dims_reg
As you can see from the output of the REPORT statement, the sales_dims_reg variable is a sparsely populated variable with 152 cells, many of which contain NA values.
----------------------------SALES_DIMS_REG-----------------------------
---------------------------------PROD----------------------------------
<PROD_DI
<PROD_UP <PROD_UP <PROD_UP <PROD_FA <PROD_FA V: <PROD_TO
C: <PROD_UP C: C: MILY: MILY: VideoDiv P: Total
GEOG ColorTV> C: BWTV> StndVCR> StrVCR> VCR> TV> > Prod>
------------------------- -------- -------- -------- -------- -------- -------- -------- --------
<GEOG_CITY: Canberra> 11,592.0 NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_CITY: Sydney> NA NA NA NA NA NA NA NA
<GEOG_CITY: Darwin> 24,868.0 NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_CITY: Brisbane> 49,556.0 NA 48,239.0 24,285.0 72,524.0 49,556.0 122,080 122,080
<GEOG_CITY: Adelaide> NA NA NA NA NA NA NA NA
<GEOG_CITY: Hobart> 17,223.0 NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_CITY: Melbourne> NA 22,000.0 NA NA NA 22,000.0 22,000.0 22,000.0
<GEOG_CITY: Perth> NA NA NA NA NA NA NA NA
<GEOG_STATE: ACT> 11,592.0 NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_STATE: NSW> NA NA NA NA NA NA NA NA
<GEOG_STATE: NT> 24,868.0 NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_STATE: QLD> 49,556.0 NA 48,239.0 24,285.0 72,524.0 49,556.0 122,080 122,080
<GEOG_STATE: SA> NA NA NA NA NA NA NA NA
<GEOG_STATE: TAS> 17,223.0 NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_STATE: VIC> NA 22,000.0 NA NA NA 22,000.0 22,000.0 22,000.0
<GEOG_STATE: WA> NA NA NA NA NA NA NA NA
<GEOG_AREA: Aust State> 66,779.0 22,000.0 67,111.0 73,065.0 140,176 88,779.0 228,955 228,955
<GEOG_AREA: Aust Terr> 36,460.0 NA 60,460.0 36,111.0 96,571.0 36,460.0 133,031 133,031
<GEOG_CONT: Australia> 103,239 22,000.0 127,571 109,176 236,747 125,239 361,986 361,986
Because the sales_dims_reg variable is dimensioned by two regular dimensions (rather than by composites or concat dimensions), the values of all of its cells (even those with an NA value) are stored in variable. You can confirm the number of physical values stored in the workspace by issuing the following statement.
SHOW OBJ(NUMVALS 'sales_dims_reg') 152.00
The result of the statement is that the value 152.00 displays. This indicates that every value in the 152 cells of the sales_dims_reg variable (even the NA values) are stored as part of the variable.
Defining a Variable Dimensioned by an Uncompressed Composite
Assume that you have created an analytic workspace with the same dimensions, relations, and aggmap as those in Example: Defining a Variable Dimensioned by Two Regular Dimensions. Now assume that you define the a composite and a variable dimensioned by that composite by issuing the following statements.
DEFINE COMP_PROD_GEOG COMPOSITE <PROD GEOG> DEFINE SALES_DIMS_COMPOSITE VARIABLE NUMBER (12,2) <COMP_PROD_GEOG <PROD GEOG>>
Assume that you populate sales_dims_composite with the same base values as you did sales_dims_reg in Example: Defining a Variable Dimensioned by Two Regular Dimensions, and that you aggregate sales_dims_composite using the same aggmap (that is, sales_aggmap) and issue the following. REPORT statement for the sales_dims_composite variable.
REPORT sales_dims_composite
A report for the sales_dims_composite variable displays the same 152 cells as the report for the sales_dims_reg variable.
-------------------------SALES_DIMS_COMPOSITE--------------------------
---------------------------------PROD----------------------------------
<PROD_DI
<PROD_UP <PROD_UP <PROD_UP <PROD_FA <PROD_FA V: <PROD_TO
C: <PROD_UP C: C: MILY: MILY: VideoDiv P: Total
GEOG ColorTV> C: BWTV> StndVCR> StrVCR> VCR> TV> > Prod>
------------------------- -------- -------- -------- -------- -------- -------- -------- --------
<GEOG_CITY: Canberra> 11,592.0 NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_CITY: Sydney> NA NA NA NA NA NA NA NA
<GEOG_CITY: Darwin> 24,868.0 NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_CITY: Brisbane> 49,556.0 NA 48,239.0 24,285.0 72,524.0 49,556.0 122,080 122,080
<GEOG_CITY: Adelaide> NA NA NA NA NA NA NA NA
<GEOG_CITY: Hobart> 17,223.0 NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_CITY: Melbourne> NA 22,000.0 NA NA NA 22,000.0 22,000.0 22,000.0
<GEOG_CITY: Perth> NA NA NA NA NA NA NA NA
<GEOG_STATE: ACT> 11,592.0 NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_STATE: NSW> NA NA NA NA NA NA NA NA
<GEOG_STATE: NT> 24,868.0 NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_STATE: QLD> 49,556.0 NA 48,239.0 24,285.0 72,524.0 49,556.0 122,080 122,080
<GEOG_STATE: SA> NA NA NA NA NA NA NA NA
<GEOG_STATE: TAS> 17,223.0 NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_STATE: VIC> NA 22,000.0 NA NA NA 22,000.0 22,000.0 22,000.0
<GEOG_STATE: WA> NA NA NA NA NA NA NA NA
<GEOG_AREA: Aust State> 66,779.0 22,000.0 67,111.0 73,065.0 140,176 88,779.0 228,955 228,955
<GEOG_AREA: Aust Terr> 36,460.0 NA 60,460.0 36,111.0 96,571.0 36,460.0 133,031 133,031
<GEOG_CONT: Australia> 103,239 22,000.0 127,571 109,176 236,747 125,239 361,986 361,986
However, because the sales_dims_comp variable is dimensioned by a composite, the 65 cells that display as NA values are not stored in variable. You can confirm the number of physical values stored in the workspace by issuing the following statement that calls the OBJ function with the NUMVALS keyword on sales_dims_composite.
SHOW OBJ(NUMVALS 'sales_dims_composite') 87.00
The result of the statement is that the value 87.00 displays. This indicates that only the 87 non-NA values are stored as part of the sales_dims_composite variable.
Defining a Variable Dimensioned by a Compressed Composite
Assume that you have created an analytic workspace with the same dimensions, relations, and aggmap as those in Example: Defining a Variable Dimensioned by Two Regular Dimensions. Now assume that you define the a composite and a variable dimensioned by that composite by issuing the following statements.
DEFINE CC_COMP_PROD_GEOG COMPOSITE <PROD GEOG> COMPRESSED DEFINE SALES_DIMS_COMP_COMPOSITE VARIABLE NUMBER (12,0) <CC_COMP_PROD_GEOG <PROD GEOG>>
Assume that you populate sales_dims_composite with the same base values as you did sales_dims_reg in Example: Defining a Variable Dimensioned by Two Regular Dimensions, and that you aggregate sales_dims_comp_composite using the same aggmap (that is, sales_aggmap). Now you issue the following statement.
REPORT sales_dims_comp_composite
A report for the sales_dims_comp_comp_composite variable displays the same 152 cells as the report for the sales_dims_reg variable.
-----------------------SALES_DIMS_COMP_COMPOSITE-----------------------
---------------------------------PROD----------------------------------
<PROD_DI
<PROD_UP <PROD_UP <PROD_UP <PROD_FA <PROD_FA V: <PROD_TO
C: <PROD_UP C: C: MILY: MILY: VideoDiv P: Total
GEOG ColorTV> C: BWTV> StndVCR> StrVCR> VCR> TV> > Prod>
------------------------- -------- -------- -------- -------- -------- -------- -------- --------
<GEOG_CITY: Canberra> 11,592.0 NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_CITY: Sydney> NA NA NA NA NA NA NA NA
<GEOG_CITY: Darwin> 24,868.0 NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_CITY: Brisbane> 49,556.0 NA 48,239.0 24,285.0 72,524.0 49,556.0 122,080 122,080
<GEOG_CITY: Adelaide> NA NA NA NA NA NA NA NA
<GEOG_CITY: Hobart> 17,223.0 NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_CITY: Melbourne> NA 22,000.0 NA NA NA 22,000.0 22,000.0 22,000.0
<GEOG_CITY: Perth> NA NA NA NA NA NA NA NA
<GEOG_STATE: ACT> 11,592.0 NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_STATE: NSW> NA NA NA NA NA NA NA NA
<GEOG_STATE: NT> 24,868.0 NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_STATE: QLD> 49,556.0 NA 48,239.0 24,285.0 72,524.0 49,556.0 122,080 122,080
<GEOG_STATE: SA> NA NA NA NA NA NA NA NA
<GEOG_STATE: TAS> 17,223.0 NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_STATE: VIC> NA 22,000.0 NA NA NA 22,000.0 22,000.0 22,000.0
<GEOG_STATE: WA> NA NA NA NA NA NA NA NA
<GEOG_AREA: Aust State> 66,779.0 22,000.0 67,111.0 73,065.0 140,176 88,779.0 228,955 228,955
<GEOG_AREA: Aust Terr> 36,460.0 NA 60,460.0 36,111.0 96,571.0 36,460.0 133,031 133,031
<GEOG_CONT: Australia> 103,239 22,000.0 127,571 109,176 236,747 125,239 361,986 361,986
However, because the sales_dims_comp_comp variable is dimensioned by a compressed composite not all of values in all of the cells are stored in the variable. The 65 cells that display as NA values are not stored in variable, Also, the values that are merely "passed up" the hierarchy are stored only once — at the lowest level of the hierarchy.
You can confirm the number of physical values stored in the workspace by issuing the following statement that calls the OBJ function with the NUMVALS keyword on sales_dims_comp_composite.
SHOW OBJ(NUMVALS 'sales_dims_comp_composite') 38.00
The result of the statement is that the value 38.00 displays. This indicates that only 38 values are stored as part of the sales_dims_comp_composite variable. These values are shown in the following table.
| GEOG | PROD_UPC:ColorTV | PROD_UPC:BWTV | PROD_UPC:StandVCR | PROD_UPC:StrVCR | PROD_FAMILY: VCR | PROD_FAMILY: TV | PROD_DIV: VideoDiv |
|---|---|---|---|---|---|---|---|
| GEOG_CITY: Canberra | 11,592.0 |
|
38,356.0 | 3,444.00 | 41,800.0 |
|
53,392.0 |
| GEOG_CITY: Darwin | 24,868.0 |
|
22,104.0 | 32,667.0 | 54,771.0 |
|
79,639.0 |
| GEOG_CITY: Brisbane | 49,556.0 |
|
48,239.0 | 24,285.0 | 72,524.0 |
|
122,080 |
| GEOG_CITY: Hobart | 17,223.0 |
|
18,872.0 | 48,780.0 | 67,652.0 |
|
84,875.0 |
| GEOG_CITY: Melbourne |
|
22,000.0 |
|
|
|
|
|
| GEOG_AREA: Aust State | 66,779.0 |
|
67,111.0 | 73,065.0 | 140,176 | 88,779.0 | 228,955 |
| GEOG_AREA: Aust Terr | 36,460.0 |
|
60,460.0 | 36,111.0 | 96,571.0 |
|
133,031 |
| GEOG_Cont: Australia | 103,239 | 127,57 | 109,176 | 236,747 | 125,239 | 361,986 |
Defining a Variable with Internal Partitions
Assume that you want to define a sales variable that is dimensioned by product and time and that is partitioned so that each year's data is in a separate partition.
Assume that the analytic workspace contains a products dimension, a time dimension that is a simple hierarchical dimension with three levels of data (day, month, and year), and a time_parentrel relation that represents the child-parent relationships between the values of time.
DEFINE TIME DIMENSION TEXT DEFINE PRODUCT DIMENSION TEXT DEFINE TIME_PARENTREL RELATION TIME <TIME>
For simplicity's sake, in this example the time and product dimensions are only partially populated and have only the following values.
TIME -------------- 2003 2002 Dec2003 Jan2003 Dec2002 Jan2002 31Dec2003 01Dec2003 31Jan2003 01Jan2003 31Dec2002 01Dec2002 31Jan2002 01Jan2002 PRODUCT ------- 00001 00002
To create the partitioned variable, take the following steps:
Define a partition template that defines one partition for each year's data.
DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
PARTITION BY LIST (TIME) -
(PARTITION TIME_2003 VALUES -
('2003','Dec2003','Jan2003', 31Dec2003',01Dec2003','31Jan2003','01Jan2003')-
PARTITION TIME_2002 VALUES -
('2002','Dec2002','Jan2002', 31Dec2002',01Dec2002','31Jan2002','01Jan2002'))
(note that for simplicity's sake, only some of each year's dimension values are specified for each partition in this example. Typically, when you want to specify a large number of values for a partition, you do not do so within the DEFINE PARTITION STATEMENT statement. Instead, you define the partition without specifying any values, and then later specify the values using MAINTAIN ADD TO PARTITION or MAINTAIN MOVE TO PARTITION statements as illustrated in Example: Specifying the Values of a Partition Using Valuesets.)
Define a partitioned sales variable with the partitions defined by the partition template named partition_sales_by_year.
DEFINE sales DECIMAL <partition_sales_by_year<time product>>
After you populate sales with day values, you can issue the following REPORT statement to see which sales values are in which partition.
REPORT DOWN PARTITION(partition_sales_by_year) time product sales PARTITION(PARTITION_SALES_BY_YEAR) TIME PRODUCT SALES ----------------------------------- ---------- ---------- ---------- TIME_2003 2003 00001 NA TIME_2003 Dec2003 00001 NA TIME_2003 Jan2003 00001 NA TIME_2003 31Dec2003 00001 14.78 TIME_2003 01Dec2003 00001 15.52 TIME_2003 31Jan2003 00001 13.61 TIME_2003 01Jan2003 00001 10.39 TIME_2003 2003 00002 NA TIME_2003 Dec2003 00002 NA TIME_2003 Jan2003 00002 NA TIME_2003 31Dec2003 00002 16.05 TIME_2003 01Dec2003 00002 12.27 TIME_2003 31Jan2003 00002 10.83 TIME_2003 01Jan2003 00002 11.07 TIME_2002 2002 00001 NA TIME_2002 Dec2002 00001 NA TIME_2002 Jan2002 00001 NA TIME_2002 31Dec2002 00001 18.80 TIME_2002 01Dec2002 00001 13.64 TIME_2002 31Jan2002 00001 12.41 TIME_2002 01Jan2002 00001 16.97 TIME_2002 2002 00002 NA TIME_2002 Dec2002 00002 NA TIME_2002 Jan2002 00002 NA TIME_2002 31Dec2002 00002 17.47 TIME_2002 01Dec2002 00002 16.58 TIME_2002 31Jan2002 00002 18.94 TIME_2002 01Jan2002 00002 18.36
Defining a Variable with External Partitions
Assume you have an analytic workspace that contains individual sales variables for each years data.
DEFINE year_2003 DIMENSION TEXT DEFINE year_2002 DIMENSION TEXT DEFINE year_2003_PARENTREL RELATION year_2003 <year_2003> DEFINE year_2002_PARENTREL RELATION year_2002 <year_2002> DEFINE sales_2003 VARIABLE DECIMAL <year_2003 product> DEFINE sales_2002 VARIABLE DECIMAL <year_2002 product>
Assume also that you want to logically combine the sales data into a single variable that has sales data for all years. To do this you add the following definitions to the analytic workspace:
A definition for a concat dimension that has the time-related dimensions of sales_2002 and sales_2003 as base dimensions.
DEFINE time DIMENSION CONCAT (year_2003 Year_2002) UNIQUE
A definition for the relation that specifies the child-parent relationship of the values of the time hierarchy.
DEFINE time_parentrel RELATION time <time>
A partition template object that defines the partitions for each year's sales data (that is, sales_2002 and sales_2003).
DEFINE part_temp_sales_by_year PARTITION TEMPLATE <time product> - PARTITION BY CONCAT (time)- (PARTITION partition_2002 <year_2002 product>, - PARTITION partition_2003 <year_2003 product>)
A sales variable with external partitions for sales_2002 and sales_2003.
DEFINE sales DECIMAL <part_temp_sales_by_year<time product>> - (PARTITION partition_2002 EXTERNAL sales_2002,- PARTITION partition_2003 EXTERNAL sales_2003)
When you issue the following REPORT statement you can see the values in the partitions of sales.
REPORT DOWN PARTITION(part_temp_sales_by_year) time product sales PARTITION(PART_TEMP_SALES_BY_YEAR) TIME PRODUCT SALES ----------------------------------- ---------- ---------- ---------- PARTITION_2002 01Jan2002 00001 14.44 PARTITION_2002 31Jan2002 00001 15.55 PARTITION_2002 01Dec2002 00001 11.39 PARTITION_2002 31Dec2002 00001 10.53 PARTITION_2002 Jan2002 00001 29.99 PARTITION_2002 Dec2002 00001 21.92 PARTITION_2002 2002 00001 51.91 PARTITION_2002 01Jan2002 00002 11.03 PARTITION_2002 31Jan2002 00002 12.20 PARTITION_2002 01Dec2002 00002 12.80 PARTITION_2002 31Dec2002 00002 13.77 PARTITION_2002 Jan2002 00002 23.23 PARTITION_2002 Dec2002 00002 26.57 PARTITION_2002 2002 00002 49.80 PARTITION_2003 01Jan2003 00001 10.00 PARTITION_2003 31Jan2003 00001 10.88 PARTITION_2003 01Dec2003 00001 NA PARTITION_2003 31Dec2003 00001 NA PARTITION_2003 Jan2003 00001 20.88 PARTITION_2003 Dec2003 00001 NA PARTITION_2003 2003 00001 NA PARTITION_2003 01Jan2003 00002 15.21 PARTITION_2003 31Jan2003 00002 13.37 PARTITION_2003 01Dec2003 00002 NA PARTITION_2003 31Dec2003 00002 NA PARTITION_2003 Jan2003 00002 28.58 PARTITION_2003 Dec2003 00002 NA PARTITION_2003 2003 00002 NA
Defining a Fixed-Width TEXT Variable
The following statement defines a TEXT variable named lastname dimensioned by employee. Values in lastname are limited to 20 characters, so that longer values are truncated.
DEFINE lastname TEXT <employee> WIDTH 20
Defining a Variable That Uses a Named B-Tree Composite
Assume that you have the following dimensions in your analytic workspace.
DEFINE month DIMENSION TEXT DEFINE product DIMENSION TEXT DEFINE region DIMENSION TEXT
When your company does promotional marketing for certain products in some but not all regions, then your variable data will be sparse along the product and region dimensions. Therefore, suppose you define a composite named proddist, whose base dimensions are product and region. There are dimension-value combinations in the composite only for those values that have data. For example, when you run a promotion for tents but not skis, then the composite includes the tents and region combinations, but not the skis and region combinations.
The following statement creates a b-tree composite named proddist whose base dimensions are product and district, and a variable called promo that is dimensioned by month and proddist.
DEFINE proddist COMPOSITE <product region> DEFINE promo VARIABLE INTEGER <month proddist <product district>>
For simplicity's sake assume that you have only stored the following dimension data in your analytic workspace.
PRODUCT -------------- Tents Skis REGION -------------- Northeast Southwest MONTH -------------- Jan2003 Feb2003 Mar2003 Apr2003 May2003 Jun2003 Jul2003 Aug2003 Sep2003 Oct2003 Nov2003 Dec2003
You decide to run a promotional sales for skis in the Northeast region in the month of September, 2003 at a cost of $5,000. Once you populate promo with this, promo contains only 12 cells—each cell is dimensioned by a value of month and the composite tuple value of <'Skis' 'Northeast'> for proddist. The cell for September 2003 contains the value $5,000, and all of the other cells contain NA. No other NA values are stored in promo; there are no cells are created for any other values of product or region.