1. User Guide
  2. Reports & Analytics
  3. Funds Transfer Pricing Cloud Service Reports & Analytics
  4. Preparing Data using SQL Query Browser

7.1.2 Preparing Data using SQL Query Browser

Data Sets are self-service Data Models that you build specifically for your Data Visualization and Analysis requirements.

A Data Set can be based on one Table, Spreadsheet, or a File. Alternatively, a Data Set can be a self-service Data Model that contains multiple Tables with relationships defined between the Tables.

A Data Set contains Data Source Connection Information, Tables, the Columns you specify, and the Data Enrichments, and Transformations that you apply.

For more information, see Visualizing Data and Building Reports in Oracle Analytics Cloud.

To access the SQL Query Browser and prepare Data, follow these steps:
  1. From the LHS Menu, select Analytics, and then select SQL Query Browser.

    The SQL Query Browser allows you to use an existing Database Connector named OFSAA Analytics – Public to interact with the underlying available Database Structures.

    Figure 7-2 Create Data Set Screen


    The Create Data Set Screen allows you to select an available Analytics Dataset.

  2. After selecting the Database Connector, you must select the Database Schema named OFSAA_ANALYTICS_PUB to proceed to the next step of Database Object Selection.

    Figure 7-3 Add Data Set


    The Add Data Set allows you to select the select the Database Schema.

  3. Provide a meaningful name to the Data Set, which will be generated from this process and be used for the SQL Query Analysis.
  4. You can search for a Database Object from the available options. You can either scroll down or search the Database Objects displayed in alphabetical order.

    Figure 7-4 Add Data Set – Search from the List


    The Add Data Set – Search from the List screen lists the available Database Objects for the selected Database Schema and allows you to select the Database Object.

    Or

    Type the Database Object Name to filter the list with Description.

    Figure 7-5 Add Data Set – Search by Name


    The Add Data Set – Search by Name screen allows you to search Database Object by Name.

    After you select the Object that want, you can proceed to the next step.

  5. You search the Columns that are available for the selected Database Object by scrolling.

    Figure 7-6 Add Data Set – Search Columns


    The Add Data Set – Search Columns screen allows you to select the Columns for the selected Database Object.

  6. Add the Database Object Column as required.

    Figure 7-7 Add Data Set – Adding the Database Object Column


    The Add Data Set – Adding the Database Object Column screen allows you to add the selected Database Object Column.

  7. Click Get Preview Data to display the retrieved Data Results.

    Figure 7-8 Data Results


    The Data Results displays the retrieved Data Results for the selected Database Object and Column.

  8. In addition, you can switch to the Enter SQL Pane Editor. You can change the auto-generated SQL Query at any time and click Get Preview Data to retrieve the results based on the modified SQL Query.

    Figure 7-9 Data Results based on modified SQL Query


    The Data Results based on modified SQL Query displays the SQL Query Browser where you can change the auto-generated Query and retrieve the results based on the modified SQL Query.

  9. Click Add to save the SQL Data.
  10. Click Data on the LHS Menu and click Data Sets to display the available Data Sets for usage.
  11. Right-click on the Data Set name to display the options as shown:

    Figure 7-10 Data Set Options


    The Data Set Options part of the screen allows you to see the various options available to you on right-click on Data Serts.

  12. In the menu that is displayed, click Create Project.