|
|
|
Data Extraction
The Data Extraction feature is a flexible tool that allows you to find and extract information from the OPERA database for a variety of purposes. For example:
To access the Data Extraction feature, select Miscellaneous > Data Extraction > Data Extraction Query. The Data Extraction Query Search screen appears.
Description. To find a query that has been saved, enter the query's description, or first part of the description, in the Description field. Select the Search button. To list all queries, leave the Description field blank and select the Search button.
Include Global. In ORS, SFA, and multi-property PMS and S&C, you can create queries that are available to all properties in the chain. To include queries that were created as globally available, select this check box. When you perform the search, the global queries will have an X in the G (Global) column.
Note: In a central system, when processing a query created using a view that generates property specific information, for example, Reservations with Profiles, the user is shown a multiselect property LOV to select properties. If the query is processed in PMS, a property filter must be used to generate data for a specific property. Otherwise, all properties to which the user has access will be queried.
To immediately process an existing query, highlight your choice and select the Process button. The Data Extraction Query Results screen appears.
To edit an existing query, highlight the query in the search results grid and select the Edit button. To create a new query, select the New button. The Data Extraction Query screen appears.
Note: To create, edit or delete an existing query, your must first be granted the appropriate permissions; QUERY_NEW, QUERY_EDIT and/or QUERY_DELETE.
The Data Extraction Query screen is the starting point for creating and editing queries.
When you create a new query, the Query Name field displays the default name assigned to the new query. You can change this name, but each query should have a unique name to make it easy to identify the query later. Once you save the query, the query name is displayed in blue text at the top of the screen and cannot be changed. In the future, to reuse or edit the query, this name will assist you in locating this query using the Query Search screen.
You can make a query global by selecting the Global check box. When the ORS license is active and a global query is processed, you are prompted to select the properties that you want to include in the query. In SFA, PMS, and S&C, selecting the Global check box makes the query available to all properties in the chain.
Designing a data extraction query is a step-by-step process. The Data Extraction Query screen guides you through the steps. As you complete each step, appropriate choices become active in the following steps, based on your previous actions and choices. When you complete the all steps and select the Add button, the query statement you composed appears in the text box in the lower part of the screen.
Although some simple queries consist of a single statement and can be built in a single pass through the Data Extraction Query steps, you may need to go back and repeat the steps one or more times to construct complex queries that contain multiple (and possibly nested) query statements.
Following is a description of the basic steps for building a query.
Step 1. Select a Data Source. Select the down arrow to choose the data source for your query. From the list of Data Sources, make your choice based on what you want to accomplish with your query.
It is a good idea to put what you are looking for into words — a short statement that can guide you in constructing your query. For example, you might say, "I want to find all contacts associated with active accounts in Illinois." A statement like this makes it clear what data you are seeking (contact names) and what filters you need to apply (a filter for accounts whose active flag is equal to Y, and a filter for State addresses equal to IL). Based on this information, you would also know that you need to select a data source that gives you contact names and account information — All Contacts With or Without Primary Accounts. Each of the data sources listed may pull data from several database tables behind the scenes. This choice — All Contacts With or Without Primary Accounts — will give you access to several OPERA database tables at once, specifically, the Contacts table and the Accounts table. If you only wanted information about contacts or about accounts but not both, you might choose All Contacts or All Accounts as your data source.
Note: Select Configuration > Setup > Data Definition for information about which fields are available from the OPERA database tables.
When you only want guest profile stay data extracted, such as for ORS/OCIS Campaign Management, select the data source option All Profiles.
The data sources available to you will depend on the license codes and application functions that are active at your property. See Notes on Data Sources, below.
Step 2. Select a Field for Filter Condition. Based on your choice of data source, you can now select from the fields in the table or tables that are available to you. Select the field that contains the data you want to find or that you wish to use as a filter for further queries. For example, if you want to consider only active accounts in your query, you might choose Active Flag Accounts as your filter field in this query. Notice that the list of Data Source Fields that appears when you select the down arrow identifies the Field Name and the Table Name where the field is found to help you in making your choice.
Note: To extract guest profile stay data from the correct property and date range (such as for what may be required for ORS/OCIS Campaign Management), one of the following fields for the filter condition must be selected. These required fields for filtering are property and reservation-specific records, which enable the mail filter (Totals Range Screen) to identify the correct property and date range information.
- Cancellations
- Cancelations – Last year
- Total Day Use Rooms
- Total Day Use Rooms – Last year
- Total Nights
- Total Nights – Last year
- No Shows
- No Shows – Last year
- Total Revenue
- Total Revenue – Last year
- Total Stays
- Total Stays – Last year
Note: When a date field Filter Condition is selected for the Step 1 Filter Condition, a range (Before/After Business Date) can be configured for the Global Alert filter by selecting the check boxes below. By checking any of the check boxes for Day, Month, Year, the user defines which parts of the date OPERA should match. For example, if the Step 1 Filter Condition is Date of Birth and the Step 2 is set to ‘Is between’ and then the check boxes are checked and set up as Business Date -2 and Business Date 2 then OPERA will display this alert for anybody that has a Birthday 2 days before up to 2 days after the current business date. Since in case of the birthday, it does not matter which year the guest was born, the Year check box has to be unchecked so OPERA is not looking for Guests that were born in the current year (since the business date is being matched, which is in the current year). All parts of the date can be included/excluded using those check boxes depending on what the user is attempting to set up.
Step 3. Specify a Filter Condition. Based on your choice in step 2, various filter conditions will be available for you to choose from when you select the down arrow. For example, when you are looking for active accounts you want to find those whose Active Flag field contains a Y; therefore, you would select Is Equal To as the filter condition. In the field underneath, enter the value you are looking for (in our example, Y).
Notice that the extra field for the entry of a value appears only if a value is required. For example, if you selected Is Empty for the filter condition rather than Is Equal To, there would be no need for you to enter a value and the data entry field would not appear.
Or, if in Step 2 you selected a filter condition field that calls for a date or range of dates (for example, an arrival date), the filter condition options available in Step 3 might include Is After or Is Between and date entry fields would appear underneath rather than a text entry field.
Step 4. Apply the Filter Condition. Now, select the Add button to copy the query statement you created into the text area.
Repeat these steps to add follow-on statements, if they are required by your query. In our example, where you are looking for contacts in Illinois, you would add another query statement that selects the account address State field as the filter field in Step 2, and Is Equal To IL as the filter condition in Step 3.
By default, as you add new statements they are combined with those that went before using the AND operator. If you want to change an AND operator to an OR operator, highlight the AND statement, click the AND/OR button, and then click the Update button. The AND operator changes to OR. You can also change an OR operator back to an AND operator using this same process: Highlight the OR statement that you want to change to an AND statement, click the AND/OR button, then select the Update button. The OR operator changes back to AND.
To nest statements, place them in parentheses. Highlight the statement where you want the nesting to start and click on the open parent (() button. Select the Update button. Next highlight the statement that you want to be the last statement inside the parent. Click the close parent ()) button. Finally, select the Update button. When you are nesting statements, the number of open parent must equal the number of close parent.
To remove a parent from a conditional statement, click the '(' OR ')' button up to four times (depending on how many parentheses have been used in the statement), until the red parent beside the button disappears. Then click the Update button.
Note: Although both AND and OR are logical operators, AND is "stronger". It binds the logical expressions on either side of it more tightly than the OR operator does (technically, AND is said to have a "higher precedence" than OR). For example, in this clause:where Accounts->Industry Code = 'A' OR Accounts- >Industry Code = 'B' AND Accounts- >Country = 'ITALY'
the AND is always acted upon first. You can break this bonding by using parentheses to enclose those expressions you want to be interpreted together. Parentheses override the normal precedence:where (Accounts->Industry Code = 'A' OR Accounts- >Industry Code = 'B') AND Accounts- >Country = 'ITALY'
Or, to get the same result the query could be written like this:where Accounts->Country = 'ITALY' AND (Accounts->Industry Code = 'A' OR Accounts- >Industry Code = 'B')
To process the query, select the Process button. The Data Extraction Query Results screen appears.
To edit an existing query, highlight the query in the Data Extraction Query Search grid and select the Edit button. The Data Extraction Query screen appears.
In the text area in the lower part of the screen, highlight the statement you want to edit. When you do so, the elements of that query appear in the Step 2 and Step 3 fields, where you can change the filter field and filter condition as necessary.
Note: You cannot change the data source for a query (Step 1). If you need to change your data source, you must create an entirely new query.
When you have made your changes, select the Update button to record the modified query statement in the text area.
To remove a statement entirely, highlight the statement in the text area and select the Delete button.
To process the query, select the Process button. The Data Extraction Query Results screen appears.
The query statements you see in the Data Extraction Query screen text area are not "real" SQQ statements. These statements are pseudo-SQQ statements that are easier to read and understand for most users. If you have a need to see the actual SQQ code behind the query statements you see on the Data Extraction Query screen, select [Alt + W]. The SQQ statement appears in a box like this:
The SQQ statement in the box is in text format and can be copied and pasted into other programs, such as SQQ Navigator.
Activity Data Sources
When the Activity > Use Time Zones for Activities application parameter is set to Y, dates related to activity queries (e.g., the date range you enter as a filter if you are using Activities with Accounts and Contacts as the data source for a query, and the field you select is Activities Completed Date) are converted to database time zone dates when executing the query. Times are not considered in data extraction queries. See Activity Time Zones for details.
Data Sources for FIT Contracts
The data source - Contracts with Accounts and Contacts is only available when the application parameter Contracts > FIT is set to Y.
Following is a listing of all available FIT contracts fields extracted as part of this data source:
Note: All calculations, such as those for Actual Room Nights at Contracted Rates, will match those on the Contract Overview screen.
Following is a list of existing data sources with the view that they use (the fields that are available for mailing from the mentioned views are filtered to only show mailing fields in these listings.)
Table Name |
Available Fields Listing |
All Accounts (ACCOUNT_VIEW) |
|
All Contacts (CONTACT_VIEW) |
|
All Accounts with or without Contacts (ACCOUNT_VIEW and CONTACT_VIEW) |
|
All Contacts with or without Primary Account (ACCOUNT_VIEW and CONTACT_VIEW) |
|
Activities with Accounts and Contacts (ACCOUNT_VIEW and CONTACT_VIEW and ACTIVITY_VIEW) |
|
Business Blocks with Accounts and Contacts (SC_BUSBLOCK_INFO) |
|
Events with Accounts, Contacts, and Business Blocks (SC_BUSBLOCK_INFO and GEM_EVENT) |
|
Reservations with Profiles [Last Name] (NAME_RESERVATION) |
|
All Profiles (NAME_VIEW) |
|
Reservations History with Profiles [Last Name] (PROFILE_HISTORY_RESERVATIONS) |
|
Profile Requests (PROFILE_REQUEST_VIEW) |
|
Guest Profiles (NAME_VIEW) |
|
General Stay Records Extract (GEN_STAYER_VIEW) |
|
General Stay Records Revenue Extract (GEN_EXT_STAYER_REV) |
When Business Blocks with Accounts and Contacts is selected as a data source, the Lost to Destination and Lost to Property fields can be selected as filters. These fields are useful when querying for information on business block cancelations. Lost to Destination filters on the Destination (e.g., competitor's chain) code specified when a business block is canceled. Lost to Property filters on the free form text specified in the Property field when a business block is canceled. Lost to Property information might include the name of a specific property to which the business was lost, or the city to which the business was lost, etc. See Business Block Cancel Tab for additional details.
Note: Block revenue offset calculations are included in this data source when the Blocks > BUSINESS BLOCK OFFSETS function is set to Y and offsets are applied to a block.
It is possible to create data extraction queries to identify contacts with or without activities based on a specific activity type and activity start date range.
Here are some examples of how to set up these kinds of queries:
1. Find all contacts with an account who do not have any activities
2. Find all contacts with an account who do not have any activities that start within a given date range.
3. Find all contacts with an account who do not have any activities of a specific activity type.
Step 3. Condition: Select Does Not Exist For and then select an activity type from the LOV.
4. Find all contacts with an account who do not have any activities of a specific activity type that start within a given date range.
5. Find all contacts with an account who have a specific activity type.
It is possible to create data extraction queries to identify accounts or contacts with or without activities based on a specific activity type and date range.
1. Find all contacts who do not have any activities:
2. Find all contacts who do not have any activities since a specific date:
3. Find all contacts who do not have any activities of a specific activity type:
4. Find all contacts who do not have any activities of a specific activity type since a specific date:
5. Find all accounts which do not have any activities:
6. Find all accounts which do not have any activities since a specific date:
7. Find all accounts which do not have any activities of a specific activity type:
8. Find all accounts which do not have any activities of a specific activity type since a specific date:
For a given individual, the contact profile and the individual profile are interlinked and share the same primary email address. The following query setup procedure is available for those properties that wish to use a separate email address for mailings directed to contact profile recipients and for mailings directed to individual profile recipients.
1. Set up an individual email type and a contact email type for the EMAIL communication role. (Email types are configured in Configuration > Profiles > Communication Types. See Communication Types for details.)
2. Use the Profiles > Mailing Individual Email Type and the Profiles > Mailing Contact Email Type application settings to identify the email type for individuals and the email type for contacts.
Note: When no value has been selected for the Profiles > Mailing Individual Email Type and/or for the Profiles > Mailing Contact Email Type, the email address flagged as Primary on the profile will be pulled by data extraction queries based on individual or contact data sources. An exception to this is the Profile Requests data source which will pull the email selected for the request if no value has been set in the application setting.
3. Set up email addresses of the appropriate email types — individual email type email address and/ or contact email type email address — for each profile.
4. Create the data extraction query.
For a given individual, the contact profile and the individual profile are interlinked and share the same primary mailing address. The following query setup procedure is available for those properties that wish to use a separate mailing address for mailings directed to contact profile recipients and for mailings directed to individual profile recipients.
1. Set up an individual address type and a contact address type. (Address types are configured in Configuration > Profiles > Address Types. See Addresses for details.)
2. When the Profiles > Address Type Default application setting is set to Default, you can use the Profiles > Contact Address Type and the Profiles > Individual Address Type application settings to identify the address type for contacts and the address type for individuals. See Contact Address Type Application Setting, Individual Address Type Application Setting, and Address Type Default Application Setting for details.
Note: When no value has been selected for the Profiles > Contact Address Type and/or for the Profiles > Individual Address Type, the mailing address flagged as Primary on the profile will be pulled by data extraction queries based on individual or contact data sources. An exception to this is the Profile Requests data source which will pull the mailing address selected for the request if no value has been set in the application setting.
3. Set up mailing addresses of the appropriate address types — individual address type mailing address and/ or contact address type mailing address — for each profile.
4. Create the data extraction query.
You can filter your data extraction query results to include only records having a specific address type by adding a filter based on the address type for the profile type specified by the data source. For example, if the data source is All Contacts, you can add an AND filter Contacts->Address Type Is Equal To BUSINESS to show only those contacts that have an address of BUSINESS address Type.
The Using Address Type and Using Format Type filters can be used to organize merge file data; however, these filters do not affect the data extraction query results itself. For example, if the data source is All Contacts, you can add a "Using" filter Using Address Type Is Equal To BUSINESS and Using Address Format Is Equal To SALES_CONTACT.
Contacts which do not have a BUSINESS address-type address will still be displayed in the query results.
In your merge file created from the query results, the contact's BUSINESS address will be included in the Formatted Address field. If the Profiles > Address Type Default application setting is set to Primary, the contact's primary address will be used in the merge file's individual address fields (Contacts Address 1, Contacts Address 2, Contacts City, etc.) and in the Formatted Address field if the contact does not have a BUSINESS address.
If the Profiles > Address Type Default is set to Default, the address type specified by the Profiles > Contact Address Type application setting is included in the merge file's individual address fields (Contacts Address 1, Contacts Address 2, Contacts City, etc.). If the Contact Address Type is BUSINESS, the BUSINESS address will be used in the individual address fields and in the Formatted Address field. If no BUSINESS address is specified for the contact, or if no Contact Address Type is specified for the application setting, the profile's primary address is used.
The filter conditions of Owner Full Name Is Equal to and Owner Full Name Is Not Equal to have been added for the Owner Full Name (Primary) field for the Data Sources of All Accounts, All Contacts, All Accounts with or without Contacts, All Contacts with or without Primary Accounts, and Activities with Accounts and Contacts.
Show Me
Creating a Query and Saving it as a Merge File
See Also
