Skip to Content

Get all connections used by the FHSQL report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Dec 13, 2017 at 03:35 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 13, 2017 at 03:28 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 13, 2017 at 04:38 PM

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

    Add comment
    10|10000 characters needed characters exceeded