Versions Compared

Old Version 9

changes.mady.by.user Natalie Gesin

Saved on

compared with

New Version 10

changes.mady.by.user Natalie Gesin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. Open IWS and select "Templates" tab in "Open" window. Select "RawService" item and click "OK":

    The Raw Service tool is opened.

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

    Fancy Bullets
    • 1 – This is button for creating new Vendor in current region (also you can use the button "Add New" from the ribbon (p.3).
    • 2 – This is list of existing items (there will show all existing vendors in current region. At this moment it's empty because on current region has not 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. 


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

    Fancy Bullets
    • 1 – is vendor Name (required field).
    • 2 – is feed Name for this vendor (also 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
    .


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

    Code Block
    <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

  5. 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.

    Fancy Bullets
    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.


  6. Review the models and 'XML_test.xml'.

    Fancy Bullets
    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.


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

  8. 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'.
  9. You can change the names here or after the structure creating.
    Example: Set the file name field or select file from local drive:
  10. Set "value separator". In our case it is of "_". After those actions in the preview grid will add parameters from file name. And 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.

  11. Check in 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)

  12. 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):


  13. Add Vendor name = 'TESTVENDOR' and Feedname = 'TESTXML':
  14. Click Create.
    After the vendor is created, you can look at settings and description of this feed and change this data if needed.

...

  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:

    Please noteNOTE: The  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:

    Code Block
    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.
    Please note 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.
    Please note 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.
    This functionality is not added to the Process for now, please note. This will be added in a future. Also please note: the View Indexes are assigned automatically for now.
    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'.