Documentation

SkuBrain 101

Importing Data from Halo Source

If you are a Halo Source user, populating your data into SkuBrain gets even easier. Login to SkuBrain and go to the Halo Source Integration page. Take note of your IP address, as shown here:

Email the SkuBrain Support Team at support@skubrain.com and request permission for this IP address to access the SkuBrain Staging area in Azure SQL. (NOTE: If you plan to run Halo Source behind a different external IP address, you will need to request for that IP address to be opened as well).

Download the Halo Source Template

The pre-built Halo Source Template for SkuBrain is available here.

Note: If you have Halo 16.1 Service Pack 2 or above, use HaloSkuBrainConnector-16.1SP2.iqp. Otherwise, use HaloSkuBrainConnector-15.1.iqp.

Please note that to proceed, you will need to have your SkuBrain Staging Area details handy. These will normally be sent to you in the SkuBrain Welcome Email. If you don’t have this information, please contact support@skubrain.com. If you do, then open the Halo Source template and make the following changes:

Edit GUID Parameter in Project tab and change value to the one provided in SkuBrain Welcome Email.

Configure data connections for Source, Staging Area and SkuBrain to match your environment. For the SkuBrain connection, use the username and password sent to you in the Welcome Email.

Note: If using the 15.1 template, you will see the Azure SQL Data connector instead of the SkuBrain connector.

Extracting and Transforming Data

In the Extract Data phase (if applicable):

  • Configure the Extract Data ETL Package to extract sales order data from Source to staging area
  • Extract the Extract Data ETL Package

In the Transform Staging phase, Transform Data ETL Package, edit SQL table ${SourceSalesTable}.

Enter SQL query to transform extracted sales order data so the SQL table contains 6 fields required by SkuBrain (check the Importing Data from SQL Azure page for further details):

Column Name Data Type Example
OrderReference nvarchar|varchar AZ-2354
LineReference nvarchar|varchar 323676-1
OrderDate datetime 24/10/2012
SKU nvarchar|varchar CH-0234
Quantity int32 5
UnitPrice double 10.99

SkuBrain also supports the following recommended fields. The fields can contain null values but need to be included in the sales order SQL query above.

Column Name Data Type Example
Category nvarchar|varchar Seasonal
Brand nvarchar|varchar Pale-Ale
DiscountAmount double 2.45
UnitCost double 5.45

Edit SQL table ${SourceStockTable}.

Enter SQL query to generate stock availability data. The table should contain the following fields:

Column Name Data Type Example
SKU nvarchar|varchar AZ-2354
Available double 5
UnitCost double 5.45
UnitPrice double 10.99
LeadTime int32 28

Run Transform Staging workflow.

Refresh Sales${GUID}_incr & Stock_${GUID}incr metadata in Upload Data to Azure ETL Package to ensure table metadata is up to date.

Notes

  • Primary Key index is currently set to LineReference and SKU fields for ${SourceSalesTable} and ${SourceStockTable} respectively – you may choose to modify this if needs be.
  • Column chosen as the Primary Key index must not be nullable – enter a Default Value for the column to ensure this is the case.

Uploading Data to Azure

Run Upload to Azure workflow. This will push your sales and stock data to Azure.