This page provide you the definition of ghost objects, how to identify them and how to clean them.
The following graph represent the simpliffied 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 Analisis unit parent, in the other hand each analysis unit may have one or several Projects child, and each Project have one or several objects child.
Fore some reason this parent to child relationship can be broken. We differentiate 2 different cases:
Broken links between analysis unit and project(s):
This act will result in Ghost project, therefore ghost objects, In order to identify and clean up the list of ghost projects and ghost objects, please refer to Ghost Projects
- Broken links between project and object(s):
This act will result in ghost objects, In order to identify and clean up the list of ghost objects, please refer to Ghost objects
After cleaning up any ghost objects and projects, run a new analysis and snapshot to update all the information in the CAST databases.
Release | Yes/No |
|---|---|
| 8.3.x |  |
| 8.2.x | |
| 8.1.x | |
| 8.0.x | |
| 7.3.x | |
RDBMS | Yes/No |
|---|---|
| Oracle Server | |
| Microsoft SQL Server |  |
| CSS3 | |
| CSS2 | |
Ghost Projects
The following query will work on CSS and oracle and when run on a knowledge base will list the set of ghost projects that are the result of broken links between analysis unit and project:
select p.IdPro,
k.KeyNam,
t.IdTyp,
t.TypNam
from (select distinct op.IdPro
from ObjPro op
union
select distinct a.IdPro
from Acc a
) p
join Keys k
on k.IdKey = p.IdPro
and k.ObjTyp not in (306, 527)
join Typ t
on t.IdTyp = k.ObjTyp
where p.IdPro not in (select distinct pd.IdPro
from AnaPro ap
join ProDep pd
on pd.IdProMain = ap.IdPro
join (select distinct op.IdPro
from ObjPro op
union
select distinct a.IdPro
from Acc a
) p
on p.IdPro = pd.IdPro
);
As an example you can find:
In order to clean up the list of Ghost Projects, please execute the following query on you knowledge base. Note that this clean up is irreversible for that, 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.
Query for CSS:
select PROJECT_DELETE(op.IdPro)
from (select distinct op.IdPro
from ObjPro op
union
select distinct a.IdPro
from Acc a
) op
join Keys k
on k.IdKey = op.IdPro
and k.ObjTyp not in (306, 527)
where op.IdPro not in (select distinct pd.IdPro
from AnaPro ap
join ProDep pd
on pd.IdProMain = ap.IdPro
join (select distinct op.IdPro
from ObjPro op
union
select distinct a.IdPro
from Acc a
) p
on p.IdPro = pd.IdPro
);
Query for Oracle - for each project_id identified in the query above run the following on the KB:
DECLARE
Result INT;
BEGIN
FOR d IN
(SELECT p.IdPro
FROM (SELECT DISTINCT op.IdPro
FROM ObjPro op
UNION
SELECT DISTINCT a.IdPro
FROM Acc a
)
p
JOIN Keys k
ON k.IdKey = p.IdPro
AND k.ObjTyp NOT IN (306,
527)
JOIN Typ t
ON t.IdTyp = k.ObjTyp
WHERE p.IdPro NOT IN
(SELECT DISTINCT pd.IdPro
FROM AnaPro ap
JOIN ProDep pd
ON pd.IdProMain = ap.IdPro
JOIN
(SELECT DISTINCT op.IdPro
FROM ObjPro op
UNION
SELECT DISTINCT a.IdPro
FROM Acc a
)
p
ON p.IdPro = pd.IdPro
)
)
LOOP
Result := PROJECT_DELETE(d.IdPro);
COMMIT;
END LOOP;
END;
Ghost Objects
The following query run on the KB on Oracle or CSS will list the set of ghost objects that are the result of broken links between project and object:
select k.IdKey,
k.KeyNam,
t.IdTyp,
t.TypNam
from Keys k
join Typ t
on t.IdTyp = k.ObjTyp
join TypCat tc
on tc.IdTyp = t.IdTyp
and tc.IdCatParent = 2500
where k.ObjTyp not in (237, 355)
and not exists (select 1
from ObjPro op
where op.IdObj = k.IdKey)
order by k.IdKey;
In order to clean up the list of Ghost objects, please execute the following query on you knowledge base. Note that this clean up is irreversible for that, we advice you to take a backup of your local, central and management base (even if the cleanup is done on only the knowledge base):
For CSS:
Create temporary table WK_ObjToDelete
create temporary table WK_ObjToDelete (IDOBJ INT NOT NULL);
Populate the temporary table with the ghost objects:
insert into WK_ObjToDelete (IDOBJ) select k.IdKey from Keys k join Typ t on t.IdTyp = k.ObjTyp join TypCat tc on tc.IdTyp = t.IdTyp and tc.IdCatParent = 2500 where k.ObjTyp not in (237, 355) and not exists (select 1 from ObjPro op where op.IdObj = k.IdKey);Clean the set of ghost objects:
select INT_OBJECT_DEL();
Drop the temporary table
drop table WK_ObjToDelete;
For Oracle:
Create temporary table WK_ObjToDelete on the KB:
create global temporary table WK_ObjToDelete (IDOBJ INT NOT NULL) ON COMMIT PRESERVE ROWS;
Populate the temporary table with the ghost objects on the KB:
insert into WK_ObjToDelete (IDOBJ) select k.IdKey from Keys k join Typ t on t.IdTyp = k.ObjTyp join TypCat tc on tc.IdTyp = t.IdTyp and tc.IdCatParent = 2500 where k.ObjTyp not in (237, 355) and not exists (select 1 from ObjPro op where op.IdObj = k.IdKey); commit;Clean the set of ghost objects:
SET SERVEROUTPUT ON EXEC DBMS_OUTPUT.PUT_LINE(INT_OBJECT_DEL());
Drop the temporary table
Truncate table WK_ObjToDelete; drop table WK_ObjToDelete;
After cleaning up any ghost objects and projects, run a new analysis and snapshot to update all the information in the CAST databases.
Impacts -
Data Functions and Transactions Functions will be reduced if the removed ghost object was part of this list
