Tuesday, 10 January 2012

Useful Metadata Queries

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
 
 


9 comments:

  1. Hi Sankaran,

    The same can be acheived by the below metadata query as well.

    WITH Parent AS
    (
    SELECT
    OBJECT_ID as OBJECT_ID,
    Parent_ID as Parent_ID,
    object_type as object_type,
    cast(OBJECT_NAME as varchar(max)) as OBJECT_NAME
    FROM
    DSSMDOBJINFO
    WHERE PARENT_ID = '00000000-0000-0000-0000-000000000000'
    --AND PROJECT_ID = '437F63F0-8EC3-47F1-A328-6266F2E28836'

    UNION ALL

    SELECT
    TH.OBJECT_ID,
    TH.PARENT_ID,
    th.object_type,
    CONVERT(varchar(max), Parent.OBJECT_NAME + '/' + TH.OBJECT_NAME) AS Path
    FROM
    DSSMDOBJINFO TH
    INNER JOIN
    Parent
    ON
    Parent.object_ID = TH.PARENT_ID
    )
    SELECT distinct OBJECT_NAME FROM Parent
    where object_type = 3
    and object_id = dbo.fn_CharToUniqueidentifier('FBF09F3F4709EE9C419ECDBA5FFAAE82')

    Thanks,
    Reginold.

    ReplyDelete
  2. Hi..I am Mark...thank u for sharing such good and valuable information on
    online training for Micrtostrategy

    ...

    ReplyDelete
  3. Thank you very much for your good information.
    As I noted in the ... given information is very usefull to every student who ever want to learn about
    Microstrategy Online Training





    ReplyDelete
  4. Your given most of the use full information..The Sales and Distribution (SAP SD) consists of all master data, system configuration, and
    transactions to complete the Order to Cash process.
    It includes the following information and processes.SAP SD TRAINING IN A SIMPLE WAY.

    sap sd online training

    ReplyDelete
  5. select c.object_name [Group], a.object_name [User]
    from dssmdobjinfo a
    inner join dssmdobjdepn b
    on b.object_id = a.object_id
    inner join dssmdobjinfo c
    on b.depn_objid = c.object_id
    where a.object_type = 34
    and a.SUBTYPE = 8704
    and c.object_name <> 'Everyone'
    order by 1

    ReplyDelete
  6. Does object_id ever changes, when the report is being overwritten with edits?
    Once report is generated, does MS has any ID which remains unique until the life of the report?

    ReplyDelete
    Replies
    1. yes, Only Version ID Keep changes for every changes in that object

      Delete
  7. Hi Sankaran,

    Thanks for this article! I've some doubt, hope you can clarify.

    This table DSSMDOBJDEPN stores data of an Object and it's components and the column names are the ones which have confused me. The DEPN_OBJID should be something COMP_OBJID.
    I want to find all the reports which are dependent or affected by a table(s). If I try the following SQL, the result set is empty but if I check through desktop, I get a bunch of reports when I search for dependents of a table recursively:

    SELECT * FROM
    (select I.PROJECT_ID PROJECT_ID,D.DEPN_OBJID OBJECT_ID
    from DSSMDOBJINFO I
    join DSSMDOBJDEPN D
    ON ( I.OBJECT_ID = D.OBJECT_ID
    AND I.PROJECT_ID = D.PROJECT_ID)
    where I.PROJECT_ID = '8E0D81C647128556F0BF6EB0812AB739'
    AND I. OBJECT_ID = '3CDF74D446F4BA9D209D2185DC74A35F'
    AND D.DEPNOBJ_TYPE NOT IN (11,26,53)
    ) A
    JOIN DSSMDOBJDEPN D1
    ON (A.PROJECT_ID = D1.PROJECT_ID
    AND A.OBJECT_ID = D1.OBJECT_ID)
    WHERE D1.DEPNOBJ_TYPE = 3

    I am missing something hope you can help.

    TIA!
    Ashish

    ReplyDelete
  8. hi
    i read your post this post gives a very better idea on sap sd. I will recommend your site for freshers and starters, they will get a brief information on sd. I really feeling great after reading this article, I am a sap sd professional I give sap sd online training . Even I did not expect this depth. I will be regular rss reader from now. Please make updates regularly.
    Thanks and regards

    ReplyDelete