This page provides you with the definition of ghost properties, how to identify them and how to clean them.
The following graph represent the simplified parenthood morphology of the objects in the knowledge base (Analysis Service database).
On one hand each object in the knowledge base have one and only one project parent that have one and only one Analysis unit parent, on the other hand each analysis unit may have one or several Projects child, and each Project have one or several objects child.
Each object may have one or several properties, and each property refers to one and only one object.
Fore some reason (As an example of root cause, there is manual deletion of entries from table keys), the properties can remain in database whereas their owner object has been deleted.
This act will result in ghost properties, In order to identify and clean up the list of ghost properties, please refer to 243193705
Release | Yes/No |
---|---|
8.3.x | |
8.2.x |
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS |
Ghost Properties
The following query, will list the set of ghost properties (properties whose owner object does not exist in database)
SELECT IDKEY FROM OBJECTS O JOIN ( SELECT DISTINCT oi.idobj FROM objinf oi LEFT JOIN keys k1 ON k1.idkey=oi.idobj WHERE k1.idkey IS NULL UNION ALL SELECT DISTINCT od.idobj FROM objdsc od LEFT JOIN keys k2 ON k2.idkey=od.idobj WHERE k2.idkey IS NULL ) objprop ON OBJPROP.IDOBJ=O.IDKEY;
As an example you can find:
idkey
-------
123456789
578462125
In order to clean up the list of Ghost Properties, please execute the following queries on you knowledge base.
Note that this clean up is irreversible, so we advice you to take a backup of your local (Analysis Service database), central (Dashboard Service database) and management base (Management Service database) even if the cleanup is done on only the knowledge base.
CREATE TEMPORARY TABLE WK_ObjToDelete AS SELECT IDKEY AS IDOBJ FROM OBJECTS O JOIN ( SELECT DISTINCT oi.idobj FROM objinf oi LEFT JOIN keys k1 ON k1.idkey=oi.idobj WHERE k1.idkey IS NULL UNION ALL SELECT DISTINCT od.idobj FROM objdsc od LEFT JOIN keys k2 ON k2.idkey=od.idobj WHERE k2.idkey IS NULL ) objprop ON OBJPROP.IDOBJ=O.IDKEY; INSERT INTO keys ( idkey , keynam , keytyp , keysubtyp , keyclass , keyprop , objtyp , idusrdevpro , keydevdat , keydevvlddat, status , sqlowner ) SELECT idkey, '' , 'K' , 1,1,1, objtyp, 1 , now() , now() , 1 , 'O' FROM objects WHERE idkey IN ( SELECT DISTINCT idobj FROM wk_ObjToDelete ); SELECT INT_OBJECT_DEL(); DROP TABLE WK_ObjToDelete;
Ticket # 8216