Clarity

Expand all | Collapse all

Attribute Rationalization Process

  • 1.  Attribute Rationalization Process

    Posted May 31, 2017 02:16 PM

    We're engaged in what I've often heard termed an "Attribute Rationalization Process" where we're reviewing our custom attribute usage for opportunities to clean up.

     

    I'm thinking I'll run a query that shows Object, Attribute Name, Attribute ID OOTB or Custom, and some kind of data about usage (example, density of usage "attribute X is not null on XX% of projects and the last updated date for a project where it is not null is MM/DD/YYYY).

     

    Instead of us re-inventing this wheel, does anyone have a business process and some information tools (queries and such) that they've already battle tested and can share? I'm going to start with Dave's awesome code here: Documenting your Configuration - but any additional tips or tricks will be greatly appreciated.



  • 2.  Re: Attribute Rationalization Process

    Posted May 31, 2017 09:01 PM

    Hi Robert, I went through this with my first Clarity implementation at a Telco and they had created over 120 additional custom configured fields against a project, with about 40 of them duplicates of what was out of the box. The process to rationalise these was manual and painful because many of them were made mandatory so query tools could not be used to determine utilisation (particularly when the attribute was not high frequency or set up once only during the investment lifecycle). So the more practical approach - while painful because we had to use Actuate and then BO - was to evaluate use of the configured fields in reports. If they weren't being reported on, we removed them from Clarity.

    Keen to see what other approaches the community has used.



  • 3.  Re: Attribute Rationalization Process

    Posted May 31, 2017 10:09 PM

    Christopher Yardin wrote:

     

    "If they weren't being reported on, we removed them from Clarity."

    Thanks Christopher. I floated this out as a possible approach today unsure if it had legs. Thanks for validating a prudent dimension for our evaluation criteria.

     

    Thank You! I too look forward to more thoughts & battle tested wisdoms from others.



  • 4.  Re: Attribute Rationalization Process

    Posted Jun 01, 2017 08:38 AM

    Sometimes you hit the limit of custom attributes and that is the reason for need for cleanup and the overhead in them not so much  duplicates.

    Start by listing OOTB and custom their names, id's and db fields.

    Next identify the ones that had no data and the ones that had only a small number of data values and when was the last update.

    That is the easy part with SQL.

    Identifying whether or not the attributes were in any views - system or user -, filters,  portlets, queries, reports, process steps, process scripts, conditions, calculations, lookups, dependencies, audits etc  is more cumbersome, but doable in the application.

    Then there are also database customization and configurations.

    One approach is to deactivate the attributes deemed not to be in use and perform the user acceptance testing.

    Can the users use the system as they are accustomed to? If yes the attributes are obsolete. If not, identify which attributes and where and roll them back.

    The overhead will be there until the attributes and any data in them is removed. That is on unrecoverable step and therefore there should backups and proof that they can be actually successfully restored.

    There are also other housekeeping tasks which reduce the obsolete overhead like cleaning the notifications, audit trails, process instances to name a few.



  • 5.  Re: Attribute Rationalization Process

    Posted Jun 01, 2017 08:45 AM

    urmas wrote:

     

    "One approach is to deactivate the attributes deemed not to be in use and perform the user acceptance testing."

    Great idea Martti. Maybe take a multi-step approach to deprecation. Identify targets for deletion, deactivate, wait 6 months. If no noise after 6 months, delete.

    Thanks!



  • 6.  Re: Attribute Rationalization Process

    Posted Jun 01, 2017 04:43 PM

    To aid with this, I wish to to produce a list of custom attributes and the latest date of data for the custom attribute.

     

    On Line 11 below I'm attempting to sub select into the ODF_CA_<object> table to produce the last date of attribute data.

    SELECT
    A.OBJECT_NAME AS 'Object Name'
    , (select name from cmn_captions_nls where language_code = 'en' and table_name = 'ODF_CUSTOM_ATTRIBUTES' and pk_id = A.id) AS 'Attribute'
    , (select description from cmn_captions_nls where language_code = 'en' and table_name = 'ODF_CUSTOM_ATTRIBUTES' and pk_id = A.id) AS 'Description'
    , A.DEFAULT_VALUE AS 'Default' 
    , A.INTERNAL_NAME AS 'Database Column / Attribute ID'
    , A.IS_CUSTOM AS 'Is Custom'
    , A.IS_ACTIVE AS 'Is Active'
    , A.CREATED_DATE AS 'Created Date'
    , A.LAST_UPDATED_DATE AS 'Last Updated Date'
    , (SELECT TOP 1 MAX(DATA.LAST_UPDATED_DATE) FROM (SELECT I.LAST_UPDATED_DATE FROM ODF_CA_IDEA I WHERE 'I'+'.'+'A.INTERNAL_NAME' IS NOT NULL) AS DATA) AS 'Last Date of Attribute Data'
    FROM ODF_CUSTOM_ATTRIBUTES A
    WHERE A.OBJECT_NAME = 'idea'
    AND IS_CUSTOM = 1
    AND A.INTERNAL_NAME != 'partition_code'
    GROUP BY
    A.OBJECT_NAME
    , A.ID
    , A.DEFAULT_VALUE
    , A.INTERNAL_NAME
    , A.IS_CUSTOM
    , A.IS_ACTIVE
    , A.CREATED_DATE
    , A.LAST_UPDATED_DATE

    Example: est_capital is 12/10/2015

     

    My code above - notsomuch. 

    It's late in the day and my eyes & brain are loosing steam. Subselect into a Pivot? Step through with a Cursor? 



  • 7.  Re: Attribute Rationalization Process

    Posted Jun 01, 2017 05:14 PM

    When you say

    On Line 11 below I'm attempting to sub select into the ODF_CA_<object> table to produce the last date of attribute data.

    does that really work as desired?

    There is one last_updated_date for each record in the ODF_CA_ table  no matter which attribute you pick.

    So what it means is not the last date the attribute value was updated, but the last date some field in the record was updated which may or may not be the same as the last time the record in the main object table was updated.

     

    So that provides a  date after the attribute has not been updated, but the last time the attribute was updated may have been even earlier, but I cannot think a way to establish the real date.

     

    However, my reasoning is that if the latest update of any record having a value for a specific custom attribute is say two three years ago, it is safe to assume that the attribute in currently not in use. In my case considering those and the attributes that did not have any data at all gave plenty to start with the clean up.

     

    Going back to the clean up actions a significant point in deactivating is not to deactivate attributes that are in scripts, reports, dynamic lookups, process conditions and steps because then those will error out and that is something the users will notice immediately.

    Start with the ones that are only in views and then modify or create new versions for the items that could error out.



  • 8.  Re: Attribute Rationalization Process

    Posted Jun 02, 2017 03:19 AM

    Agree with Urmas' point ; last_updated_date is at record level not attribute so is not as useful as it might seem (apart from it being some upper-bound for when the attribute MIGHT have been updated). If you are AUDITTING the attribute then that data would be better (if you keep it that is), but....?

     

    As for why your query does work - I'd blame tiredness too

     

    Line 11 has this in it;

    WHERE 'I'+'.'+'A.INTERNAL_NAME' IS NOT NULL

    which isn't doing what your tired brain thinks it is doing ; all it is doing is testing that the string "I.A.INTERNAL_NAME" is not null (which it always will be, its just a string) - its not looking at the data in the database ; you'd need some dynamic SQL to do that.



  • 9.  Re: Attribute Rationalization Process

    Posted Jun 02, 2017 09:23 AM

    We've been doing this too.  What I've done is created a query for each object, with each displayed attribute in the columns of the output.  I save that query as a raw data query... then I re-wrote the query to simply return a "Has Data" or "No Data" for each attribute, as a 2nd version of the query..  When I execute that query and export to excel, I can run a pivot table to see how often an attribute is being used.  (e.g., project attribute "ABC" is used on 3866 of 5188 active projects, or, 64.49%).  Then we look at the usage of our attributes to identify attributes that we can propose be retired.



  • 10.  Re: Attribute Rationalization Process

    Posted Jun 02, 2017 09:37 AM

    Thanks Peter. Love the 'density of usage' idea. Can you share this query?



  • 11.  Re: Attribute Rationalization Process

    Posted Jun 02, 2017 09:50 AM

    Sure.  Most of this won't make any sense since it's all custom attributes.  I've also left in the "counts" at the end, to look at the volume of sub-object instances like Risks, and Issues, as well as custom sub-objects.

     

    SELECT
    (CASE WHEN IP.IS_PROGRAM = 1 AND INV.ODF_OBJECT_CODE = 'project'
    THEN 'Program'
    WHEN IP.IS_PROGRAM = 0 AND INV.ODF_OBJECT_CODE = 'project'
    THEN 'Project'
    ELSE ' '
    END) "Investment Type",
    inv.code "Project Code"
    ,inv.name "Project Name"
    ,(CASE WHEN TRIM(oci.met_short_desc) IS NOT NULL THEN 'Has Short Description' ELSE 'No Short Description' END) "Short Description"
    ,(CASE WHEN TRIM(INV.DESCRIPTION) IS NOT NULL THEN 'Has Description' ELSE 'No Description' END) "Description"
    ,(SELECT full_name FROM srm_resources WHERE user_id = inv.manager_id) "Project Manager"
    ,inv.schedule_start "Proj Start Date"
    ,inv.schedule_finish "Proj Finish Date"
    ,oci.met_fin_close_date "Financial Close Date"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_INIT_CLS' AND language_code = 'en' AND lookup_code = oci.MET_INIT_CLS) "Initiative Class"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_PRJ_TYPE' AND language_code = 'en' AND lookup_code = ocp.met_prj_type) "Project Type"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_GATE_LEVEL' AND language_code = 'en' AND lookup_code = ocp.met_gate_level) "Gate Review Level"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_PROJ_STATE' AND language_code = 'en' AND lookup_code = oci.met_prj_state) "Project State"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INV_PROCESS_TYPE' AND language_code = 'en' AND lookup_code = inv.process_code)
    ||(SELECT '/'||name FROM cmn_lookups_v WHERE lookup_type = 'INV_STAGE_TYPE' AND language_code = 'en' AND lookup_code = inv.stage_code) "Project Phase"
    -- ,inv.is_active "Is Active"
    -- ,ip.is_template "Is Template"
    ,obs.level2_name "Project Ownership Dept LOB"
    ,obs.level3_name "Project Ownership Dept Sub-LOB"
    ,oci.met_incl_curr_plan_y "Incl Current Plan Yr"
    ,oci.met_par_num "PAR #"
    ,(CASE WHEN TRIM(dbms_lob.substr(oci.met_com_prj_stmnt)) IS NOT NULL THEN 'Has Statement' ELSE 'No Statement' END) "Commitment/Project Statement"
    ,(CASE WHEN EXISTS
    (SELECT 1 FROM odf_multi_valued_lookups omv
    ,cmn_lookups_v con
    WHERE omv.OBJECT = 'project'
    AND omv.attribute = 'met_cap_fmwk'
    AND omv.value = con.lookup_code
    AND con.lookup_type = 'MET_CAPB3'
    AND con.language_code = 'en'
    AND omv.pk_id = inv.id)
    THEN 'Has Cap Frmwk' ELSE 'No Cap Frmwk' END) "Capability Framework"
    ,(CASE WHEN EXISTS
    (SELECT 1 FROM odf_multi_valued_lookups omv
    ,cmn_lookups_v con
    WHERE omv.OBJECT = 'project'
    AND omv.attribute = 'met_growth_init'
    AND omv.value = con.lookup_code
    AND con.lookup_type = 'MET_SPEC_INIT'
    AND con.language_code = 'en'
    AND omv.pk_id = inv.id)
    THEN 'Has Growth Initiatives' ELSE 'No Growth Initiatives' END) "Growth Initiatives"
    ,(CASE WHEN ocp.met_ob_lob IS NOT NULL THEN 'Has Orange Book LoB' ELSE 'No Orange Book LoB' END) "Orange Book LOB"
    ,(CASE WHEN TRIM(ocp.met_prjsplink) IS NOT NULL THEN 'Has SP Link' ELSE 'No SP Link' END) "Proj SharePoint Site Link"
    ,(CASE WHEN oci.met_prj_priority IS NOT NULL THEN 'Has Proj Priority' ELSE 'No Proj Priority' END) "Proj Priority"
    ,(CASE WHEN ocp.met_tier IS NOT NULL THEN 'Has Tier' ELSE 'No Tier' END) "Tier"
    ,ocp.met_top_pgm "Top Program?"
    ,ocp.met_tp_elig "Transfer Pricing"
    ,oci.met_enf_team "Enforce Team"
    ,(SELECT LISTAGG(con.name, '; ') WITHIN GROUP (ORDER BY omv.seq)
    FROM odf_multi_valued_lookups omv
    ,cmn_lookups_v con
    WHERE omv.OBJECT = 'project'
    AND omv.attribute = 'met_inv_ben_reg'
    AND omv.value = con.lookup_code
    AND con.lookup_type = 'MET_BEN_COUNTRY'
    AND con.language_code = 'en'
    AND omv.pk_id = inv.id) "Inv Ben Region/Country"
    ,(CASE WHEN ocp.met_phase_wave IS NOT NULL THEN 'Has Wave (Day)' ELSE 'No Wave (Day)' END) "Wave (Day)"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_IT_OP_IMP' AND language_code = 'en' AND lookup_code = oci.met_it_op_imp) "IT Operational Impact"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'OBJ_INVESTMENT_PFL_CATEGORY1' AND language_code = 'en' AND lookup_code = oci.obj_pfl_category1) "IT Portfolio Category"
    ,(CASE WHEN oci.met_pln_strt_dt IS NOT NULL THEN 'Has Planned Start Date' ELSE 'No Planned Start Date' END) "Planned Start Date"
    ,(CASE WHEN oci.met_pln_comp_dt IS NOT NULL THEN 'Has Planned Completion Date' ELSE 'No Planned Completion Date' END) "Planned Completion Date"
    ,(CASE WHEN oci.met_go_live_dt IS NOT NULL THEN 'Has Go Live Date' ELSE 'No Go Live Date' END) "Go Live Date"
    ,(CASE WHEN oci.met_exec_rev_dt IS NOT NULL THEN 'Has Executive Review Date' ELSE 'No Executive Review Date' END) "Executive Review Date"
    ,(CASE WHEN ocp.met_exec_spon IS NOT NULL THEN 'Has Funding Exec' ELSE 'No Funding Exec' END) "Funding Exec"
    ,(CASE WHEN oci.met_sr_biz_lead IS NOT NULL THEN 'Has Funding Lead' ELSE 'No Funding Lead' END) "Funding Lead"
    ,(CASE WHEN oci.met_wrking_client IS NOT NULL THEN 'Has Working Client' ELSE 'No Working Client' END) "Working Client"
    ,(CASE WHEN oci.met_del_exec IS NOT NULL THEN 'Has Delivery Executive' ELSE 'No Delivery Executive' END) "Delivery Executive"
    ,(CASE WHEN oci.met_sr_it_ld IS NOT NULL THEN 'Has Delivery Lead' ELSE 'No Delivery Lead' END) "Delivery Lead"
    ,(CASE WHEN EXISTS
    (SELECT 1 FROM
    odf_multi_valued_lookups omv
    ,srm_resources srm
    WHERE omv.OBJECT = 'project'
    AND omv.attribute = 'met_stcomm_mem'
    AND omv.value = srm.id
    AND omv.pk_id = inv.id)
    THEN 'Has Steering Commitee' ELSE 'No Steering Commitee' END) "Steering Commitee"
    ,(CASE WHEN EXISTS
    (SELECT 1 from prj_obs_units a inner join prj_obs_object_types oot on (a.type_id = oot.type_id) WHERE oot.table_name='SRM_PROJECTS' and a.id = oci.met_cro_dept)
    THEN 'Has CRO Dept' ELSE 'No CRO Dept' END) "CRO Dept"
    ,(CASE WHEN TRIM(oci.met_reg_impl_due) IS NOT NULL THEN 'Has Regulatory Due Date/ID' ELSE 'No Regulatory Due Date/ID' END) "Regulatory Due Date/ID"
    ,(CASE WHEN TRIM(oci.met_reg_impl ) IS NOT NULL THEN 'Has Regulatory Implications' ELSE 'No Regulatory Implications' END) "Regulatory Implications"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_YESNO' AND language_code = 'en' AND lookup_enum = oci.met_irise_dev) "Includes UI/Screen Dev"
    ,(SELECT LISTAGG(con.name, '; ') WITHIN GROUP (ORDER BY omv.seq)
    FROM odf_multi_valued_lookups omv
    ,cmn_lookups_v con
    WHERE omv.OBJECT = 'project'
    AND omv.attribute = 'met_irise_screentype'
    AND omv.value = con.lookup_code
    AND con.lookup_type = 'MET_IRISE_SCRTYP'
    AND con.language_code = 'en'
    AND omv.pk_id = inv.id) "Screen Types"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_YESNO' AND language_code = 'en' AND lookup_enum = oci.met_irise_use) "Did Proj Use iRise?"
    ,oci.met_irise_dcurl "Link to iRise DC"
    ,(CASE WHEN oci.met_dim_func IS NOT NULL THEN 'Has Functionality Ranking' ELSE 'No Functionality Ranking' END) "Functionality Ranking"
    ,(CASE WHEN oci.met_dim_qual IS NOT NULL THEN 'Has Quality Ranking' ELSE 'No Quality Ranking' END) "Quality Ranking"
    ,(CASE WHEN oci.met_dim_dd IS NOT NULL THEN 'Has Delivery Date Ranking' ELSE 'No Delivery Date Ranking' END) "Delivery Date Ranking"
    ,(CASE WHEN oci.met_dim_cst IS NOT NULL THEN 'Has Total Cost Ranking' ELSE 'No Total Cost Ranking' END) "Total Cost Ranking"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INVESTMENT_OBJ_STATUS' AND language_code = 'en' AND lookup_enum = inv.status) "Project Status"
    ,(SELECT NAME from prj_obs_units a inner join prj_obs_object_types oot on (a.type_id = oot.type_id) WHERE oot.table_name='SRM_PROJECTS' and a.id = oci.met_cust_hierarchy) "Customer Alignment Dept"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'EIA_PROJECT_CATEGORY' AND language_code = 'en' AND lookup_code = oci.met_eia_prj_cat) "EIA Project Class"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_PRJ_TYP_GRP_LU' AND language_code = 'en' AND lookup_code = ocp.met_prj_typ_grp) "Project Type Grouping"
    ,(CASE WHEN EXISTS (SELECT 1 FROM ODF_CA_MET_ITKEYINV WHERE code = oci.met_assoc_it_inv)
    THEN 'Has Key Inv' ELSE 'No Key Inv' END) "Associated Key Investment"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_SPEND_TYPE' AND language_code = 'en' AND lookup_code = ocp.met_spnd_typ) "Spend Type"
    ,ocp.met_inv_cl_new "Investment Class"
    ,(SELECT name FROM cmn_lookups_v WHERE lookup_type = 'MET_YESNO' AND language_code = 'en' AND lookup_enum = oci.met_ce_enabled1) "MADP"
    ,inv.last_updated_date "Last Updated Date"
    ,(SELECT full_name FROM srm_resources WHERE user_id = inv.last_updated_by) "Last Updated By"
    ,(SELECT count(id) from odf_ca_met_proj_app_align where odf_parent_id = inv.id) "Applications Aligned"
    ,(SELECT COUNT(r.id) FROM RIM_RISKS_AND_ISSUES RIM, ODF_CA_RISK R WHERE RIM.ID = R.ID AND RIM.PK_ID = INV.ID) "Risks"
    ,(SELECT COUNT(i.id) FROM RIM_RISKS_AND_ISSUES RIM, ODF_CA_ISSUE I WHERE RIM.ID = I.ID AND RIM.PK_ID = INV.ID) "Issues"
    ,(SELECT COUNT(c.id) FROM RIM_RISKS_AND_ISSUES RIM, ODF_CA_CHANGE C WHERE RIM.ID = C.ID AND RIM.PK_ID = INV.ID) "Change Requests"
    ,(SELECT count(id) from odf_ca_met_apex_projrep where odf_parent_id = inv.id) "Status Reports"
    ,(SELECT count(id) from odf_ca_met_prj_prtn where odf_parent_id = inv.id) "Project Partners"
    ,(SELECT count(id) from odf_ca_met_trans_pric where odf_parent_id = inv.id) "Transfer Pricings"
    ,(SELECT count(prid) from prtask where prprojectid = inv.id) "Tasks"


    FROM inv_investments inv
    ,odf_ca_inv oci
    ,odf_ca_project ocp
    ,inv_projects ip
    ,(SELECT poa.record_id, ndo.level2_name, ndo.level3_name
    FROM prj_obs_associations poa
    ,nbi_dim_obs ndo
    WHERE poa.unit_id = ndo.obs_unit_id
    AND poa.table_name = 'SRM_PROJECTS'
    AND ndo.obs_type_id = 5001009) obs


    WHERE inv.id = oci.id
    AND inv.id = ocp.id
    AND inv.id = ip.prid
    -- AND ip.is_program <> 1
    AND ip.is_template = 0
    AND inv.odf_object_code = 'project'
    AND inv.is_active = 1
    AND inv.id = obs.record_id

    -- and inv.code = 'PM00049062'



  • 12.  Re: Attribute Rationalization Process

    Posted Jun 02, 2017 09:59 AM

    Thanks Peter. As per Dave's coaching above I'll need to move to a dynamic SQL for the code I'm working on now. I'll try to wrap in your ideas, make it dynamic (ODF_CUSTOM_ATTRIBUTES.IS_CUSTOM) and post  my completed code back up for all to use.

     

    Yesterday's spike was good - but we're moving this to the backlog. It may be a couple weeks before I have something to share. Thanks all for your ideas & contributions. Keep 'em coming!



  • 13.  Re: Attribute Rationalization Process

    Posted Jun 05, 2017 11:50 AM

    Hello Rob,

         We have had to do this at a few of my sites. Normally they start with an analysis of what attributes are not being used or very seldom, and once the EPMO has approved, they are removed. When then take a look that the ones that are occasionally used, socialize them and have business justification for their use. If they cannot be justified, they are removed. Tracking data for data sake is a burden on the PM's, unless it provides value to the organization or facilitates the project. It is amazing how many extra attributes can be added to a system over the span of a few years. (Over 650 at my current site). Regardless it Data Field Rationalization (what we call it) is a large activity and will create some interesting debates among the various user communities at your site.

     

         At CA World 2016, Disney stated that when they reinstalled CA PPM they went from well over 150 additional attributes to only six. They accomplished this by showing the attributes that existed OOTB and any new attribute had to be justified. (Disney, if I have any information wrong, please correct me, I am using my notes from back then).

     

    BTW: Love the queries folks! This is what makes the community great!



  • 14.  Re: Attribute Rationalization Process

    Posted Jun 06, 2017 12:00 PM

    Michael Thibault wrote:

     

    "At CA World 2016, Disney stated that when they reinstalled CA PPM they went from well over 150 additional attributes to only six. They accomplished this by showing the attributes that existed OOTB and any new attribute had to be justified."

    Thanks Mike. This is a powerful story to share. I've been digging - are there any CA World slides available from this presentation?



  • 15.  Re: Attribute Rationalization Process

    Posted Jun 06, 2017 12:53 PM

    Actually it was CA World 2015. I missed last year, but I do have a copy of their deck some where on a drive at home. I will try to look tonight for you.



  • 16.  Re: Attribute Rationalization Process

    Posted Jun 06, 2017 12:56 PM
      |   view attached

    Actually I had a copy of it here. See slide 11 for their previous journey. Hope this helps.

     

    Mike