Refreshing the Data in an Analytic Workspace - OLAP

The Refresh wizard accesses the same tables that the Create Analytic Workspace wizard used originally. However, you may bring new data into your relational schema in separate tables. You can either write a load program in SQL using the DBMS_AWM package, or follow these alternative steps.

These are the basic steps for refreshing a cube:

  1. In the Object View of Analytic Workspace Manager, attach the analytic workspace in Read/Write mode.
  2. In the Object View, expand the Programs folder and select the load program that was generated by the Create Analytic Workspace wizard to load data for the original build.
    If you are not sure which program to choose, then expand the Dimension folder and select the cube def dimension. On the Properties page, note the value of the AW$LOADPRGS property.
  3. On the Program page of the property viewer, edit the load program and change the name of the source table.
  4. Choose Apply, then Compile.
    Correct any errors before continuing.
  5. Right-click the name of the load program, then choose Copy to Clipboard.
  6. Open OLAP Worksheet, and execute the program. Paste the name of the program into the query window by typing Ctrl+V.
    CALL program
  7. Check the new data using the LIMIT and REPORT commands.
  8. Issue UPDATE and COMMIT commands to save the new data.

Case Study: Refreshing the Units Cube

The Global star schema provides an additional month of data in separate update tables.

  1. In the Object View, expand the Programs folder and select
    GLOBAL_AW.GLOBAL!___GET.CUBE.DATA_UNITS_CUBE_1.
  2. Display the Program page of the load program, and locate the name of the source file, UNITS_HISTORY_FACT.
    SQL DECLARE C1 CURSOR FOR SELECT CHANNEL_ID,SHIP_TO_ID,ITEM_ID, - MONTH_ID,UNITS FROM GLOBAL.UNITS_HISTORY_FACT
  3. Edit this statement by replacing UNITS_HISTORY_FACT with UNITS_UPDATE_FACT.
  4. Choose Apply, then Compile.
  5. Open OLAP Worksheet and run the revised load program with a command like this:
    CALL ___get.cube.data_units_cube_1
  6. Display a sample of the new data with commands like these:
    LIMIT channel TO '1' "Select any channel LIMIT product TO '1' "Select any product LIMIT time TO '91' "Select the new time period LIMIT customer TO '76' "Select any customer" Add the parent values of customer 76 LIMIT customer ADD ANCESTORS USING customer_parentrel REPORT DOWN customer units "View the new data CHANNEL: 1 PRODUCT: 1 --UNITS--- ---TIME--- CUSTOMER 91 -------- ------ 761,220.00 17 7,378.00 8 12,985.00 150,632.00
  7. Save the changes with these commands:
    UPDATE
    COMMIT
    or
    From the File menu of Analytic Workspace Manager, choose Save.

When a Data Refresh Requires Re-Enabling

Routine refreshes of the data do not require you to re-enable the workspace for a particular application, because the views created by the enablers do not need to be redefined for new dimension members. However, you do need to re-enable your workspace if you make changes to the logical model, such as:

  • Change the OLAP Catalog metadata for the source cubes
  • Add or delete a cube in the analytic workspace
  • Add or delete a measure in the analytic workspace
  • Add or delete a hierarchy in the analytic workspace
  • Add or delete a level in the analytic workspace
  • Change the OLAP Catalog metadata for the analytic workspace.

Because the enabling step takes only a short time to complete, you may prefer to re-enable your analytic workspace each time you refresh it.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics