The DEFINE command adds a new object to the analytic workspace. This entry describes the DEFINE command in general. The following entries discuss the use of the DEFINE command for creating specific types of object:
A TEXT expression that is the name for the new object. Follow these guidelines when specifying a value for name:
The name must consist of 1 to 64 characters. When you are using a multibyte character set, you can still specify 64 characters even when this requires more than 64 bytes. Each character may be a letter (A-Z), a number (0-9), an underline (_), or a dot (.). However, the following restrictions apply to the use of these characters:
The name cannot consist of a single dot (.) character or a single underscore (_) character.
The name cannot duplicate a reserved word. For more information on identifying reserved words, see the RESERVED function.
The first character in the name cannot be a number.
The first character cannot be a dot (.) when the second character is a number.
By default Oracle OLAP creates the definition in the current workspace. To create the definition in a different attached workspace, you can specify a qualified object name for name or you can use the AW argument to specify the workspace. Do not use both.
Caution:
Oracle OLAP does not warn you when you create an object that has the same name as an existing object in another attached workspace.The type of object being defined. The default is VARIABLE. The object types are discussed in the subsections for the DEFINE command.
Attributes are different for each type of object. The attributes are listed in the entry for each object type.
The name of an attached workspace in which you want to define the object. You can also specify a noncurrent attached workspace using a qualified object name for name. Do not use this phrase when qualified object name for name.
Specifies that the object exists only in the current session. The object is created in the EXPRESS analytic workspace to which you have read-only access. When you close the current session, the object no longer exists.
Triggering Program Execution When DEFINE Executes
Using a TRIGGER_DEFINE program, you can make the DEFINE command an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information.
Effect of DEFINE on the Status of the NAME Dimension
When you execute a DEFINE command with the NAME dimension limited to less than all its values, the status of NAME is automatically limited to ALL.
Objects created with the SESSION keyword are stored in the analytic workspace named EXPRESS instead of the current analytic workspace. Therefore, statements that operate against the current analytic workspace (such as LISTNAMES) do not list session objects unless you do one of the following:
Specify the EXPRESS analytic workspace in the statement (such as LISTNAMES AW EXPRESS)
Make the EXPRESS analytic workspace the current analytic workspace by issuing an AW ATTACH EXPRESS statement.
The DEFINE command with the AGGMAP keyword adds a new aggmap object to an analytic workspace. An aggmap object is a specification for how Oracle OLAP allocates or aggregates variable data.
Defining an aggmap merely creates an aggmap object in the analytic workspace; it does not define the calculation specification. The aggmap specification can either specify how to aggregate or how to allocate data:
For information on coding an aggregation specification, see the AGGMAP command.
For information on coding an allocation specification, see the ALLOCMAP command.
The name of the object that you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining an aggmap.
(Optional; retained for compatibility with earlier software versions.) When defining an aggmap object for aggregation (that is, an AGGMAP-type aggmap), the names of the dimensions. You cannot specify a conjoint dimension as a base dimension in the definition or specification for the aggmap.
The name of an attached workspace in which you want to define the object. For more about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. For more information about this argument, see the main entry for the DEFINE command.
Example 9-78 Creating an Aggmap for Aggregation
Suppose you define a sales variable with the following statement.
DEFINE sales VARIABLE <time, product, geography>
Assume also that you have defined an aggmap named sales.agg with the following definition and specification.
DEFINE sales.agg AGGMAP <time, product, geography> AGGMAP RELATION time.r PRECOMPUTE (time NE 'Year99') RELATION product.r PRECOMPUTE (product NE 'All') RELATION geography.r CACHE STORE END
The sales.agg aggregation specification contains the preceding three RELATION statements and a CACHE statements. In this example, you are specifying that all of the data for the time.r hierarchy of the time dimension should be aggregated, except for any data that has a time dimension value of Year99. All of the data for the product.r hierarchy of the product dimension should be aggregated, except for any data that has the product dimension value of ALL. (In this example, the product dimension has a dimension value named ALL that represents all products in the hierarchy.) All geography dimension values are aggregated. The CACHE STORE statement specifies that any data that is rolled up on the fly should be calculated just once and stored in the cache for other access requests during t he same session.
Note that users should not have write access to the analytic workspace when CACHE STORE is set, because the data calculated during the session may be saved inadvertently.
In this example, any data value that dimensioned by a Year99 time value or an ALL product dimension value is calculated on the fly.
You can now use the sales.agg aggmap with an AGGREGATE command, such as the following.
AGGREGATE sales USING sales.agg
Example 9-79 Creating an Aggmap for Allocation
Suppose you have a sales variable that you defined with the following statement.
DEFINE sales VARIABLE <time, product, geography>
To allocate data from a source to cells in the sales variable that are specified by the time and product dimension hierarchies, you have created an ASCII disk file called salesalloc.txt, which contains the following aggmap definition and specification.
DEFINE sales.alloc AGGMAP ALLOCMAP RELATION time.r OPERATOR EVEN RELATION product.r operator EVEN NAOPERATOR HEVEN SOURCEVAL ZERO CHILDLOCK DETECT END
To include the sales.alloc aggmap in your workspace, execute the following statement.
INFILE 'salesalloc.txt'
The sales.alloc aggmap is now defined, and it contains the preceding two RELATION statements, the SOURCEVAL statement and the CHILDLOCK statement. You end the entry of statements into the aggmap with the END statement. In this example, you are specifying that the first allocation of source values occurs down the time dimension hierarchy and that the source value is divided evenly between the target cells at each level of the allocation. The second allocation occurs down the product dimension hierarchy, with the source value again divided evenly between the target cells at each level of the allocation, and when the allocation encounters a deadlock, the source values is divided evenly between the target cells of the hierarchy including cells that have a basis value of NA. With the SOURCEVAL statement you specify that after the allocation, ALLOCATE sets the value of each source cell to zero. With the CHILDLOCK statement you specify that ALLOCATE detects the existence of locks on both a parent and a child element of a dimension hierarchy.
You can now use the sales.alloc aggmap with an ALLOCATE command, such as the following.
ALLOCATE sales USING sales.alloc
The preceding statement does not specify a basis or a target object so ALLOCATE uses the sales variable as the source, the basis, and the target of the allocation.
The DEFINE command with the COMPOSITE keyword adds a new named composite to an analytic workspace. Conceptually, you can think of a composite consisting of two structures:
The composite object itself. The composite contains the dimension-value combinations (that is, a composite tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the composite.
An index between the composite values and its base dimension values.
For a variable that is dimensioned by composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions. Data for the variable is stored in order, cell by cell, for each tuple in the composite. From the perspective of data storage, each combination of base dimension values in a composite is treated like the value of a regular dimension. Consequently, when you define a variable with one regular dimension and one composite, the data for the variable is stored as though it was a two-dimensional variable. Using composites to reduce the number of elements created for a variable results in more efficient data storage.
Note:
Oracle OLAP also supports the use of unnamed composites as described in "Unnamed Composites".DEFINE name COMPOSITE <dims...> [AW workspace] [index-algorithm] [SESSION]
where index-algorithm specifies the algorithm that Oracle OLAP uses to create an index that relates the composite values to its base dimension values. When you omit this optional argument, Oracle OLAP uses the value specified by the SPARSEINDEX option. Valid values for index-algorithm are:
The name of the object 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 named composite.
The names of two or more dimensions that you want to be the base dimensions of the composite. When you specify COMPRESSED as the value of index-algorithm, at least one dimension must be a hierarchal dimension.
The order of the dimensions in dims varies by the value you specify for index-algorithm:
For b-tree or hash composites, specify the dimensions in fastest to slowest-varying order as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping".
For compressed composites, it does not matter in which order you specify the dimensions. Oracle OLAP selects the order in which to store the values unless you override this optimization by specifying FORCEORDER in an AGGREGATE command or AGGREGATE function. To see the optimized order chosen by Oracle OLAP, view the cube operations log.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about the cube operations log and the DBMS_CUBE_LOG packageYou must define all the dimensions and named composites used in the list before defining the composite. DEFINE automatically creates any unnamed composites in the list for you.
The name of an attached workspace in which you want to define the object. For more information about this argument, see the main entry for the DEFINE command.
Specifies the creation of a b-tree index to relate composite values to base dimension values. BTREE is the standard indexing method for composites. For a variable that is dimensioned by a BTREE composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions.
Specifies the creation of a highly-scalable b-tree index to relate composite values to base dimension values. For a variable that is dimensioned by a BTREE64 composite, like a BTREE composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions. However, unlike a BTREE composite, a BTREE64 composite supports b-trees greater than 2 gigabytes.
Note:
Typically, you define a BTREE64 composite when you want to use it to dimension a variable which you populate from a relational table that is larger than 2 gigabytes.Specifies the creation of a compressed index to relate composite values to base dimension values. You specify COMPRESSED only when you want to create a composite for a variable that has at least one hierarchical dimension that is specified in dims and that is aggregated.
A compressed composite contains one composite tuple for each set of base dimension values that identifies non-NA detail data in the variables that use it. Additionally, for variables dimensioned by compressed composite Oracle OLAP reduces redundancy in the variable, composite, and composite index by creating a physical position in the composite only for those tuples that represent a parent with multiple descendants. Oracle OLAP then creates an index between this composite structure and the base dimensions and uses this composite structure as the dimension of the variable. Because the actual structure of a compressed composite is smaller than that of a b-tree or hash composite, a variable dimensioned by a compressed composite is also smaller than a variable dimensioned by a b-tree or hash composite. Also, because the index for a compressed composite only has nodes for parents with multiple descendants, the index of a compressed composite has fewer levels and is smaller than the index of a b-tree composite. Although performance varies depending on the depth of the hierarchies and the order of the dimensions in the composite, aggregating variables defined with compressed composites is typically much faster than aggregating variables defined with b-tree or hash composites.
Note:
Oracle OLAP compresses the data in variables dimensioned by compressed composites using the "intelligence" of the AGGREGATE command or AGGREGATE function. Consequently, there are special considerations that apply when aggregating a variable dimensioned by one or more compressed composites. See "Aggregating Variables Dimensioned by Compressed Composites" for more information.Specifies the creation of a hash index to relate composite values to base dimension values. HASH is rarely used and, then, typically, only when the composite has two or three dimensions. For a variable that is dimensioned by a b-tree or hash composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
You can use the same b-tree or hash composite to dimension several variables. (Compressed composites cannot be shared in this manner.) The actual sparsity of a variable dimensioned by a b-tree or hash composite varies depending on whether or not the composite is an unshared composite or a shared composite:
An unshared composite is a composite that is used to dimension only one variable. All types of composites (that is, b-tree, hash, and compressed composites) can be unshared composites. An unshared composite is populated only when the variable that uses it is populated. Consequently, an unshared composite perfectly reflects the sparsity of the variable that it is used to dimension. It only has the dimension value combinations for each non-NA value in that variable.
A shared composite is a composite that is used to dimension multiple variables. A shared composite can be either a b-tree or hash composite; it cannot be a compressed composite. A shared composite is populated when any of the variables that use it are populated. A shared composite has all of the dimension value combinations for non-NA values for all of the variables that it dimensions. A shared composite reflects the sparsity of all of the variable that it is used to dimension. Typically, therefore, variables dimensioned by shared composites are not perfectly sparse variables.
When the size of variables is important, or when you have variables that are sparse along the same dimensions but with significantly different patterns of sparsity, define different composites for the different variables.
This section contains a simple example of creating a named b-tree composite. For examples of using composites to dimension variables, see Example 9-99, "Defining a Variable Dimensioned by an Uncompressed Composite" and Example 9-100, "Defining a Variable Dimensioned by a Compressed Composite".
Example 9-80 Creating a Named b-Tree Composite
Assume that the value of SPARSEINDEX is BTREE. The following statements define two objects: a named composite that has a b-tree index and base dimensions of market and a variable called expenses that is dimensioned by the month dimension and the market.product composite.
DEFINE market.product COMPOSITE <market product> DEFINE expenses DECIMAL <month market.product <market product>>
The DEFINE command with the DIMENSION keyword adds a new dimension object to an analytic workspace. A dimension is a list of values that provides an index to the data.
Because the syntax of the DEFINE DIMENSION command is different depending on the type of the dimension that you are defining, four separate entries are provided:
DEFINE DIMENSION (simple) for defining a dimension with unique values of the same data type.
DEFINE DIMENSION (DWMQY) for defining a non-hierarchical dimension whose values represent a time period (day, week, month, quarter, or year).
DEFINE DIMENSION (conjoint) for defining a dimension over two or more other base dimensions when the base dimensions do not contain duplicate values or have different data types and when you want to explicitly specify the dimension value combinations.
DEFINE DIMENSION CONCAT for defining a dimension over two or more other base dimension when the base dimensions contain duplicate values or different data types or when you want Oracle OLAP to automatically populate the dimension value combinations.
DEFINE DIMENSION ALIASOF for defining an alias for a simple dimension.
Note:
Defining a dimension in the analytic workspace merely adds the definition of the dimension to the analytic workspace; it does not populate the dimension. To populate dimensions using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, or MAINTAIN statements.The DEFINE DIMENSION (simple) command defines a simple dimension. When a variable is dimensioned by regular dimensions, Oracle OLAP creates an array element for each set of its dimension values. The values of a simple dimension must be unique data values with the same data type. A simple dimension can be a flat dimension or a hierarchical dimension that contains values from different levels of a hierarchy.
Tip:
To create a hierarchical dimension using duplicate values or values of different data types, use a concat dimension as described in DEFINE DIMENSION CONCAT.DEFINE name DIMENSION type [TEMP] [AW workspace] [SESSION]
where type is the data type of the dimension. The syntax of type varies depending on the data type:
TIMESTAMP [( truncation-code )]TIMESTAMP_TZ [( truncation-code )]TIMESTAMP_LTZ [( truncation-code )]The name of the object 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 dimension.
Specifies that the values of the dimension have the TEXT data type which is equivalent to the CHAR and VARCHAR2 data types in Oracle Database. This data type stores up to 4,000 bytes for each line in the database character set.
Specifies that the values of the dimension have the NTEXT data type which is equivalent to the NCHAR and NVARCHAR2 data types in Oracle Database. This data type stores up to 4,000 bytes for each line in UTF-8 character encoding.
Specifies a special text data type that stores up to 8 single-byte characters for each line in the database character set.
For TEXT or NTEXT dimensions, the width, in bytes, of the storage area of each value of an object. Valid width values are 1 through 4000. Specify a fixed width only when you are certain that the values of a particular dimension are of similar size. When a value exceeds the specified width, it is truncated.
Specifies that the values of the dimension have the INTEGER data type. The data type for a dimension with values that are identified by their numeric position (1, 2, and so on). A data type of INTEGER means that the dimension has no character values. For ease of use, use a text or time period data type, when possible.
Specifies that the values of the dimension have the NUMBER data type. A NUMBER dimension differs from other dimensions in that its values cannot be specified by position, only by value. To specify the values of a NUMBER dimension by position, you can define an INTEGER type dimension surrogate for the NUMBER dimension.
The total number of digits a value of type NUMBER can have.
The number of digits a value of type NUMBER can have to the right of a decimal point. For example, when you specify a precision of 7 and a scale of 2, then the highest value that the dimension can have is 99999.99. When you specify a precision value, but do not specify a scale value, then the scale is 0.
Specifies that the values of the dimension have the DATETIME data type.
Specifies that the values of the dimension have the TIMESTAMP data type.
Specifies that the values of the dimension have the TIMESTAMP_TZ data type.
Specifies that the values of the dimension have the TIMESTAMP_LTZ data type.
A text expression that specifies a format model shown in Table 8-13, "Datetime Format Templates for the ROUND and TRUNC Date Functions". A format model indicates how the date and time number should be truncated.
Indicates that the dimension's values are only temporary and only for the current session. The dimension has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the dimension is saved. When you leave end your session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary dimension are NA.
The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. 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 you close the current session, the object no longer exists.
NA Values in Variables Dimensioned by Simple Dimensions
When a variable is dimensioned by regular dimensions, Oracle OLAP creates an array element for each set of its dimension values. When an array element is empty, then the element is said to contain an NA value. In some cases, this can result in a sparse variable—that is, a variable in which a relatively high percentage of array elements that are empty. There are two types of sparsity:
Controlled sparsity occurs when a range of one or more dimensions has no data; for example, a new variable dimensioned by month for which you do not have data for past months.
Random sparsity occurs when some combinations of dimension values never have any data. For example, a district might only sell certain products and never have data for other products. Other districts might sell some of those products and other ones, too.
When a sequence of array elements contain enough NA values to fill up an analytic workspace page, Oracle OLAP does not actually store any of the NA values and, instead, keeps tracks of the values internally. However, when an analytic workspace page contains both regular values and NA values, then Oracle OLAP stores all of the values. You can reduce the number of array elements with NA values by dimensioning a variable with one or more composites or conjoint dimensions. See the DEFINE COMPOSITE and DEFINE DIMENSION (conjoint) commands.
Example 9-81 Defining a Simple Dimension
This example adds the dimension city to an analytic workspace. You can attach a description to the object immediately after defining it. (You can also add the description later when you use CONSIDER and LD statements.) After defining the dimension city, you can give it values with a MAINTAIN statement.
The statements
DEFINE city DIMENSION ID LD List of cities MAINTAIN city ADD 'Boston' 'Chicago' 'Dallas' 'Seattle' DESCRIBE city
produce the following definition.
DEFINE city DIMENSION ID LD List of cities
The DEFINE DIMENSION (DWMQY) command defines a DWMQY dimension (that is a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR) whose values represent time periods. After defining a DWMQY dimension, you can use a VNF statement to add a value name format to the dimension's definition. The VNF command controls the format for entering dimension values and the format for showing them in output.
Note:
When you want to aggregate over time do not define the time dimension as a DWMQY dimension because you cannot aggregate over dimensions of this type. Instead, define the time dimension as a hierarchical dimension of typeTEXT or NTEXT.DEFINE name DIMENSION dwmqy [TEMP] [AW workspace] [SESSION]
where dwmqy is the time period of the dimension. The valid types for dwmqy are DAY, WEEK, MONTH, QUARTER, and YEAR. Each type indicates the span of the time period represented by the individual dimension values of the dimension. The syntax of dwmqy varies depending on the type:
The name of the object 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 dimension.
For the WEEK and MONTH types, specifies time periods that span a multiple number of weeks or months. With the WEEK keyword, multiple can be an INTEGER from 2 to 52. With the MONTH keyword, multiple can be 2, 3, 4, or 6.
Specifies the beginning or ending phase of a WEEK, MONTH, QUARTER, or YEAR dimension:
For single weeks, phase can be a day of the week (corresponding to a name in the DAYNAMES option) or a date.
For multiple weeks, phase must be a date.
For months, quarters, or years, phase must be a month, expressed as a month name (corresponding to a name in the MONTHNAMES option) or as a date.
When you specify phase as a date, you give the month, day, and year, enclosed in single quotes, using any of the input styles that are valid for variable values with a data type of DATE. When you specify a date with an ambiguous meaning (such as '03 05 97'), the date is interpreted according to the current setting of the DATEORDER option.
Note:
When you define a multiple-period dimension of type WEEK but you do not specify a BEGINNING or an ENDING argument, DEFINE automatically supplies a phase that begins with the date'31DEC1899'.Indicates that the dimension's values are only temporary and only for the current session. The dimension has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the dimension is saved. When you leave end your session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary dimension are NA.
The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. 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 you close the current session, the object no longer exists.
Implicit Relations Between DWMQY Dimensions
When you define two or more dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR, Oracle OLAP automatically defines implicit relations between the values of the dimensions. For example, when you define a dimension of type MONTH and a dimension of type YEAR, Oracle OLAP automatically defines a relation that associates all the MONTH values that fall within a particular year with the corresponding value of the YEAR dimension.
Using BEGINNING or ENDING Phase to Organize Data by Fiscal Calendar
For dimensions of type MONTH, QUARTER, and YEAR, the BEGINNING phase or ENDING phase argument is especially useful for data organized on a fiscal-year calendar.
By specifying a phase for a dimension of type MONTH or QUARTER, you identify the time period that is the first or last period within a year. For example, when you define a dimension of type MONTH with an ending phase of June, then June is identified as the twelfth month of the year. When a dimension of type QUARTER has an ending phase of June, the quarter ending in June is identified as the fourth quarter of the year. When you give a dimension a VNF that includes a period code, you can enter or report dimension values according to their period within the year.
By default, the single or multiple weeks in a dimension of type WEEK end on Saturday. The BEGINNING phase or ENDING phase argument lets you specify the day of the week on which each period begins or ends. For multiple-week periods, the phase argument also controls the starting or ending date for grouping the weeks into periods. By default, the starting point for grouping multiple weeks is December 31, 1899 (a Sunday).
However, the phase argument does not determine the period that is counted as the first period within a year. For dimensions of type WEEK, Period 1 in a given calendar year is always the first period that ends in that year. For example, suppose you specify a dimension of type WEEK with a four-week period ending on June 7, 1997. DEFINE works backward and forward from this date, forming weeks into four-week periods. For 1997, Period 1 is the period beginning on December 22, 1996 and ending on January 18, 1997.
Example 9-82 Defining a YEAR Dimension
The following statement defines a dimension of type YEAR that holds values for fiscal years that end on June 30.
DEFINE fyear DIMENSION YEAR ENDING june
After defining the dimension, you can give it a description and a VNF (value name format). You can use a MAINTAIN statement to give values to the dimension.
LD Fiscal years ending June 30 VNF 'FY<ff>' MAINTAIN fyear ADD 'FY97' 'FY00'
Example 9-83 Using the Default Phrase for Date in an ENDING Phrase
This example illustrates how DEFINE automatically supplies a phase that begins with the date '31DEC1899' when you define a multiple-period dimension of type WEEK but you do not specify a BEGINNING phase or an ENDING phase argument. Assume that you issue the following statements
DEFINE twoweek DIMENSION 2 WEEK DESCRIBE TWOWEEK
When you issue a DESCRIBE statement for twoweek, the following output is produced.
DEFINE twoweek DIMENSION 2 WEEK ENDING '13Jan1900'
The DEFINE DIMENSION (conjoint) command defines a conjoint dimension.
Conceptually, you can think of a conjoint dimension consisting of two structures:
The dimension object itself. The values of the dimension are combinations of values of two or more other dimensions (that is, a conjoint tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the conjoint dimension.
An index between the conjoint dimension values and its base dimension values.
Composites are another object that you can use to dimension a variable using a list of dimension value combinations. See "Differences Between Conjoint Dimensions and Composites" for a discussion of the major differences between composites and conjoint dimensions.
DEFINE name DIMENSION <dims. . .> index-algorithm [AW workspace] [SESSION]
where index-algorithm specifies the algorithm that Oracle OLAP uses to create the index into the conjoint dimension. Valid values for index-algorithm are:
The name of the conjoint dimension 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 conjoint dimension.
One or more previously defined dimensions that are the base dimensions of the conjoint dimension. Specify the dimensions in fastest to slowest-varying order as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping". You must enclose the dimension list in angle brackets.
Typically, a base dimension of a conjoint dimension is a simple dimension, but it can also be another conjoint dimension. However, when you do have a simple dimension for one value of dims, you cannot also specify for dims a conjoint or concat dimension that has same simple dimension as one of its bases.
Specifies the creation of a b-tree index to relate conjoint values to base dimension values. Typically, you specify BTREE as the index algorithm for a conjoint dimension.
Tip:
When you are unsure whether to specify BTREE or NOHASH, use NOHASH, because you can always use a CHGDFN statement to change a NOHASH conjoint into a BTREE conjoint, while you can use a CHGDFN statement to change a BTREE conjoint into a NOHASH conjoint only when the conjoint was originally defined as a NOHASH conjointSpecifies that Oracle OLAP does not create an index for the conjoint dimension, but instead uses internal structures to relate conjoint values to base dimension values. Because no index is created for NOHASH, NOHASH decreases the number of structures associated with the conjoint dimension; and, in many cases, decreases the time it takes to load and access conjoint dimension values. However, NOHASH is used infrequently, as it is a complicated algorithm that, on occasion, can result in unpredictable performance.
(Default, but not recommended.) Specifies the creation of a has index to relate conjoint values to base dimension values.
Tip:
Even though HASH is the default, typically, you specify BTREE as the index algorithm for a conjoint dimension. When your conjoint dimension has more than 3 base dimensions, for best performance, use BTREE instead of HASH.The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. 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 you close the current session, the object no longer exists.
Differences Between Conjoint Dimensions and Composites
You can use either a composite or a conjoint dimension to dimension a variable with a list of dimension value combinations. Keep the following points in mind when deciding on which type of object to use:
Object population maintenance—Conjoint dimensions offer the most control, while composites provide the greatest ease of use:
Oracle OLAP determines the dimension value combinations stored in a composite. Oracle OLAP populates a composite automatically when a variable dimensioned by composite is populated.
You determine the dimension value combinations that are stored in a composite. You must explicitly populate and maintain a conjoint dimension using MAINTAIN statements the same way you populate and maintain other dimensions.
Dimension operations —You can perform dimension operations on conjoint dimensions, but not composites; however, you can only perform dimension operations on the base dimensions of composites. For example, you can LIMIT conjoint dimensions, but you must limit the base dimensions of a composite to limit your view to a subset of composite values; and you can define relations using conjoint dimensions, but not composites.
For more information on composites, see the DEFINE COMPOSITE command.
Relationship of Conjoint Dimensions to Base Dimensions
The values of the conjoint dimension are related to the base dimensions. You can specify data in a variable dimensioned by the conjoint dimension using the conjoint value combinations, the individual values of the base dimensions, or other dimensions related to either of the base dimensions of the conjoint dimension.
Defining a Subset of a Dimension's Values
You can have a conjoint dimension with only one base dimension, which enables you to create a subset of that dimension's values. You must still enclose that one base dimension within angle brackets.
Using Conjoint Dimension Values in Expressions
To refer to the value of a conjoint dimension in an expression, specify the value following these guidelines:
Enclose the entire dimension value specification in angle brackets and then enclose this entire specification in single quotes; do not enclose the individual values in single quotes.
Use the exact upper- and lowercase spellings for the base dimension values.
When the specification includes a text value with an embedded blank, you must separate the dimension values with commas.
For example, when item.org is a conjoint dimension with base dimensions item and org, use the following format to refer to values of item.org.
'<Expenses, Direct Sales>'
Example 9-84 Defining a Conjoint Dimension
Assume that you have defined and populated the simple dimensions city, state, and region and that they have the following values.
CITY STATE REGION --------- ---------- ------ Princeton New Jersey East Newark New Jersey Central Patterson New York New York Illinois Chicago Indiana
To define a conjoint dimension named cityandstate and add values to it use the following OLAP DML statements.
DEFINE cityandstate DIMENSION <city state> MAINTAIN cityandstate add <'Princeton' 'New Jersey'> MAINTAIN cityandstate add <'Newark' 'New Jersey'> MAINTAIN cityandstate add <'Patterson' 'New Jersey'> MAINTAIN cityandstate add <'New York' 'New York'> MAINTAIN cityandstate add <'Chicago' 'Illinois'> MAINTAIN cityandstate add <'Princeton' 'Indiana'>
The DEFINE DIMENSION CONCAT commands defines a concat dimension. A concat dimension is a dimension that groups a set of base dimensions with duplicate values or different data types into one dimension.
When there are duplicate data values, you create a non-unique concat dimensions. For example, you would create a nonunique dimension for a geography hierarchy when "New York" is both the value at the city level and at the state level. When all of the data values in all of the base dimensions are unique, you can create a unique concat dimension.
Tip:
The way that you specify the values of concat dimension varies depending on whether the concat dimension is a unique or nonunique concat dimension. See "Specifying a Value of a CONCAT Dimension" for more information.The name of the object 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 concat dimension.
One or more previously-defined dimensions that are the base dimensions of the concat dimension. Specify the dimensions in fastest to slowest-varying order as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping". You must enclose the dimension list in parenthesis.
The types of dimensions that can be base dimensions varies depending on whether you are defining a unique or nonunique concat dimension:
When defining a non-unique concat dimension, a base dimension can be a simple dimension of any data type, a conjoint dimension, or another concat dimension.
When defining a unique concat dimension, a base dimension can be a simple dimension of type TEXT or ID, or another unique concat dimension if the data values of all of the base dimensions are unique and not duplicated in any of the base dimensions.
A composite cannot be the base dimension of a concat dimension.
Simple dimensions and conjoint dimensions are the bottom-level components of a concat dimension. When you specify a concat dimension as a base dimension when defining a concat, then the base dimensions of that inner concat are component dimensions of the outer concat.
The same dimension cannot appear more than once in the component dimensions of a concat dimension. However, in a concat, a conjoint dimension is an indivisible unit and Oracle OLAP does not consider the base dimensions of a conjoint in the definition of the concat. Therefore, a simple dimension can be a base dimension of a conjoint and that conjoint and the same simple dimension can be base dimensions (or components) of a concat dimension.
For example, the following definitions are permissible.
DEFINE conjointdim.a DIMENSION <simpledim.b, simpledim.c> DEFINE conjointdim.b DIMENSION <simpledim.a, simpledim.b> DEFINE conjointdim.c DIMENSION <simpledim.a, conjointdim.a> DEFINE concatdim.a DIMENSION CONCAT (simpledim.a, conjointdim.a) DEFINE concatdim.b DIMENSION CONCAT (simpledim.a, conjointdim.b) DEFINE concatdim.c DIMENSION CONCAT (simpledim.b, conjointdim.b) DEFINE concatdim.d DIMENSION CONCAT (simpledim.a, concatdim.c)
In the definition of concatdim.a, the base dimensions are simpledim.a and conjointdim.a. In the definition of concatdim.d, the base dimensions are simpledim.a and concatdim.c. The component dimensions of concatdim.d are simpledim.a, simpledim.b, and conjointdim.b. simpledim.a and simpledim.b appear only once as component dimensions even though they are the base dimensions of conjointdim.b because the base dimensions of a conjoint are not component dimensions of a concat.
However, the following definition is not permitted because the same simple dimension is a base dimension of concatdim.e and a component of concatdim.e because it is a base dimension of concatdim.b.
DEFINE concatdim.e DIMENSION CONCAT (simpledim.a, concatdim.b)
Note:
The simple dimensions in the basedimlist argument, and the simple dimensions that are base dimensions of any conjoint dimensions or concat dimensions in basedimlist, cannot have an INTEGER data type.Specifies that the text values of the base dimensions are unique. When you specify this keyword, the dimensions listed in basedimlist must be either simple text or ID dimensions or unique concat dimensions.
Indicates that the dimension's values are only temporary and only for the current session. The dimension has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the dimension is saved. When you leave end your session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary dimension are NA.
The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. 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 you close the current session, the object no longer exists.
Example 9-85 Defining a CONCAT Dimension
Assume that you have defined and populated the simple dimensions city, state, and region and that they have the following values.
CITY STATE REGION --------- ---------- ------ Princeton New Jersey East Newark New Jersey Central Patterson New York New York Illinois Chicago Indiana
You define a concat dimension based on these dimensions using the following OLAP DML statement.
DEFINE geog DIMENSION CONCAT(region cityandstate)
The values of geog are the following.
<REGION: East> <REGION: Central> <CITYANDSTATE: <Princeton New Jersey>> <CITYANDSTATE: <Newark New Jersey>> <CITYANDSTATE: <Patterson New Jersey>> <CITYANDSTATE: <New York New York>> <CITYANDSTATE: <Chicago Illinois>> <CITYANDSTATE: <Princeton Indiana>>
The DEFINE DIMENSION ALIASOF command defines a dimension alias for a simple dimension. An alias dimension has the same type and values as its base dimension. Typically, you define an alias dimension when you want to dimension a variable by the same dimension twice.
Additionally, You can use a LIMIT statement to limit alias dimensions and define variables and relations using an alias dimension. However, you cannot maintain an alias dimension directly; instead you maintain its base dimension using MAINTAIN.
The name of the object 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 dimension. Indicates that the dimension being defined is an alias for another dimension.
The name of a simple dimension for which you want to define an alias. This dimension cannot be a concat or conjoint dimension, composite, or surrogate.
Indicates that the dimension's values are only temporary and only for the current session. The dimension has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the dimension is saved. When you leave end your session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary dimension are NA.
The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. 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 you close the current session, the object no longer exists.
Example 9-86 Defining an Alias Dimension
Assume that your department has multiple projects that employees participate in and that an employee may be a leader of one project and a participant in another. Assume also that you want to track the hours that each employee participates in a project as either a leader or a participant. To keep track of this information, you can design a variable that is dimensioned by the time you want to track by (in this example, year), project, and two dimensions for employee—one dimension named employee for employee as participant and another dimension named leader for employee as leader. The following definitions support this structure.
DEFINE year DIMENSION TEXT DEFINE project DIMENSION TEXT DEFINE employee DIMENSION TEXT DEFINE leader DIMENSION ALIASOF employee DEFINE hours VARIABLE INTEGER <year project employee leader>
The following statements populate all of the dimensions.
MAINTAIN year ADD '2001' '2002' '2003' MAINTAIN project ADD 'projA' 'projB' MAINTAIN employee add 'Adams' 'Baker' 'Charles'
Note that you do not have to explicitly populate the alias dimension (that is, leader). When you populate the employee dimension, Oracle OLAP also populates its alias dimension leader.
EMPLOYEE -------------- Adams Baker Charles LEADER -------------- Adams Baker Charles
You can limit a dimension without limiting its alias; or limit an alias without limiting the dimension for which it is an alias. For example, when you issue the following statements to limit employee to Adams for project ProjA in year 2001, a report displays all of the leaders of the projects that Adams participates in.
LIMIT year TO '2001'
LIMIT employee TO 'Adams'
LIMIT project TO 'projA'
REPORT DOWN leader ACROSS employee: hours
PROJECT: projA
YEAR: 2001
--HOURS---
-EMPLOYEE-
LEADER Adams
-------------- ----------
Adams 1
Baker 2
Charles 1
On the other hand, when you limit leader to Adams for project ProjA in year 2001, a report displays all of the employees of the projects that Adams leads.
LIMIT employee TO ALL
LIMIT leader TO 'Adams'
LIMIT project TO 'projA'
REPORT DOWN leader ACROSS employee: hours
PROJECT: projA
YEAR: 2001
-------------HOURS--------------
------------EMPLOYEE------------
LEADER Adams Baker Charles
-------------- ---------- ---------- ----------
Adams 1 3 3
The DEFINE command with the FORMULA keyword adds a new formula object to an analytic workspace. You define a formula to save an expression. A formula can take the place of an expression you use repeatedly. The name of the formula takes the place of the text of the expression. Oracle OLAP does not store the data for a formula in a variable; instead it is calculated at run time each time it is requested.
See also:
"OLAP DML Formulas"The name of the object 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 formula.
The calculation to be performed to produce values when you use the formula. It can be any valid expression, including a constant or the name of a variable as described in Chapter 2, "OLAP DML Expressions".
You can specify an expression for a formula when you define it or after you define using an EQ statement. When you define a formula without specify an expression, a formula returns NA with the specified data type.
Note:
Oracle OLAP does not automatically convert text in a formula to uppercase.The intended data type for the formula when you do not specify a value for expression. You can use any of the data types that apply to variables. If you do not specify a value, the data type is determined at run time.
When you include an expression in the formula definition, DEFINE automatically determines the data type for a formula defined using expression. Later, when you add the expression using an EQ statement, its data type should match the type you specify now. When it does not, DEFINE converts the output to the specified type.
The dimensions of the formula. Enclose the list in angle brackets. The dimensions argument is optional. When the formula is a single-cell value, you do not specify any dimensions. Also, when you include an expression in the definition, you do not specify a value. DEFINE automatically determines the dimensions.
However, when you do not include an expression in the definition, you must specify the dimensions. When you add the expression later using an EQ statement, the expression must have the same dimensions as the formula definition. When it does not, DEFINE forces the output to have the specified dimensions.
Restriction:
You cannot define a formula that is dimensioned by a composite.The name of an attached workspace in which you want to define the formula. When the formula 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 you close the current session, the object no longer exists.
Effect of Changing the Characteristics of Objects Used by a Formula
When you change the name, data type, or dimensions of any of the objects used by a formula, the formula is not automatically updated. The formula causes an error when objects it refers to have been deleted or are now the wrong data type.
Storing Complex Expressions and Calculations
To define a very complex calculation, you can define a program that uses a RETURN statement to return a value. You can then use the program as a function wherever you would use an expression or formula.
Example 9-87 Defining a Formula
This example adds a formula named sales.diff to an analytic workspace. This formula calculates the percent difference between total sales for the current year and last year.
The statements
DEFINE sales.diff FORMULA LAGPCT(TOTAL(actual year) 1 year) DESCRIBE sales.diff
produce the following definition.
DEFINE sales.diff FORMULA DECIMAL <year> EQ lagpct(TOTAL(actual year) 1 year)
The DEFINE command with the MODEL keyword adds a new model object to an analytic workspace. A model is a set of interrelated equations. The calculations in an equation can be based either on variables or on dimension values. You can assign the results of the calculations directly to a variable or you can specify a dimension value for which data is being calculated. For example, in a financial application, all the equations might be based on the values of a line item dimension, and data would be calculated for line items such as total expenses and net income.
Note:
Defining a model merely creates a model object in the analytic workspace. You must also code a specification for the model, as described in MODEL.The name of the object 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 model.
The name of an attached workspace in which you want to define the object. For more information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Example 9-88 Defining a Simple Model
This example shows a simple model named income.calc that calculates the line items in an income statement. The model equations are based on the line dimension in the demo workspace. First, define the model and give it an LD.
DEFINE income.calc MODEL LD Model for calculating Income Statement items
Then use a MODEL statement to enter the specification for the model. For this example, you can enter model lines such as the ones in the following model description.
DEFINE income.calc MODEL LD Model for calculating Income Statement items MODEL dimension line net.income = opr.income - taxes opr.income = gross.margin - (marketing+selling+r.d) gross.margin = revenue - cogs END
To solve the model for the actual variable, enter data in actual for the input line items (Revenue, Cogs, Marketing, Selling, R.D, and Taxes). Then execute the following statement.
income.calc actual
The DEFINE command with the PARTITION TEMPLATE keywords adds a new partition template object to an analytic workspace. A partition template is a specification for the partitions of a partitioned variable. A partitioned variable is stored as multiple rows in the relational table of LOBs that is the analytic workspace—each partition is a row in the table. You define both partitioned and unpartitioned variables using DEFINE VARIABLE statements. Before you can define a partitioned variable you must first define a partition template object.
DEFINE name PARTITION TEMPLATE <dimlist> PARTITION BY
{RANGE|LIST} (dims_partitioned_by) ([partition_definition_statement...]) [AW workspace]
where partition_definition_statement defines a partition. The syntax varies depending on whether you specify RANGE or LIST:
For RANGE:
PARTITION partition-name VALUES LESS THAN const-exp <partition-dimlist>
For LIST:
PARTITION partition-name VALUES ([valuelist)] <partition-dimlist>
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
A list of all of the logical dimensions for the variable that you are partitioning. You must enclose the names of the dimensions in a single set of angle brackets (< >). You must define a dimension before you can include it in the definition of a partition template.
The subset of dimensions specified by dimlist that actually specify the partitions of the variable. For range and list partitioning (that is, when you specify either the RANGE or LIST keywords), you can specify only one dimension for dims_partitioned_by. You cannot partition a variable along an INTEGER dimension.
The name of the partition.
Indicates that you are specifying a RANGE partition by comparing values.
A constant expression that has the same data type as the data type of the dimension specified for dims_partitioned_by.
A list of all of the dimensions of the partition template object (although the dimensions may be members of a composite). You must enclose the names of the dimensions in a single set of angle brackets (< >). Use this argument to specify the composite (if any) used to dimension the partitions that correspond to partition-name. When you do not specify a value then the partition is dimensioned densely by all of the dimensions of the partition template object.
Indicates that you are specify a LIST partition by specifying values.
A list of dimension values, separated by commas. You must surround text values with single quotes (for example, 'mytext'). Specify values of conjoints by specify the values of the base dimensions, separated by a comma, in a single set of angle brackets (for example, <'Value1', 'Value2'>). Specify values of nonunique concat dimensions by specify the values of the base dimensions, separated by a colon, in a single set of angle brackets (for example, <'Value1': 'Value2'>).
Tip:
I f you want to use a valueset object to specify values, do not specify values for valuelist. Instead, omit valuelist from the partition template definition and use a MAINTAIN ADD TO PARTITION statement to specify values for the partition.The DEFINE command with the PROGRAM keyword adds a new OLAP DML program object to an analytic workspace. An OLAP DML program is a collection of OLAP DML statements that helps you accomplish some workspace management or analysis task. Defining a program merely creates a program object in the analytic workspace. You must also code the actual lines of the program.
See also:
"Creating OLAP DML Programs"The name of the object 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 program.
The data type of the value to be returned by the program when it is called as a function. You can use any of the data types that apply to variables.
The name of a dimension, whose value the program returns when it is called as a function. The return value is a single value of the dimension, not a position (INTEGER). The dimension must be defined in the same workspace as the program.
The name of an attached workspace in which you want to define the program. When the program returns a dimension, the program must be defined in the same workspace as the dimension. 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 you close the current session, the object no longer exists.
Use a RETURN statement in a program when you want it to return a value. The argument to the RETURN statement is an expression that specifies the value to return. When the expression does not match the declared data type or dimension, the value is converted (if possible) to the declared data type or dimension value.
When you do not specify a data type or dimension in the definition of a program, its return value is treated as worksheet data and Oracle OLAP converts any return value to the data type required by the calling context which may lead to unexpected results.
For a program to return a value, you must call the program as a function. That is, you must use it as an expression in a statement. In the following example, the program isrecent is being treated as a function. It is an argument to the REPORT command.
REPORT isrecent(actual)
When the program returns values of a dimension, the program is in the output of the LISTBY function, and OBJ(ISBY) is TRUE for the dimension.
See the entries for the ARGUMENT, CALL, and RETURN commands for more information about programs as user-defined functions.
Example 9-89 Basing Program Flow on Test Results
The saleseval program tests whether total sales for a month exceeds total planned sales for the month. The program executes different statements based on the results of the test.
DEFINE SALESEVAL PROGRAM
PROGRAM
ARGUMENT onemonth MONTH
VARIABLE excess DECIMAL
ALLSTAT
LIMIT month TO onemonth
IF TOTAL(sales, month) GT TOTAL(sales.plan, month)
THEN DO
excess = (TOTAL(sales, month) -
- TOTAL(sales.plan, month)) -
/ TOTAL(sales.plan, month) * 100
SHOW JOINCHARS('Sales exceeded plan by ' excess '%.')
DOEND
ELSE SHOW JOINCHARS('We\'re not meeting plan. ' -
'Let\'s get working!')
REPORT DOWN product W 10 ACROSS district: sales - sales.plan
END
When total sales for the month exceeds total planned sales for the month, the THEN statement lines are executed. The program calculates the percentage by which actual sales exceeds planned sales and places the result in a numeric variable called excess. The program then sends the results to the current outfile. The JOINCHARS function is used to combine the calculated expression excess with the text expression "Sales exceeded plan by" in the output.
When total sales does not exceed planned sales, the ELSE statement line is executed and a different message is produced.
After the THEN or ELSE statement lines are executed, control flows to the next line in the program, and a report of sales in excess of plan is produced.
The DEFINE command with the RELATION keyword adds a new relation object to an analytic workspace. A relation describes a correspondence between the values of two or more dimensions. It can have dimensions, just like a variable, but the values of the relation must be values from the related dimension.
Note:
Defining a relation merely adds the definition of the relation to the analytic workspace; it does not populate the relation. To populate relations using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements.The name of the object 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 relation.
Specifies the dimension to which one or more dimensions are related. A relation is normally used to store information about the relationship between two dimensions; for example, the cities that belong in each region.
In the definition, the dimension having fewer values is normally specified as the related dimension (for example, regions). The dimension having more values is normally specified as a dimension of the relation (for example, cities).
The names of the dimensions of the relation. You must enclose the names of the dimensions in a single set of angle brackets (< >). You must define a dimension before including it in the definition of a relation. Do not include composites in the dimension list.
Restriction:
Oracle OLAP does not support the use of composites as dimensions for relations. Do not attempt to define them.Tip:
When defining two relations between the same dimensions, use the RELATION command to identify which relation is the default relation.Indicates that the values of the relation are only temporary. The relation is defined in the current workspace and can contain values during the current session. However, when you update and commit the workspace, only the definition of the relation is saved. When you end the session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary relation are NA.
The name of an attached workspace in which you want to define the relation. The relation 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. The behavior specified by the SESSION keyword differs from the behavior specified by the TEMP keyword which is that the values are temporary, but the object definition remains in the workspace in which you create it.
Example 9-90 Creating, Populating, and Totaling by a Relation
The following example defines a relation between division and product, stores the values of the relation, and then totals units by division, even though units is dimensioned by product. The following statement defines the div.prod relation.
DEFINE div.prod RELATION division <product>
The following statements store values of division in div.prod.
LIMIT product TO 'Tents' 'Canoes' div.prod = 'Camping' LIMIT product TO 'Racquets' div.prod = 'Sporting' LIMIT product TO 'Sportswear' 'Footwear' div.prod = 'Clothing'
You can use a REPORT statement to see the values stored in div.prod.
report div.prod
This statement produces the following output.
PRODUCT DIV.PROD ------------- ---------- Tents Camping Canoes Camping Racquets Sporting Sportswear Clothing Footwear Clothing
The div.prod relation lets you look at division totals in a report, even though the data is dimensioned by product.
REPORT TOTAL(units division)
The DEFINE command with the SURROGATE keyword adds a new dimension surrogate object to an analytic workspace. A surrogate provides an alternative set of values for a dimension. You can use a surrogate rather than a dimension in a model, in a LIMIT command, in a qualified data reference, or in data loading with statements such as FILEREAD, FILEVIEW, SQL FETCH, and SQL IMPORT.
Note:
Defining a surrogate merely adds the definition of the dimension surrogate to the analytic workspace; it does not populate the surrogate. To populate surrogates using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements.DEFINE name SURROGATE targetname type [AW workspace] [SESSION]
where type has the following syntax:
[TEXT|NTEXT] [WIDTH n]|ID|INTEGER|NUMBER (precision[, scale] | datatime-datatype)
The name of the object 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 dimension surrogate.
The name of the dimension for which you are creating a surrogate. See "Restrictions on the Use of Surrogates" for points to keep in mind when determining the target.
The data type for a dimension surrogate with text values. When all the values of a dimension surrogate are eight single-byte characters or less, give it a data type of ID. When one or more dimension values has more than eight single-byte characters, you must give it a data type of TEXT or NTEXT. For greater efficiency and ease of use, give dimensions a data type of ID whenever possible.
For TEXT or NTEXT dimension surrogate, the width, in bytes, of the storage area of each value of an object. Valid width values are 1 through 4000. Specify a fixed width only when you are certain that the values of a particular dimension surrogate are of similar size. When a value exceeds the specified width, Oracle OLAP truncates it.
The data type for a dimension surrogate with values that are the ordinal positions (1, 2, and so on) of the values in its dimension. You might create an INTEGER type dimension surrogate for a NUMBER type dimension so that you can specify dimension values by position instead of by the value of the dimension. When you define an INTEGER type dimension surrogate, Oracle OLAP automatically assigns an INTEGER value to the surrogate for each of the positions in the dimension.
Specifies that the dimension surrogate has a data type of NUMBER. See "Numeric Data Types" for more information.
Specifies the total number of characters in the value of a dimension surrogate of type NUMBER.
Specifies the number of characters that can be to the right of a decimal point of a dimension surrogate of type NUMBER.
Specifies a datetime data type (that is, DATETIME , TIMESTAMP, TIMESTAMP_TZ, or TIMESTAMP-LTZ). See "Datetime and Interval Data Types" for more information.
The name of an attached workspace in which you want to define the dimension surrogate. The dimension for which you define the surrogate must be defined in the same workspace. 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 you close the current session, the object no longer exists. Use this keyword when the definition of the targetname dimension includes SESSION.
Restrictions on the Use of Surrogates
Keep the following restrictions in mind when determining a target for your surrogate:
You cannot create a surrogate for a dimension that has a type of DAY, WEEK, MONTH, QUARTER, or YEAR or for a composite.
When you create a surrogate for a conjoint, you cannot convert the conjoint to a composite.
You cannot specify a dimension surrogate as the dimension or related dimension argument when you define a concat dimension, a formula, a program, a relation, a valueset, or a variable. Additionally, in data loading you cannot create new dimension values using a dimension surrogate
Example 9-91 Creating an INTEGER Dimension Surrogate
The following statement creates an INTEGER type dimension surrogate for the store_id dimension.
DEFINE storepos SURROGATE store_id INTEGER
Example 9-92 Creating a NUMBER Dimension Surrogate
The following statement creates an NUMBER type dimension surrogate for the product dimension, which is a TEXT dimension that has product names as values. The precision argument to the NUMBER keyword specifies that a value in prodnum can have no more than seven characters and the scale argument specifies that no more than three characters can be to the right of the decimal point.
DEFINE prodnum SURROGATE product NUMBER(7, 3)
The following statement sets the first value of prodnum to 1083.375.
prodnum(product 1) = 1083.375
The DEFINE command with the VALUESET keyword adds a new valueset object to an analytic workspace. A valueset is a list of dimension values for one or more dimensions. You use a valueset to save dimension status lists across sessions.
Note:
Defining a valueset adds the definition of the valueset to the analytic workspace and sets all of its values to null (NA). To assign values to a valueset use the LIMIT command. You can also use a STATUS statement and the STATFIRST, INSTAT, and VALUES functions to work with a valueset.The name of the object 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 valueset.
The name of the previously-defined dimension whose values you want to store in the valueset.
When defining a multi-dimensional valueset, the names of the previously-defined dimensions by which you want the valueset dimensioned.
Indicates that the values of the valueset are only temporary. The valueset has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the valueset is saved. When you end the session or switch to another workspace, the values are discarded. Each time you start the workspace, the value of a temporary valueset is null.
The name of an attached workspace in which you want to define the valueset. The valueset 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. The behavior specified by SESSION is different from the behavior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.
Example 9-93 Creating and Assigning Values to a Valueset
This example adds the valueset named lineset to the demonstration workspace. The lineset valueset is dimensioned by line, and therefore it can be limited by the current values of the line dimension. The LD statement attaches a description to the object.
The following statements 1) limit the line dimension and display the values in status, 2) create a valueset named lineset by defining valueset and limiting the valueset to those values currently in status for the line dimension, and 3) display the values of the lineset.
LIMIT line TO FIRST 2 STATUS line
The current status of LINE is: REVENUE, COGS " Define the valueset and specify a long description for it
DEFINE lineset VALUESET line LD Valueset for LINE dimension values " Assign the values that are currently in status for line " as the values of valueset LIMIT lineset TO line UPDATE SHOW lineset
Revenue Cogs
Example 9-94 Creating and Assigning Values to a Multidimensional Valueset
Assume that your analytic workspace has the variables and dimensions with the following definitions.
DEFINE geography DIMENSION TEXT DEFINE product DIMENSION TEXT DEFINE sales VARIABLE DECIMAL <geography product> DEFINE salestax VARIABLE DECIMAL <geography>
Assume also that the analytic workspace contains the following dimensions whose values are the names of variables and dimensions within the workspace.
DEFINE all_variables DIMENSION TEXT MAINTAIN all_variables ADD 'sales' 'salestax' DEFINE all_dims DIMENSION TEXTMAINTAIN all_dims ADD 'geography' 'product'
The following statements create and populate a valueset for the values of all_variables and all_dims, and then report the values of that valueset.
DEFINE variables_dims VALUESET all_dims <all_variables>
" Assign all values of all_dims and all_variables to the valueset
LIMIT variables_dims TO ALL
REPORT variables_dims
ALL_VARIABLES VARIABLES_DIMS
---------------- ------------------------------
sales geography
product
salestax geography
product
To create a multidimensional valueset that has the correct dimensions related to the variables that use them, you issue the following statement that uses a QDR to limit the all_dims values for the salestax value of all_variables.
LIMIT variables_dims(all_variables 'salestax') TO 'geography'
REPORT variables_dims
ALL_VARIABLES VARIABLES_DIMS
---------------- ------------------------------
sales geography
product
salestax geography
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 program variables using a VARIABLE command. These variables exist only when the program is running.
Note:
Defining a variable merely adds the definition of the variable to the analytic workspace; it does not populate the variable. To populate variables using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements.DEFINE name [VARIABLE] datatype [<dims...>] [WITH NULLTRACKING] [WITH AGGCOUNT] -
[PERMANENT | TEMP ] -
[ RANSPACE64] [(partition-instance...)] [WIDTH n] [AW workspace] [SESSION]
where:
dims are the dimensions of the variable 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:
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 multiple objects with the same dimensions, most operations 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]
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 data type of the data to be stored in the variable. The data types, their abbreviations, and the range of acceptable values are shown in Table 2-1, "Summary of OLAP DML Data Types".
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.
When defining a TEXT, NTEXT, or RAW variable, specify this keyword to increase the maximum number of characters for the values of the variable from nearly 2**32 to nearly 2**64.
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.
When the variable is dimensioned by a composite, specifies that Oracle OLAP create NA2 bits for the cells of the variable.
See also:
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 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, after you update and commit, the values of a variable or a partition of a variable are saved or discarded when you 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.
By default, a top-level partition of a variable has the same permanence as the variable that contains it. Specifically, a partition of a temporary variable is a temporary partition unless you use the PERMANENT keyword to make it a permanent partition, and a 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, a 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 4,000 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.
Specifies a partition of the variable where partition-name is 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 verify if a variable is partitioned correctly, use the PARTITIONCHECK function.
The name of an attached workspace in which you want 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. The behavior specified by SESSION is different than the behavior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.
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 statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE operator and that do not have a COUNT NO 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 9-6, "OLAP DML Statements for Aggcount Variables" lists these statements.
Table 9-6 OLAP DML Statements for Aggcount Variables
| Statement | Keywords | Description |
|---|---|---|
|
WITH AGGCOUNT |
Defines a variable and an associated Aggcount variable. |
|
|
Retrieves the values of the Aggcount variable associated with the specified variable. |
||
|
ADD|DROP AGGCOUNT |
Adds or drops an Aggcount variable for the specified variable. |
|
|
HASAGGCOUNT |
Returns a |
Relational fact tables sometimes have null facts (that is, facts that have a null value). Typically, when Oracle OLAP creates a variable dimensioned by a composite, it does not create a composite tuple for an NA (or null) value. Given this typical behavior, OLAP DML variables would not correspond to their base relational fact table because the variables would eliminate the null facts.
To support OLAP DML composite-dimensioned variables that correspond to relational fact tables with null facts, Oracle OLAP has a special NA bit called an NA2 bit. These NA2 bits tracks whether or not each cell of the variable has null value because the underlying relational table has a null fact. When the corresponding fact table has a null fact, you want Oracle OLAP to intentionally include an NA value in the composite tuples for the variable and NA2 bits are used by Oracle OLAP to do just that. NA2 bits are used by Oracle OLAP when it populates variables using the SQL IMPORT command, the AGGREGATE command, and variables that were created as materialized views. They are also used by Oracle OLAP when it populates a relational table using the OLAP_TABLE SQL function. Additionally, Oracle OLAP recognizes NA2 values when evaluating expressions using arithmetic and Boolean operators.
The OLAP DML provides the following statements for working with variables that have NA2 bits:
To create a variable with NA2 bits, use the DEFINE VARIABLE statement with the NULLTRACKING phrase.
To add NA2 bits to a variable that does not have NA2 bits, use the CHGDFN statement with the NULLTRACKING phrase.
To remove NA2 bits from a variable that has NA2 bits, use the CHGDFN statement with the DROP NULLTRACKING phrase.
For testing and debugging purposes, use the NA2 function to set one or more of the NA2 bit of a variable to TRUE. Use the NAFLAG function to identify if one or more values of a variable are NA values and, if so, if the NA value is just the typical NA values that OLAP should ignore or both the typical NA value and also an NA2 value.
See also:
Oracle OLAP User's Guide for more information on creating materialized views using Oracle OLAP
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 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. 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.
When you define variables and other dimensioned objects, and when you write programs that loop over multidimensional expressions in nested loops, always try to maximize performance by matching the fastest-varying dimension with the inner loop.
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 when 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".
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.
Example 9-95 Defining an INTEGER Variable with One Regular Dimension
This example adds the variable population to an analytic 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
Example 9-96 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
Example 9-97 Defining NUMBER Variables
The following statement defines a NUMBER variable named sales that is 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
Example 9-98 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 which 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.
Example 9-99 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 9-98, "Defining a Variable Dimensioned by Two Regular Dimensions". Now assume that you define 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 9-98, "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 the variable. You can confirm the number of physical values that are 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 which indicates that only the 87 non-NA values are stored as part of the sales_dims_composite variable.
Example 9-100 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 9-98, "Defining a Variable Dimensioned by Two Regular Dimensions". Now assume that you define 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 9-98, "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 "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 which 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 |
Example 9-101 Defining a Variable with 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') <time product>-
PARTITION time_2002 VALUES ('2002', 'Dec2002', 'Jan2002', '31Dec2002', '01Dec2002', '31Jan2002', '01Jan2002') <time product>)
(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 10-53, "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
Example 9-102 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
Example 9-103 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 is 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; no cells are created for any other values of product or region.
Example 9-104 Defining a Variable with Null Tracking
Assume that you have the following objects defined in your analytic workspace.
DEFINE GEOG DIMENSION TEXT LD A dimension with a simple hierarchy for geography DEFINE geog_levellist DIMENSION TEXT LD List of Levels in in the hierarchy of the geog dimension DEFINE GEOG_PARENTREL RELATION GEOG <GEOG> LD Self-relation for geog showing parents of each value in the hierarchy DEFINE GEOG_LEVELREL RELATION GEOG_LEVELLIST <GEOG> LD Level of each dimension member for geog DEFINE product DIMENSION TEXT LD A nonhierarchical dimension DEFINE time DIMENSION TEXT LD A hierarchical text dimension for time DEFINE time_levellist DIMENSION TEXT LD List of Levels in hierarchy of the time dimension DEFINE time_parentrel RELATION time <time> LD A self-relation for time show parents of each value in the hierarchy DEFINE TIME_LEVELREL RELATION TIME_LEVELLIST <TIME> LD Level of each dimension member for time DEFINE prod_geog COMPOSITE <product geog> COMPRESSED
Now assume that you define a sales variable that you want to have dimensioned by time and the prod_geog composite. You want this variable to have null tracking because you eventually populate it using SQL IMPORT and you know that some facts in the fact table have null values. To do this you issue the following statement that includes the WITH NULLTRACKING phrase.
DEFINE sales VARIABLE DECIMAL <time prod_geog<product geog>> WITH NULLTRACKING
For testing purposes, you populate the variable using the RANDOM function. After you populate the variable in this way, you issue a report on it that shows the NA values in the variable.
REPORT DOWN time ACROSS geog: sales
PRODUCT: TVs
-----------------------SALES-----------------------
-----------------------GEOG------------------------
TIME Boston Springfield Hartford All Places
-------------- ------------ ------------ ------------ ------------
2007 NA NA NA NA
2008 NA NA NA NA
All years NA NA NA NA
Jan07 NA NA NA NA
Feb07 NA NA NA NA
Mar07 NA NA NA NA
Apr07 NA NA NA NA
May07 NA NA NA NA
Jun07 NA NA NA NA
Jul07 NA NA NA NA
Aug07 NA NA NA NA
Sep07 NA NA NA NA
Oct07 NA NA NA NA
Nov07 NA NA NA NA
Dec07 NA NA NA NA
Jan08 NA NA NA NA
Feb08 NA NA NA NA
Mar08 NA NA NA NA
Apr08 NA NA NA NA
May08 NA NA NA NA
Jun08 NA NA NA NA
Jul08 NA NA NA NA
Aug08 NA NA NA NA
Sep08 NA NA NA NA
Oct08 NA NA NA NA
Nov08 NA NA NA NA
Dec08 NA NA NA NA
PRODUCT: Radios
-----------------------SALES-----------------------
-----------------------GEOG------------------------
TIME Boston Springfield Hartford All Places
-------------- ------------ ------------ ------------ ------------
2007 NA NA NA NA
2008 NA NA NA NA
All years NA NA NA NA
Jan07 24.59 23.70 33.12 28.65
Feb07 22.78 21.42 26.28 37.06
Mar07 25.74 32.08 22.75 24.62
Apr07 22.23 23.21 20.79 28.68
May07 20.51 29.71 30.35 33.05
Jun07 34.43 35.96 33.85 39.34
Jul07 24.86 38.02 36.78 31.22
Aug07 39.05 21.08 35.80 33.81
Sep07 34.38 21.69 25.04 33.40
Oct07 33.82 39.27 20.28 24.39
Nov07 25.48 23.03 32.45 39.94
Dec07 25.14 30.66 33.75 23.37
Jan08 NA NA NA NA
Feb08 NA NA NA NA
Mar08 NA NA NA NA
Apr08 NA NA NA NA
May08 NA NA NA NA
Jun08 NA NA NA NA
Jul08 NA NA NA NA
Aug08 NA NA NA NA
Sep08 NA NA NA NA
Oct08 NA NA NA NA
Nov08 NA NA NA NA
Dec08 NA NA NA NA
For testing purposes, you also generate a report using the NAFLAG function to retrieve the type of NAs that are in the variable. As the following report shows, because it was populated using RANDOM, all of the NAs are the typical NA values; they are not NA2 values.
REPORT DOWN time ACROSS geog: NAFLAG(sales)
PRODUCT: TVs
-------------------NAFLAG(SALES)-------------------
-----------------------GEOG------------------------
TIME Boston Springfield Hartford All Places
-------------- ------------ ------------ ------------ ------------
2007 1 1 1 1
2008 1 1 1 1
All years 1 1 1 1
Jan07 1 1 1 1
Feb07 1 1 1 1
Mar07 1 1 1 1
Apr07 1 1 1 1
May07 1 1 1 1
Jun07 1 1 1 1
Jul07 1 1 1 1
Aug07 1 1 1 1
Sep07 1 1 1 1
Oct07 1 1 1 1
Nov07 1 1 1 1
Dec07 1 1 1 1
Jan08 1 1 1 1
Feb08 1 1 1 1
Mar08 1 1 1 1
Apr08 1 1 1 1
May08 1 1 1 1
Jun08 1 1 1 1
Jul08 1 1 1 1
Aug08 1 1 1 1
Sep08 1 1 1 1
Oct08 1 1 1 1
Nov08 1 1 1 1
Dec08 1 1 1 1
PRODUCT: Radios
-------------------NAFLAG(SALES)-------------------
-----------------------GEOG------------------------
TIME Boston Springfield Hartford All Places
-------------- ------------ ------------ ------------ ------------
2007 1 1 1 1
2008 1 1 1 1
All years 1 1 1 1
Jan07 0 0 0 0
Feb07 0 0 0 0
Mar07 0 0 0 0
Apr07 0 0 0 0
May07 0 0 0 0
Jun07 0 0 0 0
Jul07 0 0 0 0
Aug07 0 0 0 0
Sep07 0 0 0 0
Oct07 0 0 0 0
Nov07 0 0 0 0
Dec07 0 0 0 0
Jan08 1 1 1 1
Feb08 1 1 1 1
Mar08 1 1 1 1
Apr08 1 1 1 1
May08 1 1 1 1
Jun08 1 1 1 1
Jul08 1 1 1 1
Aug08 1 1 1 1
Sep08 1 1 1 1
Oct08 1 1 1 1
Nov08 1 1 1 1
Dec08 1 1 1 1
Again, for testing purposes, you use the NA function to set an NA2 bit on the variable cells dimensioned by the months of 2008. The following code shows the result of issuing a SHOW of the NA2 function and using that function to set the NA2 bit on the cells dimensioned by the months in 2008.
SHOW NA2 NA LIMIT time TO 'Jan08' 'Feb08' 'Mar08' 'Apr08' 'May08' 'Jun08' 'Jul08' 'Aug08' 'Sep08' 'Oct08' 'Nov08' 'Dec08' saleswithnull= NA2
For brevity's sake assume that your test now issues the following three LIMIT statements and then reports on the sales variable and the NAFLAG function against the sales variable. As the NAFLAG report illustrate, the value Jan08 which is a month to which an NA2 value was assigned returns the value of 2 for NAFLAG, while the NAFLAG report still returns the value of 1 for the year 2008.
LIMIT product to 'Radios'
LIMIT time TO 'Jan08' '2008'
LIMIT geog TO 'Boston' 'All Places'
REPORT DOWN time ACROSS geog: sales
PRODUCT: Radios
----------SALES----------
----------GEOG-----------
TIME Boston All Places
-------------- ------------ ------------
Jan08 NA NA
2008 NA NA
REPORT DOWN time ACROSS geog: NAFLAG(sales)
PRODUCT: Radios
------NAFLAG(SALES)------
----------GEOG-----------
TIME Boston All Places
-------------- ------------ ------------
Jan08 2 2
2008 1 1
Now assume that you issue the following code to remove the NA2 bits from the sales variable.
CHGDFN sales DROP NULLTRACKING
A DESCRIBE of the sales variable shows that it no longer has the WITH NULLTRACKING phrase in its definition while a report of the results of NAFLAG show that the NA values are now just the typical NA values without an NA2 bit.
DESCRIBE sales
DEFINE SALES VARIABLE DECIMAL <TIME PROD_GEOG <PRODUCT GEOG>>
REPORT DOWN time ACROSS geog: sales
PRODUCT: Radios
----------SALES----------
----------GEOG-----------
TIME Boston All Places
-------------- ------------ ------------
Jan08 NA NA
2008 NA NA
"Report on the type of NA values in the sales variable
REPORT DOWN time ACROSS geog: NAFLAG(sales)
PRODUCT: Radios
------NAFLAG(SALES)------
----------GEOG-----------
TIME Boston All Places
-------------- ------------ ------------
Jan08 1 1
2008 1 1
The DEFINE command with the WORKSHEET keyword adds a new worksheet object to an analytic workspace. A worksheet, like a spreadsheet, is a two-dimensional object that is dimensioned by a worksheet row and a worksheet column. It can temporarily store data that you want to transfer between spreadsheet packages and workspace dimensions and variables.
When you first define a worksheet, it does not contain any values. You can populate a worksheet with values from an existing spreadsheet by using an IMPORT (spreadsheet) statement or add or delete values from worksheet row and a worksheet column dimensions with a MAINTAIN statement.
The name of the object 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 worksheet.
The names of the dimensions of the worksheet. When you supply this argument, you must give the names of two INTEGER dimensions for column-dim and row-dim. When you omit this argument, the worksheet is dimensioned automatically by WKSCOL and WKSROW. See "Worksheet Dimensions" for more information.
Indicates that the worksheet is only temporary. The worksheet is defined in the specified workspace and can contain values during the current session. However, when you update and commit, only the definition of the worksheet is saved. When you end your session or switch to another workspace, the data values are discarded.
The name of an attached workspace in which you want to define the worksheet. The worksheet 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. The behavior specified by SESSION is different than the behavior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.
A worksheet must always be dimensioned by two dimensions that represent a worksheet row and a worksheet column. The worksheet row and worksheet column dimensions can either be automatically created by Oracle OLAP or explicitly created by you:
If you have not created worksheet row and worksheet column dimensions and specified their names in the column-dim and row-dim arguments of DEFINE WORKSHEET, then Oracle OLAP automatically creates the following dimensions:
For the worksheet row, an INTEGER dimension named WKSROW with values from 1 to 63.
For the worksheet column, an INTEGER dimension named WKSROW with values from 1 to 63.
Note:
WhenWKSCOL and WKSROW already exist in any attached workspace, Oracle OLAP cannot create them in the current worksheet. In this case, the DEFINE WORKSHEET command fails to create a worksheet with these default dimensions. (Note, also, that WKSCOL and WKSROW do not appear in a worksheet description generated using DESCRIBE.)You create worksheet row and a worksheet column dimensions the same way you create any other simple dimension by issuing the following statements:
Create two simple INTEGER dimensions using a DEFINE DIMENSION (simple) statement. One dimension is for row numbers and the other is for column numbers.
Using MAINTAIN statements, populate one dimension with integers that represent row numbers and populate the other with integers that represent column numbers.
Example 9-105 Defining a Worksheet
These statements define a temporary worksheet named travelexp, which is dimensioned by columns and rows.
DEFINE itemsheet WORKSHEET DEFINE columns INT DIMENSION MAINTAIN columns ADD 5 DEFINE rows INT DIMENSION MAINTAIN rows ADD 10 DEFINE travelexp WORKSHEET <columns rows> TEMPORARY
Example 9-106 Importing Spreadsheet Data
You can import data from a spreadsheet to a worksheet. When all the cells contain the same type of data, you can use UNRAVEL to transfer the data to a variable with one statement. You can also limit the worksheet dimensions to a smaller group of cells and use UNRAVEL to transfer each group to a separate variable. To transfer imported data from a worksheet named itemsheet to a variable named items, you might use the following statements.
DEFINE itemsheet WORKSHEET IMPORT itemsheet FROM dif FILE 'file name' LIMIT WKSCOL TO FIRST 3 LIMIT WKSROW TO FIRST 10 items = UNRAVEL(itemsheet)