How to generate a comprehensive list of properties grouped by Property Categories and Node Types

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:

1.List of all property definitions grouped by Property Category
2.Property definitions grouped by Node Types
3.List of Validations assigned to each hierarchy and the validation type used.

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.

This is a tedious and a time-consuming activity. Plus, obviously, this approach is fraught with potential for errors and misses.

An alternate approach was needed that is accurate, reliable, and less time consuming.  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.

I will be discussing on the approach that I followed to extract the above three reports using simple SQL queries.

1. Property Definitions grouped by Property Category

In the first example, the SQL query outputs all the property definitions grouping them by property category.

SELECT a.[c_category_code] as Property_Category,
c.[c_abbrev] As Property_Name,
c.[c_label] As DRM_Label,
c.[c_descr] AS Description,
c.[e_prop_type] As Property_Type,
c.[e_data_type] As Data_Type,
c.[c_default_value] As Default_Value,
c.[b_hidden] as Hidden_Property
FROM [dbo].[RM_Category] a,
[dbo].[RM_Property_Category] b,
[dbo].[RM_Property_Definition] c
where a.[i_category_id]=b.[i_category_id]
and b.[i_property_id]=c.[i_property_id];

The report, which is an output of the above query gives the list of Properties related to “System” and” HFM” 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.

2. Property Definition group by Node Type

My second requirement was to get a list of property definitions grouped by Node Types.  We have used the below query to meet the requirement.

SELECT  a.[c_abbrev] As Node_Type,
c.[c_abbrev] As Property_Name,
c.[c_label] As DRM_Label,
c.[c_descr] AS Description,
c.[e_prop_type] As Property_Type,
c.[e_data_type] As Data_Type,
c.[c_default_value] As Default_Value,
[b_hidden] as Hidden_Property
FROM [dbo].[RM_Node_Type] a,
[dbo].[RM_Node_Type_Property] b,
[dbo].[RM_Property_Definition] c
where a.[i_node_type_id]=b.[i_node_type_id]
and b.[i_property_id]=c.[i_property_id];

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.

3. List of validations and type of validation modes used in each hierarchy in a version.

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.

SELECT  c.[c_abbrev] As Hierarchy_Name,
a.[c_abbrev] As Validation_name,
a.[c_label] As Vallidation_Label,
a.[c_descr] As Error_Message,
a.[e_validation_type__allowed] As Validation_Mode,
a.[e_validation_level] as Validation_Level
FROM [dbo].[RM_Validation_Definition] a ,
[dbo].[RM_Validation_Prop_Hierarchy] b,
[dbo].[RM_Hierarchy] c
where a.[i_validation_id]=b.[i_validation_id]
and b.[i_hierarchy_id]=c.[i_hierarchy_id];

The output of our above query results in a report listing the validation assign to each Hierarchy along      with the Validation Mode, Error Message, etc.