Define the XML File Structure

Owned by Natalie Gesin (Deactivated)
Last updated: Aug 09, 2024 by Pamela Totas (Deactivated)
9 min read

Create Vendors

  1. Open IWS and select "Templates" tab in "Open" window. Select "Raw Service" item and click OK.

The Raw Service tool is opened.

  1. Select a vendor. You can create a new vendor or choose the existing vendor:

    • 1 – This is button for creating a new Vendor in current region (also you can use the button "Add New" from the ribbon 

    • 2 – This is list of existing items (all existing vendors in current region will display. In this example it's empty because on thecurrent region has no vendors).

    • 3 – This is ribbon tab with some actions for current vendor:

      1. Add New - creates new vendor.

      2. Publish – uploads created vendor to current region.

      3. Run – run the Data Load process (after Publish only) from IWS.

      4. Preview – shows profile data for current vendor. 

     

  2. To create a new Vendor, click Add New or New Vendor / Load Sample. Select the "XML" tab:

    • 1 – is vendor Name (required field).

    • 2 – is feed Name for this vendor (required field).

    • 3 – some description for current vendor and feed.

    • 4 – is view a common settings, data structure or dimension table settings.

    • 5 – is section of data format (at this moment, is available CSV and XML format).

    • 6 – is view a data or a name of file ("Data" shows the settings for data in the file, "File Name specific" shows the settings for parsing the name of file).

    • 7 – is view of this data ("Sample data" shows the data in grid (parsed by settings). "Raw data" shows the data as simple text format). You can add, remove and change any data in both tabs.

    • 8– is a button "Load from File" for creating the structure.

    • 9 – is a grid with data ("Data Preview" tab).

    • 10 – is a button for creating vendor and feed by set settings.

    • 11 – is a button for cancel process of creating new vendor

  3. Save to file the following data with name of file 'XML_test.xml':

    <AllDataReport>   <TopLevelVolatilityRows>     <TopLevelVolatilityRow TotalVolatility="0.0636788" SystematicVolatility="0.0256774" SpecificVolatility="0.000478844" TaR="0.167854" SystematicPercentageVol="0.99664333" SpecificPercentageVol="0.00743222" CompositionType="Portfolio" Id="4" />     <TopLevelVolatilityRow TotalVolatility="0.0644788543" SystematicVolatility="0.083223457" SpecificVolatility="2.56785367853367E-05" TaR="0.78854357888" SystematicPercentageVol="0.999764456666" SpecificPercentageVol="0.0004356788765" CompositionType="Benchmark" Id="5" />     <TopLevelVolatilityRow TotalVolatility="0.00763355677" SystematicVolatility="0.0076445533" SpecificVolatility="0.0066433455" TaR="0.00652244566" SystematicPercentageVol="0.45788744666" SpecificPercentageVol="0.5478954436677" CompositionType="Active" Id="6" />     <TopLevelVolatilityRow TotalVolatility="0.00763355677" SystematicVolatility="0.0076445533" SpecificVolatility="0.0066433455" TaR="0.00652244566" SystematicPercentageVol="0.45788744666" SpecificPercentageVol="0.5478954436677" CompositionType="Active" Id="7" />     <TopLevelVolatilityRow TotalVolatility="0.00763355677" SystematicVolatility="0.0076445533" SpecificVolatility="0.0066433455" TaR="0.00652244566" SystematicPercentageVol="0.45788744666" SpecificPercentageVol="0.5478954436677" CompositionType="Active" Id="8" />     <TopLevelVolatilityRow TotalVolatility="0.00763355677" SystematicVolatility="0.0076445533" SpecificVolatility="0.0066433455" TaR="0.00652244566" SystematicPercentageVol="0.45788744666" SpecificPercentageVol="0.5478954436677" CompositionType="Active" Id="9" />   </TopLevelVolatilityRows>   <TopLevelBsaParallelShockRows>     <TopLevelBSAParallelShockRow Shock="100" PortfolioTotalReturn="0" ActiveTotalReturn="0.0006598542" BenchmarkTotalReturn="-0.000214584" Currency="USD" Id="4" />     <TopLevelBSAParallelShockRow Shock="100" PortfolioTotalReturn="-0.052698446" ActiveTotalReturn="-0.002115584565" BenchmarkTotalReturn="-0.05625845236" Currency="CAD" Id="5" />     <TopLevelBSAParallelShockRow Shock="100" PortfolioTotalReturn="0" ActiveTotalReturn="0" BenchmarkTotalReturn="0" Currency="EUR" Id="6" />     <TopLevelBSAParallelShockRow Shock="100" PortfolioTotalReturn="0.0535" ActiveTotalReturn="0.024675" BenchmarkTotalReturn="0.0018445" Currency="NZD" Id="7" />     <TopLevelBSAParallelShockRow Shock="100" PortfolioTotalReturn="0" ActiveTotalReturn="0" BenchmarkTotalReturn="0" Currency="AUD" Id="8" />     <TopLevelBSAParallelShockRow Shock="100" PortfolioTotalReturn="0.0024567" ActiveTotalReturn="0.003677" BenchmarkTotalReturn="0.002467" Currency="DKK" Id="9" />   </TopLevelBsaParallelShockRows> </AllDataReport>

    This XML example contains:
    - the root node AllDataReport
    - 2 models – complex nodes: TopLevelVolatilityRows, TopLevelBsaParallelShockRows
    - records – repeated nodes: TopLevelVolatilityRow, TopLevelBSAParallelShockRow

  4. Click to "Load from File" and select 'XML_test.xml'.
    The data will be displayed in the grid.
    The Models and Records are resolved automatically.
    The grid contains the following columns:
    Node Name – the name of node in the current XML, it could be XSD node or XSD attribute
    Alias – the alias for node to use as column name.
    XPath – the XPath of nodes or attributes.
    Model – ticked complex nodes with repeated child node(s). Every model is a separate set of data. Every set of data is loaded by the separate process with own Batch Id. The separate Profile is created for every Model.
    Record – repeated node(s) within the complex node (Model). All XSD attributes and child XSD nodes for Record will be Table Columns.

     

  5. Review the models and 'XML_test.xml'.
    Model 1: TopLevelVolatilityRows
    Repeated Record: TopLevelVolatilityRow
    Columns of View / Dimension table: TotalVolatility, SystematicVolatility, SpecificVolatility, TaR, SystematicPercentageVol, SpecificPercentageVol, CompositionType, Id
    Model 2: TopLevelBsaParallelShockRows
    Repeated Record: TopLevelBSAParallelShockRow
    Columns of View / Dimension table: Shock, PortfolioTotalReturn, ActiveTotalReturn, Currency, Id
    NOTE: if you will not tick the model node, this model will not be added to load process.

  6. Tab "Raw data' shows the full XML file.

  7. You can add the parsed file name to the load process. Click to 'File Name specific' tab.
    The name of file is displayed. Specify the delimiter – '_'. The new columns – 'FileName_Param_1' and 'FileName_Param_2'.

  8. You can change the names here or after the structure creating.
    Example: Set the file name field or select file from local drive:

  9. Set "value separator". In this case it is of "_". After those actions the preview grid will add parameters from the file name. You can set name for each parameter in column "Alias".You can set this name in "Structure" tab as well.

    This is global vars and those vars will be added to each selected models.

  10. Check the database:
    Data is loaded into the following DB tables in this task - check the usual columns only, these columns have the value of SOURCE_COL_NUM < 0:
    DM2_MASTER_COL_TBL, SQL query for check:
    select * from DM2DBO.DM2_LOAD_COL_TBL
    where BATCH_ID = YOUR_BATCH_ID and SOURCE_COL_NUM < 0
    DM2_LOAD_COL_TBL, SQL query for check:
    select * from DM2DBO.DM2_LOAD_COL_TBL
    where BATCH_ID = YOUR_BATCH_ID and SOURCE_COL_NUM < 0

    DM2_MASTER_COL_VAL_TBL, SQL query for check:
    select * from DM2DBO.DM2_MASTER_COL_VAL_TBL
    where COL_ID in
    (select COL_ID from DM2_LOAD_COL_TBL where BATCH_ID = YOUR_BATCH_ID and SOURCE_COL_NUM < 0) 
    DM2_LOAD_CH_TBL, SQL query for check:
    select * from DM2DBO.DM2_LOAD_CH_TBL
    where COL_ID in
    (select COL_ID from DM2_LOAD_COL_TBL where BATCH_ID = YOUR_BATCH_ID and SOURCE_COL_NUM < 0)
    DM2_LOAD_NM_TBL – no numeric values in global columns
    DM2_LOAD_DT_TBL, SQL query for check:
    select * from DM2DBO.DM2_LOAD_DT_TBL
    where COL_ID in
    (select COL_ID from DM2_LOAD_COL_TBL where BATCH_ID = YOUR_BATCH_ID and SOURCE_COL_NUM < 0)

  11. You can control tabs "Structure" and "Dimensions".
    A "Structure" tab displays names, types, formats of columns, and key fields of dimension table – you can change it now or after a structure creating. In the documentation, the structure will be corrected and described later.

    You can see that Data Type for root node is 'MAINMODEL', for Models nodes is 'MODEL', for Records nodes is 'RECORD'.
    A "Dimensions" tab contains the name of Dimension table – it also will be added after a structure creating (but you can enter the name of dimension table just now).
    You can enter the name for every Model (1 model = 1 load process)

    Add Vendor name = 'TESTVENDOR' and Feedname = 'TESTXML'

  12. Click Create.
    After the vendor is created, you can look at settings and description of this feed and change this data if needed.

Review Settings and Feed Description

  1. Open "Structure Description" tab for check created structure of data for this feed:

    • Is the same description of this feed from "Vendor Feeds" tab;

    • Is Structure preview of the feed data;

    • Is columns.

  2. You can change the data type and data format in this tab.
    Fields:
    Key – is key fields for the data – needed for dimension table. If the Dimension table will be created, please select key fields for this table. If no fields have been selected as key fields, the Dimension table will not be created;
    Name – is name of field (generated by header line);
    Alias – is alias for this field;
    Example Value – is an example of column's value from XML file;
    Data Type – is type of data for this field – CHAR, NUMBER or DATE for now (new data types can be added in the future). Please change if the type is incorrect;
    Format – is format of data for this field, only for DATE type, for example – 'YYYYMMDD', 'YYYY-MM-DD', 'MM/DD/YYYY', etc. Please change if the format is incorrect for data. Should be null for all types exclude DATE;
    Enum – enumeration for this field;
    NA and Missing Data Handling – is value for missing data in this field;
    Description – is some description of this field.
    Selected – the data of selected column will be loaded if column ticks (will be realized in the future).

  3. You can tick the fields as Key: these fields will be the key fields in Dimension table(s). If the Dimension table should be created for one of models, tick the fields within this Model node. This option will be described below.

  4. Change the File Names columns here. You can delete these rows – right-click to row and select "Delete Row".

  5. You can add the alias for models.
    For example, add alias 'model_1' for the model TopLevelVolatilityRows and 'model_2' for the model TopLevelBsaParallelShockRows:

    The specified aliases will be used in the names of load processes instead of model's names.

Create Dimension Tables

  1. Open the tab "Dimensions". You can specify the name of dimension table(s) in the field "Table Name for model …".

  2. Enter the names for both models:
    "DM2D_VOLTST" for model 1 and "DM2D_SHCKTST" for model 2:


    NOTE: The name of Dimension table must begin from 'DM2D_' (this prefix will be added if you enter the name without 'DM2D_') and the length of the name (without 'DM2D_' prefix) should be <= 20 symbols.

  3. Open again the tab "Structure" and tick the Key fields for every Model.
    For example, tick the fields as key CompositeType and Id for the Model TopLevelVolatilityRows:

    And tick the fields as key Currency and Id for the Model TopLevelBsaParallelShockRows:

  4. Click "Publish" for uploading this vendor and feed to current region.
    Example of Profile which will be published on the region:

    NAME:DELIVERYFORMAT:TYPE:PARAMETER:VALUE:CSV:DELIMITER:PIPE: NAME:MODEL_NAME:TYPE:PARAMETER:VALUE:TESTVENDOR_TESTXML_TopLevelVolatilityRows: NAME:TABLE_NAME:TYPE:PARAMETER:VALUE:DM2D_VOLTST: NAME:KEY_FIELDS:TYPE:PARAMETER:VALUE:"CompositionType","Id": NAME:TotalVolatility:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:1:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@TotalVolatility: NAME:SystematicVolatility:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:2:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@SystematicVolatility: NAME:SpecificVolatility:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:3:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@SpecificVolatility: NAME:TaR:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:4:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@TaR: NAME:SystematicPercentageVol:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:5:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@SystematicPercentageVol: NAME:SpecificPercentageVol:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:6:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@SpecificPercentageVol: NAME:CompositionType:TYPE:COLUMN:DATATYPE:CHAR:DATAFORMAT::SELECTED:Y:VIEWIDX:7:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@CompositionType: NAME:Id:TYPE:COLUMN:DATATYPE:NUMBER:DATAFORMAT::SELECTED:Y:VIEWIDX:8:NODETYPE:ATTR:XPATH:./TopLevelVolatilityRows/TopLevelVolatilityRow/@Id:

    Rows and attributes in profile -
    For TYPE='PARAMETER':
    DELIVERYFORMAT is the format of data file, for CSV the DELIMITER is specified (for now the XML files is converted to CSV with PIPE delimiter)
    MODEL_NAME in the name of structure. This is the concatenation of Vendor name + '' + Feedname + '' + name of Model node.
    NOTE: the Model Name will be transferred to the upper case.
    TABLE_NAME is the name of Dimension table. If the name is not specified, the dimension table will not be created.
    NOTE: the Table Name will be transferred to the upper case.
    KEY_FIELDS is the list of keys for Dimension table. If null, the dimension table will not be created.
    For TYPE='COLUMN'
    Every row describes one simple column.
    Attributes for TYPE = 'COLUMN'
    NAME – name of column
    DATATYPE – type of column: CHAR, NUMBER, DATE or type of node: MAINMODEL (root node), MODEL (model node), RECORD (record node)
    DATAFORMAT – format for DATE columns: for example, 'YYYYMMDD'
    VIEWIDX – View Index, for a future functionality using creating DB View. This is a number of fields in the sequence of fields in the extract from DB View (created by Data Load process, for example, column 'RUNDATE' is first, 'REPLYFILENAME' is second, etc. in a extract). A View will be created only if VIEWIDX will not be null for one or more columns. If all columns will not contain VIEWIDX, a DB view will not be created.
    NOTE: This functionality is not currently added to the Process.  It will be added in a future release. Also, please note that the View Indexes are currently assigned automatically.

    NODETYPE – shows is the columns a child XSD node or an XSD attribute
    XPATH – Xpath for XSD node / arrtibute

  5. If the profiles are correct, click "Publish" in bottom area. Both profiles will be saved to the APP directory /dynamic/msgcenter/profile/inbound/.
    The name of profile is Vendor name + '' + Feed name + '' + Model name or alias (if alias specified, it will be used instead of name of model). For example, for this data file the file will be saved as 'TESTVENDOR_TESTXML_TopLevelVolatilityRows.txt'.