How does CAST determine which tables are considered as detail in a Data Entity?
CAST uses OMG CISQ AFP specifications to determine when the tables that form a Data Entity are considered as "detail" as shown below:
The OMG CISQ AFP specifications states the following:
In order for a data table to be grouped under a master data table, the following tests must be checked in sequence:
- The table has primary key with a foreign key constraint to another table. This is a table extension pattern.
- The table has a unique index over a foreign key to another table. This is another table extension pattern.
- The table is exclusively composed by 2 or 3 foreign key attributes and, optionally, a primary key. This is an associative table. Associative tables are always aggregated with another table. One of the related tables is picked based on the best match algorithm. Associative tables are never considered master tables.
- The table has a set of mandatory foreign keys with a cascade delete constraint. This is a classical master detail relation. The master table is chosen based on the best name match.
- The table has a set of foreign keys with other tables with similar names (similar suffix and prefix). The master table is chosen based on the best name match
- the OMG CISQ "Automated Function Points" specification for more detailed information
- Built-in parameters node - Right hand panel for more information about how CAST determines when objects are excluded from being part of Data Entities.
During the data entity grouping process described above, a special analysis is performed for all SQL tables. All other object types are used on a one-to-one basis to create datafunction objects (including those defined manually in the CAST Transaction Configuration Center.
For SQL tables, the automated CAST Transaction Configuration Center grouping process may cause a "main object" (or master) table of a Data Entities to appear as a "detail object" of another Data Entity. Note that this situation only occurs via the automated grouping process - i.e. it is not possible to achieve this manually using the CAST Transaction Configuration Center GUI.
This default automatic behaviour provided by the CAST Transaction Configuration Center is, however, contrary to the OMG CISQ AFP specification that states that "when table A is a master and also a detail of master table B, it should be merged under master table B with all its detailed tables".
DF_A ( A, a1, a2 ) where A is master and a1, and a2 are detailsDF_B ( B, A ) where B is master and A is a detail
in the above example, and according to the OMG CISQ AFP specification, DF_A (Data Entity A) should be removed and merged into DF_B (Data Entity B) as follows:
DF_B ( B, A, a1, a2 ) where B is Master
In order to resolve this issue, CAST has provided an on-demand option that will ensure that the OMG CISQ AFP specifications are fully adhered to. The option can be activated by:
- executing an SQL script against your CAST Analysis Service
- and then running the Compute option in the CAST Transaction Configuration Center.
Below you will find the SQL script required to activate/deactivate the option for each RDBMS supported by CAST. Note that you will need to modify the scripts as follows:
- Replace TURN_ON with 1 to turn on the option, 0 to turn it off
- Replace TRACE_ON with 1 to turn on the log, 0 to turn it off
declare ret int := 0 ;beginret := SET_TCC_OPTION_DFREORDER( TURN_ON , TRACE_ON );end;
CAST Storage Service
set search_path=db_localselect SET_TCC_OPTION_DFREORDER (TURN_ON , TRACE_ON );
Microsoft SQL Server
exec SET_TCC_OPTION_DFREORDER TURN_ON , TRACE_ON
In what situations will this option work?
- If the master table is referenced by one and only one other Data Entity, then the Data Entity with this master table will be merged with all its details into the other datafunction where the master table is a detail.
- If the master table is referenced more than once as a detail table of other Data Entities, then the master table will be removed from all the other Data Entities' detail lists, and it will remain as a master table.
The following situations are not covered by the option:
- If a cyclical reference exists, such as:
DF_A ( A, B) where A is Master DF_B ( B, A) where B is Master
Impact of activating the option
Applying this option may decrease the total number of Data Entities and as a consequence will cause changes in the computed Function Point values for Data Entities and Transactions.
When the TRACE_ON value is set to "1", logging is saved to the WK_TCC_REORDER_TRACE table in the CAST Analysis Service.
Deactivating the option
If you decide to deactivate the option (i.e. run the relevant script with the value TURN_ON set to "0") then you will need to run the Compute option to undo the changes introduced by activating the option.