In the last part of data materialization series, we take a look at building an automated process for materialization of all (or part) of metadata stored in MMX.

The proposed automated process in a cycle over object types that utilizes results from steps 2, 3 and 4.

But definition, in MMX only instances of non-abstract classes can exist, so, when looking for objects types to create views for we should exclude those.

SELECT 
	object_type_cd, 
	object_type_nm
FROM
	MD_OBJECT_TYPE
WHERE
	abstract_ind = 0

Additionally, we would like to exclude from materialization process all enumeration type object type. Identifying an enumeration object type is trickier so let's agree on the following definition of enumeration: enumeration object type does not have properties nor relations types and descends directly from Metamodel types class and it does not have any child class types, i.e.

...
AND object_type_cd NOT IN (
	SELECT 
		thisType.object_type_cd
	FROM 
		MD_OBJECT_TYPE thisType
	JOIN
		MD_OBJECT_TYPE parentType 
		ON parentType.object_type_cd = thisType.parent_object_type_cd
		AND parentType.parent_object_type_cd = mmxmd.object_type_code('MMXFramework', 'Metamodel')
	LEFT OUTER JOIN
			(SELECT 
				object_type_cd, 
				count(property_type_cd) prop_count
			FROM
				MD_PROPERTY_TYPE
			GROUP BY object_type_cd) propTypes
		ON propTypes.object_type_cd = thisType.object_type_cd
		AND propTypes.prop_count = 0
	LEFT OUTER JOIN
			(SELECT 
				object_type_cd, 
				count(relation_type_cd) rel_count
			FROM
				MD_RELATION_TYPE
			GROUP BY object_type_cd) fromRelTypes
		ON fromRelTypes.object_type_cd = thisType.object_type_cd
		AND fromRelTypes.rel_count = 0
	LEFT OUTER JOIN
			(SELECT 
				related_object_type_cd object_type_cd, 
				count(relation_type_cd) rel_count
			FROM
				MD_RELATION_TYPE
			GROUP BY related_object_type_cd) toRelTypes
		ON toRelTypes.object_type_cd = thisType.object_type_cd
		AND toRelTypes.rel_count = 0 
	LEFT OUTER JOIN 
			(SELECT 
				parent_object_type_cd object_type_cd,
				count(object_type_cd) child_count
			FROM 
				MD_OBJECT_TYPE
			GROUP BY parent_object_type_cd) childTypes
 		ON childTypes.object_type_cd = thisType.object_type_cd
		AND childTypes.child_count = 0 
	WHERE 
		  propTypes.object_type_cd IS NULL
		  AND fromRelTypes.object_type_cd IS NULL
		  AND toRelTypes.object_type_cd IS NULL
		AND childTypes.object_type_cd IS NULL)

Complete list of articles in the materialization series:

Part 1: Materialization choices
Part 2: Materializing objects and properties
Part 3: Materializing objects and properties: what was left behind
Part 4: Materializing many-to-many relations
Part 5: Builing an automated materialization engine