OBI Cube Maintenance

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

Properties must have license codes for:

• OPI_<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. In this case, a separate license code is not required for OCIS DataMarts. (Within an ORS/OCIS environment without OBI, if DataMarts are required, a separate add-on license is required: OPI_BIDA)).

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). The fact tables contain the most accurate data after the end of each business day. 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.

Note: 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. This information is available in the OPERA Business Intelligence front-end, through standard reports.

Building and Maintaining OBI Cubes

Access the Opera Scheduler Procedures screen (Scheduler>Not Running>New) to set up a schedule to: 1) build a cube for the first time, or 2) rebuild it after an upgrade, and to 3) update and maintain OBI cubes after they have been built. This screen lists the procedures that are available to run.

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

OBI Cube Scheduler Procedures

Highlight the cube you want to build and maintain. Your options, with a description, follows. Please note that new cubes are still in development and may not reflect this list.

Build and Maintain (Update)

• 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.
• 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.
• BI - Future Resv Monthly Cube - Select this option to build a cube with detailed statistics for 'on the books' reservations and blocks.
• 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 – Maintain Cubes. Select this option to guarantee that cleanup and maintenance jobs are executed for existing cubes. This option is selected after the cube(s) have been initially built. This job runs at all times and is automatically set to run every 10 minutes, by default. See Maintaining All Existing Previously Built OBI Cubes below.

Select Run to set up the schedule when you would like the highlighted cube to be built. The Schedule Screen appears where you can set up a schedule for refreshing data. See Reports Scheduler for an explanation of the Schedule screen. 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).

Note: A collection of standard reports is available in OBI so the cube building processes can be monitored and automatic alerts can be set up. This is to verify from within OBI that the records are transmitting properly from the property.

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. All existing data will be overwritten.

Maintaining All Existing Previously Built OBI Cubes

Use this procedure to refresh and maintain all previously built OBI cubes (described in the OBI Cube Scheduler Procedures, above).

• Highlight the BI - Maintain Cubes option.
• Select Run to set up the schedule when you would like all previously built cubes to be refreshed. The Schedule Screen appears where you can set up a schedule for refreshing data for all cubes.
• Select the Schedule as Every 1 Hour for all weekdays in the Repeat section. Select the Repeat Until Indefinite radio button. By default, the system updates and refreshes the data every 10 minutes.
• Press OK and this will assign the Job ID for the processing job. All active Opera BI cubes existing in Opera will automatically be updated.

  Note: The first time time the Maintain Cubes function runs, Opera will check the repeat frequency and set it to 10 minutes automatically. This can be verified by viewing the Next Run Date in the Scheduler.

Maintenance Processing Time

When scheduling OBI cube maintenance, please note that 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.

End of Day Process Setup for OBI Maintenance

• The procedure data_mart_api.populate_financial_stat_daily needs to be enabled in the End of Day Final Procedures.
• Properties connected to OCIS should also ensure that the End of Day business event is enabled.

See Also

• About OBI
• OBI Setup
• Welcome to OBI