MicroStrategy Metadata Queries -Part 1

Hello Friends, Welcome back ..It’s been almost long time since I’ve written a new post.The main reason for that is, things have been changing very fast for me personally and professionally.  I’ve always had a particular approach and belief to BI, but it’s really started to take shape and become clear over the last few months.  Feeling rejuvenated after the break. 

Here is my new experience with metadata queries to find out the object Path by using the object GUID. 

As MicroStrategy Metadata table is not storing the object path directly in any table , however we can find the object ID and Object Name from DSSMDOBJINFO table and then we can find the parent GUID from object ID and then we can search for object name by using the Parent ID . The same can be found by using the recursive query. It is using the concept of hierarchical data in Oracle databases using recursive subquery factoring. 

*************Recursive Queries in Oracle***********************

WITH Parent(object_id,Parent_ID,object_type,OBJECT_NAME) AS

(

SELECT

OBJECT_ID as OBJECT_ID,

Parent_ID as Parent_ID,

object_type as object_type,

OBJECT_NAME as OBJECT_NAME

FROM DSSMDOBJINFO

WHERE PARENT_ID = '00000000000000000000000000000000' --Parent_ID for root folder(/project/public object/Reports/..)

and PROJECT_ID = 'XXX' --Change the project GUID

UNION ALL

--Recursive members to find out the parent ID .

SELECT

TH.OBJECT_ID,

TH.PARENT_ID,

th.object_type,

(Parent.OBJECT_NAME || '\' || TH.OBJECT_NAME) as Path

FROM DSSMDOBJINFO TH

INNER JOIN Parent ON Parent.object_ID = TH.PARENT_ID  

where PROJECT_ID = 'XXX' ---Change Project GUID

)

SELECT distinct object_id,OBJECT_NAME FROM Parent 

where object_type = 3 

and  object_ID='XXX' ---ObjectGUID to search 

;