Data preparation

Before you create your prediction model, you'll want to make sure your data is in Common Data Service and that it's in the correct format.

Create your custom entity

Do you have data that you want to import into Common Data Service for training in AI Builder? First, you have to create an entity. In this example, we'll provide a solution that has predefined custom entities. To use your own data, create a custom entity and substitute your own entity for the example used here.

Note

For best results, use a dataset that is less than 1.5 GB in size. Otherwise, AI Builder uses only 1.5 GB of your data to train and predict. Since you can’t control which data exceeding the 1.5 GB limit is not used, you should optimize your data to stay under 1.5 GB.

Example dataset for binary prediction and numerical prediction

Use the following dataset if you want to predict true/false outcomes, or for numerical prediction.

  1. Download the AI Builder sample datasets solution: AIBuilderOnlineShopperIntention_1_0_0_0.zip.

  2. In Power Apps, select Solutions in the left pane, and then select Import at the top of the screen.

  3. In the pop-up window, select Choose File, and then select AIBuilderOnlineShopperIntention_1_0_0_0.zip, which you downloaded in step 1.

  4. Follow the on-screen instructions to import the solution, and then select Close after you finish.

Next, import the sample data into the entity. In this example, we use the aib_onlineshopperintention.csv file.

  1. In the list of AI Builder samples, select the aib_onlineshopperintention.csv file, and then select Download to open the raw version of the file.

  2. Copy the URL from the address bar in your browser. In this case, the URL to copy is: https://raw.githubusercontent.com/microsoft/PowerApps-Samples/master/ai-builder/aib_onlineshopperintention.csv

  3. In Power Apps, select Entities in the left pane, select Get data > Text/CSV, and then paste the copied URL from the last step into the File path or URL box.

  4. Set the following properties:

    • On-premises data gateway = (none)
    • Authentication kind = Anonymous

    Then select Next.

  5. On the Map entities screen, make sure Load to existing entity is selected, and under Destination entity, select aib_onlineshopperintention in the drop-down menu.

  6. Select the Delete rows that no longer exist in the query output check box.

  7. Select the Automap function in the upper-right corner of the Field-mapping screen, and then select Next.

  8. On the Refresh settings screen, select the Refresh manually check box, and then select Create to start the import process.

Allow some time for the import to be completed. Then make sure the data is imported correctly.

  1. In Power Apps, go back to Entities under Data, and select Online Shopper Intention.

  2. Select Views, and then select Active Online Shopper Intention.

  3. Add fields on the left side to validate that all the fields have been imported correctly.

  4. Select Publish to save the current view with the selected fields.

And you're done!

Example dataset for predicting multiple outcomes

  1. In the list of AI Builder samples, download the AI Builder sample datasets solution: BrazilianCommerce_1_0_0_4_managed.zip

  2. In Power Apps, select Solutions in the left pane, and then select Import at the top of the screen.

  3. In the pop-up window, select Choose File, and then select BrazilianCommerce_1_0_0_4_managed.zip, which you downloaded in step 1.

  4. Follow the on-screen instructions to import the solution, and then select Close after you finish.

  5. Download customer.csv, order.csv, and product.csv from AI Builder samples.

    After the solution is imported, select the gear icon in the upper-right corner of the Power Apps screen, and then select Advanced settings.

  6. Select Settings, and then select Data Management.

    Select Data management

  7. Select IMPORT DATA from the top menu bar.

  8. In the Data file name section, select customer.csv, and then select Next.

  9. Select Next until you get to the Map Record Types screen.

  10. Select BC Customer from the drop-down menu, and then select Next. Map the fields as shown in the following table.

    Source field Map to
    customer_id ID
    customer_city City
    customer_state State
    customer_zip_code_prefix Zip code
  11. Select Next, select Submit, and then select Finish.

  12. Repeat the process, but this time use product.csv and map it to BC Product. Map the fields as shown in the following table.

    Source field Map to
    product_id ID
    product_category_name Category
    product_description_lenght Description Length
    product_height_cm Height cm
    product_length_cm Length cm
    product_name_lenght Name Length
    product_photos_qty Photos Quantity
    product_weight_g Weight g
    product_width_cm Width cm

Wait until both of these imports are complete before moving on to the next step.

  1. Repeat the process, but this time use order.csv and map it to BC Order. Map the fields like this:

    Source field Map to
    order_id ID
    customer_id Customer (Lookup)
    freight_value Freight Value
    order_delivered_customer_date Delivered Date
    order_estimated_delivery_date Estimated Delivery Date
    order_purchase_timestamp Purchase Date
    order_status Order Status
    price Price
    product_id Product (Lookup)

In the Lookup reference dialog box, make sure that the check box is selected and that the field is ID.

Lookup reference dialog box'

And you're done!

Next step

Create a prediction model