GuidelinesToValidateApp

From Semantifi Wiki

Note : This page has to be refined to clearly separate the guidelines and the actual scripts for testing for conformity of items that have been outlined as the guidelines. 

The below script has queries which can be executed against an app to know whether any discrepancies exist wrt the various components of SWI like asset entities, business entities etc.

In the queries given below, replace the application id and the asset id with the actal values of the application and the asset which are to be validated.

-- get the app id
SELECT id AS app_id FROM application WHERE NAME = 'Recovery';

-- check for image of the application (it should not be -1)
SELECT image_id FROM application WHERE id=1203;

-- get the asset id
SELECT id AS asset_id FROM asset WHERE application_id=1203; -- 10065

-- check for duplicates in tables
SELECT t.name FROM tabl t LEFT JOIN asset_entity ae ON t.id = ae.table_id WHERE ae.entity_type='T' AND ae.asset_id=10065
GROUP BY t.name HAVING COUNT(*) > 1;

-- check for duplicates in columns
SELECT c.name FROM colum c LEFT JOIN asset_entity ae ON c.id = ae.column_id
WHERE ae.entity_type='C' AND ae.asset_id=10065
GROUP BY c.name HAVING COUNT(*) > 1;

-- check for duplicates in members
SELECT m.LOOKUP_DESCRIPTION FROM membr m LEFT JOIN asset_entity ae ON m.id = ae.member_id
WHERE ae.entity_type='M' AND ae.asset_id=10065
GROUP BY m.LOOKUP_DESCRIPTION HAVING COUNT(*) > 1;

-- check for the existence of default metrics in the asset
SELECT c.name, c.kdx_data_type FROM default_metric dm, asset_entity ae, colum c WHERE dm.colum_aed_id = ae.id AND ae.column_id = c.id
AND ae.asset_id = 10065;

-- get the unmapped columns
SELECT CONCAT(ta.name, ' : ', co.name) FROM asset_entity asen, colum co, tabl ta WHERE entity_type='C' AND asset_id=10065 AND asen.id
NOT IN
(SELECT ae.id FROM asset_entity ae, mapping m, colum c WHERE ae.id=m.asset_entity_id AND ae.asset_id=10065 AND
ae.entity_type = 'C' AND ae.column_id=c.id) AND asen.table_id=ta.id AND asen.column_id=co.id;

-- get the unmapped members
SELECT CONCAT(ta.name, ' : ', co.name, ' : ', me.lookup_description) FROM asset_entity asen, colum co, tabl ta, membr me
WHERE entity_type='M' AND asset_id=10065 AND asen.id
NOT IN
(SELECT ae.id FROM asset_entity ae, mapping m, colum c, membr b WHERE ae.id=m.asset_entity_id AND ae.asset_id=10065 AND
ae.entity_type = 'M' AND ae.column_id=c.id AND ae.member_id=b.id) AND
asen.table_id=ta.id AND asen.column_id=co.id AND asen.member_id=me.id;

-- get the model id
SELECT model_id FROM application_model_mapping WHERE application_id=1203; -- 1206

-- time frame concepts
SELECT be.id, c.name FROM business_entity be, concept c WHERE be.model_group_id=1206 AND be.type_id=201 AND
be.entity_type='C' AND c.id=be.concept_id;

-- time frame columns (data type should be integer)
SELECT ae.id AS AEID, c.name AS COLNAME, c.data_type AS DATA_TYPE
FROM colum c LEFT JOIN asset_entity ae ON c.id = ae.column_id
WHERE ae.entity_type='C' AND ae.asset_id=10065 AND
(c.name LIKE '%year' || c.name LIKE '%yr' || c.name LIKE '%date' || c.name LIKE '%dt' || c.name LIKE '%qtr' || c.name LIKE '%qr');

-- check if mappings exist for each of the time-frame columns
SELECT * FROM mapping WHERE asset_entity_id IN (
SELECT ae.id FROM colum c LEFT JOIN asset_entity ae ON c.id = ae.column_id
WHERE ae.entity_type='C' AND ae.asset_id=10065 AND
(c.name LIKE '%year' || c.name LIKE '%yr' || c.name LIKE '%date' || c.name LIKE '%dt' || c.name LIKE '%qtr' || c.name LIKE '%qr'));

-- check if all the columns which have been mapped to the MeasurableEntity type concepts, are marked as MEASURE or not, has the ancillary info like data type, format etc
SELECT
m.asset_entity_id as AEDID,
co.name as COLNAME,
co.kdx_data_type as KDX_DATA_TYPE,
co.data_type as DATA_TYPE,
co.data_format as DATA_FORMAT,
co.unit as UNIT,
co.unit_type as UNIT_TYPE
FROM mapping m, asset_entity asen, colum co, business_entity be, concept c
WHERE m.business_entity_id = be.id AND be.model_group_id=1206 AND be.type_id=101 AND
be.entity_type='C' AND c.id=be.concept_id AND m.asset_entity_id=asen.id AND asen.column_id=co.id;

-- for getting all the ri onto terms of relations which occur more then once
-- from the results of the query, check if the word_type values are different, if they are same then mark as duplicate
SELECT r.word FROM ri_onto_term r WHERE r.id > 1000 AND r.model_group_id = 1206 GROUP BY r.word HAVING COUNT(*) > 1;

To Be Done:

- RI duplicates (generated properly - check for old apps), word types are correct?, SFLs are present (check for missing SFLs)
- is properly modelled ? (CRCs and relations)
- abbreviations for instances and their RI Onto Terms
- Cloud components

- instance mapping to multiple members

- Identify speical concept we know like state, city, Companies (not sure how to achive this)

- ?? any other items ??