Mining JSON Data Using Oracle Data Miner 4.1

In this topic, you will create a workflow and add the JSON data source.

1. Right-click the Big Data Analysis project and select New Workflow from the menu.

2. In the Create Workflow window, enter JSON Attribute Analysis as the name and click OK.

   

   Result: Just like in the Using Oracle Data Miner 4.1 tutorial, an empty workflow tabbed window opens with the Workflow name that you specified.

   

   Note: In this example, the other projects were previous created by completing other tutorials in this suite.

3. Next, add a Data Source node for the JSON data.

   A. In the Components tab, open the Data category.

   B. Drag and drop the Data Source node onto the Workflow pane. Result: The Define Data Source wizard opens automatically.

   C. In the Available Tables/View list, scroll down and select the ODMR_SALES_JSON_DATA table as shown here:

   

   Notes:

   • Notice that there is only one column (JSON_DATA) in this table, of JSON(CLOB) data type. The JSON prefix indicates that the data stored is in JSON format, and that CLOB is the original data type.
   • This table is installed as part of the demo data that is provided by the Oracle Data Miner.

D. Click Finish to at the data source node to the workflow.



4. Next, right-click the data source node and select Run from the menu.

   Result: When a JSON data source node is run, the execution process parses the JSON documents to produce a relational schema that represents the JSON document structure.

   Note: The Data Source node recognizes that the CLOB is json is due to an IS_JSON constraint that has been added to the column definition of the table

5. Now, drag and drop a JSON Query node from the Transforms group to the workflow.

   

6. Connect the data source node to the JSON Query node,like this:

   

7. Double-click the JSON Query node to open the JSON Query Node Editor window.

   A. Then, select JSON_DATA as the Column:

   

   Results: The available JSON attributes are listed in the Structure tab.

   B. Select all of the JSON attributes down to, but not including SALES. Note: You will define aggregations for two of the SALES nexted attributes (QUANTITY_SOLD and AMOUNT_SOLD).

   

8. Next, select the Aggregate tab. Here, you will define aggregations for QUANTITY_SOLD and AMOUNT_SOLD attributes (within the SALES array).

   A. First, click the Edit Attribute tool (pencil icon) in the Group By Attributes region.

   B. In the Edit Group By window, select CUST_ID as the Group-By attribute, as shown below. (Notice the Group-By attribute can consists of multiple attributes.)

   

   C. Click OK to return to the Aggregate tab.

   D. Now, click the Add Attribute tool (green "+" icon) in the Aggregation Attributes region.

   E. In the Add Aggregations Dialog window, scroll down and select both the AMOUNT_SOLD and QUANTITY_SOLD attributes. Then click the Formula tool and select SUM from the list as the aggregation function.

   

   F. Next, click the Sub-Group By Element tool (next to the Formula tool) and select PROD_ID from the list. This selection specifies calculation of quantity sold and amount sold per product per customer.

   

   Note: Specifying a Sub-Group By column creates a nested table, and the nested table contains columns with data type DM_NESTED_NUMERICALS.

   G. Click OK to return to the Add Aggregates Dialog.

   F. Click OK again to return to the JSON Query Node Editor, which should look like this:

   

   Notes: The Preview tab displays the generated relational output.

   The Output Columns tab shows all output columns and their corresponding source JSON attributes, as shown here. (The output columns can be renamed by using the in-place edit control.)

   

   The Output Data tab shows the actual data in the generated relational output.

   

9. Click OK to close the JSON Query Node Editor.

10. Finally, right-click the JSON Query node and select Run from the menu.

    When the process is complete, the workflow should look like this:

    

    Notes: The generated relational output is single-record case format; each row represents a case. If we had not defined the aggregations for the JSON array attributes, the relational output would have been in multiple-record case format, which is not suitable for building mining models except for an Association model.

In this topic, first you add a table node to the workflow to persist the JSON Query results. Then you define classification models for the JSON data by using a Classification node.

1. Add a Create Table node to the workflow.

   A. Drag and drop a Create Table node to the workflow, like this:

   

   B. Connect the JSON Query node to the OUTPUT node.

   C. Double-click OUTPUT node, and change the table name to JSON_OUTPUT. Then click OK.

   

   D. Finally, rename the OUTPUT node to Table for Performance.

   Result: The workflow should look like this:

   

   Note: This practice enhances performance when building models against the JSON data.

2. Now, add a Classification node to the workflow.

   A. First, expand the Models category in the Components tab. Then drag and drop the Classification node to the Workflow pane, like this:

   

   B. Next, connect the Table for Performance node to the Class Build node. (The Edit Classification Build Node window automatically appears.)

3. In the Build tab of the Edit Classification Build Node window:

   A. Select AFFINITY_CARD as the Target attribute, and CUST_ID as the Case ID attribute.

   

   B. Click OK to save these settings and close the Edit Classification Build Node window.

   Result: The workflow should now look something like this:

   

In this topic, you build the classification models against the structured JSON data. Then, you examine the model results.

1. Right-click the Class Build node and select Run from the menu.

   Results: When the build is complete, all nodes contain a green check mark in the node border.

   

2. Right-click the Class Build node and select Compare Test Results from the menu.

   Results: A window opens showing a graphical comparison of the models in the Performance tab, as shown here:

   

   Notes: The results show that the Naive Bayes and SVM models produce a higher degree of Predictive Confidence, Average Accuracy, and Overall Accuracy than the other two model types. (Your Test Results may vary slightly in all cases.)

3. Select the Performance Matrix tab and perform the following:

   A. Select the SVM model.

   

   Notes: For the selected model in the upper pane, target value details are provided in the lower pane. The correct prediction % for the Target Value of "1" ("Yes" on the Affinity Card) is 83.13% for the SVM model.

   B. Select the NB model.

   

   Notes: The correct prediction % for the Target Value of "1" ("Yes" on the Affinity Card) is 74.69% for the NB model.

4. When you are done looking at the Performance Matrix tab, close the Class Build window.

5. Next, right-click the Class Build node and select View Models > CLAS_SVM_1_#

   Result: A window opens with statistical analysis of the attributes that are most important in predicting the target (Affinity Card) by using the SVM model. In the Coefficients tab, de-select the Sort by absolute value option, and then sort in descending order using the Coefficient column, as shown here.

   

   Conclusion: The SVM model indicates that the EDUCATION and HOUSEHOLD_SIZE attributes are the most significant contributors to the prediction. Notice that four of the top five predictive attributes are these two.

6. Dismiss the Decision Tree display tab and close the workflow when you are done viewing the Decision Tree display.