OBI Cube Maintenance

Note: This topic applies to OBI having licenses OPB_<version number> OBI Enterprise (OBIEE - Multiple Properties), or OPI_BUIP_<version number> OBI Standard (Single) using OLAP technology and data cubes. This topic does not apply to standalone S&C Advanced Reporting whose data is accessed directly from OPERA tables via OLTP. For S&C Advanced Reporting, see S&C Advanced Reporting Subject Areas or S&C Advanced Reporting Setup.

OPERA Business Intelligence (OBI) cube maintenance provides the OBI user with options to initially build a new cube for a property (or properties) from data compiled from a specific year and to schedule cube data updates thereafter. Cube building and maintenance is done through the OPERA Scheduler (accessed from Utilities > Utilities > OPERA Scheduler.) When the OPR_<version number> ORS license code is active, the Scheduler can also be accessed from the ORS main menu Miscellaneous > Scheduler). This topic discusses some requirements and background information for building and scheduling maintenance for OBI cube data. For more information about OPERA Business Intelligence, see About OPERA Business Intelligence and also OBI Setup.

Prerequisites

License Codes

Properties must have license codes for:

• OPB_<version number> OBI Enterprise (Multiple Properties), or
• OPI_BUIP_<version number> OBI Standard (Single)
• When purchasing and installing the OBI application and licenses, you will also automatically receive access to OCIS Datamarts (tables containing statistical data used to build cubes).

End of Day/Night Audit

The End of Day/Night Audit needs to be set up. Datamart messages will be triggered by the End of Procedures business event generated by the Night Audit.

To set up the End of Day/Night Audit

1. Ensure that the user is granted the permission End of Day > Night Audit Setup.
2. Go to System Configuration > Setup > Business Events > Configuration, select the module NIGHT AUDIT and the Action Type NA DATAMART with the Data Element BUSINESS DATE checked.
3. Ensure that the business event for "End of Procedures" is configured and subscribed to by the property.
4. Enable the procedure data_mart_api.populate_financial_stat_daily in the End of Day Final Procedures.
5. Go to OXI > Interface Configuration > Interface Parameters and select the types of Datamarts you want.
6. Start the Night Audit by accessing the End of Day Routine > Night Audit Procedure.

RAC Environment Only

When the OBI Schema is in a RAC environment, users can select different server nodes (instances) to be used when building and maintaining BI Cubes. Assigning server nodes for these background jobs, keeping them separate from regular user sessions, will improve OPERA performance.

• To select the preferred server nodes (instances) to be used when building and maintaining OBI Cubes, access the application setting OBI > Preferred Instance List. Instances can only be selected/switched for a cube maintenance job that is not currently running.

Data Resources and Cube Maintenance Overview

Starting at the property level, dedicated OPERA processes capture groupings of select data from across the hundreds of OPERA tables and place the data in appropriate fact tables (relational tables). This process occurs at the End of Day sequence/Night Audit (the process that closes and balances each day's activities for the property). Fact tables store summarized, or aggregated, data from each property. OBI cubes access these tables as a source for quick retrieval of data during the cube building and maintenance process. For example, the Reservation Pace Cube may use fact table sources of Future Reservations, Budget Data, and Physical Rooms. OBI Cubes do not read any data from non-OPERA sources.

Note: OBI accesses the same property budget and forecast data existing in the Budget Forecast module to build its budget and forecasting reports. See Budget Forecast for more information.

• After the initial build, the OBI cube maintenance process adds, updates, and deletes rows in the OBI cube for data that has been added, updated, and deleted from the fact tables. If a reservation is changed from 1 day to 2 days, the change is immediately reflected in the OPERA relational tables and updated into the data fact tables after the Night Audit. The OBI cube, however, is not updated until the OBI Cube Maintenance process is completed as scheduled.
• Cube building/maintaining jobs should always be scheduled so that they have enough time to run before the next job starts. Jobs should never overlap.
• OPERA keeps a detailed log of the timing, results, and errors generated by these jobs in dedicated tables. The cube building processes can be monitored and automatic alerts can be set up. This helps verify from within OBI that the records are transmitting properly from the property. This information is available in the OPERA Business Intelligence front-end, through standard reports.

OBI Cube Scheduler Procedures

With the OPERA Scheduler Procedures screen, you can set up a schedule to build and maintain cubes. Access the OPERA Scheduler Procedures screen (Scheduler > Not Running > New). This screen lists the procedures that are available to run:

• Full Build (Fully build the cube for the first time or rebuild the cube after an upgrade)
• Build & Maintain (Schedule the highlighted, previously built cube for updates)
• Maintain (Schedule table and log updates for all previously built cubes)
• Incremental (Schedule incremental updates after Daily Builds)
• The BI - External File Import job is only required for the POS Cube as it imports the files from the POS and stores them in OPERA Datamart Tables. See the POS Cube, below.

  Note: For information relating to setting up the OBI cube parameters and dimensions prior to building and maintaining the cubes, refer to OBI Setup.

Highlight the cube you want to build and maintain and select the Run button. The Schedule Screen appears where you can set up the frequency.

Full Build or Rebuild

To fully build, either initially build or rebuild a cube that is independent of maintenance, highlight the cube option of BI - Full <cube name>. For example, BI - Full Company Cube, Full Build of Company Cube.

Note: During a full build or rebuild of a cube, new data is layered over previous data and the cube continues to occupy more disk space. Two options are available to reduce the amount of space occupied by cubes during a full rebuild.

- The Rebuild AWs process drops (deletes) and then recreates AWs during the full build of one of its associated cubes. See OBI Setup for more information about permissions, the application setting, and process.

- When the application parameter OBI DROP CUBE WITH FULL REBUILD is set to Y, and the user has the permission OBI > DROP CUBE WITH FULL BUILD, cubes will always be dropped (deleted) and recreated with every scheduled full rebuild.

When OBI cubes are built for the first time, or completely rebuilt after an upgrade, the application setting OBI > Settings > OBI Start Year can be activated to select a specific year from when data will be used to build the cube. All cubes, except Reservation Pace Cube, can use this setting. The start date will be the 1st day of January of the year specified in this setting. If this application setting is not set, the cube activated to be (re)built through the OPERA Scheduler takes data from the 1st of January of the current year, minus 2 years (e.g., if the business date is 12-Jun-08 and the parameter is blank, the start date will be 01-Jan-06).

• Select Run to set up the schedule when you would like the cube built. The Scheduler Screen appears where you can set up a schedule for refreshing data. See Reports Scheduler for an explanation of the Scheduler screen.

Dimension Security and Rebuilds

When the OBI DIM Security parameter is set to Y, users will need to rebuild all OBI cubes. After a cube is rebuilt, the dimensions will only be linked to the property that owns them and will be property specific. Using the Profiles dimension as an example, users in Property A will only be able to see the Profiles specific to Property A, and users in Property B will only be able to see the Profiles for Property B, etc.

Build & Maintain (Update)

To maintain and update a cube after it has initially been built, highlight the cube name BI - <cube name>. For example, BI - Block History Cube, Build and Maintain Block History Cube.

• Select Run to set up the schedule when you would like the cube updated. The Scheduler Screen appears where you can set up a schedule for refreshing data. See Reports Scheduler for an explanation of the Schedule screen.

  Note: A manual rebuild process is also available for rebuilding an existing cube. This process allows you to select the snapshot date, timeframe, and in a multi-property environment the specific properties whose data you want rebuilt. The Rebuild Cube Manually screen is available when selecting the Rebuild button from the OBI Setup screen.

BI Cubes

Note: When the OBI DIM Security parameter is set to Y, users will need to rebuild all OBI cubes. The drop cube with full rebuild should be used when rebuilding the cubes. After a cube is rebuilt, the dimensions will only be linked to the property that owns them and will be property specific. Using the Market dimension as an example, users in Property A will only be able to see the market codes specific to Property A, and users in Property B will only be able to see the market codes for Property B, etc.

• BI - Daily Summary Cube. Select this option to build a cube that provides a snapshot of each property after the ‘end of day’ process has finished. It is recommended that this cube be updated/maintained daily.
• BI - Financial History Cube. Select this option to build a cube that provides financial information at the transaction code level. It is recommended that this cube be updated/maintained weekly.
• BI - Reservation Pace Cube. Select this option to build a booking pace and forecasting cube. It recommended that this cube be updated/maintained daily. This is activated with the application setting OBI > OBI PAST DATA CALCULATION METHOD. The process will only apply to data calculated since the day the setting is activated. If the old data needs to be refreshed, then a Full Build of the Reservation PACE Cube is required. In OBI Answers, users can choose whether they want to compare past figures of rooms, revenues, etc. through the Reservation PACE Cube based on the day of the week or by date only.

  Note: To reduce processing time and improve performance, the Reservation Pace Cube can also be manually rebuilt by selecting the Rebuild button from the OBI Setup screen. When using the Rebuild Cube Manually process with the Reservation Pace Cube, only selected properties' data in a multi-property environment are uploaded from the OPERA Database to the Fact Table, thus reducing time and improving efficiency. Prior to this enhancement, the system would upload all properties' data in the environment from the OPERA Database to the Fact Table and then filter the selected properties' data for the Reservation Pace Cube's build job.
  
  Also, multiple daily Maintenance/Incremental jobs of the RESERVATION PACE Cube can be run in parallel to each other.

• BI - Company Cube. Select this option to build a cube that provides statistics for Companies. It is recommended that this cube be updated/maintained monthly.
• BI - Travel Agent Cube. Select this option to build a cube that provides statistics for Travel Agents. It is recommended that this cube be updated/maintained monthly.
• BI – Reservation History Cube. Select this option to provide detailed statistics for past reservations on a daily level. It is recommended that this cube be updated/maintained daily.
• BI - Request Log Cube. Select this option to capture requests made from ORS. Reports on channels by day and hour are possible from this cube. It is recommended that this cube be updated/maintained daily.
• BI - Monthly Resv History Cube. Select this option to build a cube that provides detail statistics for past reservations on a monthly level. It is recommended that this cube be updated/maintained daily.
• BI - Monthly Resv Pace Cube. Select this option to build a cube at the monthly level data for future reservations and blocks. It is recommended that this cube be updated/maintained daily. When the OBI_SYNC_LOCAL_DATAMARTS_FOR_PACE and OBI_USE_LOCAL_DATAMARTS_FOR_PACE parameters are set to Y, data is loaded from the reservation_summary_log_lcl_vw and obi_reservation_stat_daily_lcl views. These views are built from local tables in the BI database.
• BI - Future Resv Monthly Cube - Select this option to build a cube with detailed statistics for 'on the books' reservations and blocks. When the OBI_SYNC_LOCAL_DATAMARTS_FOR_PACE and OBI_USE_LOCAL_DATAMARTS_FOR_PACE parameters are set to Y, data is loaded from the reservation_summary_log_lcl_vw and obi_reservation_stat_daily_lcl views. These views are built from local tables in the BI database.
• BI - Block History Cube - Select this option to build a cube of historical data for past allotments. The records will be populated once a day after night audit for allotment dates equal to the closing date being run by the night audit process.
• BI - POS Cube. Select this option to build a cube of the POS check data gathered from the POS system. Prior to building the POS cube, the following procedures must be done.

  Note: The POS Cube is being developed in two phases. Phase 1, the POS Cube will interface with the MICROS 9700 and MICROS 3700 POS systems. Phase 2 (future) - The POS Cube will interface with all POS systems.

  • The Financial Stat Daily Table is required to be updated prior to building the cube. Run "Synchronize Financial Statistics" for the range of the dates you need to build the Cube. Select Utilities > Synchronize Utilities > Synchronize Financial Statistics to display the Synchronize Financial Statistics screen. Use this screen to recalculate and update the financial adjustments for a specified period. This ensures that all adjustments and transactions are included in the current Financial Stat Daily Table (and also populates the check number and check number id columns in the financial_stat_daily table). This same procedure can also be initiated by the End of Day Final Procedure - Populates Financial Daily Statistics for Datamart from Financial Transactions Table (data_mart_api.populate_stat_daily).
  • Ensure that the path to the shared POS files has been set up and selected in OBI Application Setting OBI EXTERNAL FILE DIRECTORY. To set up the path, create a base path and directory.For example:
    • Base path = \\BISERVER\Transfer\OBI\POS. This will be the folder where all imported files will be stored. The Oracle Database needs to have permission to read, write to this Shared Folder.
  • Add this directory to your DBA directories in OPERA. Go to Utilities > OPERA SQL >. Run the below code. Replace \\biserver\transfer\OBI\POS with your path.

    DECLARE

    v VARCHAR2(200);

    BEGIN

    v := Obi_Ext_p.Create_Ext_Directory('EXT_FILE_DIR', '\\BISERVER\Transfer\OBI\POS');

    END;

  • Verify that the Oracle Directory Object 'EXT_FILE_DIR' is created by running the Query below:

    select * from dba_directories

OWNER	DIRECTORY NAME	DIRECTORY PATH
SYS	EXT_FILE_DIR	\\BISERVER\transfer\OBI\POS

• Update the path in OPERA application settings:

  Setup > Application Settings > OBI > Settings > OBI EXTERNAL FILE DIRECTORY = EXT_FILE_DIR.

• Set the OPERA Scheduler BI - External File Import job to run accordingly based on how often the data files are uploaded from the POS (every 1 hour or every 30 minutes, for example).
  • This job needs to run one time before the POS systems start uploading the data files as it is during this first run that it creates the directory structure where the POS import files need to be placed. It will create a subfolder for each POS system and each property that has BI_Enabled. For example:

  \\BISERVER\Transfer\OBI\POS\EXT_FILES\MICROS9700\Resort 1

  \\BISERVER\Transfer\OBI\POS\EXT_FILES\MICROS9700\Resort 2

• Once the import directory is created, the application setting updated and the data files are uploaded from the POS, the BI_External File Import job will import the data files into the OPERA datamart for the cube building.

• BI - Turnaway Cube. Select this option to build a cube that allows you to analyze and address rejection and lost business patterns. With the TURNAWAY Cube, you can run reports on Turnaways by Resort, Time (date/date range), Market Code, Turnaway Code, and Employee and then analyze the causes of the Turnaways to make a more informed decision to address them. Only Turnaways from the Central System will be reported, as Turnaways are not currently transferred from PMS to ORS. Because of this condition of not gathering information from PMS but only ORS, the Incremental Build job cannot run as it is dependent on the datamarts. The other standard jobs can be done: Full Build, Daily (Build & Maintain) and Manual Build.
• BI – Maintain Cubes. Select this option to guarantee that cleanup and maintenance jobs are executed for existing cubes. This job is selected after the cubes have been initially built. This job runs at all times and is automatically set to run every 10 minutes, by default. See Maintaining OBI Tables and Logs below.

Maintaining BI Cube Tables and Logs

Use the BI Maintain Cubes job to refresh all dimension tables and logs so they are loaded into the cube during the build process. This ensures the tables and logs are current and old data is purged. The BI Maintain Cubes job executes a series of background processes, including:

• Resyncs Dimension Templates. Refreshes the dimensions (Room, Rate, Market, etc.) from the OPERA tables so if new dimensions are created since the last time the process was executed, it will automatically insert them into BI.
• Maintains Time Dimensions. Refreshes the time dimensions. For example, this process purges old snapshot dates based on the setup in the application settings OBI > OBI DAILY SNAPSHOT TYPE and OBI > OBI MONTHLY SNAPSHOT DATE.
• Purges Datamart Tables. Purges old data from the datamart tables.
• Populates the Request_Log_Summary Table. Transfers data from the request_log_odd and request_log_even tables into the request_log_summary table.
• Cleans up BI Log and Error tables. Purges the BI Log and BI Error tables.

Each of these processes execute once a day even if the BI Maintain Cubes job is set to run multiple times a day. If the process finds, for example, the “Populate Request Log “ job has already been completed for that day, then the job skips the process and does not run it again until the next day at the scheduled time.

To run the BI - Maintain Cubes job:

• Highlight the BI - Maintain Cubes job.
• Select Run to set up the schedule when you would like all previously built cubes and their dimensions to be maintained. The Schedule Screen appears where you can set up a schedule for refreshing data for all cubes. See Reports Scheduler for an explanation of the Schedule screen.
• Select the Schedule for the desired time for all weekdays in the Repeat section. Select the Repeat Until Indefinite option button. By default, after the system first runs the BI - Maintain Cubes job, however, it automatically adjusts the schedule to run every 30 minutes.
• Press OK and the system will assign the Job ID for the processing job. All active OPERA BI cubes existing in OPERA will automatically be updated.

Generating Incremental Builds After Daily Builds

Users have the option of generating an OBI Incremental Build Job per cube after a Daily Build. This process monitors the Daily Build job to see if it was successful and if any new data has been generated since by a property; then the process waits a selected amount of time and refreshes the cube with the new data. The OBI Incremental Build Job is a convenient process for multi-property environments whose properties are in different time zones and the datamart data gets refreshed at different times during the Night Audit. This process ensures that the most current data in the cube is always available to all properties.

• Select the application setting OBI > OBI INCR BUILD WAIT TIME and enter the amount of time you want the OBI Incremental Build Job to start after the last successful Daily Build of the cube.
• On the OPERA Scheduler Procedures screen (Scheduler > Not Running > New), highlight the cube you want to run the Incremental Build on (identified by BI - Incr. <name of cube>, such as BI - Incr. Company Cube).
• Select the Run button. The Schedule Screen appears where you can set up a schedule for the Incremental Build. See Reports Scheduler for an explanation of the Schedule screen.

Maintenance Processing Time

When scheduling OBI cube maintenance, the time it takes to complete the maintenance process is greatly dependent upon the following factors:

1. Number of rows in the OPERA fact tables
   • A 1,000 room property may have 10 times more numbers of rows more than a 100 room property.
2. Number of dimensions in the cube
   • This is by far the main factor when calculating performance during the cube maintenance process.
3. Number of hierarchies within each dimension
   • A dimension with two hierarchies (i.e. All Channels and Channel Code) will perform much faster than a dimension with five hierarchies (i.e. All Dates, Year, Quarter, Month and Day). This is because depending on the cube definition, every dimension and most of their hierarchies must be summed up during the cube maintenance process.
4. Memory on the OPERA Database server
5. Speed of the processors

Example:

The Reservation History Cube, for a 200 room hotel, should take less than 60 minutes to build initially for a 2 year period. After the initial build, daily maintenance should take between 10 and 20 minutes.

See Also

• About OBI
• OBI Setup
• Welcome to OBI