Need to expand Score to including data mapping capabilities, typically found in Excel Spreadsheets

[dubnemo]

Here is the data model we reviewed at the Face to Face meeting:

I can create the DDL if needed.

[Mapping Specifications.pptx]

(https://github.com/OAGi/Score/files/4344987/Mapping.Specifications.pptx)

(https://github.com/OAGi/Score/files/4344990/InvoiceVoucher.Mapping.Specification.xlsx)

[hakjuoh]

Here's DDLs for MySQL.

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `bizappl`;
CREATE TABLE `bizappl` (
  `bizappl_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`bizappl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `bizappl_interface`;
CREATE TABLE `bizappl_interface` (
  `bizappl_interface_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bizappl_interface_type_id` bigint(20) unsigned DEFAULT NULL,
  `bizappl_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`bizappl_interface_id`),
  KEY `bizappl_interface_bizappl_interface_type_id_fk` (`bizappl_interface_type_id`),
  KEY `bizappl_interface_bizappl_id_fk` (`bizappl_id`),
  CONSTRAINT `bizappl_interface_bizappl_id_fk` FOREIGN KEY (`bizappl_id`) REFERENCES `bizappl` (`bizappl_id`),
  CONSTRAINT `bizappl_interface_bizappl_interface_type_id_fk` FOREIGN KEY (`bizappl_interface_type_id`) REFERENCES `bizappl_interface_type` (`bizappl_interface_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `bizappl_interface_type`;
CREATE TABLE `bizappl_interface_type` (
  `bizappl_interface_type_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bizappl_interface_type` varchar(200) DEFAULT NULL,
  `bizappl_process_id` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`bizappl_interface_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `bizappl_term`;
CREATE TABLE `bizappl_term` (
  `bizappl_term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bizappl_id` bigint(20) unsigned DEFAULT NULL,
  `guid` varchar(41) DEFAULT NULL,
  `bizappl_term` varchar(200) NOT NULL,
  `language` varchar(20) NOT NULL DEFAULT 'en-US',
  `definition` text,
  `example_data` text,
  `source` text,
  PRIMARY KEY (`bizappl_term_id`),
  KEY `bizappl_term_bizappl_id_fk` (`bizappl_id`),
  CONSTRAINT `bizappl_term_bizappl_id_fk` FOREIGN KEY (`bizappl_id`) REFERENCES `bizappl` (`bizappl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `bizappl_term_interface`;
CREATE TABLE `bizappl_term_interface` (
  `bizappl_interface_id` bigint(20) unsigned NOT NULL,
  `bizappl_term_id` bigint(20) unsigned NOT NULL,
  KEY `bizappl_term_interface_bizappl_interface_id_fk` (`bizappl_interface_id`),
  KEY `bizappl_term_interface_bizappl_term_id_fk` (`bizappl_term_id`),
  CONSTRAINT `bizappl_term_interface_bizappl_interface_id_fk` FOREIGN KEY (`bizappl_interface_id`) REFERENCES `bizappl_interface` (`bizappl_interface_id`),
  CONSTRAINT `bizappl_term_interface_bizappl_term_id_fk` FOREIGN KEY (`bizappl_term_id`) REFERENCES `bizappl_term` (`bizappl_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mapping_rule`;
CREATE TABLE `mapping_rule` (
  `mapping_rule_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `abie_id` bigint(20) unsigned DEFAULT NULL,
  `asbiep_id` bigint(20) unsigned DEFAULT NULL,
  `bbiep_id` bigint(20) unsigned DEFAULT NULL,
  `bbie_sc_id` bigint(20) unsigned DEFAULT NULL,
  `bizappl_id` bigint(20) unsigned DEFAULT NULL,
  `bizappl_interface_id` bigint(20) unsigned DEFAULT NULL,
  `rule` text,
  `instruction` text,
  `example_data` text,
  `abie_source_target_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`mapping_rule_id`),
  KEY `mapping_rule_bizappl_id_fk` (`bizappl_id`),
  KEY `mapping_rule_bizappl_interface_id_fk` (`bizappl_interface_id`),
  KEY `mapping_rule_abie_id_fk` (`abie_id`),
  KEY `mapping_rule_asbiep_id_fk` (`asbiep_id`),
  KEY `mapping_rule_bbiep_id_fk` (`bbiep_id`),
  KEY `mapping_rule_bbie_sc_id_fk` (`bbie_sc_id`),
  CONSTRAINT `mapping_rule_abie_id_fk` FOREIGN KEY (`abie_id`) REFERENCES `abie` (`abie_id`),
  CONSTRAINT `mapping_rule_asbiep_id_fk` FOREIGN KEY (`asbiep_id`) REFERENCES `asbiep` (`asbiep_id`),
  CONSTRAINT `mapping_rule_bbiep_id_fk` FOREIGN KEY (`bbiep_id`) REFERENCES `bbiep` (`bbiep_id`),
  CONSTRAINT `mapping_rule_bbie_sc_id_fk` FOREIGN KEY (`bbie_sc_id`) REFERENCES `bbie_sc` (`bbie_sc_id`),
  CONSTRAINT `mapping_rule_bizappl_id_fk` FOREIGN KEY (`bizappl_id`) REFERENCES `bizappl` (`bizappl_id`),
  CONSTRAINT `mapping_rule_bizappl_interface_id_fk` FOREIGN KEY (`bizappl_interface_id`) REFERENCES `bizappl_interface` (`bizappl_interface_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `rule_component`;
CREATE TABLE `rule_component` (
  `rule_component_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mapping_rule_id` bigint(20) unsigned DEFAULT NULL,
  `bizappl_term_id` bigint(20) unsigned DEFAULT NULL,
  `rule_component_sequence` int(11) DEFAULT NULL,
  `rule_start_literal` text,
  `rule_end_literal` text,
  `term_path` text,
  PRIMARY KEY (`rule_component_id`),
  KEY `rule_component_mapping_rule_id_fk` (`mapping_rule_id`),
  KEY `rule_component_bizappl_term_id_fk` (`bizappl_term_id`),
  CONSTRAINT `rule_component_bizappl_term_id_fk` FOREIGN KEY (`bizappl_term_id`) REFERENCES `bizappl_term` (`bizappl_term_id`),
  CONSTRAINT `rule_component_mapping_rule_id_fk` FOREIGN KEY (`mapping_rule_id`) REFERENCES `mapping_rule` (`mapping_rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[dubnemo]

@hakjuoh please hold off for a moment. We may use different names, initially focusing on bizappl_term, which may solve #602 as well. However, #602 is a bigger challenge.

I am not clear if we need all the associations to mapping rule as you identify. These are typically only at Fields.xsd level (bccp), a more specifically a BIE's equivalent.

We will also have more metadata about the bizappl, which is a business application (JDE, SAP, etc). Name Version Software Vendor Name Version Product Name Date Placed in Production URL (e.g., ServiceNow CMDB reference)

The bizappl that will have multiple application interfaces, (bizappl_interface) which are composed of multiple bizappl_terms.

A bizappl_term may be using in multiple bizappl_interface (hence the ternary relationship).

A mapping rule is composed of one or more rule components.

A mapping rule will point to a property in a BIE, such as identifier, lastModificationDateTime, and we should somehow capture the navigation path, perhaps using dot notation. Cardinality in the path will be required.

[dubnemo]

@sfohn please review this Issue. I am now proposing that we have separate bizterm #602 to support the glossary/ synonym capabilities, independent of data mapping specific to B2B and application integration. @kbserm @hakjuoh

[dubnemo]

I have attributed the bizappl table, and created a new table bizappl_type for an enumerated list showing SaaS, PaaS, Hosted, On-Prem

DDL:

CREATE TABLE bizappl ( bizappl_id INTEGER NOT NULL, bizappl_type_id INTEGER NOT NULL, bizappl_vendor_name VARCHAR2(4000), bizappl_vendor_product_name VARCHAR2(100), bizappl_friendly_name VARCHAR2(200), bizappl_deployed_version VARCHAR2(100), bizappl_deployed_date DATE, bizappl_latest_version VARCHAR2(100), bizappl_cmdb_uri URITYPE, bizappl_vendor_product_uri URITYPE );

COMMENT ON COLUMN bizappl.bizappl_type_id IS 'iPaaS iSaaS Hosted On-Prem';

COMMENT ON COLUMN bizappl.bizappl_vendor_name IS 'name of the software vendor that owns the source code to the application';

COMMENT ON COLUMN bizappl.bizappl_friendly_name IS 'Friendly name of the application as provide by the business owner';

COMMENT ON COLUMN bizappl.bizappl_deployed_version IS 'The current version of the product in product today';

COMMENT ON COLUMN bizappl.bizappl_deployed_date IS 'The current version of the product in product today';

COMMENT ON COLUMN bizappl.bizappl_latest_version IS 'the version of the application currently avaiable and supported by the software vendor';

COMMENT ON COLUMN bizappl.bizappl_cmdb_uri IS 'URI to the application definition in CMDB';

COMMENT ON COLUMN bizappl.bizappl_vendor_product_uri IS 'URI to the vendor website showing the application marketing information';

CREATE INDEX bizappl_type_idx ON bizappl ( bizappl_type_id ASC );

ALTER TABLE bizappl ADD CONSTRAINT bizappl_pk PRIMARY KEY ( bizappl_id );

CREATE TABLE bizappl_type ( bizappl_type_id INTEGER NOT NULL, bizappl_type VARCHAR2(200) );

COMMENT ON COLUMN bizappl_type.bizappl_type IS 'iPaaS iSaaS Hosted On-Prem';

ALTER TABLE bizappl_type ADD CONSTRAINT bizappl_typev1_pk PRIMARY KEY ( bizappl_type_id );

ALTER TABLE bizappl ADD CONSTRAINT bizappl_bizappl_type_fk FOREIGN KEY ( bizappl_type_id ) REFERENCES bizappl_type ( bizappl_type_id );

[dubnemo]

@hakjuoh @kbserm This model is not going to work as-is. We need the definition of source to target bizappl_term(s) as it relates to specific source and target interfaces. I also need to include B2B mappings, which are often standard-based, implemented by a specific business partner (not application).

[dubnemo]

@kbserm This is my current thinking. I still need to remove the BBIE table reference, and consider what @hakjuoh put together, joining with the ABIE table. I believe the remaining CCS references belong in the associative table between the interface and the term tables, and NOT the mapping rule level.

NOTE: an interface can be a profiled BIE!

I have been annotating my model.

[dubnemo]

I have an idea how to include code lists to the mapping capabilities. This is the only area where we use code lists, not as enumerations or restrictions. I will update the ERD this week.

@hakjuoh @kbserm @sfohn What is/are the code list table(s)?

[dubnemo]

@kbserm @hakjuoh

I think the ERD is done now.

Here is the diagram for review, and the DDL.

data_mapping.ddl.txt

[dubnemo]

Looking at my old data model, I realized I forgot to complete the code list cross-referencing (XREF) capabilities.

One of the possible rule components is a code lookup through the cross-reference table, which also includes a default value if no value is provided (which happens quite often).

Note the application or partner interface codes are related to an application term, which some terms (fields) are enumerated codes.

The application term code then relates to a code defined in a code lists in Score. I suggest code lists in Score are ALWAYS based on standard code lists, managed by agencies.

sql_server_2012_data_mapping.ddl.txt

[dubnemo]

Update on the ERD, showing a rule_function table. We can discuss next time, but do read the KNOWN Issue note.

[dubnemo]

Also dropping in an example mapping specification from OAGIS Catalog BIE (in oagiscore.net) to staging tables in SQL Server. This was contributed to AgGateway and would also be a great example to replicate in Score. Note how I illustrate the iterations. These get mapped into $index* values, which then serve as the filters in the JSON Path statements.

AgGatewaySubmission2020-11-16OAGISCatalogStagingTablesMappingSpecification.xlsx

[dubnemo]

Reviewed 2022-04-20

[dubnemo]

The existing tables previously defined in this issue for externally defined interface definitions need to be replaced by CC-view tables, to store the externally defined definition as another library. Associative tables to link to the BIE view and the CC view would have metadata, include context, and the business rule composition based on the baseline functions from JSONata++.
GRANTs are required to access the additional table. The rule component would store a single function and the method of its usage. A rule is composed of multiple rule components.

Example rule composition that has both concatenation and embedding:

"gtinid": ( $payload01.Crop != 'TREATMENT' and $length($string($payload01.Crop)) > 12 ) ? ($payload01.ItemNumber) : '',

Rule component detail from this example:

• two JSON Path references are used ($payload01.Crop and $payload01.ItemNumber,
• if-then-else conditional logic (!=, and, >, ?, :),
• embedded functions ($string within $length).