3.2.3. Create Dimensions

For the dimensions, we will use Products and Customers. Each product refers to some product line, that is the type of product, e.g. Ford T belongs to the Vintage Cars product line.

Customers belong to certain cities, they, in turn, refer to some countries, the countries are grouped into territories.

  1. First, create the Product transformation. Drag and drop the Table input node onto the worksheet and define the fields we need for the report: product id, name and product_line_id.

    star schema 2
    Figure 3. Product table input

  2. Then create an Insert/Update node for products:

    star schema 3
    Figure 4. Insert/Update Product

  3. Create the transformation for product lines:

    star schema 4
    Figure 5. Product transformation

  4. Finalize the first transformation with the Update node:

    star schema 5
    Figure 6. Update Product

  5. Create the Customer transformation in the same way, including City and Territory levels, and add it to the Product one:

    star schema 6
    Figure 7. Customer transformation

  6. When the transformation is ready, wrap it in the corresponding job, using the START and Success nodes and the Abort job exit node in case of an error:

    star schema 12
    Figure 8. Dimensions job