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
