This analysis answers the question:
"Which Web Intelligence reports utilize a given table based on Universe objects backed by a table?"
BI4 Version
SELECT ut.tablename, 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 inner join unv2_object_tables uot on uot.unv_object_id = uo.id inner join unv2_tables ut on ut.tableid = uot.tableid and uot.unv_id = unvs.id WHERE unvs.universecuid = wdp.universecuid AND unvs.cmsid = wd.cmsid and ut.unv_universes_id = unvs.id |
XI3 Version
SELECT ut.tablename, 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.dataprovidername 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 webi1200_docobjects wro ON wro.classname = uc.classname AND wro.objectname = uo.objectname INNER JOIN WEBI1200_DATAPROVIDERS WDP ON WRO.webi1200_dataprovider_ID = WDP.ID INNER JOIN WEBI1200_DOCUMENTS WD ON WD.ID = WDP.WEBI1200_DOCUMENT_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 inner join unv2_object_tables uot on uot.unv_object_id = uo.id inner join unv2_tables ut on ut.tableid = uot.tableid and uot.unv_id = unvs.id WHERE unvs.universecuid = wdp.universecuid AND unvs.cmsid = wd.cmsid and ut.unv_universes_id = unvs.id |