Page Comparison

Using core ontology for ETL processes is the best way to work with Data Rules, but sometimes it is necessary to extend the core resource to support additional fields and custom data. In this case, the best practice is to create an extension of the Data Rules core resource which will contain all the necessary information about the new fields.

Supported Types of Extension Columns

Details below is required minimum to describe additional fields as an extension element:

...

owner - database SCHEMA
table_name - database TABLE
column_name - database COLUMN

Fields for an

...

Extension Table

When you create Data Rules extension which already has existing DB structure in market database, you have to mimic the same structure in Snowflake. Each element must be described to have the same table in Snowflake, as in below example:

...

https://consul.dev.az.eagleinvsys.com/ui/az-dev/kv/services/svc-eds/

Example:

Code Block
"vocabulary":{ "newElementChar": { "path": "extension/newElementChar", "datatype": "string", "formatdialect": { "length": 16 } }, ... }

Fields for

...

Summary Extension Tables

Fields for summary extension tables can be created if core resource has summary extension table
(e.g. warehouseposition).

All these fields are provided in summary_vocabulary block.

Example when you have predefined DB structure:

Code Block

"vocabulary":{
    "newField": {
		"path": "extension/positionDetailId",
		"datatype": "integer",
		"owner": "CLIENTID",
		"table_name": "POSITION_DETAIL_EXT",
		"column_name": "POSITION_DETAIL_ID",
		"formatdialect": {
			"precision": "38",
			"scale": "0"
		}
  },
  ...
},
"summary_vocabulary":{
    "newSummaryField": {
		"path": "extension/newSummaryField",
		"datatype": "integer",
		"owner": "JANUS",
		"table_name": "POSITION_EXT",
		"column_name": "POSITION_ID",
		"formatdialect": {
			"precision": "38",
			"scale": "0"
	},
	...
}

Example when you don`t have predefined DB structure:xample when you don`t have predefined DB structure:

Code Block

"vocabulary":{
    "newField": {
		"path": "extension/positionDetailId",
		"datatype": "integer",
		"formatdialect": {
			"precision": "38",
			"scale": "0"
		}
  },
  ...
},
"summary_vocabulary":{
    "newSummaryField": {
		"path": "extension/newSummaryField",
		"datatype": "integer",
		"formatdialect": {
			"precision": "38",
			"scale": "0"
	},
	...
}

...

owner - {in_market_client_id}DBO
table_name - {RESOURCE_NAME}_EXT
table_name for summary vocabulary - {RESOURCE_NAME}_EXT_SUMMARY
column_name - {ELEMENT}

...

Create an

...

Extension

To generate an extension via Rest API endpoint in Vault:

Please use the Ontology Publish Service API: .

https://apps.dev.az.eagleinvsys.com/api/vault/ontology-publish/api/doc#/metadata-mapping-rest-api/submitEDSMetadataMappingPOST

...

Info
‘generateldm’ parameter should be passed as “N”

To generate an extension in Eagle via RTR:

To generate an extension via RTR the following RTR should be used via stream eagle_ml-2-0_default_cm_exec_eds , the example is below.

...

dynamic/metadata/custom/ontology/{in_market_cliend_id}

Processing

...

Rule Generation for an

...

Extension

A Processing rule is automatically generated when you create an extension.
Alter DDL for an extension will be automatically executed when you generate extension via API.
Cache will be stored in Redis and can be found via the following key:

...

The processing rule for the extension can be also generated via RTR for the corresponding core resource.
An example of the RTR to regenerate processing rule for an extension of core Data Rules warehouseposition resource:

Alter DDL for an

...

Extension

This is an optional step, the new tables and fields will be created in the DB during the execution of processing rule generation:

...

Alter DDL RTR for extension resource is the same as Alter DDL RTR for a core resource.

Load and Extract Data to/from Extension Table

Congratulations! Now extension is successfully generated and ready to use.

Load

To load data, please use the same RTR as for the Data Rules core resource.

New fields or Extensions elements are listed under the “extension” node:

Sample xml:

Code Block
... <extension> <newElementNum>1</newElementNum> <newElementString>str</newElementString> </extension> ...

Sample csv:

Code Block
...,extension/newElementNum,extension/newElementString,... ...,1,str,...

An example of warehouseposition extension data is attached:

Extract

To extract data use the same RTR as for the core resource

Note
Attention: if new DB fields are described in extension, then Alter DDL is mandatory step for Load and Extract. Otherwise, there will be failures.

How to Remove an Extension

Delete extension ontology files from azure:

https://portal.azure.com/#@eagleinvsys.net/resource/subscriptions/030c4927-46ed-4031-934e-d8cb445e75d0/resourceGroups/dev-eastus2-storage-eds/providers/Microsoft.Storage/storageAccounts/deveastus2eds/containersList
metadata/custom/ontology/{in_market_client_id}/{in_market-client_id}_{resource}*.json

Example for EDS tenant warehouseposition extension:
metadata/custom/ontology/da1t1cedstest/da1t1cedstest_warehouseposition.json
metadata/custom/ontology/da1t1cedstest/da1t1cedstest_warehouseposition-models.json

Delete processing rule for extension from cache

https://apps.dev.az.eagleinvsys.com:8443/api/vault/metadata/api/doc#/processing-rule/deleteResource

...

name = DA1T1CEDSTEST_WAREHOUSEPOSITION
version = 2.0.39.1.0.1

Extensions in Oracle

End users can create an extension in Eagle via EDS UI for any core resource in case of Extract.

...

genericentity
genericsmf
warehouseposition
warehousetrade
warehousecashactivity

...

Create an extension in Eagle via EDS UI

Info
Prerequisite: custom database table exists in the Eagle Oracle database

Please open EDS Tool to create new EDS extension. It can be found from Eagle pearl menu:

...

Code Block

			<extension>
				<nmTradeDate1>2022-09-30</nmTradeDate1>
				<nmTradeDate2>2007-04-14</nmTradeDate2>
				<nmTradeNumber1>16</nmTradeNumber1>
				<nmTradeNumber2>12.162</nmTradeNumber2>
				<nmTradeNumber3>2022609.8</nmTradeNumber3>
				<nmTradeVarchar1>asasasasasasas1</nmTradeVarchar1>
				<nmTradeVarchar2>2h</nmTradeVarchar2>
			</extension>

...

Create an

...

Extension for the

...

Resources with

...

History Mode

DB table has to be created with the same name as for composite extension + _HIST suffix, for example, if composite extension table is called ESTAR.ENTITY_EXT, the history table should be ESTAR.ENTITY_EXT_HIST

The history table should have a proper primary key:

genericsmf - the PK for composite extension tables is SECURITY_ALIAS and SECURITY_ALIAS, EFFECTIVE_DATE, SRC_INTFC_INST for history extension table
genericentity - the PK for composite extension table is ENTITY_ID and ENTITY_ID, EFFECTIVE_DATE for history extension table (history mode is currently not available )

Do not add a history table to the extension model in EDS Tool, only composite table should be there.

To load history data to extension use historyOnlyFlag=Y parameter in the data file.

Versions Compared

Old Version 3

New Version 4

Key

Supported Types of Extension Columns

Fields for an

Extension Table

Fields for

Summary Extension Tables

Create an

Extension

Processing

Rule Generation for an

Extension

Alter DDL for an

Extension

Load and Extract Data to/from Extension Table

Load

Extract

How to Remove an Extension

Extensions in Oracle

Create an extension in Eagle via EDS UI

Create an

Extension for the

Resources with

History Mode