×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Metadata Query

How to document report object dependencies in the metadata ? by dmcmunn
Posted: 29 Apr 04

Here's a query I've used to get up to speed on a new project via SQL...You can cut and paste the results into your tool of choice and sort things out...

/* SQL Used to generate documentation
 * Server: MYMETADATA
 * Database: MSImd; Schema Owner: MSImd
 * MSI Project Name: MYOLAPDEV
 * Limitation: Only documents up to 10 levels of folders/objects
*/

select o.project_id
     , case o.object_type
          when 1 then 'filter (1)'
          when 2 then 'template (2)'
          when 3 then 'report (3)'
          when 4 then 'metric (4)'
          when 6 then 'autostyle (6)'
          when 8 then 'folder (8)'
          when 10 then 'prompt (10)'
          when 11 then 'function (11)'
          when 12 then 'attribute (12)'
          when 13 then 'fact (13)'
          when 14 then 'hierarchy (14)'
          when 15 then 'table (15)'
          when 21 then 'attribute id (21)'
          when 22 then 'schema (22)'
          when 24 then 'warehouse catalog (24)'
          when 25 then 'warehouse catalog definition (25)'
          when 26 then 'table column (26)'
          when 28 then 'property sets (28)'
          when 39 then 'search (39)'
          when 42 then 'package (42)'
          when 47 then 'consolidations (47)'
          when 52 then 'link (52)'
          when 53 then 'table (53)'
          when 56 then 'drill map (56)'
          when 58 then 'security filter (58)'
          else '(?' + rtrim(cast(o.object_type as char(3))) + '?)'
       end object_type,
       case when o.object_type = 8 then
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
         + '/' + o.object_name
       else
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
       end folder
       , o.object_name
       , o.object_id
       , dod.depn_object_type
       , dod.depn_object_subtype
       , dod.depn_object_name
       , dod.depn_object_id
       , df.folder

  from MSImd.dssmdobjinfo o
 
inner join MSImd.dssmdobjinfo p1
  on o.parent_id = p1.object_id
 and o.project_id = p1.project_id
 
left outer join MSImd.dssmdobjinfo p2
  on p1.parent_id = p2.object_id
 and p1.project_id = p2.project_id
 
left outer join MSImd.dssmdobjinfo p3
  on p2.parent_id = p3.object_id
 and p2.project_id = p3.project_id
 
left outer join MSImd.dssmdobjinfo p4
  on p3.parent_id = p4.object_id
 and p3.project_id = p4.project_id
 
left outer join MSImd.dssmdobjinfo p5
  on p4.parent_id = p5.object_id
 and p4.project_id = p5.project_id
 
left outer join MSImd.dssmdobjinfo p6
  on p5.parent_id = p6.object_id
 and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
  on p6.parent_id = p7.object_id
 and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
  on p7.parent_id = p8.object_id
 and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
  on p8.parent_id = p9.object_id
 and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
  on p9.parent_id = p10.object_id
 and p9.project_id = p10.project_id

inner join
(
select obj.project_id
     , case obj.object_type
          when 1 then 'filter (1)'
          when 2 then 'template (2)'
          when 3 then 'report (3)'
          when 4 then 'metric (4)'
          when 6 then 'autostyle (6)'
          when 8 then 'folder (8)'
          when 10 then 'prompt (10)'
          when 11 then 'function (11)'
          when 12 then 'attribute (12)'
          when 13 then 'fact (13)'
          when 14 then 'hierarchy (14)'
          when 15 then 'table (15)'
          when 21 then 'attribute id (21)'
          when 22 then 'schema (22)'
          when 24 then 'warehouse catalog (24)'
          when 25 then 'warehouse catalog definition (25)'
          when 26 then 'table column (26)'
          when 28 then 'property sets (28)'
          when 39 then 'search (39)'
          when 42 then 'package (42)'
          when 47 then 'consolidations (47)'
          when 52 then 'link (52)'
          when 53 then 'table (53)'
          when 56 then 'drill map (56)'
          when 58 then 'security filter (58)'
          else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
       end container_object_type
    ,  dep.object_id container_object_id
    ,  obj.object_name container_object_name
    ,  case dob.object_type
          when 1 then 'filter (1)'
          when 2 then 'template (2)'
          when 3 then 'report (3)'
          when 4 then 'metric (4)'
          when 6 then 'autostyle (6)'
          when 8 then 'folder (8)'
          when 10 then 'prompt (10)'
          when 11 then 'function (11)'
          when 12 then 'attribute (12)'
          when 13 then 'fact (13)'
          when 14 then 'hierarchy (14)'
          when 15 then 'table (15)'
          when 21 then 'attribute id (21)'
          when 22 then 'schema (22)'
          when 24 then 'warehouse catalog (24)'
          when 25 then 'warehouse catalog definition (25)'
          when 26 then 'table column (26)'
          when 28 then 'property sets (28)'
          when 39 then 'search (39)'
          when 42 then 'package (42)'
          when 47 then 'consolidations (47)'
          when 52 then 'link (52)'
          when 53 then 'table (53)'
          when 56 then 'drill map (56)'
          when 58 then 'security filter (58)'
          else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
       end depn_object_type
    ,  dob.subtype depn_object_subtype
    ,  dep.depn_objid depn_object_id
    ,  dob.object_name depn_object_name

from MSImd.dssmdobjdepn dep

inner join MSImd.dssmdobjinfo pro
   on dep.project_id = pro.project_id
  and pro.parent_id = '00000000000000000000000000000000'
  and pro.object_name = 'MYOLAPDEV'
  and pro.object_type = 8

inner join MSImd.dssmdobjinfo obj
   on dep.project_id = obj.project_id
  and dep.object_id = obj.object_id
  and dep.object_type = obj.object_type

inner join MSImd.dssmdobjinfo dob
   on dep.project_id = dob.project_id
  and dep.depn_objid = dob.object_id
  and dep.depnobj_type = dob.object_type

where obj.object_type in (1, 2, 3, 4, 8, 10, 12, 13, 56)
and dob.subtype not in (1026, 2816)
) dod
   on o.project_id = dod.project_id
  and o.object_id = dod.container_object_id

inner join
(
select o.project_id
     , case when o.object_type = 8 then
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
         + '/' + o.object_name
       else
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
       end folder
       , o.object_id

  from MSImd.dssmdobjinfo o
 
inner join MSImd.dssmdobjinfo p1
  on o.parent_id = p1.object_id
 and o.project_id = p1.project_id
 
left outer join MSImd.dssmdobjinfo p2
  on p1.parent_id = p2.object_id
 and p1.project_id = p2.project_id
 
left outer join MSImd.dssmdobjinfo p3
  on p2.parent_id = p3.object_id
 and p2.project_id = p3.project_id
 
left outer join MSImd.dssmdobjinfo p4
  on p3.parent_id = p4.object_id
 and p3.project_id = p4.project_id
 
left outer join MSImd.dssmdobjinfo p5
  on p4.parent_id = p5.object_id
 and p4.project_id = p5.project_id
 
left outer join MSImd.dssmdobjinfo p6
  on p5.parent_id = p6.object_id
 and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
  on p6.parent_id = p7.object_id
 and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
  on p7.parent_id = p8.object_id
 and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
  on p8.parent_id = p9.object_id
 and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
  on p9.parent_id = p10.object_id
 and p9.project_id = p10.project_id

where o.project_id = (select project_id
                        from MSImd.dssmdobjinfo
                       where object_type = 8
                         and object_name = 'MYOLAPDEV')
) df
  on  dod.project_id       = df.project_id
 and  dod.depn_object_id   = df.object_id

where o.project_id = (select project_id
                        from MSImd.dssmdobjinfo
                       where object_type = 8
                         and object_name = 'MYOLAPDEV')
and o.object_type = 3      
-- the following filters report selection to those found only these two folders with these GUIDs:
and ( 'FOLDER1GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id)
 or   'FOLDER2GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id))
         
order by o.object_type
       , p10.object_name
       , p9.object_name
       , p8.object_name
       , p7.object_name
       , p6.object_name
       , p5.object_name
       , p4.object_name
       , p3.object_name
       , p2.object_name
       , p1.object_name
       , o.object_name
       , dod.depn_object_type
       , dod.depn_object_name
       , dod.depn_object_id

 
 

 
 

Here's some SQL Server 2000 SQL I've used to perform recursive dependency analysis of a given report...

/* SQL to rebuild the project object relationship table */
truncate table MSImd.dssmd_x

insert MSImd.dssmd_x
select o.project_id
     , o.object_type object_type_no
     , case o.object_type
          when 1 then 'filter (1)'
          when 2 then 'template (2)'
          when 3 then 'report (3)'
          when 4 then 'metric (4)'
          when 6 then 'autostyle (6)'
          when 8 then 'folder (8)'
          when 10 then 'prompt (10)'
          when 11 then 'function (11)'
          when 12 then 'attribute (12)'
          when 13 then 'fact (13)'
          when 14 then 'hierarchy (14)'
          when 15 then 'available table (15)'
          when 21 then 'attribute id (21)'
          when 22 then 'schema (22)'
          when 24 then 'warehouse catalog (24)'
          when 25 then 'warehouse catalog definition (25)'
          when 26 then 'table column (26)'
          when 28 then 'property sets (28)'
          when 39 then 'search (39)'
          when 42 then 'package (42)'
          when 47 then 'consolidations (47)'
          when 52 then 'link (52)'
          when 53 then 'architected table (53)'
          when 56 then 'drill map (56)'
          when 58 then 'security filter (58)'
          else '(?' + rtrim(cast(o.object_type as char(3))) + '?)'
       end object_type,
       case when o.object_type = 8 then
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
         + '/' + o.object_name
       else
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
       end object_folder
       , o.object_name
       , o.object_id
       , dod.depn_object_type_no
       , dod.depn_object_type
       , dod.depn_object_subtype
       , dod.depn_object_name
       , dod.depn_object_id
       , df.folder depn_object_folder
  
--  into MSImd.dssmd_x

  from MSImd.dssmdobjinfo o
 
inner join MSImd.dssmdobjinfo p1
  on o.parent_id = p1.object_id
 and o.project_id = p1.project_id
 
left outer join MSImd.dssmdobjinfo p2
  on p1.parent_id = p2.object_id
 and p1.project_id = p2.project_id
 
left outer join MSImd.dssmdobjinfo p3
  on p2.parent_id = p3.object_id
 and p2.project_id = p3.project_id
 
left outer join MSImd.dssmdobjinfo p4
  on p3.parent_id = p4.object_id
 and p3.project_id = p4.project_id
 
left outer join MSImd.dssmdobjinfo p5
  on p4.parent_id = p5.object_id
 and p4.project_id = p5.project_id
 
left outer join MSImd.dssmdobjinfo p6
  on p5.parent_id = p6.object_id
 and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
  on p6.parent_id = p7.object_id
 and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
  on p7.parent_id = p8.object_id
 and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
  on p8.parent_id = p9.object_id
 and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
  on p9.parent_id = p10.object_id
 and p9.project_id = p10.project_id

inner join
(
select obj.project_id
     , case obj.object_type
          when 1 then 'filter (1)'
          when 2 then 'template (2)'
          when 3 then 'report (3)'
          when 4 then 'metric (4)'
          when 6 then 'autostyle (6)'
          when 8 then 'folder (8)'
          when 10 then 'prompt (10)'
          when 11 then 'function (11)'
          when 12 then 'attribute (12)'
          when 13 then 'fact (13)'
          when 14 then 'hierarchy (14)'
          when 15 then 'available table (15)'
          when 21 then 'attribute id (21)'
          when 22 then 'schema (22)'
          when 24 then 'warehouse catalog (24)'
          when 25 then 'warehouse catalog definition (25)'
          when 26 then 'table column (26)'
          when 28 then 'property sets (28)'
          when 39 then 'search (39)'
          when 42 then 'package (42)'
          when 47 then 'consolidations (47)'
          when 52 then 'link (52)'
          when 53 then 'architected table (53)'
          when 56 then 'drill map (56)'
          when 58 then 'security filter (58)'
          else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
       end container_object_type
    ,  dep.object_id container_object_id
    ,  obj.object_name container_object_name
    ,  dob.object_type depn_object_type_no
    ,  case dob.object_type
          when 1 then 'filter (1)'
          when 2 then 'template (2)'
          when 3 then 'report (3)'
          when 4 then 'metric (4)'
          when 6 then 'autostyle (6)'
          when 8 then 'folder (8)'
          when 10 then 'prompt (10)'
          when 11 then 'function (11)'
          when 12 then 'attribute (12)'
          when 13 then 'fact (13)'
          when 14 then 'hierarchy (14)'
          when 15 then 'available table (15)'
          when 21 then 'attribute id (21)'
          when 22 then 'schema (22)'
          when 24 then 'warehouse catalog (24)'
          when 25 then 'warehouse catalog definition (25)'
          when 26 then 'table column (26)'
          when 28 then 'property sets (28)'
          when 39 then 'search (39)'
          when 42 then 'package (42)'
          when 47 then 'consolidations (47)'
          when 52 then 'link (52)'
          when 53 then 'architected table (53)'
          when 56 then 'drill map (56)'
          when 58 then 'security filter (58)'
          else '(?' + rtrim(cast(dob.object_type as char(3))) + '?)'
       end depn_object_type
    ,  dob.subtype depn_object_subtype
    ,  dep.depn_objid depn_object_id
    ,  dob.object_name depn_object_name

from MSImd.dssmdobjdepn dep

inner join MSImd.dssmdobjinfo pro
   on dep.project_id = pro.project_id
  and pro.parent_id = '00000000000000000000000000000000'
  and pro.object_name = 'MYOLAPDEV'
  and pro.object_type = 8

inner join MSImd.dssmdobjinfo obj
   on dep.project_id = obj.project_id
  and dep.object_id = obj.object_id
  and dep.object_type = obj.object_type

inner join MSImd.dssmdobjinfo dob
   on dep.project_id = dob.project_id
  and dep.depn_objid = dob.object_id
  and dep.depnobj_type = dob.object_type

where obj.object_type in (1, 2, 3, 4, 8, 10, 12, 13, 56)
and dob.subtype not in (1026, 2816)
) dod
   on o.project_id = dod.project_id
  and o.object_id = dod.container_object_id

inner join
(
select o.project_id
     , case when o.object_type = 8 then
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
         + '/' + o.object_name
       else
         isnull(p10.object_name,'')
         + '/' + isnull(p9.object_name,'')
         + '/' + isnull(p8.object_name,'')
         + '/' + isnull(p7.object_name,'')
         + '/' + isnull(p6.object_name,'')
         + '/' + isnull(p5.object_name,'')
         + '/' + isnull(p4.object_name,'')
         + '/' + isnull(p3.object_name,'')
         + '/' + isnull(p2.object_name,'')
         + '/' + isnull(p1.object_name,'')
       end folder
       , o.object_id

  from MSImd.dssmdobjinfo o
 
inner join MSImd.dssmdobjinfo p1
  on o.parent_id = p1.object_id
 and o.project_id = p1.project_id
 
left outer join MSImd.dssmdobjinfo p2
  on p1.parent_id = p2.object_id
 and p1.project_id = p2.project_id
 
left outer join MSImd.dssmdobjinfo p3
  on p2.parent_id = p3.object_id
 and p2.project_id = p3.project_id
 
left outer join MSImd.dssmdobjinfo p4
  on p3.parent_id = p4.object_id
 and p3.project_id = p4.project_id
 
left outer join MSImd.dssmdobjinfo p5
  on p4.parent_id = p5.object_id
 and p4.project_id = p5.project_id
 
left outer join MSImd.dssmdobjinfo p6
  on p5.parent_id = p6.object_id
 and p5.project_id = p6.project_id

left outer join MSImd.dssmdobjinfo p7
  on p6.parent_id = p7.object_id
 and p6.project_id = p7.project_id

left outer join MSImd.dssmdobjinfo p8
  on p7.parent_id = p8.object_id
 and p7.project_id = p8.project_id

left outer join MSImd.dssmdobjinfo p9
  on p8.parent_id = p9.object_id
 and p8.project_id = p9.project_id

left outer join MSImd.dssmdobjinfo p10
  on p9.parent_id = p10.object_id
 and p9.project_id = p10.project_id

where o.project_id = (select project_id
                        from MSImd.dssmdobjinfo
                       where object_type = 8
                         and object_name = 'MYOLAPDEV')
) df
  on  dod.project_id       = df.project_id
 and  dod.depn_object_id   = df.object_id

where o.project_id = (select project_id
                        from MSImd.dssmdobjinfo
                       where object_type = 8
                         and object_name = 'MYOLAPDEV')
--and o.object_type = 3      
-- the following chooses reports found only these two folders represented by the GUIDs below
--and ( 'FOLDER1GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id)
-- or   'FOLDER2GUIDGOESHERE' in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id, p7.parent_id, p8.parent_id, p9.parent_id, p10.parent_id))



--Step #1
truncate table MSImd.MSI_objx

--Step #2 plug in guid of report to document......................>
exec MSImd.asp_GenObjDependents @sParentGUID='REPORTGUIDGOESHERE', @iLevel=1

--Step #3 get unique list of objects dependent on the original parent
select
  a11.depn_object_type_no
, a11.depn_object_type
, a11.depn_object_name
, a11.depn_object_folder
, a11.depn_object_id
--, a11.seq_no
from
(
select space((y.nesting_level - 1) * 2) + cast(y.nesting_level as char(6)) nesting_level
 , obj.object_type_no
 , obj.object_type
 , obj.object_folder
 , obj.object_name
 , obj.object_id
 , obj.depn_object_type_no
 , obj.depn_object_type
 , obj.depn_object_name
 , obj.depn_object_id
 , obj.depn_object_folder
 , y.seq_no
  from MSImd.dssmd_x obj
 inner join MSImd.MSI_objx y
    on obj.object_id = y.parent_object_id
   and obj.depn_object_id = y.depn_object_id

where obj.object_id + obj.depn_object_id in
  (select x.parent_object_id + x.depn_object_id
     from MSImd.MSI_objx x
 group by x.parent_object_id, x.depn_object_id)
and obj.depn_object_type_no in (12,1,10,15,13,4)
) a11
group by
  a11.depn_object_type_no
, a11.depn_object_type
, a11.depn_object_name
, a11.depn_object_folder
, a11.depn_object_id
order by
  a11.depn_object_type_no
, a11.depn_object_type
, a11.depn_object_name
, a11.depn_object_folder
, a11.depn_object_id









CREATE PROCEDURE MSImd.asp_GenObjDependents
  (
  @sParentGUID varchar(32)
, @iLevel int
  )
 AS

/* set an absolute maximum number of levels to drill down as a runaway protection */
if @iLevel > 16 return

if not exists (select * from dbo.sysobjects where id = object_id(N'[MSImd].[MSI_objx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    create table MSImd.MSI_objx (
     parent_object_id char(32) null,
     depn_object_id   char(32) null,
     nesting_level int null,
     seq_no int identity (1,1)
     );
END

declare @pobj_id varchar(32)
declare @dobj_id varchar(32)
declare @dobj_type integer
declare @sSQL varchar(110)

declare curParentObjects CURSOR LOCAL FAST_FORWARD FOR
select object_id, depn_object_id, depn_object_type_no from MSImd.dssmd_x where object_id = @sParentGUID

OPEN curParentObjects

FETCH NEXT FROM curParentObjects
INTO @pobj_id, @dobj_id, @dobj_type
  
WHILE @@FETCH_STATUS = 0
BEGIN

  insert MSImd.MSI_objx (parent_object_id, depn_object_id, nesting_level)
    values (@pobj_id, @dobj_id, @iLevel);

   /* only recurse through: filters(1), templates(2), reports(3), metrics(4), prompts(10), attributes(12), facts(13) and drill maps(56) */
  if @dobj_type in (1,2,3,4,10,12,13,56)
    BEGIN
      set @sSQL =   "MSImd.asp_GenObjDependents @sParentGUID='" + @dobj_id + "'" + ", @iLevel=" + ltrim(rtrim(cast(@iLevel + 1 as varchar(8))))
      exec  (@sSQL)
    END
 
  FETCH NEXT FROM curParentObjects
  INTO @pobj_id, @dobj_id, @dobj_type
END
CLOSE curParentObjects
DEALLOCATE curParentObjects
GO

 
 
 
Here's a general recursive report object documentation "how-to" along with a query for returning the results indented based on level of recursion where the dependency was found...

--Step #1
truncate table MSImd.MSI_objx

--Step #2 plug in guid of report to document......................>
exec MSImd.asp_GenObjDependents @sParentGUID='REPORTGUIDGOESHERE', @iLevel=1

--Step #3
select space((y.nesting_level - 1) * 2) + cast(y.nesting_level as char(6)), obj.*, y.seq_no
  from MSImd.dssmd_x obj
 inner join MSImd.MSI_objx y
    on obj.object_id = y.parent_object_id
   and obj.depn_object_id = y.depn_object_id

where obj.object_id + obj.depn_object_id in
  (select x.parent_object_id + x.depn_object_id
     from MSImd.MSI_objx x
 group by x.parent_object_id, x.depn_object_id)
and obj.depn_object_type in ('attribute (12)', 'filter (1)', 'prompt (10)', 'table (15)', 'fact (13)', 'metric (4)')
order by y.seq_no
 
 

Back to MicroStrategy, Inc. solutions FAQ Index
Back to MicroStrategy, Inc. solutions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close