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