Business Intelligence (BusinessObjects)
Page tree
Skip to end of metadata
Go to start of metadata

Connections, universes and relationships between them

Document1

This document is composed by 5 reports:

  1.  Connections_List - extracts all connections grouped by folder path and filterable by input control 
  2.  UNV_List  - extracts all .unv grouped by folder path and filterable by input control
  3.  UNX_List  - extracts all .unx grouped by folder path and filterable by input control
  4.  UNV_Connection_List - extracts only UNV list with related connections (with connections folder path and universes folder path, both filterable by input controls; there is also a variable "Universe_without_conn" that verifies if there are universes without connection (anomaly)
  5.  UNX_Connection_List - extracts only UNX list with related connections (with connections folder path and universes folder path, both filterable by input controls; there is also a variable "Universe_without_conn" that verifies if there are universes without connection (anomaly)

 

Below screenshots of every query (five in total) used to extract data:

Query1 (Connections_All):

SELECT
Properties.si_name,
Properties.si_kind,
datetime(Properties.si_creation_time),
datetime(Properties.si_update_ts),
int(Properties.si_id),
relationship(Properties.si_parent_folder),
Level1.si_name,
folderPath(Level1.si_path)
FROM
Properties,
Properties Level1
WHERE
( Properties.join=Level1.join )
AND
Properties.si_kind = 'CCIS.DataConnection'

 

Query2 (UNV_List):

SELECT
int(Properties.si_id),
datetime(Properties.si_creation_time),
datetime(Properties.si_update_ts),
Properties.si_name,
Properties.si_specific_kind,
relationship(Properties.si_parent_folder),
Level1.si_name,
folderPath(Level1.si_path)
FROM
Properties,
Properties Level1
WHERE
( Properties.join=Level1.join )
AND
Properties.si_specific_kind = 'universe'

 

Query3 (UNX_List):

SELECT
int(Properties.si_id),
datetime(Properties.si_creation_time),
datetime(Properties.si_update_ts),
Properties.si_name,
Properties.si_specific_kind,
relationship(Properties.si_parent_folder),
Level1.si_name,
folderPath(Level1.si_path)
FROM
Properties,
Properties Level1
WHERE
( Properties.join=Level1.join )
AND
Properties.si_specific_kind = 'dsl.universe'

 

Query4 (Unv_to_cnx):

SELECT
int(Properties.si_id),
relationships(Properties.si_dataconnection)
FROM
Properties
WHERE
int(Properties.si_id) IN (<Id from query UNV_List>)

 

Query5 (Unx_to_cnx) :

SELECT
int(Properties.si_id),
relationships(Properties.SI_SL_UNIVERSE_TO_CONNECTIONS)
FROM
Properties
WHERE
int(Properties.si_id) IN (<Id from query UNX_List>)

 

I use the last query "Environment" in each webi document to obtain the name of the CMS environment I'm exploring :

SELECT
Properties.si_name
FROM
Properties
WHERE
Properties.si_kind = 'EnterpriseNode'

 

Document biar: Biar_20171127.zip

Guide to construct the document: Document_to_explore_conn_unv_unx.docx

  • No labels