Skip to Content
0

Get all connections used by the FHSQL report

Dec 13, 2017 at 01:24 AM

41

avatar image

Hi Experts,

I'm back because I need some help from you...

I'm currently performing an inventory of our current BO system.

I could generate a report of all reports and which universes they are related.

However, I know that there are some reports which are developed using FHSQL (Free Hand SQL) instead of UNIVERSE.

I can easily get the report name by the SQL below:

SELECT * FROM CI_INFOOBJECTS WHERE SI_UNIVERSE.SI_TOTAL = 0

However, I need to know what connections each FHSQL report use.

One way to check it, would be open each report manually and then check what connections they are using.
But as there are lots of reports using FHSQL.

Do you have any idea of how to know what connections each FHSQL report use?

Any help is appreciated!

Thanks,
Marcos

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Bharath B N
Dec 13, 2017 at 03:35 PM
1

Using REST SDK you will get it.

Please try this using any REST client (Browser plugins like: Postman, RestClient...).

Please refer this blog for taking session token:

https://blogs.sap.com/2017/04/21/session-management-in-bi-platform-rest-sdk-rws/

Get data providers of a WebI document

This URL returns data provider list of a given WebI document specified in the URL parameter.

URL:

[GET] <url>/documents/{documentId}/dataproviders

Response type:

application/xml or application/json

Parameters:

documentId: a valid identifier of a WebI document

Example (XML format):

[GET] <url>/documents/7007/dataproviders

Response

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataproviders>
    ...
    <dataprovider>
        <id>DP5</id> 
        <name>FreeHandSQL based on beachOraJdbc</name> 
        <dataSourceId>6250</dataSourceId> 
        <dataSourceType>fhsql</dataSourceType> 
        <updated>2014-08-29T08:32:37.000+02:00</updated>
    </dataprovider>
</dataproviders>

Get details of a data provider of a WebI document

This URL returns the detail of a data provider of a given WebI document specified in the URL parameter.

URL:

[GET] <url>/documents/{documentId}/dataproviders/{dataproviderId}

Response type:

application/xml or application/json

Parameters:

documentId: a valid identifier of a WebI document
dataproviderId: a valid identifier of a data provider

Example (XML format):

[GET] <url>/documents/7007/dataproviders/DP5

<?xml version="1.0" encoding="UTF-8"?>
<dataprovider>
    <id>DP5</id> 
    <name>FreeHandSQL based on beachOraJdbc</name> 
    <dataSourceId>6250</dataSourceId> 
    <dataSourceType>fhsql</dataSourceType> 
    <updated>2014-08-29T08:43:26.000+02:00</updated> 
    <duration>1</duration> 
    <isPartial>false</isPartial> 
    <rowCount>7</rowCount> 
    <flowCount>1</flowCount> 
    <dictionary>
        <expression dataType="Numeric" qualification="Measure" highPrecision="false">>
            <id>DP5.DO0</id> 
            <name>COUNTRY_ID</name> 
            <dataSourceObjectId>DS5.DO0</dataSourceObjectId> 
            <formulaLanguageId>[COUNTRY_ID]</formulaLanguageId> 
            <aggregationFunction>None</aggregationFunction>
        </expression> 
        <expression dataType="String" qualification="Dimension">
            <id>DP5.DO1</id> 
            <name>COUNTRY</name> 
            <dataSourceObjectId>DS5.DO1</dataSourceObjectId> 
            <formulaLanguageId>[COUNTRY]</formulaLanguageId>
        </expression>
    </dictionary> 
    <properties>
        <property key="sql">SELECT * from country</property> 
        <property key="maxRows">-1</property> 
        <property key="timeout">-1</property>
    </properties>
</dataprovider>

If this is fulfilling your requirement, then I can help you to run same thing from some script to make it for multiple webi Documents.

More Info : https://help.sap.com/viewer/58f583a7643e48cf944cf554eb961f5b/4.2.4/en-US/45f881a36e041014910aba7db0e91070.html

Share
10 |10000 characters needed characters left characters exceeded
Bharath B N
Dec 13, 2017 at 03:28 PM
0

You have to write Java code:

1. List the reports which are using FHSQL (as you mentioned in question)

2. Call the REST API for each report to get the list of data providers (with connection IDs) (This API is available for WEBI reports)

-

Bharath

Share
10 |10000 characters needed characters left characters exceeded
Jawahar Konduru Dec 13, 2017 at 04:38 PM
0

I don't think you can get this information using Query Builder. You can use SDK to extract the connection information.

Share
10 |10000 characters needed characters left characters exceeded