Informatica Business Intelligence reports
This page will show you all the Available reports for Informatica Business Intelligence reports, once you select a report name, it will open the report query page. copy and paste it to your PC and run it from, toad or any other DB tools you have.
Available reports
Lists all attributes and metrics by repository, schema, and dimension to which each attribute or metric belongs.
select “DIMENSION_NAME”,
“OBJECT_NAME”,
“LX_LOCATION”,
“REPOSITORY_NAME”,
“OBJECT_TYPE”,
“OBJECT_DESCRIPTION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as OBJECT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME as DIMENSION_NAME, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME as OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as OBJECT_DESCRIPTION , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Dimension’, ‘Folder,Dimension’) AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE IN (‘Attribute’, ‘Measure’) AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “OBJECT_TYPE”, “DIMENSION_NAME”, “OBJECT_NAME”
Lists the hierarchies in a schema.
select “REPOSITORY_NAME”,
“LX_LOCATION”,
“HIERARCHY_NAME”,
“HIERARCHY_DESCRIPTION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME as REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME as HIERARCHY_NAME , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) as HIERARCHY_DESCRIPTION , ODS_IMA_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_ELEMENT.CLASS_TYPE = ‘Hierarchy’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ AND ODS_IMA_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ )) “query”
order by “REPOSITORY_NAME”, “HIERARCHY_NAME”, “HIERARCHY_DESCRIPTION”, “LX_LOCATION”
Lists attributes and metrics contained in the report documents.
select “EXPRESSION”,
“OBJECT_TYPE”,
“OBJECT_DESCRIPTION”,
“OBJECT_NAME”,
“LX_LOCATION”,
“REPORT_DOCUMENT_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS OBJECT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME AS REPORT_DOCUMENT_NAME, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as OBJECT_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN (‘definition’, ‘Select’) AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as EXPRESSION , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_ELEMENT.CLASS_TYPE = ‘Folder’ AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘ReportAttribute’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “OBJECT_TYPE”, “REPORT_DOCUMENT_NAME”, “OBJECT_NAME”, “OBJECT_DESCRIPTION”
Lists reports by repository and folder to which it belongs.
select “REPORT_FOLDER_NAME”,
“REPORT_NAME”,
“REPOSITORY_NAME”,
“REPORT_DESCRIPTION”,
“LX_LOCATION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME AS REPORT_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS REPORT_FOLDER_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS Report_DESCRIPTION , ODS_IMA_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE = ‘Folder’ AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.CLASS_TYPE = ‘Report’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “REPORT_NAME”, “REPORT_FOLDER_NAME”, “REPORT_DESCRIPTION”
Lists attributes and metrics by repository, schema, and dimension to which each attribute or metric belongs.
select “L2METRIC”,
“OBJECT_TYPE”,
“LX_LOCATION”,
“FOLDER_NAME”,
“OBJECT_DESCRIPTION”,
“REPOSITORY_NAME”,
“OBJECT_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME AS FOLDER_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS OBJECT_TYPE , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS OBJECT_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN (‘definition’, ‘Select’) AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2METRIC , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR, IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Folder’, ‘Folder,Dimension’, ‘Dimension’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE IN (‘Attribute’, ‘Measure’) AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “FOLDER_NAME”, “OBJECT_TYPE”, “OBJECT_NAME”, “OBJECT_DESCRIPTION”, “L2METRIC”