11 Acquiring Data From Other Sources

11.3.1 About the File Reader Programs

Table 11-1 describes the OLAP DML file reader commands. Refer to the Oracle OLAP DML Reference for the complete syntax, usage notes, and examples of these commands and functions.

11.3.2 Writing a Program for Reading Files

While reading from a file, you can format the data from each field individually, and use DML functions to process the information before assigning it to a workspace object. Reading a file generally involves the following steps.

1. Open the data file.

2. Read data from the file one record or line at a time.

3. Process the data and assign it to one or more workspace objects.

4. Close the file.

The FILEREAD and FILEVIEW commands have the same attributes and can do the same processing on your data. However, they differ in important ways:

• The FILEREAD command loops automatically over all records in the file and processes them automatically. Use FILEREAD when all records that you wish to read in the file are the same. FILEREAD is easier to use and faster than FILEVIEW.

  Because FILEREAD is able to process most files, it is shown in the examples in this chapter.

• The FILEVIEW command processes one record at a time. FILEVIEW is the more powerful of the two file-reading commands; it can process all of files that FILEREAD can, plus process different types of records.

Example 11-1 provides a template for a developing a file-reading program in the OLAP DML. Refer to "Fetching Dimensions Members From Tables" for strategies for reading dimension members.

VARIABLE funit INTEGER          "Define local variable for file handle
TRAP ON CLEANUP                 "Divert processing on error to CLEANUP label
funit = FILEOPEN('directory/datafile' READ)  "Open the file

"Read the file with FILEREAD
FILEREAD funit                                
   .
   .
   .
CLEANUP:                                   "Cleanup label
IF funit NE na                             "Close the file
   THEN FILECLOSE funit

11.3.3 Mapping Fields to Workspace Objects

The FILEREAD command maps fields to workspace objects. A source file can be structured with records in any of the following ways:

• Ruled files contain data in columns, with fields defined by a starting position and a width.

• Structured PRN files contain strings of text or numbers. A text field is enclosed in quotation marks. A number field can contain periods (.) in addition to numbers, but any other character, including spaces and commas, terminates the field.

• CSV files (for Comma-Separated Values) use a special character, the delimiter, to separate the fields in a record.

The target for the data in an analytic workspace is either a dimension, a relation, or a variable. Dimensions can either be maintained by adding new members, or they can be used just to align incoming data with existing dimension members. In standard form analytic workspaces, a variable is typically an attribute or a measure.

COLUMN n WIDTH n workspace_object

2         13         51         54          2
         2         13         51         56          2
         2         13         51         57          2
         2         13         51         58          2

FILEREAD funit RULED -
   COLUMN  1 WIDTH 10 channel -
   COLUMN 11 WIDTH 11 product -
   COLUMN 22 WIDTH 11 customer -
   COLUMN 33 WIDTH 11 time -
   COLUMN 44 WIDTH 11 units_variable

FIELD n workspace_object

FILEREAD funit STRUCTURED -
   FIELD 1 channel -
   FIELD 2 product -
   FIELD 3 customer -
   FIELD 4 time -
   FIELD 5 units_variable

FIELD n workspace_object

2,13,51,54,2
2,13,51,56,2
2,13,51,57,2
2,13,51,58,2

FILEREAD funit CSV DELIMITER ',' -
   FIELD 1 channel -
   FIELD 2 product -
   FIELD 3 customer -
   FIELD 4 time -
   FIELD 5 units_variable

11.3.4 Setting Dimension Status for Reading Measures

Whenever you read data values into a variable, you must set the status of each dimension. Typically, the incoming records contain a field for each dimension; when a record is processed in the analytic workspace, the dimensions are temporarily limited to these values so that data targeted at a variable or relation is stored in the correct cell. However, if the records omit one or more dimensions, then you must set them manually before reading the file.

For example, if your file contains data only for the Direct Sales channel for August 2003, and thus does not have fields specifying the channel or time, then your program must limit the CHANNEL and TIME dimensions before reading the file. Otherwise, the data is aligned with the first member of those dimensions (All Channels and Jan-98).

11.3.5 Optimizing a Data Load

Your data will load fastest if the variables in your analytic workspace are defined with fastest and slowest varying dimensions that match the order of records in the source data file. If you have control over the order of records in the source data file, then you can create the data file to match the variables in your analytic workspace. Otherwise, you may need to choose between optimizing for loads and optimizing for queries when defining the dimension order of variables in your analytic workspace.

For example, a data file might have records sorted in this order:

• Lists all records for the first channel, then all records for the second channel, and so forth.

• Lists all products for the first channel, then all products for the second channel, and so forth.

• Lists all customers for the first product, then lists all customers for the second product, and so forth.

• Lists all time periods for the first customer, then all time periods for the second customer, and so forth.

In a workspace variable definition, the order of the dimensions identifies the way the data is stored. The fastest-varying dimension is listed first, and the slowest-varying dimension is listed last.

For this sample file, the data load will proceed fastest if the target variable is defined with TIME as the fastest varying dimension and CHANNEL as the slowest varying dimension, so the dimensions are listed in this order: TIME PRODUCT CUSTOMER CHANNEL. With a composite dimension, the definition looks like this:

DEFINE UNITS_VARIABLE VARIABLE DECIMAL <TIME UNITS_CUBE_COMPOSITE <CUSTOMER PRODUCT CHANNEL>>

Having the TIME dimension as the fastest varying dimension outside the composite also provides good run-time performance for time-based analysis, because the time periods are clustered together. This is a best-case scenario, in which the workspace variables are optimized for queries, and the data file is sorted correctly for the fastest loads.

However, if you have a separate data file for each time period, then TIME becomes the slowest-varying dimension for the load. In this case, there is a conflict between the dimension order that optimizes queries, and the dimension order that optimizes data loads. You need to choose which dimension order is best under these circumstances.

If you have a small batch window in which to load data, you may need to optimize for the data load by defining variables with TIME as the last dimension, as shown here:

DEFINE UNITS_VARIABLE VARIABLE DECIMAL <UNITS_CUBE_COMPOSITE <CUSTOMER PRODUCT CHANNEL> TIME>

11.3.6 Reading and Maintaining Dimension Members

The records in a data file typically contain fields for dimension values that identify the cell in which the data values should be stored. When all of the dimension values in the file already exist in your analytic workspace, you can use the default attribute MATCH in the dimension field description. MATCH accepts only dimension values that already are in the analytic workspace.

When an incoming value does not match, the command signals an error. Your file reader program can handle the error by skipping the record and continuing processing, or by halting the processing and letting you check the validity of the data file. The test for whether the error is caused by a new dimension member or another type of error is based on the transient value of the ERRORNAME option when the error is signaled.

Example 11-2 provides a template for error handling that permits processing to continue when a dimension value in the data file does not match a dimension value in the analytic workspace.

When your data file contains all new, or a mixture of new and existing dimension values, you can add the new values and all the associated data to the analytic workspace by using the APPEND attribute in the field description, as shown here:

FILEREAD funit -
     COLUMN n APPEND WIDTH n dimension

VARIABLE funit INTEGER          "Define local variable for file handle
TRAP ON oops                    "Divert processing on error to oops label
funit = FILEOPEN('directory/datafile' READ)  "Open the file

next:                                      "Resume processing label
FILEREAD funit                             "Read the file with FILEREAD
   .
   .
   .

WHILE FILENEXT(funit)                      "Or read it with FILEVIEW
   DO
   FILEVIEW funit...
   DOEND

FILECLOSE funit                            "Close the file
RETURN                                     "End normal processing
oops:                                      "Error label
IF funit NE na AND ERRORNAME NE 'ATTN'
   THEN DO
   TRAP ON oops
   GOTO next                                "Resume processing at next label
   DOEND
IF funit NE na                              "Close the file on error
   THEN FILECLOSE funit

11.3.7 Transforming Incoming Values

The FILEREAD command enables you to modify values as they are read into the analytic workspace.

FILEREAD funit -     COLUMN 1 WIDTH 15 RSET '-04' time

FILEREAD funit -
     COLUMN 1 WIDTH 15 time = UPCASE(VALUE)

11.4.1 OLAP DML Support for SQL

You can use almost any SQL statement that is supported by Oracle in the OLAP DML SQL command. You can use SELECT to copy data from relational tables into analytic workspace objects. You can use the INSERT command to copy data from analytic workspace objects into relational tables.

The following Oracle SQL extensions are also supported:

• The FOR UPDATE clause in the SELECT statement of a cursor declaration, so that you can update or delete data associated with the cursor

• The WHERE CURRENT OF cursor clause in UPDATE and DELETE statements for interactive modifications to a table

• Stored procedures and triggers

COMMIT and ROLLBACK are ignored as arguments to the SQL command. To commit your changes, issue the OLAP DML UPDATE and COMMIT commands. You cannot roll back using the OLAP DML.

Most SQL commands are submitted directly to the SQL command processor; however, a small number are first processed in the OLAP DML, and their syntax may be slightly different from standard SQL. Refer to the Oracle OLAP DML Reference for further details.

Table 11-2 describes the OLAP DML commands that support embedded SQL.

11.4.2 Process: Copying Data From Relational Tables Into Analytic Workspace Objects

Using the OLAP DML, you can populate a standard form analytic workspace from relational tables by taking the following steps:

1. Define the analytic workspace objects that will hold the relational table data.

   Follow the steps listed in "How to Manually Create a Standard Form Analytic Workspace". Then browse the analytic workspace to identify the objects you need to populate.

2. Write an OLAP DML program for each dimension. Compile and run the programs.

   Read the following instructions in "Fetching Data From Relational Tables".

3. Write an OLAP DML program for each cube. Compile and run the programs.

   Read the instructions in "Fetching Measures From Tables".

11.4.3 Fetching Dimensions Members From Tables

There are several strategies for fetching dimension members. The best practice is to fetch just the dimension members first, and fetch their attributes as a separate step. For Time members, the best practice is to fetch one level at a time, making a separate pass through the source table for each level. This practice enables you to fetch the Time members in the correct order so that they do not need to be sorted afterward.

However, the simplest method, and the one shown here, populates dimension members at all levels, and all of the objects that support hierarchies, at the same time. Before using this method, be sure that SEGWIDTH is set correctly, as discussed in "Setting the Segment Size".

is a template that you can use for fetching dimensions in one pass. The program does the following:

• Reads the level columns one at a time, with their attribute columns, beginning with the top level (or most aggregate) and concluding with the base level. The syntax supports one hierarchy; refer to Example 11-18 for the equivalent syntax in FILEREAD for handling multiple hierarchies.

  Because the parent relation is being populated at the same time, the parents must be added to the workspace dimension before their children. Otherwise, an error will occur, because a relation only accepts as legitimate values the members of a particular dimension. This is not an issue if you load all dimension members first.

• Populates a Boolean member_inhier variable manually. The n shown in the syntax is either a 1 (for yes) or a 0 (for no).

• Populates the member_levelrel relation manually with the appropriate level name for the column. Level names must exactly match the members of the levellist dimension.

• Populates the member_parentrel relation with the parent dimension member from the appropriate column.

• Includes commands for handling errors, which are omitted from other examples so they are easier to read.

SQLMESSAGES=YES        " Display error messages on the screen
TRAP ON CLEANUP        " Go to the CLEANUP label if an error occurs
SQL DECLARE cursor CURSOR FOR SELECT -
    top_level, n, 'levelname', parent_level, attribute, attribute,...-
               .
               .
               .
    base_level, n, 'levelname', attribute, attribute,... - 
    FROM table -
    WHERE where_clause
 
" Signal an error if the command failed
IF SQLCODE NE 0
   THEN SIGNAL declerr 'Define cursor failed'
 
" Open the cursor
SQL OPEN cursor
IF SQLCODE NE 0
   THEN SIGNAL openerr 'Open cursor failed'
 
" Fetch the data
SQL IMPORT cursor INTO -
   :APPEND dimension, :inhier, :levelrel, :parentrel, :attribute, :attribute, ...
   
IF SQLCODE NE 0 AND SQLCODE NE 100
   THEN SIGNAL geterr 'Fetch failed'
 
" Save these changes
UPDATE  
COMMIT 
 
CLEANUP:
SQL CLOSE cursor
SQL CLEANUP

11.4.4 Sorting Dimension Members

When you fetch dimension members at all levels in a single pass through the source table, they are mixed together in the target workspace dimension. For most dimensions, the order does not affect processing, although you can sort the members by level if you wish.

However, it is very important for Time dimension members to be ordered chronologically within levels so that the Time dimension supports time series analysis. Functions such as LEAD, LAG, and MOVINGAVERAGE use the relative position of Time members in their calculations. For example, LAG returns the dimension member that is a specified number of values before the current member. If the time periods are not in chronological order, the returned value is meaningless.

Your analytic workspace will perform better if you load the dimensions in the correct order instead of sorting them afterward.

contains an OLAP DML program template for sorting the Time dimension. It does the following:

• Defines a valueset in which to hold the sorted values.

• Sorts the Time dimension by level first, then by end date within level.

• Stores the sorted values in the valueset.

• Reorders the Time dimension members.

IF NOT EXISTS('valueset')
  THEN DEFINE valueset VALUESET time_dim
LIMIT time_dim TO ALL
"Sort levels in descending order and time periods in ascending order
SORT time_dim D time_dim_LEVELREL A time_dim_END_DATE
LIMIT valueset TO time_dim
MAINTAIN time_dim MOVE VALUES(valueset) FIRST

"Save these changes
UPDATE
COMMIT

11.4.5 Fetching Measures From Tables

To fetch data from relational tables, you must write a program that defines a SQL cursor with the selection of data that you want to fetch, then retrieves the data into the analytic workspace objects that you previously created.

is a template that you can use for fetching all of the measures for a particular cube. It does the following:

• Identifies a key column with the members of each dimension. When the data is fetched into a variable, the dimensions are limited to the appropriate cell by these values.

• Orders the source data to match the target variable.

  The ORDER BY clause in the SELECT statement is the reverse of the dimension list for the variable. If a variable is dimensioned by <Product Geography Time> so that Product is the fastest varying and Time is the slowest, then the ORDER BY clause sorts the rows by Time, Geography, and Product. This ordering speeds the data load.

• Requires that values in the key columns match existing members in the target workspace dimensions.

  A required match prevents new dimension members from being created without their level, parentage, attributes, and so forth.

SQLMESSAGES=YES        " Display error messages on the screen
TRAP ON CLEANUP         " Go to the CLEANUP label if an error occurs
" Define a cursor for selecting data
SQL DECLARE cursor CURSOR FOR SELECT -
    key1, key2, key3, keyn -
    meas1, meas2, meas3, measn -
    FROM table 
    ORDER BY slowest_dim, ..., fastest_dim
" Signal an error if the command failed
IF SQLCODE NE 0
   THEN SIGNAL declerr 'Define cursor failed'
 
" Open the cursor
SQL OPEN cursor
IF SQLCODE NE 0
   THEN SIGNAL openerr 'Open cursor failed'
 
" Fetch the data
SQL IMPORT cursor INTO :MATCH dim1, :MATCH dim2, -
  :MATCH dim3, :MATCH dimn, -
  :var1, :var2 :var3
IF SQLCODE NE 0 AND SQLCODE NE 100
   THEN SIGNAL geterr 'Fetch failed'
 
" Save these changes
UPDATE  
COMMIT 
 
CLEANUP:
SQL CLOSE cursor  " Close the cursor
SQL CLEANUP       " Free resources

11.5.1 Using ___POP.FMLYREL

The ___POP.FMLYREL program populates the member_gid variable and member_familyrel variable for a dimension of a data cube. You must execute ___POP.FMLYREL for each dimension. Use this syntax to call ___POP.FMLYREL:

CALL ___POP.FMLYREL(aw, aw!dim, aw!dim_HIERLIST, aw!dim_LEVELLIST, aw!dim_LEVELREL, dim, aw!dim_PARENTREL, aw!dim_INHIER)

Where:

All arguments are text expressions, so you must enclose literal text in single quotes. Use all upper-case letters for the arguments.

For an example, see "Populating Additional Standard Form Metadata Objects".

11.5.2 Using ___ORDR.HIERARCHIES

The ORDR.HIERARCHIES program populates the default_order attribute of a data dimension. You must run it for each dimension of a data cube. Use this syntax to run ORDR.HIERARCHIES:

CALL ___ordr.hierarchies('aw!dim', 'aw!dim_HIERLIST', 'aw!dim_HIER_CREATEDBY', 'dim_PARENTREL', 'dim_ORDER', 'dim_INHIER')

Where:

All arguments are text expressions, so you must enclose literal text in single quotes. Use all upper-case letters for the arguments.

For an example, see "Populating Additional Standard Form Metadata Objects".

11.6.1 Designing and Implementing the GLOBALX Star Schema

Because Global data is already stored in the GLOBAL star schema, GLOBALX can simply mimic its design for the Price and Units cubes. The only difference is that while GLOBAL is populated with data, GLOBALX contains empty tables.

11.6.1.1 GLOBALX Schema Diagram

Example 11-6 diagrams the schema relationships.

Example 11-6 GLOBALX Schema Diagram

Description of the illustration globxdia.gif

11.6.2 Creating OLAP Catalog Metadata for the GLOBALX Schema

The metadata for the GLOBALX star schema can be generated by any available method: the OLAP Management tools in Oracle Enterprise Manager, the OLAP Bridge in Oracle Warehouse Builder, or the CWM2 PL/SQL package. This example arbitrarily uses the CWM2 packages.

Take these steps to create OLAP Catalog metadata for the GLOBALX schema:

1. Create the SQL scripts listed in "SQL Scripts for OLAP Catalog Metadata".

2. Log in to SQL*Plus or a similar SQL command processor as the GLOBALX user.

3. Issue these SQL commands so that you can see the full report from the metadata validator, both on the screen and saved to a file:

   SET LINESIZE 135
   SET SERVEROUT ON SIZE 999999
   EXECUTE cwm2_olap_manager.set_echo_on
   SPOOL filepath
   SET ECHO ON
   
   

   The buffer for server output holds a maximum of 1,000,000 characters. If you are building a large application, you may need to control the size of the output with a combination of SET_ECHO_ON, SET_ECHO_OFF, BEGIN_LOG, and END_LOG commands.

4. Execute the CWM2 scripts using the SQL @ command.

5. After the scripts execute without errors, issue a SQL COMMIT statement.

6. Examine the metadata in Analytic Workspace Manager, as shown in Figure 11-1.

   1. Open Analytic Workspace Manager and connect as the GLOBALX user.

   2. In the OLAP Catalog view, expand the Cubes, GLOBALX, and Relational Cubes folders.

Figure 11-1 GLOBALX Metadata Displayed in Analytic Workspace Manager

Description of the illustration cwm2.gif

11.6.3 Creating the GLOBALX Analytic Workspace

You can create the GLOBALX analytic workspace from the empty tables and OLAP Catalog metadata using any of the available methods: The Create Analytic Workspace wizard in Analytic Workspace Manager, the OLAP bridge in Oracle Warehouse Builder, or the DBMS_AWM PL/SQL procedures. This example uses the wizard to generate a script containing calls to DBMS_AWM, then modifies the script.

Take these steps to create the GLOBALX analytic workspace.

1. Create the GLOBALX_AW user with access rights to the GLOBALX tables and tablespaces, using a script like the one in "SQL Scripts for Defining Users and Tablespaces".

2. Open Analytic Workspace Manager and log in to the database as the GLOBALX_AW user.

3. From the Tools menu, choose Create Analytic Workspace Using Wizard.

4. Make these choices in the wizard:

   • Specify Analytic Workspace page: Type GLOBALX as the workspace name, and select GLOBALX_AW as the schema.

   • Select Cubes page: Select all cubes in the GLOBALX relational schema.

   • Choose Data Loading Options page: Select Build analytic workspace and load dimensions and facts. Clear the Generate unique keys box.

     There is no data in the tables to load; however, this choice populates more catalogs in the analytic workspace than the other choices. The lack of data does not cause the build to fail.

   • Choose Advanced Storage and Naming Options page: Select Display the pages for setting the advanced storage options. Clear the Prefix measure names with cube names box.

     Because no data is available, the tools cannot determine the correct order of the dimensions, nor an appropriate segment size. You must provide this information or the analytic workspace will run slower than it should.

   • Create Composite Dimension and following pages: Create a composite for the Units Cube named UNITS_CUBE_COMPOSITE with the dimensions in this order: CUSTOMER PRODUCT CHANNEL. Omit TIME from the composite.

   • Specify Segment Width and Dimension Order page: For the Units Cube, specify the dimensions like this.

     TIME                        85
     <CUSTOMER PRODUCT CHANNEL>  1000000
     
     

5. Save the new analytic workspace.

6. Open the GLOBALX analytic workspace in OLAP Worksheet, and make the following modifications:

   1. Delete and redefine UNIT_COST_VARIABLE and UNIT_PRICE_VARIABLE so they are dimensioned by <TIME PRODUCT>.

      These 80% dense, two-dimensional measures will perform better without a composite. Refer to "Examining Sparsity Characteristics for GLOBAL" for a discussion of composite definitions. Follow the instructions in "Manually Changing Object Definitions".

   2. Delete PRICE_CUBE_COMPOSITE.

   3. Set the segment size on either one of the variables with a command like this:

      CHGDFN unit_price_variable SEGWIDTH 85 50
      
      

      These settings reserve contiguous disk space for 85 time periods and 50 products. A single command changes the segment size on all measures in the same cube.

   4. Set the segment size on the dimension attributes with the following commands:

      CHGDFN time_end_date SEGWIDTH 85 1
      CHGDFN time_long_description SEGWIDTH 85 1 1
      CHGDFN customer_long_description SEGWIDTH 80 2 1
      CHGDFN product_long_description SEGWIDTH 50 1 1
      CHGDFN channel_long_description SEGWIDTH 3 1 1
      
      

   5. Save these changes by issuing UPDATE and COMMIT commands.

11.6.4 Fetching the Price Cube From Relational Tables

The Price cube has two measures, UNIT_COST and UNIT_PRICE, with two dimensions, PRODUCT and TIME. In this example, the data is loaded manually from the GLOBAL star schema. However, it could be loaded from any form of relational tables using the method described here.

Take these steps to populate the Price cube in the GLOBALX analytic workspace:

1. Open Analytic Workspace Manager and connect to the database as the GLOBALX_AW user.

2. In the Object View, open the GLOBALX analytic workspace in read/write mode.

3. Create the OLAP DML programs for fetching the PRODUCT and TIME dimension members.

   You can create and compile programs in the Object View or in OLAP Worksheet. You can execute programs, and view the contents of the objects they populated, only in OLAP Worksheet.

4. Open OLAP Worksheet and execute the programs using the CALL command.

   CALL program_name
   
   

5. After the programs run without error, check the contents of the target workspace objects to verify that they are populated correctly.

6. Issue UPDATE and COMMIT commands to save the loaded data.

7. Create and execute a data load program for the Price cube.

8. After that program runs without error, check the data in the target variables

9. Issue UPDATE and COMMIT commands to save the loaded data.

CALL getprod

DEFINE GETPROD PROGRAM
PROGRAM
TRAP ON CLEANUP
" Define cursor c1 
SQL DECLARE c1 CURSOR FOR SELECT -
    total_product_id, 1, 'TOTAL_PRODUCT', total_product_dsc, -
    class_id, 1, 'CLASS', total_product_id, class_dsc, -
    family_id, 1, 'FAMILY', class_id, family_dsc, -
    item_id, 1, 'ITEM', family_id, item_dsc, item_package_id -
    FROM global.product_dim
 
" Open the cursor
SQL OPEN c1
 
" Fetch the data
SQL FETCH c1 LOOP INTO -
   :APPEND product, :product_inhier, :product_levelrel, :product_long_description, -
   :APPEND product, :product_inhier, :product_levelrel, :product_parentrel, -
          :product_long_description, -
   :APPEND product, :product_inhier, :product_levelrel, :product_parentrel,-
          :product_long_description, -
   :APPEND product, :product_inhier, :product_levelrel, :product_parentrel, -
          :product_long_description, :product_package
 
" Save these changes
UPDATE
COMMIT
 
CLEANUP:
SQL CLOSE c1
SQL CLEANUP
END

LIMIT product TO product_levelrel EQ 'ITEM'
LIMIT product KEEP FIRST 2
LIMIT product ADD ANCESTORS USING product_parentrel
REPORT W 8 DOWN product W 16 <product_long_description product_levelrel>
      W 10 <product_parentrel product_inhier>
 
ALL_LANGUAGES: AMERICAN_AMERICA
         -------------------PRODUCT_HIERLIST--------------------
         --------------------PRODUCT_ROLLUP---------------------
         PRODUCT_LONG_DES                  PRODUCT_PA PRODUCT_IN
PRODUCT      CRIPTION     PRODUCT_LEVELREL  RENTREL      HIER
-------- ---------------- ---------------- ---------- ----------
13       Envoy Standard   ITEM             4                 yes
14       Envoy Executive  ITEM             4                 yes
4        Portable PCs     FAMILY           2                 yes
2        Hardware         CLASS            1                 yes
1        Total Product    TOTAL_PRODUCT    NA                yes

CALL gettime
CALL timesort

DEFINE GETTIME PROGRAM
PROGRAM
TRAP ON CLEANUP 
SQL DECLARE c1 CURSOR FOR SELECT -
    year_id, 1, 'YEAR', year_dsc, year_timespan, year_end_date, -
    quarter_id, 1, 'QUARTER', year_id, quarter_dsc, quarter_timespan, quarter_end_date, -
    month_id, 1, 'MONTH', quarter_id, month_dsc, month_timespan, month_end_date -
    FROM global.time_dim -
    ORDER BY month_end_date
 
" Open the cursor
SQL OPEN c1
 
" Fetch the data
SQL FETCH c1 LOOP INTO -
   :APPEND time, :time_inhier, :time_levelrel, :time_long_description, -
          :time_time_span, :time_end_date,-
   :APPEND time, :time_inhier, :time_levelrel, :time_parentrel, -
          :time_long_description, :time_time_span, :time_end_date,-
   :APPEND time, :time_inhier, :time_levelrel, :time_parentrel, -
          :time_long_description, :time_time_span, :time_end_date
 
" Save these changes
UPDATE
COMMIT
 
CLEANUP:
SQL CLOSE c1
SQL CLEANUP
END

DEFINE TIMESORT PROGRAM
PROGRAM
" Create a valueset to hold the sorted values
IF NOT EXISTS('timeset')
  THEN DEFINE timeset VALUESET time
LIMIT time TO ALL
" Sort by descending levels and ascending end-dates
SORT time D time_LEVELREL A time_end_date
" Save sorted values in the valueset
LIMIT timeset TO time
" Reorder the dimension members
MAINTAIN time MOVE VALUES(timeset) FIRST
END

LIMIT time TO FIRST 10
LIMIT time ADD LAST 3
REPORT W 5 DOWN time W 8 <time_long_description time_parentrel time_levelrel time_inhier time_end_date time_time_span>
 
ALL_LANGUAGES: AMERICAN_AMERICA
      --------------------TIME_HIERLIST--------------------
      ----------------------CALENDAR-----------------------
      TIME_LON
      G_DESCRI TIME_PAR TIME_LEV TIME_INH TIME_END TIME_TIM
TIME   PTION    ENTREL   ELREL     IER     _DATE    E_SPAN
----- -------- -------- -------- -------- -------- --------
1     1998     NA       YEAR          yes 31DEC98    365.00
2     1999     NA       YEAR          yes 31DEC99    365.00
3     2000     NA       YEAR          yes 31DEC00    366.00
4     2001     NA       YEAR          yes 31DEC01    365.00
85    2002     NA       YEAR          yes 31DEC02    365.00
102   2003     NA       YEAR          yes 31DEC03    365.00
119   2004     NA       YEAR          yes 31DEC04    366.00
5     Q1-98    1        QUARTER       yes 31MAR98     90.00
6     Q2-98    1        QUARTER       yes 30JUN98     91.00
7     Q3-98    1        QUARTER       yes 30SEP98     92.00
106   Apr-04   116      MONTH         yes 30APR04     30.00
107   May-04   116      MONTH         yes 31MAY04     31.00
108   Jun-04   116      MONTH         yes 30JUN04     30.00

DEFINE UNIT_PRICE_VARIABLE VARIABLE DECIMAL <TIME PRODUCT>
LD IMPLEMENTATION Variable for UNIT_PRICE Measure
 
DEFINE UNIT_COST_VARIABLE VARIABLE DECIMAL <TIME PRODUCT>
LD IMPLEMENTATION Variable for UNIT_COST Measure

CALL getpricecube

DEFINE GETPRICECUBE PROGRAM
PROGRAM
" Define a cursor for selecting data
SQL DECLARE c1 CURSOR FOR SELECT -
    item_id, month_id, unit_price, unit_cost -
    FROM global.price_and_cost_history_fact -
    ORDER BY item_id, month_id
 
" Open the cursor
SQL OPEN c1
 
" Fetch the data
SQL FETCH c1 LOOP INTO :MATCH product, :MATCH time, -
  :unit_price_variable, :unit_cost_variable
 
" Save these changes
UPDATE
COMMIT
SQL CLOSE c1  " Close the cursor
SQL CLEANUP
END

SHOW ANY(variable NE NA)

SHOW ANY(unit_price_variable NE NA)

LIMIT time TO '44' '45'
LIMIT time ADD ANCESTORS USING time_parentrel
LIMIT product TO '13'
LIMIT product ADD ANCESTORS USING product_parentrel
REPORT unit_price unit_cost
 
               ----------------------------------------PRODUCT----------------------------------------
               ---------13---------- ----------4---------- ----------2---------- ----------1----------
TIME           UNIT_PRICE UNIT_COST  UNIT_PRICE UNIT_COST  UNIT_PRICE UNIT_COST  UNIT_PRICE UNIT_COST
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
44               3,008.91   2,862.51   9,483.71   8,944.35  19,163.02  18,071.18  19,960.72  18,714.88
45               3,142.99   2,926.79   9,590.01   9,024.35  18,969.89  17,924.55  19,735.20  18,542.48
13               9,152.01   8,655.17  28,452.92  26,883.70  57,229.23  53,982.32  59,577.63  55,873.16
3               34,314.40  32,681.09 111,333.24 105,481.00 224,713.71 211,680.12 234,516.47 219,574.10

11.6.5 Loading the Units Cube From Flat Files

The Units cube has one measure, UNITS, and four dimensions, TIME, CUSTOMER, PRODUCT, and CHANNEL. The TIME and PRODUCT dimensions have already been added to the analytic workspace in "Fetching the Price Cube From Relational Tables", so unless additional dimension members are contained in the flat files, these two dimensions do not need to be maintained. However, the CUSTOMER and CHANNEL dimensions must be fully populated before loading the UNITS measure.

This example loads data from three flat files:

• CHANNEL.DAT contains all CHANNEL dimension members and their attributes. It is equivalent to the CHANNEL_DIM dimension table in the GLOBAL star schema, described in Chapter 3.

• CUSTOMER.DAT contains all CUSTOMER dimension members and their attributes. It is equivalent to the CUSTOMER_DIM dimension table in the GLOBAL star schema, described in Chapter 3.

• UNITS.DAT contains the base-level data for the UNITS measure. It is equivalent to the UNITS_HISTORY_FACT fact table in the GLOBAL star schema, described in Chapter 3.

The basic process for loading from flat files is the same as loading from relational tables, as described earlier in "Fetching the Price Cube From Relational Tables". The difference is only in the OLAP DML programs.

2,Direct Sales,1,All Channels
3,Catalog,1,All Channels
4,Internet,1,All Channels

CALL read_channels

DEFINE READ_CHANNELS PROGRAM
PROGRAM
VARIABLE funit INTEGER          "Define local variable for file handle
TRAP ON CLEANUP                 "Divert processing on error to CLEANUP label
funit = FILEOPEN('gx/channels.dat' READ)  "Open the file
 
FILEREAD funit CSV -
   FIELD 3 APPEND channel channel_inhier=yes channel_levelrel='ALL_CHANNELS' -
   FIELD 4 channel_long_description -
   FIELD 1 APPEND channel channel_inhier=YES channel_levelrel='CHANNEL' -
   FIELD 2 channel_long_description -
   FIELD 3 channel_parentrel
 
CLEANUP:
IF funit NE na
   THEN FILECLOSE funit
END

REPORT W 8 DOWN channel W 12 <channel_long_description channel_parentrel channel_levelrel channel_inhier>
 
ALL_LANGUAGES: AMERICAN_AMERICA
         -----------------CHANNEL_HIERLIST------------------
         ------------------CHANNEL_ROLLUP-------------------
         CHANNEL_LONG CHANNEL_PARE CHANNEL_LEVE CHANNEL_INHI
CHANNEL  _DESCRIPTION    NTREL         LREL          ER
-------- ------------ ------------ ------------ ------------
1        All Channels NA           ALL_CHANNELS          yes
2        Direct Sales 1            CHANNEL               yes
3        Catalog      1            CHANNEL               yes
4        Internet     1            CHANNEL               yes

49 "Bavarian Indust, GmbH Rome"     22 "Bavarian Industries"         5 "Manufacturing" ...
50 "Bavarian Indust, GmbH London"   22 "Bavarian Industries"         5 "Manufacturing" ...
55 "CiCi Douglas Chattanooga"       24 "CiCi Douglas"                5 "Manufacturing" ...
                         .
                         .
                         .

CALL read_customers

DEFINE READ_CUSTOMERS PROGRAM
PROGRAM
VARIABLE funit INTEGER          "Define local variable for file handle
TRAP ON CLEANUP                 "Divert processing on error to CLEANUP label
funit = FILEOPEN('gx/customers.dat' READ)  "Open the file
 
FILEREAD funit STRUCTURED -
   FIELD 13 APPEND customer -
      customer_inhier(customer_hierlist 'SHIPMENTS_ROLLUP')=yes -
      customer_levelrel='ALL_CUSTOMERS' -
   FIELD 14 customer_long_description(customer_hierlist 'SHIPMENTS_ROLLUP') -
   FIELD 11 APPEND customer -
      customer_inhier(customer_hierlist 'SHIPMENTS_ROLLUP')=yes -
      customer_levelrel='REGION' -
   FIELD 12 customer_long_description(customer_hierlist 'SHIPMENTS_ROLLUP') -
   FIELD 13 customer_parentrel(customer_hierlist 'SHIPMENTS_ROLLUP') -
   FIELD 9 APPEND customer -
      customer_inhier(customer_hierlist 'SHIPMENTS_ROLLUP')=yes -
      customer_levelrel='WAREHOUSE' -
   FIELD 10 customer_long_description(customer_hierlist 'SHIPMENTS_ROLLUP') -
   FIELD 11 customer_parentrel(customer_hierlist 'SHIPMENTS_ROLLUP') -
   FIELD 7 APPEND customer -
      customer_inhier(customer_hierlist 'MARKET_ROLLUP')=yes -
      customer_levelrel='TOTAL_MARKET' -
   FIELD 8 customer_long_description(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 9 customer_parentrel(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 5 APPEND customer -
      customer_inhier(customer_hierlist 'MARKET_ROLLUP')=yes -
      customer_levelrel='MARKET_SEGMENT' -
   FIELD 6 customer_long_description(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 7 customer_parentrel(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 3 APPEND customer -
      customer_inhier(customer_hierlist 'MARKET_ROLLUP')=yes -
      customer_levelrel='ACCOUNT' -
   FIELD 4 customer_long_description(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 5 customer_parentrel(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 1 APPEND customer -
      customer_inhier(customer_hierlist 'SHIPMENTS_ROLLUP')=yes -
      customer_inhier(customer_hierlist 'MARKET_ROLLUP')=yes -
      customer_levelrel='SHIP_TO' -
   FIELD 2 customer_long_description(customer_hierlist 'SHIPMENTS_ROLLUP') -
   FIELD 2 customer_long_description(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 3 customer_parentrel(customer_hierlist 'MARKET_ROLLUP') -
   FIELD 9 customer_parentrel(customer_hierlist 'SHIPMENTS_ROLLUP')
 
CLEANUP:
IF funit NE na
   THEN FILECLOSE funit
END

LIMIT customer TO customer_levelrel 'SHIP_TO'  "Select base-level members
LIMIT customer KEEP FIRST 2                    "Keep the first 2 base-level members
LIMIT customer ADD ANCESTORS USING customer_parentrel  "Add all their ancestors
SORT customer A customer_levelrel A CUSTOMER           "Sort the selected members within levels
 
REPORT W 8 DOWN customer W 16 <customer_long_description customer_levelrel> -
   W 6 <customer_parentrel customer_inhier>
 
ALL_LANGUAGES: AMERICAN_AMERICA
         ---------------------------------------CUSTOMER_HIERLIST---------------------------------------
         -----------------MARKET_ROLLUP----------------- ---------------SHIPMENTS_ROLLUP----------------
                                           CUSTOM CUSTOM                                   CUSTOM CUSTOM
         CUSTOMER_LONG_DE CUSTOMER_LEVELRE ER_PAR ER_INH CUSTOMER_LONG_DE CUSTOMER_LEVELRE ER_PAR ER_INH
CUSTOMER    SCRIPTION            L         ENTREL  IER      SCRIPTION            L         ENTREL  IER
-------- ---------------- ---------------- ------ ------ ---------------- ---------------- ------ ------
22       Bavarian         ACCOUNT          5         yes NA               ACCOUNT          NA         NA
         Industries
1        All Customers    ALL_CUSTOMERS    NA         NA All Customers    ALL_CUSTOMERS    NA        yes
5        Manufacturing    MARKET_SEGMENT   7         yes NA               MARKET_SEGMENT   NA         NA
9        Europe           REGION           1          NA Europe           REGION           1         yes
49       Bavarian Indust, SHIP_TO          22        yes Bavarian Indust, SHIP_TO          16        yes
         GmbH Rome                                       GmbH Rome
50       Bavarian Indust, SHIP_TO          22        yes Bavarian Indust, SHIP_TO          20        yes
         GmbH London                                     GmbH London
7        Total Market     TOTAL_MARKET     14        yes NA               TOTAL_MARKET     NA         NA
14       Germany          WAREHOUSE        9          NA Germany          WAREHOUSE        9         yes
16       Italy            WAREHOUSE        9          NA Italy            WAREHOUSE        9         yes
20       United Kingdom   WAREHOUSE        9          NA United Kingdom   WAREHOUSE        9         yes
 

CHANNEL_ID    ITEM_ID SHIP_TO_ID   MONTH_ID      UNITS
---------- ---------- ---------- ---------- ----------
         2         13         51         54          2
         2         13         51         56          2
         2         13         51         57          2
         2         13         51         58          2
         2         13         51         59          2
         2         13         51         61          1

DEFINE UNITS_VARIABLE VARIABLE DECIMAL <TIME UNITS_CUBE_COMPOSITE <CUSTOMER PRODUCT CHANNEL>>

CALL read_units

DEFINE READ_UNITS PROGRAM
PROGRAM
VARIABLE funit INTEGER          "Define local variable for file handle
TRAP ON cleanup                 "Divert processing on error to cleanup label
funit = FILEOPEN('gx/units_cube.dat' READ)  "Open the file
 
FILEREAD funit STRUCTURED -
    FIELD 1 channel -
    FIELD 2 product -
    FIELD 3 customer -
    FIELD 4 time -
    FIELD 5 units_variable
 
cleanup:
IF funit NE na
   THEN FILECLOSE funit
END

limit time to '50' to '60'
limit channel to '2'
limit product to '13' '14'
limit customer to '51'
report down time across product: units_variable

CHANNEL: 2
CUSTOMER: 51
               ---UNITS_VARIABLE----
               -------PRODUCT-------
TIME               13         14
-------------- ---------- ----------
50                   2.00       2.00
51                   2.00         NA
52                   2.00       2.00
53                   1.00       2.00
54                   2.00       2.00
55                     NA       2.00
56                   2.00       2.00
57                   2.00         NA
58                   2.00       1.00
59                   2.00       2.00
60                     NA       1.00

11.6.6 Populating Additional Standard Form Metadata Objects

If you enable the GLOBALX analytic workspace for the BI Beans now, the dimension views will have many empty columns. For example, the view of the CHANNEL dimension has these empty columns:

The ___POP_FMLYREL and ___ORDR.HIERARCHIES programs populate the workspace objects that are displayed by these columns. shows the commands used for the CHANNEL dimension. Repeat these commands for the PRODUCT, CUSTOMER, and TIME dimensions.

You do not need to re-enable the GLOBALX workspace after populating these objects. The data is available through the views as soon as you commit the changes to the database.

" Populate CHANNEL_GID and CHANNEL_FAMILYREL
CALL ___POP.FMLYREL('GLOBALX', 'GLOBALX!CHANNEL', 'GLOBALX!CHANNEL_HIERLIST', 'GLOBALX!CHANNEL_LEVELLIST', 'GLOBALX!CHANNEL_LEVELREL', 'CHANNEL', 'GLOBALX!CHANNEL_PARENTREL', 'GLOBALX!CHANNEL_INHIER')

" Populate CHANNEL_ORDER
call ___ordr.hierarchies('GLOBALX!CHANNEL', 'GLOBALX!CHANNEL_HIERLIST', 'GLOBALX!CHANNEL_HIER_CREATEDBY', 'CHANNEL_PARENTREL', 'CHANNEL_ORDER', 'CHANNEL_INHIER')

11.6.7 Using Tools with the GLOBALX Analytic Workspace

You can now use the Create and Deploy Aggregation Plan wizards and the enablers.

For refreshing the data, you must revise your data loading programs to access new data sources or to restrict the load to new time periods.