Lesson 1: Adding steps to your mining flow that extract the mining model

In this lesson, you will add steps to your mining flow that extract the mining model into tables.

About this task

Overview

The last step of your mining flow starts the Associations visualizer so you can view the association rule model that the Association operator creates. In this lesson, you add steps that are parallel to the Visualizer operator. These steps organize association rules into several fields and store the rules in database tables.

Tasks in this lesson

To get started, complete the following tasks:
  1. Adding an Associations Extractor operator
  2. Creating a target table and adding a Table Target operator for association rules
  3. Creating a target table and adding a Table Target operator for rule bodies

Adding an Associations Extractor operator

You must add an Associations Extractor operator to organize your mining model into fields that can be stored in tables.

About this task

Procedure

To add an Associations Extractor operator to your flow:

Procedure

  1. Open your mining flow if it is not already open:
    • Right-click the Market_basket_analysis mining flow under the Mining Flows folder of the Warehouse Mining Tutorial project in the Data Project Explorer view and click Open.
  2. In the Design Studio canvas, drag an Associations Extractor operator from the Data Mining section of the palette to your flow.
  3. Specify your Associations operator as input to your new Associations Extractor operator:
    • Click the Model port of the Product Affinities Associations operator and then click the Model port of your new Associations Extractor operator.

Results

Each output port of the Associations Extractor represents a different table of information from the association rule model. The rule port represents a table that contains most elements of the association rules and quality information for each rule. The following table shows how a row of the table might look.
Table 1. Sample content of table of association rules
ID BODYID HEAD HEADNAME LENGTH BODYTEXT SUPPORT CONFIDENCE LIFT
1 296 45 Husky Rope 200 3 [ Husky Harness Extreme ] [ Husky Rope 100 ] 0.0010155 0.3508772 24.947115
The row contains the support, confidence, and lift of the rule "customers who purchased Husky Harness Extreme and Husky Rope 100 also purchased Husky Rope 200."

Notice that the table does not include product IDs for the items in the body of the rule. The table cannot have columns for the IDs because a rule body can include one or several items, depending on how you configured the Associations operator. For a shopping cart application, however, you want to retrieve association rules that are relevant to the items in the shopping cart.

To map from product IDs to association rules, you can use the table that the rulebody port provides. The following table shows how some rows of the table might look.
Table 2. Sample content of table of association rule bodies
BODYID ITEMNAME ITEM
296 Husky Harness Extreme 48
296 Husky Rope 100 44
The two rows together specify the body of an association rule.

You can find an example of an SQL query that uses these tables in the last lesson of this module.

What to do next

In the next two tasks, you will add operators to your flow that store these two tables in your database.

Creating a target table and adding a Table Target operator for association rules

You must create a table to store your association rules, and add a corresponding Table Target operator to your flow.

About this task

Procedure

To create the PRODUCT_AFFINITY target table and add a Table Target operator to your flow:

Procedure

  1. Create a table that is suitable for your association rules.
    1. Right-click the rule port of your Associations Extractor operator and click Create Suitable Table.
    2. In the Create Suitable Table window, specify the following settings.
      Option Value
      Database / data model Accept the default value.
      Table name PRODUCT_AFFINITY
      Table schema GOSALESCT
      Table space Accept the default value.
      Automatically create and connect to target operator Select this option.
    3. Click Finish.
    Design Studio creates a PRODUCT_AFFINITY table in the GOSALESCT schema, and creates a corresponding Table Target operator.
  2. Optional: Examine the GOSALESCT.PRODUCT_AFFINITY table.
    • Right-click the PRODUCT_AFFINITY Table Target operator and click Run to this step.
    In the SQL Results view, you can see that the GOSALESCT.PRODUCT_AFFINITY table has fields for the elements and quality metrics of association rules.
  3. Configure your new Table Target operator:
    • Double-click the PRODUCT_AFFINITY Table Target operator and complete the wizard.
      Page Steps
      General Select Delete previous content.

What to do next

Next you will complete similar steps to extract the rule bodies.

Creating a target table and adding a Table Target operator for rule bodies

You must create a table to store your association rule bodies, and add a corresponding Table Target operator to your flow.

About this task

Procedure

To create the PRODUCT_AFFINITY_BODY target table and add a Table Target operator to your flow:

Procedure

  1. Create a table that is suitable for your association rule bodies.
    1. Right-click the rulebody port of your Associations Extractor operator and click Create Suitable Table.
    2. In the Create Suitable Table window, specify the following settings.
      Option Value
      Database / data model Accept the default value.
      Table name PRODUCT_AFFINITY_BODY
      Table schema GOSALESCT
      Table space Accept the default value.
      Automatically create and connect to target operator Select this option.
    3. Click Finish.
    Design Studio creates a PRODUCT_AFFINITY_BODY table in the GOSALESCT schema, and creates a corresponding Table Target operator.
  2. Optional: Examine the GOSALESCT.PRODUCT_AFFINITY_BODY table.
    • Right-click the PRODUCT_AFFINITY_BODY Table Target operator and click Run to this step.
    In the SQL Results view, you can see that the GOSALESCT.PRODUCT_AFFINITY_BODY table has fields that map items to rule bodies.
  3. Configure your new Table Target operator:
    • Double-click the PRODUCT_AFFINITY_BODY Table Target operator and complete the wizard.
      Page Steps
      General Select Delete previous content.
  4. Save your mining flow:
    • In the menu bar, click File > Save.

What to do next

Now your mining flow will update the GOSALESCT.PRODUCT_AFFINITY and GOSALESCT.PRODUCT_AFFINITY_BODY tables when the flow runs.

What's next?

In the next lesson, you will design a control flow that runs your mining flow.