Designer 2000 QA


Better than a matrix: Function Entity Usages Exceptions

By Hervé Deschamps.

On most projects we first start defining the data model (entites, attributes, relationship, UID, etc.). We then start working on the function model (processes, function hierarchy, specification). When the data model is reasonably stable, we start defining entity and attribute usages against each function.The cross referencing between the data and the function model conduces to a lot of refinments on both sides.

When both models (or part of them) are thought to be complete it's time to use the matrix diagrammer. It can be used to produce a function-entity usage matrix or a function-attribute usage matrix, depending of the level of detail of your analysis. A matrix is a very powerful QA tool. In our context it allows us to detect anomalies like entities not used by any function (frequent), functions not using any entity, or other potential anomalies detailed bellow.

Unfortunatelly, when application systems grow to 100+ functions and 70+ entities, using a matrix to perform the QA becomes unwieldly. A lot of time can be wasted handling multiple sheets of paper and it becomes easy to miss a line or column in a 7000+ cell matrix!

The problem with the matrix is that it is a systematic tool: one must scan several times throuh all the matrix cells in order to detect problems.We need to work by exception and let the data scanning be performed by the computer! This is what what the utility that we present will do for you.

This utility will highlight:

set scan off
set pagesize 2000
set linesize 70
set heading off

col name format a40
col function_label format a8
col short_definition format a47

spool fun_ent_qa.lst

prompt
prompt
prompt
prompt
prompt**************************************************
prompt*          Analysis Quality Check Utility
prompt*               for Designer 2000
prompt*
prompt**************************************************
prompt
rem                 Written by:     Herve Deschamps
rem                 Last Modified:  6/16/1997
prompt
prompt
prompt
prompt

prompt
prompt
prompt >>>> Entities not used by any business function.

select ent.name
from ci_entities ent
where application_system_owned_by = 57834 and
      not exists (select '1'
                  from ci_function_entity_usages
                  where entity_reference = ent.id)
order by ent.name
/

 

prompt
prompt
prompt >>>> Entities not created by any business function.

select ent.name
from ci_entities ent
where application_system_owned_by = 57834 and
      not exists (select '1'
                  from ci_function_entity_usages
                  where entity_reference = ent.id and
                        create_flag = 'Y') and
      exists (select '1'
              from ci_function_entity_usages
              where entity_reference = ent.id)
order by ent.name
/
 

prompt
prompt
prompt >>>> Entities not retrieved by any business function.

select ent.name
from ci_entities ent
where application_system_owned_by = 57834 and
      not exists (select '1'
                  from ci_function_entity_usages
                  where entity_reference = ent.id and
                        retrieve_flag = 'Y') and
      exists (select '1'
              from ci_function_entity_usages
              where entity_reference = ent.id)
order by ent.name
/

 

prompt
prompt
prompt >>>> Entities not updated by any business function.

select ent.name
from ci_entities ent
where application_system_owned_by = 57834 and
      not exists (select '1'
                  from ci_function_entity_usages
                  where entity_reference = ent.id and
                        update_flag = 'Y') and
      exists (select '1'
              from ci_function_entity_usages
              where entity_reference = ent.id)
order by ent.name
/
 

prompt
prompt
prompt >>>> Entities not deleted by any business function.

select ent.name
from ci_entities ent
where application_system_owned_by = 57834 and
      not exists (select '1'
                  from ci_function_entity_usages
                  where entity_reference = ent.id and
                        delete_flag = 'Y') and
      exists (select '1'
              from ci_function_entity_usages
              where entity_reference = ent.id)
order by ent.name
/

 

prompt
prompt
prompt >>>> Functions not using any entity.
select fun.function_label, fun.short_definition
from ci_functions fun
where application_system_owned_by = 57834 and
      atomic_flag = 'Y' and
      not exists (select '1'
                  from ci_function_entity_usages
                  where function_reference = fun.id)
order by fun.function_label
/
 

prompt
prompt
prompt >>>> Functions that have entity usages with no flag.
select distinct fun.function_label, fun.short_definition
from ci_functions fun, ci_function_entity_usages feu
where application_system_owned_by = 57834 and
      feu.function_reference = fun.id and
      feu.create_flag is null and
      feu.retrieve_flag is null and
      feu.update_flag is null and
      feu.delete_flag is null
order by fun.function_label
/

spool out
 

Many more checks can be added to this utility. Indeed, from one project to another we customize this utility. If the analysis team define attribute usages for each function, then this utility should at least check for:

Even at entity level, some projects may require that only one function create or delete a given entity instance, at least most of the time. This utility could thus be enhanced to highlight entities created or deleted by more than one business function.

Whatever may be the projects' special needs, the key idea behind this QA report is that it enables us to work by exception therefore to save a considerable amount of time.

One last note about the SQL*Plus script above. It works on one application system at a time. Before running it, we update the script with the relevant internal application ID in Designer 2000. In the example above, we're looking at application 57834. In order to make it work for you, you can edit the script and search/replace 57834 by whatever appropriate application system ID. You can obtain this ID from the RON by highlighting an entity, going over to the properties window, clicking on the name of the application that owns the entity and pressing key F5. The application ID is displayed in field 'Internal value'. Of course you could also obtain this ID by querying the Designer 2000 view ci_application_systems. Even better, you could enhance this script to obtain the application name from the user at runtime and getting the corresponding ID.

The scripts published on this web site are as the author use them. They all have a lot of room for enhancement. The author would be glad to recieve your enhancements and would give you the credit next time this page is updated.

Please send me comments or suggestions:  herve@iherve.com

 Take a look at my HomePage