mmx metadata framework
...the DNA of your data
MMX metadata framework is a lightweight implementation of OMG Metadata Object Facility built on relational database technology. MMX framework
is based on three general concepts:
Metamodel | MMX Metamodel provides a storage mechanism for various knowledge models. The data model underlying the metadata framework is more abstract in nature than metadata models in general. The model consists of only a few abstract entities... see more.
Access layer | Object oriented methods can be exploited using inheritance to derive the whole data access layer from a small set of primitives created in SQL. MMX Metadata Framework provides several diverse methods of data access to fulfill different requirements... see more.
Generic transformation | A large part of relationships between different objects in metadata model are too complex to be described through simple static relations. Instead, universal data transformation concept is put to use enabling definition of transformations, mappings and transitions of any complexity... see more.

Metadata materialization, part 4: Materializing many-to-many relations

November 29, 2011 10:03 by Viktor Varlamov

In the previous part of these series we build a view generator for multiple properties. Quite analogues to that is the work we do in this part of the series.

There is only one correct way of presenting many-to-many relations and that is MD_RELATION table. But MD_RELATION column names are quite ambiguous to a novice user or system being integrated, so the sole purpose of such a view would be add semantic labels to the columns and objects.

Continuing with materialization of object type $ObjectType with object type code $ObjectTypeCd:

FOR EACH $MultRelation IN 
SELECT
	relType1.relation_type_cd,
	relType1.relation_type_nm,
	relType1.related_relation_type_nm,
	objType1.object_type_nm,
	objType2.object_type_nm as related_object_type_cd
FROM
	MD_RELATION_TYPE relType1
JOIN
	MD_OBJECT_TYPE objType1
	ON objType1.object_type_cd = relType1.object_type_cd
JOIN
	MD_OBJECT_TYPE objType2
	ON objType2.object_type_cd = relType1.related_object_type_cd
WHERE
	relType1.object_type_cd = $ObjectTypeCd
	AND relType1.multiplicity_ind = 1

We build view:

CREATE VIEW v_$MultRelation.relation_type_nm_$MultRelation.related_relation_type_nm AS
SELECT
	rel1.object_id AS $MultRelation.object_type_nm_id,
	rel1.related_object_id AS $MultRelation.related_object_type_cd,
	rel1.changed_dt,
	rel1.changed_by
FROM
	MD_RELATION rel1
WHERE
	relation_type_cd = $MultRelation.relation_type_cd
	AND state_ind = 1

In the next, and the last part of the series, we will look at what we need to know to build an automated process for materializing all or part of MMX metadata.



Metadata materialization, part 3: Materializing objects and properties: what was left behind

November 23, 2011 17:59 by Viktor Varlamov

In the previous part of these series we build a view generator for object materialization that included pointers to parent objects, non-multiple enumeration and text/date/numeric properties and many-to-one relations. What was left out were the multiple properties.

To include multiple properties in the object type based view, we must choose one of the solutions:

  • add as many columns for each property type as there are maximum instances for a single object
  • limit number of instances we materialize for each property type
  • build a separate view for such property types exposing all active properties.

Of these options, for the automated materialization engine only the last one is suitable:

  • having a non-fixed number of columns is extremely bad for integration views
  • and when limiting the number of instances included in the view, we will need some additional storage object to store the materialization settings as MMX model does not provide hosting for such information.

So, continuing with materialization of object type $ObjectType with object type code $ObjectTypeCd

FOR EACH $MultipleProp IN
	SELECT
		prop1.property_type_cd,
		prop1.property_type_nm
	FROM
		MD_OBJECT_TYPE type1
	JOIN
		MD_PROPERTY_TYPE prop1
		ON prop1.object_type_cd = type1.object_type_cd
	WHERE
		type1.object_type_cd = $ObjectTypeCd
 

We create a separate view:

CREATE VIEW $ObjectType_$MultipleProp.property_type_nm AS
	SELECT 
		prop1.property_id,
		obj1.object_id AS $ObjectType_id,
		prop1.property_nm,
		prop1.value_ds as property_value,
		prop1.changed_dt,
		prop1.changed_by
	FROM
		MD_OBJECT obj1
	JOIN
		MD_PROPERTY prop1
		ON prop1.object_id = obj1.object_id
		AND prop1.property_type_cd = $MultipleProp.property_type_cd
	WHERE 
		obj1.object_type_cd = $ObjectTypeCd
		AND obj1.state_ind = 1
 

Alternatively for multiple text properties, if we have modeled the multiplicity only for purposes of saving values in different languages, we can materialize the property values in the main object type view for a chosen default language. But that requires that such assumption is validated in the process of metadata validation prior to exposing data via views.

In next part of the series, we take on materialization of many-to-many relations.



Metadata materialization, part 2: Materializing objects

November 15, 2011 17:48 by Viktor Varlamov

In this part of the materialization series we will build an object materialization view generator. Output of such view generator may be then later used as the bases for your actual materialization views.

So what kind properties can we expose in such a view? As we will be building a view generator that will use (a lot of) JOINs, we can include only those property types that are safe* to include - those that in the metamodel definition have multiplicity indicator set false, i.e. there can be maximum on one instance of such property per object. Also it is safe* to include one-to-one relations.

* Safe in this case means that the JOINs will not produce duplicate rows, provided that metadata is validated against the metamodel.

One-to-many (or many-to-one) relations can also be materialized in object type based views. The rule is: we include the pointer to related object in the view that corresponds to the "many" part of the relation type Example:

An instance if ClassA may be referenced by multiple instances of ClassB. ClassB view will have column referencing ClassA.

And now on to building the view: in the following we will be building a materialization view for class $ObjectType, with object_type_cd = $ObjectTypeCd.

1. Id, name, tag, description and change date and user

This part is pretty obvious, but it lays the foundation for our next steps

CREATE VIEW v_$ObjectType AS
SELECT
	obj1.object_id as $ObjectType_id,
	obj1.object_nm as $ObjectType_nm,
	obj1.object_tag as $ObjectType_tag,
	obj1.object_ds as $ObjectType_ds,	
	/* more_columns_to_come */
	obj1.changed_dt,
	obj1.changed_by
FROM
	MD_OBJECT obj1
	/* more_joins_to_make */ 
WHERE
	obj1.object_type_cd = $ObjectTypeCd

2. Parent object

In MMX parent-child relations are stored in the MD_OBJECT.parent_object_id and possible parent-child relations are described in MD_RELATION_TYPE with taxonomy indication set to true. Depending on the metamodel, class can have no, one or more possible parents. So,

FOR EACH $ParentType IN
	SELECT 
	parent1.object_type_cd as parent_type_cd,
	CASE WHEN type1.object_type_cd = parent1.object_type_cd THEN 'Parent_' || parent1.object_type_nm ELSE parent1.object_type_nm END as parent_type_nm
	FROM
		MD_OBJECT_TYPE type1
	LEFT JOIN
		MD_RELATION_TYPE parentRel1
		ON parentRel1.related_object_type_cd = type1.object_type_cd
		AND parentRel1.taxonomy_ind = 1
	LEFT JOIN
		MD_OBJECT_TYPE parent1
		ON parent1.object_type_cd = parentRel1.object_type_cd
		AND parent1.abstract_class_ind = 0
	WHERE 
		type1.object_type_cd = $ObjectTypeCd
		AND type1.abstract_class_ind = 0

we must add following join to our view:

	
	...
	  LEFT JOIN MD_OBJECT par1
	 	ON par1.object_id = obj1.parent_object_id 
		AND par1.object_type_cd = 
	 	AND par1.state_ind = 1
	...

Also add a new column to the view:

	...
	par1.object_id AS  _id, 
	...
 

3. Non-multiple enumeration type properties

For enumeration properties we will try to make our integration views extra-confortable as expose the literals instead of object_id-s, so this will require us to do two joins for each property:

FOR EACH $EnumProperty IN 
	SELECT
		prop1.property_type_cd,
	 	prop1.property_type_nm,
		type2.object_type_cd AS enum_type_cd,
	FROM
		MD_OBJECT_TYPE type1
	JOIN
		MD_PROPERTY_TYPE prop1
		ON prop1.object_type_cd = type1.object_type_cd
		AND prop1.multiplicity_ind = 0
		AND prop1.data_type_cd = 107
	JOIN
		MD_OBJECT_TYPE type2
		ON type2.object_type_cd = prop1.domain_cd
	WHERE
		type1.object_type_cd = $ObjectTypeCd

we add these joins

	...
	LEFT JOIN MD_PROPERTY ep_1
	    ON ep_1.object_id = obj1.object_id
	    AND ep_1.property_type_cd = $EnumProperty.property_type_cd
	    AND ep_1.state_ind = 1
	LEFT JOIN MD_OBJECT epo_1
	    ON epo_1.object_id = ep_1.value_id
	    AND epo_1.object_type_cd =  $EnumProperty.enum_type_cd
	    AND epo_1.state_ind = 1
	...

Also add a new column to the view

	...
	   epo_1.object_nm AS  $EnumProperty.property_type_nm
	...
 

4. Non-multiple non-enumeration properties

These properties are defined with multiplicity indicator set to false, so that there can be zero or just one instance of such properties.

FOR EACH $NonEnumProperty IN
	SELECT
		type1.object_type_cd,
		type1.object_type_nm,
		prop1.property_type_cd,
		prop1.property_type_nm,
		prop1.data_type_cd 
	FROM
		MD_OBJECT_TYPE type1
	JOIN
		MD_PROPERTY_TYPE prop1
		ON prop1.object_type_cd = type1.object_type_cd
		AND prop1.multiplicity_ind = 0
		AND prop1.data_type_cd <> mmxmd.object_type_code('Core', 'Enumeration')
	WHERE
		type1.object_type_cd = $ObjectTypeCd

We add following join:

	...
	LEFT JOIN MD_PROPERTY nep_1
		ON nep_1.object_id = obj1.object_id
		AND nep_1.property_type_cd = $NonEnumProperty.property_type_cd
		AND nep_1.state_ind = 1
	...

and add following column to the SELECT part:

	...
	nep_1.value_ds AS $NonEnumProperty.property_type_nm,
	...

Note: Property values can be text, numbers or dates depending on the property type definition. In the scope of this article we do not deal with casting them to correct data type, but in your integration views casting is most advisable.

5. Many-to-one relations pointing to this object type

FOR EACH $SingleRelation IN
	SELECT 
		reltype1.relation_type_cd,
		reltype1.related_relation_type_nm rel_type_nm,
		type2.object_type_nm rel_object_type_nm
	FROM
		MD_OBJECT_TYPE type1
	JOIN
		MD_RELATION_TYPE reltype1
		ON reltype1.object_type_cd = type1.object_type_cd
		AND reltype1.taxonomy_ind = 0
		AND reltype1.related_multiplicity_ind = 0
	JOIN
		MD_OBJECT_TYPE type2
		ON type2.object_type_cd = reltype1.related_object_type_cd
	WHERE
		type1.object_type_cd = $ObjectTypeCd

we add following join:

  	...
	LEFT JOIN MD_RELATION sr_1
		ON sr_1.object_id = obj1.object_id
		AND sr_1.relation_type_cd = $SingleRelation.relation_type_cd
		AND sr_1.state_ind = 1
	...

And this column to the column list:

	...
	sr_1.related_object_id AS  $SingleRelation.rel_type_nm,
	...

And that's it: we have our view ready.

In the next parts of these series we will talk about all the things that we left out of this view, namely:

  • multiple numerical and date properties
  • multiple text properties
  • many-to-many relations