Create a Data Model from a Custom Query

Use this topic to create a data model from a custom query.

1. Log in to Fusion Service as a user who can use Reports and Analytics.

2. Click Navigator > Tools > Reports and Analytics.

3. Click Browse Catalog.

   Your browser opens the Oracle Business Intelligence catalog in a new browser tab.

4. Click New, and then from the drop down list, select Data Model.

5. On the Diagram tab, click the Plus icon, and select SQL Query from the drop down menu, and do the following:

   1. In the Name field, enter: Migration Data.

   2. From the Data Source drop down list, select ApplicationDB_CRM.

   3. From the Type of SQL drop down list, select Standard SQL.

   4. Paste the following SQL statement into the SQL query text box.

      SELECT
          event_id,
          job_id,
          contact_party_id,
          hzp.party_unique_name   contact_name,
          event_type_cd,
          etlookup.meaning        event_type,
          event_status_cd,
          eslookup.meaning        event_status,
          event_note,
          login_id,
          history.last_update_date
      FROM
          fusion.svc_css_idp_migr_history   history,
          fnd_lookups                       etlookup,
          fnd_lookups                       eslookup,
          hz_parties                        hzp
      WHERE
          etlookup.lookup_code = event_type_cd
          AND etlookup.lookup_type = 'ORA_SVC_CSS_EVENT_TYPE_CD'
          AND eslookup.lookup_code = event_status_cd
          AND eslookup.lookup_type = 'ORA_SVC_CSS_EVENT_STATUS_CD'
          AND hzp.party_id = history.contact_party_id
          AND history.last_update_date >= :JOB_START_DATE

   5. Click OK.

6. In the Add Parameter dialog box, select the JOB_STATE_DATE check box and click OK.

7. In the Parameters page, click the Data Type drop down list for JOB_START_DATE parameter and select Date.

8. Click the Mandatory check box.

9. In the Display Label field, enter Job Start Date.

10. In the Data Model pane, click Properties > Data Sets > Migration Data.

11. Click the gear icon for each entry in the G_1 table, select properties each time, and then enter the information shown in the following table:

    Name	Display Name	Data Type
    EVENT_ID	Event ID	Long
    JOB_ID	Job ID	String
    CONTACT_PARTY_ID	Party ID	Long
    CONTACT_NAME	Name	String
    EVENT_TYPE_CD	Event Type Code	String
    EVENT_TYPE	Event Type	String
    EVENT_STATUS_CD	Event Status Code	String
    EVENT_STATUS	Status	String
    EVENT_NOTE	Comments	String
    LOGIN_ID	Login ID	String
    LAST_UPDATE_DATE	Last Update Date	Date and Time

12. Click JOB_ID and ensure that only that row is selected, then click the Gear icon and select Group By.

13. Click CONTACT_PARTY_ID and ensure that only that row is selected, then click the Gear icon and select Group By.

14. Click the Save As icon, and give the data model a meaningful name such as Migration DM.

15. Click the View Data button.

16. Enter a value for the Job Start Date and click View to view sample data.

17. Save as sample data.