3 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 Server.

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 OFSAA Analytics – Public to interact with the underlying available Database Structures.

2. After selecting the Database Connector, you must select the Database Schema to proceed to the next step of Database Object Selection.
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.

   Or

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

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

5. Drag and drop the tables to the Join Diagram. The Fact table must be added before you add the dimension tables. The fact table must be the left-most table in the Join Diagram.

   Note:

   If you want to build the dataset table Joins manually, rather than have Oracle Analytics automatically create them, then deselect the ‘Auto Join Tables’ toggle to turn off the Automatic Join creation.
6. Add the required Dimension tables.

   Note:

   By using the ctrl key you can select multiple Dimension tables.
7. Add joins between the Dimension and Fact tables by dragging the Dimension table to the Fact table.
8. Select the required joining condition.

   Note:

   Click Yes to treat the ID column as an attribute.
9. Add Joins between other selected Dimension tables as well.
10. To prepare a table's data, go to the tab row at the bottom of the Dataset editor and click the table's tab. Alternatively, from the Join Diagram, double-click the table, or right-click the table and select Open.
11. Use the Transform editor to perform any data transformations or enrichments.
12. Delete unwanted columns by clicking the Column, select Options, and then select Delete.
13. Convert the ID columns to attribute by clicking the column icon.
14. Select or remove multiple columns by clicking Edit Definition to open the Edit Definition window.
15. Once the changes are completed, save the dataset and then create the visualizations by clicking Create Workbook.
16. Drag and drop the required columns to create the visualizations.
17. Custom data sets are available, from the LHS menu, click Data, and then click Datasets path.
18. You can create a workbook by right-clicking the Data Set name and then selecting Create Workbook.