Can we get Universe Connection details like DB Host Name, DB Type using Query Builder or Restful API

[Former Member]

Hi, I am trying to Get Universe Connection Details like Service Name, User Name , Type from Query Builder but I am unable to get from Query Builder, Can you please let me know any suggestions to get this information from Query Builder or Restful API

Thanks

[Former Member]

HI Dan, Thanks for your Information, We are able to Set up SL Java SDK and trying to get the Connection Information (Data source Name, User Name) using Below Query

SlContext context=SlContext.create();

IEnterpriseSession enterpriseSession = CrystalEnterprise.getSessionMgr().logon("USERID", "PW", "SERVER NAME", "AUT MODE"); context.getService(CmsSessionService.class).setSession(enterpriseSession);

1st

CmsResourceService cmsService=context.getService(CmsResourceService.class);

List<String> connections=cmsService.getUniverseConnections("/Universes/FOLDER NAME/UNIVERSE NAME.unx" ); System.out.println(connections);

2nd

DatabaseConnection connx=cmsService.loadConnection("/Connections/FOLDER NAME/CONNECTION NAME"); System.out.println(connx.getParameter ("DATASOURCE").getValue());

context.close();

enterpriseSession.logoff();

We are able to get Connection name using 1st part of query, while running 2nd part of query to get Data source Information we are getting error. any suggestions will be helpful.

Thanks in advance

Regards

Vedeeswar

[daniel_paulsen]

The Semantic Layer Java SDK will get you the information you are looking for. You will not be able to get that level of detail using QueryBulder or the SL REST SDK.
https://help.sap.com/viewer/4359a0ef221e4a1098bae432bdd982c1/4.2.6/en-US/ec5218116fdb101497906a7cb0e...

Dan

[DellSC]

You can also get the information from QueryBuilder - it will take two queries for each universe, though: One to get the connection info and the other to get each universe that uses the connection. I'm not entirely sure that the connection info is available in the RESTful Web Service because that just provides basic info without the properties that you need to get the info you want.

In my Java code to get this information, I get the info about the connections first and then get the info about the universes that use the connection. That way I don't have to query for specific connections multiple times. Here's the query I use to get the connection info:

query://{Select * from CI_APPOBJECTS where SI_KIND in ('CCIS.DataConnection', 'CommonConnection'}"

CCIS.DataConnections are regular universe connections and CommonConnections are OLAP connections.

The connections have several properties that you're interested in:

1. "SI_CONNUNIVERSE" is a property bag that contains the SI_ID values of the universes that use the connection. Use the "SI_TOTAL" value of this property bag to determine how many universes you need to get info for.

2. "SI_CONNECTION_DATABASE" tells you what type of database you're connecting to.

3. "SI_CONNECTION_NETWORKLAYER" tells you what type of connection this is, e.g. ODBC, OLEDB, etc.

Unfortunately, the information about which server and database the connection links to is encrypted and I haven't found a way to unencrypt it.

-Dell

[ayman_salem]

With RESTful you only get a list of connections and some connection details

...

list of connections:

http://<server>:6405/biprws/raylight/v1/connections

{"connections": 
 {"connection": [ 
  {"@type": "Olap",
  "id": 8806,
  "cuid": "AbOVtAYfuAFDsprk3AuVAw8",
  "name": "ABAP_BW_NPL",
  "folderId": 584
  },
  {...},
  {...},
  {"@type": "Relational",
   "id": 5644,
   "cuid": "AQoPFEYGN_hJucYDziTXhiI",
   "name": "efashion-webi",
   "folderId": 584
  },
  {...},
  {...},
  {...}
 ],
 }
}

.......

connection details: (only DB and networklayer,.....)

http://<server>:6405/biprws/raylight/v1/connections/5644

{"connection": 
 {"@type": "Relational",
  "id": 5644,
  "cuid": "AQoPFEYGN_hJucYDziTXhiI",
  "name": "efashion-webi",
  "folderId": 584,
  "path": "Connections",
  "database": "MS Access 2007",
  "networkLayer": "ODBC"
 }
}

.......

......

For security reasons, the DB service name and username are not available in readable form

You can see it as encrypted form in "SI_CONNECTION_PARAMSTRING and SI_CONNECTION_CONNECTSTRING"

Further information is readable like: "Connection database" and the "Connection client parameter",....... SI_CONNECTION_DATABASE, SI_CONNECTION_CLIENTPARAMS

.....

.....

select * from CI_APPOBJECTS where SI_ID='5644'

.....

[former_member230921]

How to use SAP BI Platform Rest APIs Videos:

https://www.youtube.com/watch?v=d0FjJ4Jqjfs

https://www.youtube.com/watch?v=Y3d5lGsuE98

[Former Member]

Thanks for your help on this. we are able to set the things with the help of above information.

Now we are trying to get the information about Connection Metadata like DB Service Name and User Name , Can you please let me know any suggestions on this?

Thanks

Vedeeswar

[former_member230921]

http://<server>:<port>/biprws/v1/cmsquery (Query the BusinessObjects repository using REST SDK (RWS))

Query: Select * from CI_APPOBJECTS where SI_KIND in ('CCIS.DataConnection', 'CommonConnection')

https://answers.sap.com/answers/591742/view.html

[former_member230921]

REST API to get connection details :

http://<server>:<port>/biprws/raylight/v1/connections/<connection-id>;

https://answers.sap.com/answers/591738/view.html

[Former Member]

We are also having same kind of requirement.

[former_member230921]

Samples for BI REST APIs: https://wiki.scn.sap.com/wiki/display/BOBJ/RESTful+APIs

CMS Query using REST (same functionality as Query Builder) : Query the BusinessObjects repository using REST SDK (RWS)

[Former Member]

HI Bharath, Thanks for your time. This is first time we are accessing Rest API, Can you please let me know how can we set up the connection to Rest API?

We are trying to get the info from Query Builder using below query

SELECT * FROM CI_APPOBJECTS where SI_SPECIFIC_KIND = 'CCIS.DataConnection' and SI_Name = 'CONNECTION NAME' with this we are able to see connection name and last modified info..etc but we are not able to see

DB Service Name and User Name details Can you please let us know where can we find this details.

Thanks in Advance

Vedeeswar

[former_member230921]

If you are using 4.2 SP05+ then REST APIs available on both Tomcat and WACs server.

first you can use any REST client from browser like : Postman or REST Client ......

1. Create a token using /logon/long API (https://blogs.sap.com/2017/04/21/session-management-in-bi-platform-rest-sdk-rws/)

2. Then you have to pass the token created using logon/long API in headers for all the APIs.

[former_member230921]

Detail steps:

https://help.sap.com/viewer/5431204882b44fc98d56bd752e69f132/4.2.6/en-US/45f99c666e041014910aba7db0e...

[Former Member]

HI Bharath, we are not able to access the link above detail steps one. getting 403 access error.

Thanks

Vedeeswar

[former_member230921]

https://help.sap.com/viewer/db6a17c0d1214fd6971de66ea0122378/4.2.6/en-US/45f61c236e041014910aba7db0e...

https://help.sap.com/viewer/db6a17c0d1214fd6971de66ea0122378/4.2.6/en-US/45f58fe26e041014910aba7db0e...

[former_member230921]

Using REST API:

https://help.sap.com/viewer/5431204882b44fc98d56bd752e69f132/4.2.6/en-US/ec5584596fdb101497906a7cb0e...