Useful Metadata Queries - Microstrategy

Object ID in MSTR 9.2:

Object id in MSTR 9.2 metadata tables has been stored as Unique identifier. So we cannot use Metadata object id in MSTR Desktop and vice versa.

But we have a built in functions to convert this.

MSTR Metadata Object ID to Desktop Object ID:

dbo.fn_UniqueidentifierToCharMsTR ()

MSTR Desktop Object ID to MSTR Metadata Object ID

dbo.fn_CharToUniqueidentifier()

Metadata query to get user and group details Excluding Everyone group:

select GROUPNAME.object_name [Customer_group],USERNAME.object_name [User_name] from dbo.DSSMDOBJINFO GROUPNAME

      join

            (select object_name,OBJDEPN.depn_objid from dbo.DSSMDOBJINFO b

                   join

                    (select distinct Object_id,depn_objid from dbo.DSSMDOBJDEPN

                     where object_id in (select object_id from dbo.DSSMDUSRACCT where isgroup=0)

                     and depn_objid <> (select object_id from dbo.DSSMDOBJINFO where object_name ='Everyone')) OBJDEPN

             on OBJDEPN.object_id = b.object_id) USERNAME

on USERNAME.depn_objid =GROUPNAME.object_id

where GROUPNAME.object_name <> 'Everyone'

order by GROUPNAME.object_name

Metadata query to get all users:

select distinct login from dbo.DSSMDUSRACCT where isgroup =0

Metadata Query to get number of users:

select count (distinct login) from dbo.DSSMDUSRACCT where isgroup =0

Metadata Query to List all object which is having access to particular group:

 select Object_name,Object_id,Object_type from dbo.DSSMDOBJSECU
where Object_id in (
  select Object_id from dbo.DSSMDOBJSECU
    where trust_id = (select Object_id from  dbo.DSSMDOBJINFO where object_name like ('User GROUP'))
and project_id ='Project ID')

Metadata Query to get Object Path from Object_id:

create table:
--------------------------
create table #temp_object_id ([PROJECT_ID] [uniqueidentifier] NOT NULL,[OBJECT_ID] [uniqueidentifier] NOT NULL, object_path varchar(max));

insert records:
--------------------------
EX:

insert into #temp_object_id (PROJECT_ID,OBJECT_ID) values( 'PROJECT_ID','OBJECT_ID');


how to do :
--------------------------
play with excel


procedure to generate result:
------------------------------

begin

declare @temp_project_id uniqueidentifier

declare @temp_parent_id uniqueidentifier

declare @temp_object_id uniqueidentifier

declare @orig_object_id uniqueidentifier

declare @temp_object_name nvarchar(max)

declare @object_name nvarchar(250)

declare @Count Int

declare @loop_check int

declare @LoopCount Int

declare @sql varchar(max)


Select Identity(int,1,1) ID, PROJECT_ID, OBJECT_ID into #Temp from #temp_object_id

Select @Count = @@RowCount

Set @LoopCount = 1

select * from #Temp

While @LoopCount <= @Count
Begin


Select @temp_project_id=PROJECT_ID,@temp_object_id=OBJECT_ID,@orig_object_id=OBJECT_ID from #Temp Where ID = @LoopCount

--print '@temp_project_id=' + cast( @temp_project_id as varchar(250))

--print '@temp_object_id' + cast( @temp_object_id as varchar(250))

set @loop_check =1

--'00000000-0000-0000-0000-000000000000'

set @temp_object_name = ''

While (@loop_check <> 0)
Begin        
      
      
       select @temp_parent_id=PARENT_ID,@object_name=OBJECT_NAME from DSSMDOBJINFO where OBJECT_ID=@temp_object_id and PROJECT_ID=@temp_project_id
      
     
      
      
       --print '@temp_parent_id=' + cast( @temp_parent_id as varchar(250))
      
    --print '@object_name' + @object_name
   
    if ( @orig_object_id != @temp_object_id )      
         set @temp_object_name = @object_name + '/' + @temp_object_name
      
       --print @temp_object_name
      
        set @temp_object_id = @temp_parent_id
      
       if (@temp_parent_id = '00000000-0000-0000-0000-000000000000')
               break
      
End

print @temp_object_name

update #temp_object_id set object_path=@temp_object_name where
PROJECT_ID=@temp_project_id and OBJECT_ID=@orig_object_id

Set @LoopCount=@LoopCount + 1
           
End

Drop table #Temp

End


result from temp table :
--------------------------


select * from  #temp_object_id