{"id":1636,"date":"2023-01-13T13:25:25","date_gmt":"2023-01-13T07:55:25","guid":{"rendered":"https:\/\/blogs.infosys.com\/infosys-cobalt\/?p=1636"},"modified":"2023-01-13T13:25:25","modified_gmt":"2023-01-13T07:55:25","slug":"list-out-properties-and-validations-in-data-relationship-management-to-migrate-to-enterprise-data-management","status":"publish","type":"post","link":"https:\/\/blogs.infosys.com\/infosys-cobalt\/cloud-analytics\/list-out-properties-and-validations-in-data-relationship-management-to-migrate-to-enterprise-data-management.html","title":{"rendered":"List out Properties and Validations in Data Relationship Management to migrate to Enterprise Data Management"},"content":{"rendered":"<p><span style=\"text-decoration: underline\"><strong>How to generate a comprehensive list of properties grouped by Property Categories and Node Types<\/strong><\/span><\/p>\n<p>In a recent engagement I was supposed to migrate a DRM (Data Relationship Management) application to EDM (Enterprise Data Management). One of the primary things that had to be kept in mind was that I should not miss migrating any of the properties or validations. The challenge involved first and foremost to have a comprehensive list of Properties and Validations ready from my existing DRM Application. In a nutshell, below was what was required:<\/p>\n<p>1.List of all property definitions grouped by Property Category<br \/>\n2.Property definitions grouped by Node Types<br \/>\n3.List of Validations assigned to each hierarchy and the validation type used.<\/p>\n<p>&nbsp;<\/p>\n<p>For the first two points, the regular approach would be to use DRM migration utility to generate the XML file and import it into an excel to get a consolidated list of properties and validation.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-1671 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/storage\/2023\/01\/DRM_PIC1.png\" alt=\"\" width=\"626\" height=\"363\" \/><\/p>\n<p>This is a tedious and a time-consuming activity. Plus, obviously, this approach is fraught with potential for errors and misses.<\/p>\n<p>An alternate approach was needed that is accurate, reliable, and less time consuming.\u00a0 Over here, the knowledge of how application data is stored on system tables, and creation of SQL queries that are generic and reusable, for the extraction of this data comes in handy.<\/p>\n<p>&nbsp;<\/p>\n<p>I will be discussing on the approach that I followed to extract the above three reports using simple SQL queries.<\/p>\n<p><span style=\"text-decoration: underline\"><strong>1. Property Definitions grouped by Property Category<\/strong><\/span><\/p>\n<p>In the first example, the SQL query outputs all the property definitions grouping them by property category.<\/p>\n<blockquote><p>SELECT a.[c_category_code] as Property_Category,<br \/>\nc.[c_abbrev] As Property_Name,<br \/>\nc.[c_label] As DRM_Label,<br \/>\nc.[c_descr] AS Description,<br \/>\nc.[e_prop_type] As Property_Type,<br \/>\nc.[e_data_type] As Data_Type,<br \/>\nc.[c_default_value] As Default_Value,<br \/>\nc.[b_hidden] as Hidden_Property<br \/>\nFROM [dbo].[RM_Category] a,<br \/>\n[dbo].[RM_Property_Category] b,<br \/>\n[dbo].[RM_Property_Definition] c<br \/>\nwhere a.[i_category_id]=b.[i_category_id]<br \/>\nand b.[i_property_id]=c.[i_property_id];<\/p><\/blockquote>\n<p>The report, which is an output of the above query gives the list of Properties related to \u201cSystem\u201d and\u201d HFM\u201d Property Category, along with other granular information related to the property definitions like the Property Label, Property Type, Data Type, etc. We can customize the query based on the requirement by analyzing the system tables.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-1672 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/storage\/2023\/01\/DRM_PIC2.png\" alt=\"\" width=\"621\" height=\"313\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline\"><strong>2. Property Definition group by Node Type<\/strong><\/span><\/p>\n<p>My second requirement was to get a list of property definitions grouped by Node Types. \u00a0We have used the below query to meet the requirement.<\/p>\n<blockquote><p>SELECT\u00a0 a.[c_abbrev] As Node_Type,<br \/>\nc.[c_abbrev] As Property_Name,<br \/>\nc.[c_label] As DRM_Label,<br \/>\nc.[c_descr] AS Description,<br \/>\nc.[e_prop_type] As Property_Type,<br \/>\nc.[e_data_type] As Data_Type,<br \/>\nc.[c_default_value] As Default_Value,<br \/>\n[b_hidden] as Hidden_Property<br \/>\nFROM [dbo].[RM_Node_Type] a,<br \/>\n[dbo].[RM_Node_Type_Property] b,<br \/>\n[dbo].[RM_Property_Definition] c<br \/>\nwhere a.[i_node_type_id]=b.[i_node_type_id]<br \/>\nand b.[i_property_id]=c.[i_property_id];<\/p><\/blockquote>\n<p>The report, which is an output of the above SQL query gives a list of property definitions associated with a Node Type. In the report we can see the property definitions grouped by Account and Entity Node Types.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-1673 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/storage\/2023\/01\/DRM_PIC3.png\" alt=\"\" width=\"592\" height=\"363\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline\"><strong>3. List of validations and type of validation modes used in each hierarchy in a version.<\/strong><\/span><\/p>\n<p>The third SQL query generates a list of validations assigned to a hierarchy and also the mode of validation in use for a particular hierarchy.<\/p>\n<blockquote><p>SELECT\u00a0 c.[c_abbrev] As Hierarchy_Name,<br \/>\na.[c_abbrev] As Validation_name,<br \/>\na.[c_label] As Vallidation_Label,<br \/>\na.[c_descr] As Error_Message,<br \/>\na.[e_validation_type__allowed] As Validation_Mode,<br \/>\na.[e_validation_level] as Validation_Level<br \/>\nFROM [dbo].[RM_Validation_Definition] a ,<br \/>\n[dbo].[RM_Validation_Prop_Hierarchy] b,<br \/>\n[dbo].[RM_Hierarchy] c<br \/>\nwhere a.[i_validation_id]=b.[i_validation_id]<br \/>\nand b.[i_hierarchy_id]=c.[i_hierarchy_id];<\/p><\/blockquote>\n<p>The output of our above query results in a report listing the validation assign to each Hierarchy along\u00a0\u00a0\u00a0\u00a0\u00a0 with the Validation Mode, Error Message, etc.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-1670 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/storage\/2023\/01\/DRM_PIC4.png\" alt=\"\" width=\"611\" height=\"307\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to generate a comprehensive list of properties grouped by Property Categories and Node [&hellip;]<\/p>\n","protected":false},"author":318,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[11,69,72],"tags":[191,158],"coauthors":[177],"class_list":["post-1636","post","type-post","status-publish","format-standard","hentry","category-cloud-analytics","category-oracle","category-oracle-cloud-infrastructure","tag-drmtoedmmigration","tag-edm"],"acf":[],"_links":{"self":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts\/1636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/users\/318"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/comments?post=1636"}],"version-history":[{"count":10,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts\/1636\/revisions"}],"predecessor-version":[{"id":1719,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts\/1636\/revisions\/1719"}],"wp:attachment":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/media?parent=1636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/categories?post=1636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/tags?post=1636"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/coauthors?post=1636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}