Example of the RTR to generate dataset and mashup file via EDS fieldatrribute extract is similar to EagleML extract. This extract contains meta information about Eagle fields like field type, name, database table, description and etc. This implementation links two metadata storages - MDC which is the Eagle metadata storage and Ontology, EDS metadata storage.
As a result, the data set is enriched with additional details. Core, custom and derived fields are supported.
Please use eagle_ml-2-0_extractdefault_servicecm_controlexec_message stream
...
eds stream to run EDS fieldattribute extract:
| Expand | ||
|---|---|---|
|
EQL REST API is supported, for example: https://o171-e001-ww01.eagleinvsys.com/
...
eagle/v2/eql
| Code Block |
|---|
{
"ResourceName": "fieldattribute",
"ServiceName": "EBS",
"ServiceMethodName": "extract",
"savesqlquery": "Y"
}
|
The following filters are supported:
fieldattributeidfieldtypefieldname
| Code Block |
|---|
<taskParameter> <name>fieldattributeid</name> <dataType>S</dataType> <value>264</value> </taskParameter> <taskParameter> <name>ServiceMethodName<<name>fieldtype</name> <dataType>S</dataType> <value>ontologymetadata<<value>fieldtype_VALUE</value> </taskParameter> <taskParameter> <name>ResourceName<<name>fieldname</name> <value>ALL<<dataType>S</value>dataType> </taskParameter> <value>fieldname_value</value> </taskParameters> </EagleML> |
Generated dataset (tpe\dynamic\metadata\core\datasets\ontology_metadata.json):
| Code Block |
|---|
{
"_id": "ontology_metadata",
"dataframes": [
{
"_id": "ontology_metadata",
"type": "dftFile",
"group_name": "Ontology",
"description": "Ontology metadata",
"maxrows": 9999999,
"source_sink": {
"sink_type": "ssFileType",
"sink_params": {
"file_name": "estar/tpe/dynamic/metadata/core/ontology/templates/model_ALL.csv",
"mode": "r"
},
"source_format_dialect": {
"lineterminator": "\n",
"delimiter": "|~",
"headerlines": 1,
"headerat": 1,
"dialectname": "simplesplit"
}
},
"format": "csv",
"vocabulary": {
"FIELD_PATH": "FIELD_PATH",
"FIELD_DEFINITION": "FIELD_DEFINITION"
},
"unique_key": [
{
"field": "FIELD_PATH",
"expression": "|FIELD_PATH|"
}
],
"source_query_policy": {
"cache_mode": "none",
"method": "file",
"base_template": "",
"full": {
"filter_params": {
"filter_definitions": {}
}
},
"filter_params": {
"filter_definitions": {}
}
},
"cache_policy": {
"type": "aside",
"cache_provider": "cache-manager-file",
"storage_method": "keyvalue",
"compression_codec": "N",
"encryption_codec": "N",
"format_dialect": "mashup_csv_dialect",
"ttl_delta": 0,
"ttl_unique": 60
}
}
]
}
|
The mashup file (model_ALL.csv) contains two columns FIELD_PATH and FIELD_DEFINITION. This file is located: estar/tpe/dynamic/metadata/core/ontology/templates/model_ALL.csv
These definitions are ontology definitions from the folder(tpe\dynamic\metadata\core\ontology\definition) for canonical elements from the corresponding files:
...
The Field attribute extract uses the following query to pull data from Eagle environment:
SELECT RTRIM(MASTERTABLE.CALC_TYPE) CALC_TYPE,
RTRIM(MASTERTABLE.COMMENTS) COMMENTS,
RTRIM(MASTERTABLE.DATABASE_NAME) DATABASE_NAME,
TO_CHAR(MASTERTABLE.DENOMINTOR_ID) DENOMINTOR_ID,
RTRIM(MASTERTABLE.FIELD_ATTRIBUTE_DESCRIPTION) FIELD_ATTRIBUTE_DESCRIPTION,
TO_CHAR(MASTERTABLE.FIELD_ATTRIBUTE_ID) FIELD_ATTRIBUTE_ID,
TO_CHAR(MASTERTABLE.FIELD_ATTRIBUTE_PROCESS_ID) FIELD_ATTRIBUTE_PROCESS_ID,
RTRIM(MASTERTABLE.FIELD_FORMAT) FIELD_FORMAT,
RTRIM(MASTERTABLE.FIELD_INDICATOR) FIELD_INDICATOR,
RTRIM(MASTERTABLE.FIELD_NAME) FIELD_NAME,
RTRIM(MASTERTABLE.FIELD_TYPE) FIELD_TYPE,
TO_CHAR(MASTERTABLE.NUMERATOR_ID) NUMERATOR_ID,
TO_CHAR(MASTERTABLE.NUMERATOR_ID2) NUMERATOR_ID2,
TO_CHAR(MASTERTABLE.REF_CODE_ID) REF_CODE_ID,
TO_CHAR(MASTERTABLE.SRC_INTFC_INST) SRC_INTFC_INST,
RTRIM(MASTERTABLE.STAT_FIELD) STAT_FIELD,
RTRIM(MASTERTABLE.TABLE_NAME) TABLE_NAME,
CASE WHEN MASTERTABLE.UPDATE_DATE IS NOT NULL THEN TO_CHAR(CAST(MASTERTABLE.UPDATE_DATE AS TIMESTAMP WITH TIME ZONE), 'YYYY-MM-DD') || 'T' || TO_CHAR(CAST(MASTERTABLE.UPDATE_DATE AS TIMESTAMP WITH TIME ZONE), 'HH24:MI:SSTZH:TZM') ELSE NULL END UPDATE_DATE,
RTRIM(MASTERTABLE.UPDATE_SOURCE) UPDATE_SOURCE,
FIELD_ATTRIBUTE_DETAIL.ATTRIB_USER_DATA ATTRIB_USER_DATA,
RTRIM(FIELD_ATTRIBUTE_DETAIL.CURRENCY_PROCESS) CURRENCY_PROCESS,
RTRIM(FIELD_ATTRIBUTE_DETAIL.FIELD_CATEGORY) FIELD_CATEGORY,
RTRIM(FIELD_ATTRIBUTE_DETAIL.FIELD_IDENTIFIER) FIELD_IDENTIFIER,
RTRIM(FIELD_ATTRIBUTE_DETAIL.FIELD_VALUE_TYPE) FIELD_VALUE_TYPE,
RTRIM(FIELD_ATTRIBUTE_DETAIL.READ_ONLY) READ_ONLY,
TO_CHAR(FIELD_ATTRIBUTE_DETAIL.SCOPE) SCOPE,
RTRIM(FIELD_ATTRIBUTE_PROCESS.DATABASE_NAME) DATABASE_NAME_1,
TO_CHAR(FIELD_ATTRIBUTE_PROCESS.FIELD_ATTRIBUTE_PROCESS_ID) FIELD_ATTRIBUTE_PROCESS_ID_1,
RTRIM(FIELD_ATTRIBUTE_PROCESS.PARAMETER_LIST) PARAMETER_LIST,
RTRIM(FIELD_ATTRIBUTE_PROCESS.PROCESS_DESCRIPTION) PROCESS_DESCRIPTION,
RTRIM(FIELD_ATTRIBUTE_PROCESS.PROCESS_TYPE) PROCESS_TYPE,
RTRIM(FIELD_ATTRIBUTE_PROCESS.STORED_PROCEDURE_NAME) STORED_PROCEDURE_NAME,
RTRIM(FIELD_PROCESS_DEFINITION.PARAMETER_TYPE) PARAMETER_TYPE,
RTRIM(FIELD_PROCESS_DEFINITION.PARAMETER_VALUE) PARAMETER_VALUE,
TO_CHAR(FIELD_PROCESS_DEFINITION.SEQ_NO) SEQ_NO,
FIELD_PROCESS_DEFINITION.USER_DATA USER_DATA
FROM RULESDBO.FIELD_ATTRIBUTES MASTERTABLE
LEFT JOIN RULESDBO.FIELD_ATTRIBUTE_DETAIL FIELD_ATTRIBUTE_DETAIL ON MASTERTABLE.FIELD_ATTRIBUTE_ID = FIELD_ATTRIBUTE_DETAIL.FIELD_ATTRIBUTE_ID
LEFT JOIN RULESDBO.FIELD_ATTRIBUTE_PROCESS FIELD_ATTRIBUTE_PROCESS ON MASTERTABLE.FIELD_ATTRIBUTE_PROCESS_ID = FIELD_ATTRIBUTE_PROCESS.FIELD_ATTRIBUTE_PROCESS_ID
LEFT JOIN RULESDBO.FIELD_PROCESS_DEFINITION FIELD_PROCESS_DEFINITION ON MASTERTABLE.FIELD_ATTRIBUTE_ID = FIELD_PROCESS_DEFINITION.FIELD_ATTRIBUTE_ID and FIELD_PROCESS_DEFINITION.USER_DATA is not NULL
WHERE (1=1)
Output record sample:
taskParameter> |
The processing rule for EDS fieldattribute extract was improved to support new element fieldAttributeOntologyDescription for all fields with direct or simple mapping.
| Code Block |
|---|
<referenceTransaction> <header> <objectType>FieldAttribute</objectType> </header> <fieldAttribute> <calcType>di</calcType> <databaseName>TRADES<<databaseName>cash</databaseName> <fieldAttributeDescription>i TradeLocal Amount<Currency</fieldAttributeDescription> <fieldAttributeId>153<<fieldAttributeId>138</fieldAttributeId> <fieldIndicator>t<<fieldIndicator>cf</fieldIndicator> <fieldName>TRADE<fieldName>LOCAL_AMOUNT<CURRENCY</fieldName> <fieldType>num</fieldType> <tableName>TRADE</tableName> <fieldFormat>2</fieldFormat><tableName>CASH_ACTIVITY</tableName> <fieldIdentifier>I TRADELOCAL AMOUNT<CURRENCY</fieldIdentifier> <scope>2</scope> <readOnly>Y</readOnly> <fieldAttributeOntologyDescription>The trade amount associated with this disposal lot. The proceeds of the close event<fieldAttributeOntologyDescription>Local currency. Three-letter currency code.</fieldAttributeOntologyDescription> </fieldAttribute> </referenceTransaction> |
Value in mashup file:
model_ALL.csv
TRADE.TRADE_AMOUNT|~The trade amount associated with this disposal lot. The proceeds of the close event.
| View file | ||
|---|---|---|
|
Changes in processing rule for fieldattribute.
...
Now it contains logic to perform mashup, this logic is generated based on ontology. Value for new field is populated from mashup file generated based on ontology elements definition.
| Code Block |
|---|
"register_for_mashups": [
{
"_alias": "ontology_metadata",
"dataset": "ontology_metadata",
"dataframe": "ontology_metadata",
"keyexpr": "|FIELD_PATH|",
"columns": "*"
}
],
"mashups": {
"ontology_metadata": "select first FIELD_DEFINITION from ontology_metadata where (|mainds.TABLE_NAME| + '.' + |mainds.FIELD_NAME|)=(|FIELD_PATH|)"
}
|
...
| Info |
|---|
This field will be part of the EDS extract by default. In order to create a mashup file the manual step is required. Described here |
A new logic was implemented for derived or calculated fields. Now all user defined expressions for the calculated fields will be part of the extract. Two additional elements are part of the extract:
fieldExpression element includes user friendly expression available in MDC
userData element contains RAW definition of the value stored in the database
New fields are included by default, but they can be excluded by setting RTR parameter includederivedattributes to N.
| Code Block |
|---|
<referenceTransaction>
<header>
<objectType>FieldAttribute</objectType>
</header>
<fieldAttribute>
<statField>Y</statField>
<fieldAttributeDescription>DC for subtraction</fieldAttributeDescription>
<fieldAttributeId>1429</fieldAttributeId>
<fieldAttributeProcessId>1014</fieldAttributeProcessId>
<fieldIndicator>DE</fieldIndicator>
<fieldType>NUM</fieldType>
<fieldFormat>2</fieldFormat>
<fieldCategory><none></fieldCategory>
<currencyProcess>NOT</currencyProcess>
<fieldValueType>NOT</fieldValueType>
<fieldIdentifier>DETAIL CAL FOR SUBTRACTION - SUPERUSERS</fieldIdentifier>
<scope>2</scope>
<readOnly>N</readOnly>
<processDescription>Expression Calculator</processDescription>
<processType>USERDEFINED</processType>
<fieldExpression>H-PD-Market_Value_Income - H-PD-Local_Market_Value</fieldExpression>
<userData>#`FP1014~#`FD1170~-~#`FD1155~</userData>
</fieldAttribute>
</referenceTransaction>
|
| Info |
|---|
The initial configuration steps are required. Described here |
This functionality is limited for the calculated fields below with field_attribute_process_id:
1010 Arithmetic Addition Calculator
1011 Arithmetic Subtraction Calculator
1012 Arithmetic Division Calculator
1013 Arithmetic Multiplication Calculator
1014 Expression Calculator
1015 Adjustments Calculator
1025 Trade Adjustment Calculator
1030 IRR Calculator
1031 Modified Dietz Calculator
1032 Dollar Value Added Calculator
1033 Average Invested Balance Calculator
Example extract is attached.
| View file | ||
|---|---|---|
|