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 (As an example of root cause you can refer to Urgent Notice - CAST AIP - Risk and impacts of synchronization when restoring only knowledge base or central schema instead of the entire triplet - 4th November 2016), this parent to child relationship can be broken. We differentiate 2 different cases:

  1. 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

  2. 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(tick)
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
7.3.x(tick)
RDBMS
Yes/No
Oracle Server(tick)
Microsoft SQL Server (question)
CSS3(tick)
CSS2(tick)

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:

 978145;"Sources_SHELL_13540";1000001;"universalProject"

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;

As an example you can find:
 437;"what_ordered.gif";274;"CAST_Web_File"

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:

  1. Create temporary table WK_ObjToDelete

    create  temporary table WK_ObjToDelete (IDOBJ INT NOT NULL);
  2. 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);
  3. Clean the set of ghost objects:

    select INT_OBJECT_DEL();
  4. Drop the temporary table

    drop table WK_ObjToDelete;

For Oracle:

  1. Create temporary table WK_ObjToDelete on the KB:
     

    create global temporary table WK_ObjToDelete (IDOBJ INT NOT NULL) ON COMMIT PRESERVE ROWS;


  2. 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;


  3. Clean the set of ghost objects:
     

    SET SERVEROUTPUT ON
    
    EXEC DBMS_OUTPUT.PUT_LINE(INT_OBJECT_DEL());


  4. 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