This query returns all universe objects used in a Web Intelligence Report. Notice the where condition filter on Universe Name.
BI4 & XI3, Oracle / SQL Server
SELECT * FROM ( SELECT unvs.cmsid, unvs.universeid, uo.objectid, uc.id AS "CLASSID", folders.folderpathcomplete, unvs.universename AS "UNIVERSENAME", uc.classname AS "CLASSNAME", uo.objectname AS "OBJECTNAME", uc.classlineage AS "LINEAGE", WDP.NAME AS "DATAPROVIDERNAME", WDP.id AS "DATAPROVIDERID", WD.objectid AS "SI_ID", WD.NAME AS "DOCUMENTNAME", WD.cuid AS "DOCUMENTCUID", 'WEBI' AS "KIND" FROM unv2_classes uc INNER JOIN unv2_universes unvs ON unvs.id = uc.unv_universes_id INNER JOIN unv2_objects uo ON uo.unv_universes_id = uc.unv_universes_id AND uc.id = uo.unv_classes_id INNER JOIN webi1400_resultobjects wro ON wro.foldername = uc.classname AND wro.NAME = uo.objectname INNER JOIN webi1400_dataproviders WDP ON WRO.webidataprovider_id = WDP.id INNER JOIN webi1400_documents WD ON WD.id = WDP.webi1400document_id INNER JOIN documents D ON D.cuid = WD.cuid AND D.cmsid = WD.cmsid INNER JOIN folders ON ( folders.cmsid = D.cmsid AND folders.cuid = D.parentfoldercuid ) WHERE unvs.cuid = wdp.universecuid AND unvs.cmsid = wd.cmsid and unvs.isdeleted = 0 and d.isdeleted = 0 and folders.isdeleted = 0 UNION ALL SELECT webi1200_documents.cmsid AS "CMSID", unv2_universes.universeid AS "UNIVERSEID", unv2_objects.objectid AS "OBJECTID", unv2_objects.unv_classes_id AS "CLASSID", folders.folderpathcomplete AS "FOLDERPATHCOMPLETE", unv2_universes.universename AS "UNIVERSENAME", cast(UNV2_OBJECTS.CLASSNAME as nvarchar2(200)) AS "CLASSNAME", unv2_objects.objectname AS "OBJECTNAME", cast(UNV2_OBJECTS.LINEAGE as nvarchar2(200)) AS "LINEAGE", cast(WEBI1200_DATAPROVIDERS.DATAPROVIDERNAME as nvarchar2(200)) AS "DATAPROVIDERNAME", webi1200_dataproviders.id AS "DATAPROVIDERID", webi1200_documents.objectid AS "SI_ID", webi1200_documents.NAME AS "DOCUMENTNAME", webi1200_documents.cuid AS "DOCUMENTCUID", 'WEBI' AS "KIND" FROM unv2_objects INNER JOIN webi1200_docobjects ON ( webi1200_docobjects.objectname = unv2_objects.objectname AND webi1200_docobjects.classlineage = unv2_objects.lineage ) INNER JOIN webi1200_dpqueries ON ( webi1200_docobjects.webi_dataproviderqueries_id = webi1200_dpqueries.id ) INNER JOIN webi1200_dataproviders ON ( webi1200_dataproviders.id = webi1200_dpqueries.webi_dataproviders_id ) INNER JOIN unv2_universes ON ( unv2_universes.cuid = webi1200_dataproviders.universecuid ) INNER JOIN webi1200_documents ON ( webi1200_documents.id = webi1200_dataproviders.webi1200_document_id ) INNER JOIN documents D ON D.cuid = webi1200_documents.cuid AND D.cmsid = webi1200_documents.cmsid INNER JOIN folders ON ( folders.cmsid = D.cmsid AND folders.cuid = D.parentfoldercuid ) WHERE unv2_objects.unv_universes_id = unv2_universes.id AND unv2_universes.cmsid = webi1200_documents.cmsid and unv2_universes.isdeleted = 0 and folders.isdeleted = 0
UNION ALL
SELECT DESKI_DOCUMENTS.CMSID as "CMSID", UNV2_UNIVERSES.UNIVERSEID as "UNIVERSEID", UNV2_OBJECTS.OBJECTID as "OBJECTID", UNV2_OBJECTS.UNV_CLASSES_ID as "CLASSID", FOLDERS.FOLDERPATHCOMPLETE as "FOLDERPATHCOMPLETE", UNV2_UNIVERSES.UNIVERSENAME as "UNIVERSENAME", cast(UNV2_OBJECTS.CLASSNAME as nvarchar2(200)) AS "CLASSNAME", UNV2_OBJECTS.OBJECTNAME as "OBJECTNAME", UNV2_OBJECTS.LINEAGE as "LINEAGE", DESKI_DATAPROVIDERS.DATAPROVIDERNAME as "DATAPROVIDERNAME", DESKI_DATAPROVIDERS.ID as "DATAPROVIDERID", DESKI_DOCUMENTS.DOCUMENTID as "SI_ID", DESKI_DOCUMENTS.DOCUMENTNAME as "DOCUMENTNAME", DESKI_DOCUMENTS.DESKICUID as "DOCUMENTCUID", 'DESKI' as "KIND"
FROM UNV2_OBJECTS INNER JOIN DESKI_QUERY_OBJECTS ON (DESKI_QUERY_OBJECTS.OBJECTNAME=UNV2_OBJECTS.OBJECTNAME and DESKI_QUERY_OBJECTS.CLASSNAME = UNV2_OBJECTS.CLASSNAME) INNER JOIN DESKI_QUERIES ON (DESKI_QUERY_OBJECTS.DESKI_QUERIES_ID=DESKI_QUERIES.ID) INNER JOIN DESKI_DATAPROVIDERS ON (DESKI_DATAPROVIDERS.ID=DESKI_QUERIES.DESKI_DATAPROVIDERS_ID) INNER JOIN UNV2_UNIVERSES ON (UNV2_UNIVERSES.ISLATESTSNAPSHOT = 1 AND UNV2_UNIVERSES.CUID=DESKI_DATAPROVIDERS.UNIVERSECUID ) INNER JOIN DESKI_DOCUMENTS ON (DESKI_DOCUMENTS.ID=DESKI_DATAPROVIDERS.DESKI_DOCUMENTS_ID) INNER JOIN DOCUMENTS D on D.CUID = DESKI_DOCUMENTS.DESKICUID and D.CMSID = DESKI_DOCUMENTS.CMSID INNER JOIN FOLDERS ON ( FOLDERS.CMSID = D.CMSID AND FOLDERS.CUID = D.PARENTFOLDERCUID ) where UNV2_OBJECTS.UNV_UNIVERSES_ID = UNV2_UNIVERSES.ID AND UNV2_UNIVERSES.CMSID = DESKI_DOCUMENTS.CMSID and UNV2_UNIVERSES.isdeleted = 0 and d.isdeleted = 0 and folders.isdeleted = 0
UNION ALL
SELECT unvs.CMSID ,unvs.OBJECTID as "UNIVERSEID" ,uo.ID as "OBJECTID" ,uc.ID AS "CLASSID" ,FOLDERS.FOLDERPATHCOMPLETE ,unvs.name AS "UNIVERSENAME" ,cast(uc.classname as nvarchar2(200)) AS "CLASSNAME" ,cast(uo.objectname as nvarchar2(200)) AS "OBJECTNAME" ,cast(uc.classlineage as nvarchar2(200)) AS "LINEAGE" ,cast(wdp.name as nvarchar2(200)) AS "DATAPROVIDERNAME" ,WDP.ID AS "DATAPROVIDERID" ,WD.OBJECTID AS "SI_ID" ,WD.NAME AS "DOCUMENTNAME" ,WD.CUID AS "DOCUMENTCUID" ,'WEBI' AS "KIND" FROM unx_classes uc INNER JOIN unx_universes unvs ON unvs.id = uc.UNX_ID INNER JOIN unx_objects uo ON uo.unx_id = uc.unx_id AND uc.id = uo.unx_class_id INNER JOIN webi1400_resultobjects wro ON wro.identifier = uo.identifier INNER JOIN WEBI1400_DATAPROVIDERS WDP ON WRO.WEBIDATAPROVIDER_ID = WDP.ID INNER JOIN WEBI1400_DOCUMENTS WD ON WD.ID = WDP.WEBI1400DOCUMENT_ID INNER JOIN DOCUMENTS D ON D.CUID = WD.CUID AND D.CMSID = WD.CMSID INNER JOIN FOLDERS ON ( FOLDERS.CMSID = D.CMSID AND FOLDERS.CUID = D.PARENTFOLDERCUID ) WHERE unvs.cuid = wdp.universecuid AND unvs.cmsid = wd.cmsid and unvs.isdeleted = 0 and d.isdeleted = 0 and folders.isdeleted = 0
) RESULT_TABLE LEFT OUTER JOIN ALL_AUDITS A3 ON A3.OBJECT_CUID = RESULT_TABLE.DOCUMENTCUID AND A3.CMSID = RESULT_TABLE.CMSID
where
universename = 'MetaMiner
|