Extra info

Adding columns to an existing, non-custom, table is not allowed. Instead you can create a new “extra_info” table or in some cases you can use pre-existing extra info dataflows

Using a pre-existing extra info

We have pre-existing data flows for the following:

  • Locations
  • Vendors
  • Products

The product data flow is prehaps the most used and replaces the old “item extra info” dataflow. Here is a diagram showing the flow for both product item and product item sku extra info: inst To add a new column you only need to change 1 view and the staging framework will automatically add the columns in the tables: inst Note that the prep_cus tables are populated by using a view AND a procedure. The default procedure is a dummy that does nothing but if you wish you can add code into that procedure.

We don’t have pretty pictures for the other data flows but the concept is the same so it’s not hard to search for the appropriate staging elements.

Example: How to add an product_item_sku extra info column

  1. Alter the view prep_cus.v_product_item_extra_info and add your column. If you are populating the raw_cus.PRODUCT_SKU_EXTRA_INFO table you can use that as a source but if you are using Navision you will most likely be getting the information from a nav table such as nav.Items
  2. Optional: If you wish to see the changes immediately run the following 2 staging elements. They will automatically add the column to the destination tables.
EXEC [core].[execute_stg_element] @stg_element_name = N'prep_cus_product_item_sku_extra_info'
EXEC [core].[execute_stg_element] @stg_element_name = N'core_product_item_sku_extra_info_stg_to_prod'
  1. Optional: If you would like to add the new column as mbe column we suggest using the helper proc tb.add_mbe_column (in prod database), something like this:
EXEC tb.add_mbe_column 'core.product_item_sku_extra_info', 'my_supercool_column'

Note that the helper proc has other parameter, such as format and aggregation but if you leave those blank the proc will do it’s best to determine those based on the data type of the column

Creating your own extra info

If you wish to add columns where there is no standard data flow you can use the core.create_extra_info helper procedure

Example: For some strange reason we would like additional columns for the prep.product_assortment table.

EXEC core.create_extra_info
    @table = N'prep.product_assortment',
   	@create_mapping_view = 1,
	@create_mapping_procedure = 1

What this procedure will do is the following:

  • Create a table prep_cus.product_assortment_extra_info. The table will have only the primary key columns corresponding to the prep.product_assortment table.
  • Because the @create_mapping_view parameter is set to 1 it will do the following:
    • Create a dummy mapping view prep_cus.v_product_assortment_extra_info. This is a skeleton view for you to alter to add the new columns
    • Create a merge staging element for the above view/table combo
    • Place the staging element into the same group(s) as the element that populates the parent table (prep.product_assortment)
  • Because the @create_mapping_procedure parameter is set to 1 it will do the following:
    • Create a dummy mapping procedure prep_cus.product_assortment_extra_info_populate. This is a skeleton procedure for you to populate columns
    • Create a staging element for the above procedure
    • Place the staging element into the same group(s) as the element that populates the parent table (prep.product_assortment)
  • Additionally the following helper objects will be created:
    • A view joining the original prep.product_assortment and the new prep_cus.product_assortment_extra_info tables will be created. This view is called prep.v_product_assortment_joined_with_extra_info
    • A synonym prep.syn_product_assortment that points to the above mentioned view. The point of the view and synoym is to give you faster access to the new custom columns where you might need it. It should be as simple as replacing prep.product_assortment for prep.syn_product_assortment within procedures or views.

Notes:

  • The core.create_extra_info has additional parameters that will allow you to drop already created objects if you are not running this for the first time. It’s best to check the procedure itself for documentation on how to use those parameters.
  • The @create_mapping_view and @create_mapping_procedure parameters are optional. It is possible to use only one of them or if you choose you can skip both. If you skip both the source of the new extra info table will be the same as the source for the original table. This can be useful if creating extra info tables for e.g. erp/nav tables where the source should be the original erp table.
  • The staging elements created use the add_missing_columns_to_target option so the only thing needed to add a column is to add it to the source view and the staging framework will add it to the table the next time you run the element
  • The join view (prep.v_product_assortment_joined_with_extra_info) that joins the original table and the new extra info table is automatically refreshed when it detects the presense of new columns so you don’t need to worry about manually adding the columns to the view. Since the synonym points to this view the new columns will instantly be accessible if using the synonym.