Skip to Content
avatar image
Former Member

External strategy for tables

Hello, is there anybody who has an experience with external strategy for Tables? Especially someone who uses synonyms in database.

Any suggestions would be appreciated any tutorial or pdf will  be great help

thanks & regard's

linda watson

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 04, 2013 at 08:44 AM

     

    Hi Linda,

     

    I would like to take this opportunity to wish you a happy new year 2013 filled with prosperity, joy, and contentment.

    External strategy is only supported in BI 4.0 Universe Design tool. Please find the help guide below-
    http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_universe_design_tool_en.pdf -Page 417

    This feature is not supported in BI 4.0 Information Design tool.

    Please find the documents that details 'external strategy' in depth:

    http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_designer_en.pdf Page 543

    

    In other hand, please check this KB as example :

    https://service.sap.com/sap/support/notes/1202630 

    I hope that this can help.

    Best Regards

    Ines

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 04, 2013 at 09:44 AM

    Adding to what Ines has advised.

    You can add the following to the oracle.stg file to display the public synonym

    <Strategy Name="Allow_Public_Synonym">

    <Type>STRUCT</Type>

    <SQL>

    SELECT ' ', '|', ATC.OWNER, '|', ATC.TABLE_NAME,'|', ATC.COLUMN_NAME,'|', DECODE(SUBSTR(ATC.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', ATC.NULLABLE,'|'

    FROM

        ALL_TAB_COLUMNS ATC

    WHERE

        ATC.OWNER NOT IN ('SYS','SYSTEM','ORDSYS', 'MDSYS', 'OLAPSYS', 'CTXSYS', 'WKSYS', 'WMSYS', 'XDB')

    UNION

    SELECT

        DISTINCT ' ','|', ASYN.OWNER AS "OWNER", '|', ASYN.SYNONYM_NAME, '|', ATC.COLUMN_NAME, '|', DECODE(SUBSTR(ATC.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', ATC.NULLABLE,'|'

    FROM

        SYS.ALL_SYNONYMS ASYN,

        SYS.ALL_TAB_COLUMNS ATC

    WHERE

        ASYN.TABLE_NAME=ATC.TABLE_NAME

    AND

        ASYN.TABLE_OWNER NOT IN ('SYS','SYSTEM','ORDSYS', 'MDSYS', 'OLAPSYS', 'CTXSYS', 'WKSYS', 'WMSYS', 'XDB')

    AND

       ASYN.TABLE_OWNER=ATC.OWNER

    AND

        ASYN.OWNER NOT IN ('PUBLIC')

    UNION

    SELECT

        DISTINCT ' ','|', DECODE(ASYN.OWNER,'PUBLIC',USER,ASYN.OWNER) AS "OWNER", '|', ASYN.SYNONYM_NAME, '|', ATC.COLUMN_NAME, '|', DECODE(SUBSTR(ATC.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', ATC.NULLABLE,'|'

    FROM

        SYS.ALL_SYNONYMS ASYN,

        SYS.ALL_TAB_COLUMNS ATC,

        DUAL

    WHERE

        ASYN.TABLE_NAME=ATC.TABLE_NAME

    AND

        ASYN.TABLE_OWNER NOT IN ('SYS','SYSTEM','ORDSYS', 'MDSYS', 'OLAPSYS', 'CTXSYS', 'WKSYS', 'WMSYS', 'XDB')

    AND

        ASYN.OWNER IN ('PUBLIC')

    AND

        ASYN.TABLE_OWNER=ATC.OWNER

    AND

        ASYN.SYNONYM_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM USER_TAB_COLUMNS)

    ORDER BY 3,5

    </SQL>

    </Strategy>

    Thanks

    -Nanda

    Add comment
    10|10000 characters needed characters exceeded