Data preparation
- 3 minutes to read
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.
Download the AI Builder sample datasets solution: AIBuilderOnlineShopperIntention_1_0_0_0.zip.
In Power Apps, select Solutions in the left pane, and then select Import at the top of the screen.
In the pop-up window, select Choose File, and then select AIBuilderOnlineShopperIntention_1_0_0_0.zip, which you downloaded in step 1.
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.
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.
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
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.
Set the following properties:
- On-premises data gateway = (none)
- Authentication kind = Anonymous
Then select Next.
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.
Select the Delete rows that no longer exist in the query output check box.
Select the Automap function in the upper-right corner of the Field-mapping screen, and then select Next.
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.
In Power Apps, go back to Entities under Data, and select Online Shopper Intention.
Select Views, and then select Active Online Shopper Intention.
Add fields on the left side to validate that all the fields have been imported correctly.
Select Publish to save the current view with the selected fields.
And you're done!
Example dataset for predicting multiple outcomes
In the list of AI Builder samples, download the AI Builder sample datasets solution: BrazilianCommerce_1_0_0_4_managed.zip
In Power Apps, select Solutions in the left pane, and then select Import at the top of the screen.
In the pop-up window, select Choose File, and then select BrazilianCommerce_1_0_0_4_managed.zip, which you downloaded in step 1.
Follow the on-screen instructions to import the solution, and then select Close after you finish.
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.
Select Settings, and then select Data Management.
Select IMPORT DATA from the top menu bar.
In the Data file name section, select customer.csv, and then select Next.
Select Next until you get to the Map Record Types screen.
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 Select Next, select Submit, and then select Finish.
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.
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.
And you're done!