Creating a Data Model

1. In the R&A application screen, select the drop down menu for “Create” and click on “Data Model”.

2. A new “Untitled” screen will open. Select the drop down menu for the “+” sign under the “Diagram” tab. Click on “Analysis”.

3. A “New Data Set – Analysis” window will open.

   1. Enter a Data Set “Name”.

   2. Select the “Analysis” used for the Data Set.

   3. Enter “90” seconds for “Time Out”. (Best Practices Recommendation)

4. Click “OK” and return to the “Untitled” screen.

5. Select the Event Triggers option and click the “+” sign.

6. Change the name of the trigger to “init_security” (example below).

7. In the Oracle DB Default Package field enter “RA_P” then press the Update Default Package button.

8. In the Event Trigger field enter “RA_P.INIT(:xdo_user_name, :xdo_user_roles)

9. Select the Data Sets option then select the “Structure” tab. The screen will default to the “Table View”

10. The “XML Tag Name” column maps the “Data Source” information with the “Display Name” information. Enter the appropriate attribute names in the “XML Tag Name” column. (XML Tag Names do not allow space, use the “_” sign instead of space)

11. Click on “Properties” in the “Data Model” menu.

12. In the “XML Output Options” option, uncheck “Include Parameter Tags” box and check the “Include Empty Tags for Null Elements” box. (Best Practice Recommendation)

13. Return to the created Data Set and select the “Data” tab. Click on “View” and the system will run through the Data Set to confirm that all the attributes are correctly mapped.

14. Click on “Save As Sample Data”.

15. Select “Save” from the top right corner of the screen. Add “_DM” to indicate the file is a Data Model. (Best Practice Recommendation)

16. Data Model is now created and saved.

BIP Sql Based Data Model Security, Variables, LOV Queries and Bursting

When creating a BI Publisher data model utilizing SQL, OPERA R&A restricts the use of the variables being used and requires the user to apply specific mandatory joins and the data security trigger for user-based access. The LOV queries and Bursting queries used will not be able to utilize any table that is restricted due to the Data Model Security. There are three (3) tables that can be utilized without any impact by the table level security, but any transactional data returned in the output, will be determined by the end users data access level. The below tables can be used in LOV and Bursting queries without any impact:

• RESORT

• CHAIN_CODE

• PRT_PROPERTY

The attempt to use any other table, will not return the desired results. Alternatively, users can utilize logical queries from an analysis to determine results in LOV and Bursting queries. Additional information can be found in the bursting section of the user’s guide.

Mandatory Joins

When using the SQL based data model, the following joins are mandatory to be used when more than one (1) table is used to build the query.

Mandatory Joins on DSI and Organizationid to each table being joined.

Security Trigger

1. Go to Event Triggers section, add new New Event Triggers (+), rename it as init_security, and select Type as Before Data from the drop down.

   

2. Go to the PL/SQL block, under the Oracle DB Default Package in the text box enter RA_P and press the Update Default Package button, the Available Functions section will get populated. Next update the Event Trigger section with the following, which can also be copied and pasted: RA_P.INIT(:xdo_user_name, :xdo_user_roles)

3. PRT_PROPERTY

Variables

The following data type variables are available to be used against data models.

Field Variables

• p_tax_id VARCHAR2

• p_resorts VARCHAR2

• p_from_date DATE;

• p_to_date DATE;

• p_chain VARCHAR2

• p_business_date DATE;

Character

• p_c1 VARCHAR2

• p_c2 VARCHAR2

• p_c3 VARCHAR2

• p_c4 VARCHAR2

• p_c5 VARCHAR2

• p_c6 VARCHAR2

• p_c7 VARCHAR2

• p_c8 VARCHAR2

• p_c9 VARCHAR2

• p_c10 VARCHAR2

Numeric

• p_n1 NUMBER;

• p_n2 NUMBER;

• p_n3 NUMBER;

• p_n4 NUMBER;

• p_n5 NUMBER;

• p_n6 NUMBER;

• p_n7 NUMBER;

• p_n8 NUMBER;

• p_n9 NUMBER;

• p_n10 NUMBER;

Date

• p_d1 DATE;

• p_d2 DATE;

• p_d3 DATE;

• p_d4 DATE;

• p_d5 DATE;

• p_d6 DATE;

• p_d7 DATE;

• p_d8 DATE;

• p_d9 DATE;

• p_d10 DATE;

Example of the Parameter Screen

Data Source

The data source always to be used is the Analytics, we cannot use any other data source considering the security applies only to source Analytic. The sample Resorts query to be used for the resorts parameter.

SELECT DISTINCT r.Resort FROM Resort r, Prt_Property p WHERE r.Organizationid = p.Organizationid AND r.Resort = p.Resort AND r.Dsi = p.Datasourceid AND p.Active = 1 ORDER BY r.Resort

List of Values

Additional Restricted Variables

The Oracle product provided templates may use other variable parameters. These parameters are specific to the respective templates and should not be removed or used in any other custom queries or data models. Utilizing these can potentially cause errors in the running of the data model and is not supported.

• task_sheet_no VARCHAR2

• attendant VARCHAR2

• show_departure_only VARCHAR2

• show_summary VARCHAR2

• show_guest_name VARCHAR2

• show_alternate_names VARCHAR2

• show_specials VARCHAR2

• show_room_features VARCHAR2

• show_items VARCHAR2

• show_house_use VARCHAR2

• show_block_code VARCHAR2

• show_traces VARCHAR2

• dept_id VARCHAR2

• use_priority VARCHAR2

• from_task_date VARCHAR2

• to_task_date VARCHAR2

• p_roomstatus VARCHAR2

• p_sections VARCHAR2

• p_rooms VARCHAR2